本文共 21786 字，大约阅读时间需要 72 分钟。
一、参数优化前压力测试
0、优化测试前提虚拟机vm12.5，OS centos 6.9（系统已优化），cpu2（I5 4288u 2.6GHZ）,MEM4GB ,HardDisk:Apple SSD(SM-0512F)1、模拟数据库数据
为了测试我们创建一个test1的库创建一个tb1的表，然后导入20万行数据，脚本如下：vim slap.sh#!/bin/bash HOSTNAME="localhost" PORT="3306" USERNAME="root" PASSWORD="123" DBNAME="oldboy" TABLENAME="lufei" #create database mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}" create_db_sql="create database if not exists ${DBNAME}" mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}" #create table create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1) not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100) default null)" mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}" #insert data to table i="1" while [ $i -le 200000 ] do insert_sql="insert into ${TABLENAME} values($i,''guojialei_$i,'1','110011198809163418','1988-09-16','2017-09-13','oldboyedu_$i')" mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}" let i++ done #select data select_sql="select count(*) from ${TABLENAME}" mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}" 执行脚本：
sh slap.sh2、检查数据可用性
mysql -uroot -p123select count(*) from test1.tb1;
3、在没有优化之前我们使用mysqlslap来进行压力测试
mysqlslap --defaults-file=/etc/my.cnf \ --concurrency=100 --iterations=1 --create-schema='test1' \--query='select * from test1.tb1' engine=innodb \--number-of-queries=2000 -uroot -p123 -verboseBenchmark Running for engine rbose Average number of seconds to run all queries: 31.463 seconds Minimum number of seconds to run all queries: 31.463 seconds Maximum number of seconds to run all queries: 31.463 seconds Number of clients running queries: 100 Average number of queries per client: 20
--------------------------------mysqlslap使用说明----------------------------
mysqlslap工具介绍 mysqlslap来自于mariadb包，测试的过程默认生成一个mysqlslap的schema,生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。常用选项：
--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的存储过程或者sql语句来执行测试--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database--commint=N 多少条DML后提交一次--compress, -C 如服务器和客户端都支持压缩，则压缩信息--concurrency=N, -c N 表示并发量，即模拟多少个客户端同时执行select；可指定多个值，以逗号或者--delimiter参数指定值做为分隔符--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次--only-print 只打印测试语句而不实际执行--detach=N 执行N条语句后断开重连--debug-info, -T 打印内存和CPU的相关信息
测试示例：
1）单线程测试
[root@centos7 ~]# mysqlslap -a -uroot -pEnter password: Benchmark Average number of seconds to run all queries: 0.004 seconds Minimum number of seconds to run all queries: 0.004 seconds Maximum number of seconds to run all queries: 0.004 seconds Number of clients running queries: 1 Average number of queries per client: 0
2）多线程测试，使用--concurrency来模拟并发连接
[root@centos7 ~]# mysqlslap -uroot -p -a -c 500Enter password: Benchmark Average number of seconds to run all queries: 3.384 seconds Minimum number of seconds to run all queries: 3.384 seconds Maximum number of seconds to run all queries: 3.384 seconds Number of clients running queries: 500 Average number of queries per client: 0
3）同时测试不同的存储引擎的性能进行对比
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-infoEnter password: Benchmark Running for engine myisam Average number of seconds to run all queries: 0.192 seconds Minimum number of seconds to run all queries: 0.187 seconds Maximum number of seconds to run all queries: 0.202 seconds Number of clients running queries: 500 Average number of queries per client: 2Benchmark Running for engine innodb Average number of seconds to run all queries: 0.355 seconds Minimum number of seconds to run all queries: 0.350 seconds Maximum number of seconds to run all queries: 0.364 seconds Number of clients running queries: 500 Average number of queries per client: 2User time 0.33, System time 0.58Maximum resident set size 22892, Integral resident set size 0Non-physical pagefaults 46012, Physical pagefaults 0, Swaps 0Blocks in 0 out 0, Messages in 0 out 0, Signals 0Voluntary context switches 31896, Involuntary context switches 0
4）执行一次测试，分别500和1000个并发，执行5000次总查询
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --number-of-queries 5000 --debug-infoEnter password: Benchmark Average number of seconds to run all queries: 3.378 seconds Minimum number of seconds to run all queries: 3.378 seconds Maximum number of seconds to run all queries: 3.378 seconds Number of clients running queries: 500 Average number of queries per client: 10Benchmark Average number of seconds to run all queries: 3.101 seconds Minimum number of seconds to run all queries: 3.101 seconds Maximum number of seconds to run all queries: 3.101 seconds Number of clients running queries: 1000 Average number of queries per client: 5User time 0.84, System time 0.64Maximum resident set size 83068, Integral resident set size 0Non-physical pagefaults 139977, Physical pagefaults 0, Swaps 0Blocks in 0 out 0, Messages in 0 out 0, Signals 0Voluntary context switches 31524, Involuntary context switches 3
5）迭代测试
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-infoEnter password: Benchmark Average number of seconds to run all queries: 3.307 seconds Minimum number of seconds to run all queries: 3.184 seconds Maximum number of seconds to run all queries: 3.421 seconds Number of clients running queries: 500 Average number of queries per client: 10User time 2.18, System time 1.58Maximum resident set size 74872, Integral resident set size 0Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0Blocks in 0 out 0, Messages in 0 out 0, Signals 0Voluntary context switches 73904, Involuntary context switches 3
二、优化细节
1、参数优化
1.1 Max_connections（1）简介Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。（2）判断依据show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+show status like 'Max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 101 | +----------------------+-------+
如果max_used_connections跟max_connections相同,那么就是max_connections设置过低或者超过服务器的负载上限了，低于10%则设置过大.
（3）修改方式举例vim /etc/my.cnf Max_connections=1024
1.2 back_log
（1）简介mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它。
（2）判断依据
show full processlist发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值（3）修改方式举例
vim /etc/my.cnf back_log=1024
1.3 wait_timeout和interactive_timeout
（1）简介
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
（2）设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低（3）修改方式举例
wait_timeout=1200interactive_timeout=1200
1.4 key_buffer_size
（1）简介key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度（2）设置依据
通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。mysql> show variables like "key_buffer_size%";+-----------------+---------+| Variable_name | Value |+-----------------+---------+| key_buffer_size | 8388608 |+-----------------+---------+1 row in set (0.00 sec)mysql> mysql> show status like "key_read%";+-------------------+-------+| Variable_name | Value |+-------------------+-------+| Key_read_requests | 10 || Key_reads | 2 |+-------------------+-------+2 rows in set (0.00 sec)mysql>
一共有10个索引读取请求，有2个请求在内存中没有找到直接从硬盘中读取索引
注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。
可以使用检查状态值created_tmp_disk_tables得知：mysql> show status like "created_tmp%";+-------------------------+-------+| Variable_name | Value |+-------------------------+-------+| Created_tmp_disk_tables | 0 || Created_tmp_files | 6 || Created_tmp_tables | 1 |+-------------------------+-------+3 rows in set (0.00 sec)
通常地，我们习惯以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。
看以下例子：在调用mysqldump备份数据时，大概执行步骤如下：180322 17:39:33 7 Connect root@localhost on7 Query /*!40100 SET @@SQL_MODE='' */7 Init DB guo7 Query SHOW TABLES LIKE 'guo'7 Query LOCK TABLES `guo` READ /*!32311 LOCAL */7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=17 Query show create table `guo`7 Query show fields from `guo`7 Query show table status like 'guo'7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`7 Query UNLOCK TABLES7 Quit
其中，有一步是：show fields from guo。从slow query记录的执行计划中，可以知道它也产生了 Tmp_table_on_disk。
所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。
（3）配置方法
key_buffer_size=64M 1.5 query_cache_size
（1）简介：查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。注：两个sql语句，只要想差哪怕是一个字符（列如大小写不一样；多一个空格等）,那么这两个sql将使用不同的一个cache。（2）判断依据mysql> show status like "%Qcache%";+-------------------------+---------+| Variable_name | Value |+-------------------------+---------+| Qcache_free_blocks | 1 || Qcache_free_memory | 1031360 || Qcache_hits | 0 || Qcache_inserts | 0 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 2002 || Qcache_queries_in_cache | 0 || Qcache_total_blocks | 1 |+-------------------------+---------+8 rows in set (0.00 sec)
---------------------状态说明--------------------
Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。注：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocksQcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。Qcache_lowmem_prunes：多少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；Qcache_total_blocks：当前Query Cache 中的block 数量；。
（3）配置示例
mysql> show variables like '%query_cache%' ;+------------------------------+---------+| Variable_name | Value |+------------------------------+---------+| have_query_cache | YES || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 1048576 || query_cache_type | OFF || query_cache_wlock_invalidate | OFF |+------------------------------+---------+6 rows in set (0.00 sec)mysql>
-------------------配置说明-------------------------------
以上信息可以看出query_cache_type为off表示不缓存任何查询各字段的解释：query_cache_limit：超过此大小的查询将不缓存query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
修改/etc/my.cnf,配置完后的部分文件如下：
query_cache_size=256Mquery_cache_type=1
1.6 max_connect_errors
max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
max_connect_errors=20001.7 sort_buffer_size
（1）简介：每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或GROUP BY操作，（2）配置依据Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存（3）配置方法修改/etc/my.cnf文件，在[mysqld]下面添加如下：sort_buffer_size=1M1.8 max_allowed_packet
（1）简介：mysql根据配置文件会限制，server接受的数据包大小。（2）配置依据：有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数（3）配置方法：max_allowed_packet=32M 1.9 join_buffer_size
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
1.10 thread_cache_size
(1)简介服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.（2）配置依据通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)试图连接到MySQL(不管是否连接成功)的连接数
mysql> show status like 'threads_%';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| Threads_cached | 8 || Threads_connected | 2 || Threads_created | 4783 || Threads_running | 1 |+-------------------+-------+4 rows in set (0.00 sec)
Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
(3)配置方法：
thread_cache_size=32 1.11 innodb_buffer_pool_size
（1）简介对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。（2）配置依据：InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'+---------------------------------------+-------------+| Variable_name | Value |+---------------------------------------+-------------+| Innodb_buffer_pool_dump_status | not started || Innodb_buffer_pool_load_status | not started || Innodb_buffer_pool_pages_data | 1557 || Innodb_buffer_pool_bytes_data | 25509888 || Innodb_buffer_pool_pages_dirty | 0 || Innodb_buffer_pool_bytes_dirty | 0 || Innodb_buffer_pool_pages_flushed | 2305 || Innodb_buffer_pool_pages_free | 63977 || Innodb_buffer_pool_pages_misc | 2 || Innodb_buffer_pool_pages_total | 65536 || Innodb_buffer_pool_read_ahead_rnd | 0 || Innodb_buffer_pool_read_ahead | 64 || Innodb_buffer_pool_read_ahead_evicted | 0 || Innodb_buffer_pool_read_requests | 32036288 || Innodb_buffer_pool_reads | 600 || Innodb_buffer_pool_wait_free | 0 || Innodb_buffer_pool_write_requests | 280891 |+---------------------------------------+-------------+17 rows in set (0.00 sec)mysql>Innodb_buffer_pool_pages_dataThe number of pages in the InnoDB buffer pool containing data. The number includes both dirty andclean pages.Innodb_buffer_pool_pages_totalThe total size of the InnoDB buffer pool, in pages.Innodb_page_sizeInnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to beeasily converted to bytes
（3）配置方法
innodb_buffer_pool_size=1024M 1.12 innodb_flush_log_at_trx_commit
（1）简介主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
（2）配置依据
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。（3）配置方法innodb_flush_log_at_trx_commit=1 1.13 innodb_thread_concurrency
（1）简介此参数用来设置innodb线程的并发数量，默认值为0表示不限制。（2）配置依据在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能。如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），如果你的目标是将MySQL与其他应用隔离，你可以考虑绑定mysqld进程到专有的虚拟CPU。但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。
（3）配置方法：
innodb_thread_concurrency=8 1.14 innodb_log_buffer_size
此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
innodb_log_buffer_size=8M1.15. innodb_log_file_size = 50M
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.1.16. innodb_log_files_in_group = 3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为31.17.read_buffer_size = 1M
MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享18.read_rnd_buffer_size = 1M
MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。19.bulk_insert_buffer_size = 8M
批量插入数据缓存大小，可以有效提高插入效率，默认为8M1.20.binary log
log-bin=/data/mysql-binbinlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4Mmax_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小max_binlog_size= 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。expire_logs_days = 7 //定义了mysql清除过期日志的时间。二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。
innodb_max_dirty_pages_pct
innodb_additional_mem_pool_size (于2G内存的机器，推荐值是20M。32G内存的?100M)transaction_isolation1.21 安全参数
Innodb_flush_method=(O_DIRECT, fdatasync)
1、fdatasync
（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘2、 Innodb_flush_method=O_DIRECT
（1）在数据页需要持久化时，直接写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘最高安全模式： innodb_flush_log_at_trx_commit=1 innodb_flush_method=O_DIRECT最高性能模式： innodb_flush_log_at_trx_commit=0 innodb_flush_method=fdatasync
一般情况下，我们更偏向于安全。
“双一标准” innodb_flush_log_at_trx_commit=1 sync_binlog=1 innodb_flush_method=O_DIRECT
三、参数优化结果
[mysqld]basedir=/application/mysqldatadir=/application/mysql/datasocket=/tmp/mysql.socklog-error=/var/log/mysql.loglog_bin=/data/binlog/mysql-binbinlog_format=rowskip-name-resolveserver-id=52gtid-mode=onenforce-gtid-consistency=truelog-slave-updates=1relay_log_purge=0max_connections=1024back_log=128wait_timeout=60interactive_timeout=7200key_buffer_size=16Mquery_cache_size=64Mquery_cache_type=1query_cache_limit=50Mmax_connect_errors=20sort_buffer_size=2Mmax_allowed_packet=32Mjoin_buffer_size=2Mthread_cache_size=200innodb_buffer_pool_size=1024Minnodb_flush_log_at_trx_commit=1innodb_log_buffer_size=32Minnodb_log_file_size=128Minnodb_log_files_in_group=3binlog_cache_size=2Mmax_binlog_cache_size=8Mmax_binlog_size=512Mexpire_logs_days=7read_buffer_size=2Mread_rnd_buffer_size=2Mbulk_insert_buffer_size=8M[client]socket=/tmp/mysql.sock
再次压力测试 ：
[root@db02 ~]# mysqlslap --defaults-file=/etc/my.cnf \> --concurrency=100 --iterations=1 --create-schema='test1' \> --query='select * from test1.tb1' engine=innodb \> --number-of-queries=2000 -uroot -p123 -verboseWarning: Using a password on the command line interface can be insecure.Benchmark Running for engine rbose Average number of seconds to run all queries: 7.271 seconds Minimum number of seconds to run all queries: 7.271 seconds Maximum number of seconds to run all queries: 7.271 seconds Number of clients running queries: 100 Average number of queries per client: 20
对比之前：
mysqlslap --defaults-file=/etc/my.cnf \ --concurrency=100 --iterations=1 --create-schema='test1' \--query='select * from test1.tb1' engine=innodb \--number-of-queries=2000 -uroot -p123 -verboseBenchmark Running for engine rbose Average number of seconds to run all queries: 31.463 seconds Minimum number of seconds to run all queries: 31.463 seconds Maximum number of seconds to run all queries: 31.463 seconds Number of clients running queries: 100 Average number of queries per client: 20
转载于:https://blog.51cto.com/12083623/2359679