MySQL5.6基本优化配置

马丰

2023-03-14

本文向大家介绍MySQL5.6基本优化配置，包括了MySQL5.6基本优化配置的使用技巧和注意事项，需要的朋友参考一下

下面开始优化下my.conf文件(这里的优化只是在mysql本身的优化，之前安装的时候也要有优化)

cat /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
##################################################
#innodb
user=mysql
innodb_buffer_pool_size=6G
innodb_log_file_size=4G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_file_io_threads=4
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
innodb_thread_concurrency = 0
innodb_additional_mem_pool_size=16M
innodb_autoinc_lock_mode = 2
##################################################
# Binary log/replication
log-bin
sync_binlog=1
sync_relay_log=1
relay-log-info-repository=TABLE
master-info-repository=TABLE
expire_logs_days=7
binlog_format=ROW
transaction-isolation=READ-COMMITTED
#################################################
#cache
tmp_table_size=512M
character-set-server=utf8
collation-server=utf8_general_ci
skip-external-locking
back_log=1024
key_buffer_size=1024M
thread_stack=256k
read_buffer_size=8M
thread_cache_size=64
query_cache_size=128M
max_heap_table_size=256M
query_cache_type=1
binlog_cache_size = 2M
table_open_cache=128
thread_cache=1024
thread_concurrency=8
wait_timeout=30
join_buffer_size = 1024M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
#################################################
#connect
max-connect-errors=100000
max-connections=1000
#################################################
explicit_defaults_for_timestamp=true
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
##################################################
# Binary log/replication(这里主要是复制功能，也就是主从,提前配置好，后面讲主从配置)
#二进制日志
log-bin
#为了在最大程序上保证复制的InnoDB事务持久性和一致性
sync_binlog=1
sync_relay_log=1
#启用此两项,可用于实现在崩溃时保证二进制及从服务器安全的功能
relay-log-info-repository=TABLE
master-info-repository=TABLE
#设置清除日志时间
expire_logs_days=7
#行复制
binlog_format=ROW
#mysql数据库事务隔离级别有四种(READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE)
transaction-isolation=READ-COMMITTED

#cache
#内部内存临时表的最大值
tmp_table_size=512M
character-set-server=utf8
collation-server=utf8_general_ci
#即跳过外部锁定
skip-external-locking
#MySQL能暂存的连接数量(根据实际设置)
back_log=1024
#指定索引缓冲区的大小,只对MyISAM表起作用,这里写上也没有关系
key_buffer_size=1024M
#这条指令限定用于每个数据库线程的栈大小
thread_stack=256k
#当一个查询不断地扫描某一个表,MySQL会为它分配一段内存缓冲区
read_buffer_size=8M
#线程缓存
thread_cache_size=64
#查询缓存大小
query_cache_size=128M
#内部内存临时表的最大值,每个线程都要分配
max_heap_table_size=256M
#将查询结果放入查询缓存中
query_cache_type=1
#代表在事务过程中容纳二进制日志SQL语句的缓存大小
binlog_cache_size = 2M
#同样是缓存表大小
table_open_cache=128
#缓存线程
thread_cache=1024
#推荐设置为服务器 CPU核数的2倍
thread_concurrency=8
wait_timeout=30
#表和表联接的缓冲区的大小
join_buffer_size = 1024M
#是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存
sort_buffer_size=8M
#随机读取数据缓冲区使用内存
read_rnd_buffer_size = 8M

#connect
#是一个MySQL中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码
max-connect-errors=100000
#连接数
max-connections=1000
#开启查询缓存
explicit_defaults_for_timestamp=true
#mysql服务器能够工作在不同的模式下,并能针对不同的客户端以不同的方式应用这些模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量。
1.   连接请求的变量：
1)     max_connections
MySQL的最大连接数，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小会经常出现ERROR 1040: Too many connections错误，可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。
show variables like ‘max_connections' 最大连接数
show status like ‘max_used_connections'响应的连接数
如下：
mysql> show variables like ‘max_connections‘;
+———————–+——-+
| Variable_name　| Value |
+———————–+——-+
| max_connections | 256　　|
+———————–+——-+
mysql> show status like ‘max%connections‘;
+———————–+——-+
| Variable_name　      | Value |
+—————————-+——-+
| max_used_connections | 256|
+—————————-+——-+
max_used_connections / max_connections * 100% （理想值≈ 85%）
如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。
2)     back_log
MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到 max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。
当观察你主机进程列表（mysql> show full processlist），发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了。
默认数值是50，可调优为128，对系统设置范围为小于512的整数。
3)     interactive_timeout
一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户。
默认数值是28800，可调优为7200。
2.   缓冲区变量
全局缓冲：
4)     key_buffer_size
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值 Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
举例如下：
mysql> show variables like ‘key_buffer_size‘;
+——————-+————+
| Variable_name | Value      |
+———————+————+
| key_buffer_size | 536870912 |
+———— ———-+————+
key_buffer_size为512MB，我们再看一下key_buffer_size的使用情况：
mysql> show global status like ‘key_read%‘;
+————————+————-+
| Variable_name　 | Value    |
+————————+————-+
| Key_read_requests| 27813678764 |
| Key_reads　　　| 6798830      |
+————————+————-+
一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，设置在1/1000左右较好
默认配置数值是8388600(8M)，主机有4GB内存，可以调优值为268435456(256MB)。
5)     query_cache_size
使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。
通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%'获得）。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。
query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。
query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值 Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit。
举例如下：
mysql> show global status like ‘qcache%‘;
+——————————-+—————–+
| Variable_name                  | Value　       |
+——————————-+—————–+
| Qcache_free_blocks　       | 22756　      |
| Qcache_free_memory　    | 76764704    |
| Qcache_hits　　　　　      | 213028692 |
| Qcache_inserts　　　　     | 208894227   |
| Qcache_lowmem_prunes   | 4010916      |
| Qcache_not_cached　| 13385031    |
| Qcache_queries_in_cache | 43560　|
| Qcache_total_blocks          | 111212　     |
+——————————-+—————–+
mysql> show variables like ‘query_cache%‘;
+————————————–+————–+
| Variable_name　　　　　       | Value　     |
+————————————–+———–+
| query_cache_limit　　　　　    | 2097152     |
| query_cache_min_res_unit　     | 4096　　 |
| query_cache_size　　　　　    | 203423744 |
| query_cache_type　　　　　   | ON　          |
| query_cache_wlock_invalidate | OFF　 |
+————————————–+—————+
查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服务器查询缓存碎片率＝20.46％，查询缓存利用率＝62.26％，查询缓存命中率＝1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
每个连接的缓冲
6)    record_buffer_size
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
默认数值是131072(128K)，可改为16773120 (16M)
7)     read_rnd_buffer_size
随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
一般可设置为16M
8)     sort_buffer_size
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
默认数值是2097144(2M)，可改为16777208 (16M)。
9)     join_buffer_size
联合查询操作所能使用的缓冲区大小
record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100
10) table_cache
表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables'获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M。
11) max_heap_table_size
用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#
这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。
12) tmp_table_size
通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。
mysql> show global status like ‘created_tmp%‘;
+——————————–+———+
| Variable_name　　           | Value　|
+———————————-+———+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files　　　| 58　　|
| Created_tmp_tables　　| 1771587 |
+——————————–+———–+
每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，应该相当好了
默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞
13) thread_cache_size
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。
通过比较 Connections和Threads_created状态的变量，可以看到这个变量的作用。
默认值为110，可调优为80。
14) thread_concurrency
推荐设置为服务器 CPU核数的2倍，例如双核的CPU, 那么thread_concurrency的应该为4；2个双核的cpu, thread_concurrency的值应为8。默认为8
15) wait_timeout
指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。

3.    配置InnoDB的几个变量
innodb_buffer_pool_size
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。
根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。

innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的 ACID；设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。

innodb_log_buffer_size
log缓存大小，一般为1-8M，默认为1M，对于较大的事务，可以增大缓存大小。
可设置为4M或8M。

innodb_additional_mem_pool_size
该参数指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。
根据MySQL手册，对于2G内存的机器，推荐值是20M，可适当增加。

innodb_thread_concurrency=8
推荐设置为 2*(NumCPUs+NumDisks)，默认一般为8

MySQL 5.6相比于前代GA版本性能提升显著，但默认缓存设置对于小型站点并不合理。通过修改my.ini文件中的performance_schema_max_table_instances参数，能够有效降低内存占用。

以下是5.6默认的设置
performance_schema_max_table_instances 12500
table_definition_cache 1400
table_open_cache 2000
可以调成，或者在小点都可以。

performance_schema_max_table_instances=600
table_definition_cache=400
table_open_cache=256

performance_schema_max_table_instances
The maximum number of instrumented table objects 检测的表对象的最大数目。
table_definition_cache
The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum and default values are both 400.
缓存frm文件

table_open_cache
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
table_open_cache指的是缓存数据文件的描述符(Linux/Unix)相关信息
这个很重要啊，之前mount个单独的文件，数据库一直不成功，原来是这个在作怪啊。
chcon -R -t mysqld_db_t /home/myusqldata

下面是其它网友的补充

mysql> show variables;
一、慢查询

mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+------------------+-------+

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name    | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 4148 |
+---------------------+-------+
配置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有4148个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁：http://www.percona.com/docs/wiki/release:start，记得找对应的版本。
打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主－从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。
二、连接数
经常会遇见”MySQL: ERROR 1040: Too manyconnections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：

Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85％左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。
三、Key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数，下面一台以MyISAM为主要存储引擎服务器的配置：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%
比如上面的数据，key_cache_miss_rate为0.0244%，4000个索引读取请求才有一个直接读硬盘，已经很BT 了，key_cache_miss_rate在0.1%以下都很好（每1000个请求有一个直接读硬盘），如果key_cache_miss_rate在 0.01%以下的话，key_buffer_size分配的过多，可以适当减少。
MySQL服务器还提供了key_blocks_*参数：

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
四、临时表

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，应该相当好了。我们再看一下MySQL服务器对临时表的配置：

+---------------+-------+
比较合适的值为：

Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

六、进程使用情况
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2     |
| Threads_created | 570 |
| Threads_running | 1     |
+-------------------+-------+
如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置：
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
示例中的服务器还是挺健康的。
七、查询缓存(query cache)
mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks    | 22756     |
| Qcache_free_memory    | 76764704   |
| Qcache_hits          | 213028692 |
| Qcache_inserts       | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached    | 13385031   |
| Qcache_queries_in_cache | 43560     |
| Qcache_total_blocks     | 111212 |
+-------------------------+-----------+
MySQL查询缓存变量解释：
Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：每次查询在缓存中命中时就增大
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。
Qcache_total_blocks：缓存中块的数量。
我们再查询一下服务器关于query_cache的配置：
mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name             | Value     |
+------------------------------+-----------+
| query_cache_limit          | 2097152 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size          | 203423744 |
| query_cache_type          | ON        |
| query_cache_wlock_invalidate | OFF    |
+------------------------------+-----------+
各字段的解释：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小
query_cache_size：查询缓存大小
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
示例服务器查询缓存碎片率＝ 20.46％，查询缓存利用率＝ 62.26％，查询缓存命中率＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
八、排序使用情况
mysql> show global status like 'sort%';
+-------------------+------------+
| Variable_name     | Value    |
+-------------------+------------+
| Sort_merge_passes | 29       |
| Sort_range        | 37432840 |
| Sort_rows       | 9178691532 |
| Sort_scan       | 1860569 |
+-------------------+------------+
Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量Sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上，MySQL会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 Sort_buffer_size 会减少Sort_merge_passes 和创建临时文件的次数。但盲目的增加 Sort_buffer_size 并不一定能提高速度，
另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的好处，
九、文件打开数(open_files)
mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 1410   |
+---------------+-------+

随着大量默认选项的改进, MySQL 5.6比以前版本需要调优的选项大为减少. 在本文中我将讲述需要优化的配置项.

InnoDB设置

1.innodb_buffer_pool_size —— 默认值为 128M. 这是最主要的优化选项,因为它指定 InnoDB 使用多少内存来加载数据和索引(data+indexes). 针对专用MySQL服务器,建议指定为物理内存的 50-80%这个范围. 例如,拥有64GB物理内存的机器,缓存池应该设置为50GB左右.
如果将该值设置得更大可能会存在风险,比如没有足够的空闲内存留给操作系统和依赖文件系统缓存的某些MySQL子系统(subsystem),包括二进制日志(binary logs),InnoDB事务日志(transaction logs)等.

2.innodb_log_file_size —— 默认值为 48M. 有很高写入吞吐量的系统需要增加该值以允许后台检查点活动在更长的时间周期内平滑写入,得以改进性能. 将此值设置为4G以下是很安全的. 过去的实践表明,日志文件太大的缺点是增加了崩溃时所需的修复时间,但这在5.5和5.6中已得到重大改进.

3.innodb_flush_method —— 默认值为 fdatasync. 如果使用硬件RAID磁盘控制器, 可能需要设置为 O_DIRECT. 这在读取InnoDB缓冲池时可防止“双缓冲(double buffering)”效应,否则会在文件系统缓存与InnoDB缓存间形成2个副本(copy).
如果不使用硬件RAID控制器,或者使用SAN存储时, O_DIRECT 可能会导致性能下降.MySQL用户手册和 Bug #54306 详细地说明了这一点.

4.innodb_flush_neighbors —— 默认值为 1. 在SSD存储上应设置为0(禁用) ,因为使用顺序IO没有任何性能收益. 在使用RAID的某些硬件上也应该禁用此设置,因为逻辑上连续的块在物理磁盘上并不能保证也是连续的.

5.innodb_io_capacity and innodb_io_capacity_max —— 这些设置会影响InnoDB每秒在后台执行多少操作. 如果你深度了解硬件性能(如每秒可以执行多少次IO操作),则使用这些功能是很可取的,而不是让它闲着.

有一个很好的类比示例: 假如某次航班一张票也没有卖出去 —— 那么让稍后航班的一些人乘坐该次航班,有可能是很好的策略,以防后面遇到恶劣的天气. 即有机会就将后台操作顺便处理了,以减少同稍后可能的实时操作产生竞争.

有一个很简单的计算: 如果每个磁盘每秒读写(IOPS)可以达到 200次, 则拥有10个磁盘的 RAID10 磁盘阵列IOPS理论上 =(10/2)* 200 = 1000. 我说它“很简单”,是因为RAID控制器通常能够提供额外的合并,并有效提高IOPS能力. 对于SSD磁盘,IOPS可以轻松达到好几千.

将这两个值设置得太大可能会存在某些风险,你肯定不希望后台操作妨碍了前台任务IO操作的性能. 过去的经验表明,将这两个值设置的太高,InnoDB持有的内部锁会导致性能降低(按我了解到的信息,在MySQL5.6中这得到了很大的改进).

innodb_lru_scan_depth - 默认值为 1024. 这是mysql 5.6中引入的一个新选项. Mark Callaghan 提供了一些配置建议. 简单来说,如果增大了 innodb_io_capacity 值, 应该同时增加 innodb_lru_scan_depth.

复制(Replication)

假如服务器要支持主从复制,或按时间点恢复,在这种情况下,我们需要:

1.log-bin —— 启用二进制日志. 默认情况下二进制日志不是事故安全的(not crash safe),但如同我以前的文章所说, 我建议大多数用户应该以稳定性为目标. 在这种情况下,你还需要启用: sync_binlog=1, sync_relay_log=1, relay-log-info-repository=TABLE and master-info-repository=TABLE.

2.expire-logs-days —— 默认旧日志会一直保留. 我推荐设置为 1-10 天. 保存更长的时间并没有太多用处,因为从备份中恢复会快得多.

3.server-id —— 在一个主从复制体系(replication topology )中的所有服务器都必须设置唯一的 server-id.

4.binlog_format=ROW —— 修改为基于行的复制. 我最近写的另一篇基于行的复制 ,里面叙述了我真的很喜欢它的原因,因为它可以通过减少资源锁定提高性能. 此外还需要启用两个附加设置: transaction-isolation=READ-COMMITTED and innodb_autoinc_lock_mode = 2.

其他配置(Misc)

1.timezone=GMT 将时区设置为格林尼治时间. 越来越多的系统管理员建议将所有服务器都设置为格林尼治时间(GMT). 我个人非常喜欢这点,因为现在几乎所有的业务都是全球化的. 设置为你本地的时区似乎是有点武断的.

2.character-set-server=utf8mb4 and collation-server=utf8mb4_general_ci 如之前的文章所讲述的 ,utf8 编码对新应用来说是更好的默认选项. 您还可以设置 skip-character-set-client-handshake 以忽略应用程序想要设置的其他字符集(character-set).

3.sql-mode —— MySQL默认对不规范的数据很宽容,并且会静默地截断数据. 在我之前的一篇文章中, 我提到新应用程序最好设置为：

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,

 NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,

 NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,

 NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY.

4.skip-name-resolve —— 禁用反向域名解析. DNS解析在某些系统上可能有点慢/不稳定,所以如果不需要基于主机名的授权,我建议避免这种解析.

5.max_connect_errors —— Todd Farmer 写道 :“[这个功能]提供了没有实际意义的暴力访问攻击保护”. 事实上当设置skip-name-resolve 时, max_connect_errors 甚至不起作用(见上一段所述).

防火墙是更合适的解决方案,通常我将3306端口屏蔽,不管是公网的还是内网的端口,只有特定的应用程序可以访问和连接到MySQL.
我通常会设置 max_connect_errors=100000, 这样我可以避免任何“双重配置”,保证它不会碍事.

6.max-connections ——默认值是151. 我看到很多用户将他设置得比较大,大多在 300 ~ 1000之间.
通常不可避免地这个值会被设置得更大,但让我有点紧张的是, 16核的机器在IO阻塞的情况下也只有大约 2x~10x 的连接执行能力.
你可能希望,许多打开的连接都是空闲并休眠的. 但如果他们都处于活跃状态的话,可能会创建大量新的线程(thread-thrash).
如果条件允许,可以为应用程序配置优化数据库连接池(connection-pools)来解决这个问题,而不是打开并保持大量连接;
当然那些不使用连接池(non-pooled ), 迅速打开,执行任务后又尽可能快地关闭连接的应用也是可行的.
从5.5开始的另一种解决方案(在MySQL社区版和企业版之间有一些差异) 是使用线程池插件.

总结(Conclusion)

假设MySQL服务器的配置为:
1.64GB物理内存
2.硬件RAID控制器(假设每秒IO可达 2000 IOPS)
3.需要主从复制(Replication)
4.新的应用(eg. 非遗留系统)
5.有防火墙保护
6.不需要基于域名(hostnames,主机名)的授权
7.全球化应用,并不想固定在某一时区.
8.想要程序可靠稳定(durable).

则配置可能如下所示:


# InnoDB settings

innodb_buffer_pool_size=50G

innodb_log_file_size=2G

innodb_flush_method=O_DIRECT

innodb_io_capacity=2000

innodb_io_capacity_max=6000

innodb_lru_scan_depth=2000
# Binary log/replication

log-bin

sync_binlog=1

sync_relay_log=1

relay-log-info-repository=TABLE

master-info-repository=TABLE

expire_logs_days=10

binlog_format=ROW

transaction-isolation=READ-COMMITTED

innodb_autoinc_lock_mode = 2
# Other

timezone=GMT

character-set-server=utf8

collation-server=utf8_general_ci

sql-mode="STRICT_TRANS_TABLES,

 ERROR_FOR_DIVISION_BY_ZERO,

 NO_AUTO_CREATE_USER,

 NO_AUTO_VALUE_ON_ZERO,

 NO_ENGINE_SUBSTITUTION,

 NO_ZERO_DATE,

 NO_ZERO_IN_DATE,

 ONLY_FULL_GROUP_BY"

skip-name_resolve

max-connect-errors=100000

max-connections=500
# Unique to this machine

server-id=123

到最后了，小牛知识库小编为大家分享一个配置测试不错的适合高配高负载的服务器

[client]
port        = 3306
socket        = /tmp/mysql.sock
default-character-set = utf8
#设置客户端的字符编码
[mysqld]
# generic configuration options
port        = 3306
socket        = /tmp/mysql.sock
basedir="d:/database/mysql/"
datadir="d:/database/mysql/data/"
tmpdir = "d:/database/mysql/tmp/"
#*** char set ***
character-set-server = utf8
#设置服务器端的字符编码

#下面三个参数12500,1400,2000
performance_schema_max_table_instances = 12500
table_definition_cache = 1400
table_open_cache = 2000

#*** network ***
back_log = 1024
#skip-networking #默认没有开启
max_connections = 10000
#max_connect_errors = 3000
table_open_cache = 4096
#external-locking #默认没有开启
max_allowed_packet = 256M
max_heap_table_size = 128M

#*** timeout ***
interactive_timeout=1000
wait_timeout=1000

# *** global cache ***
read_buffer_size = 8M
read_rnd_buffer_size = 64M
sort_buffer_size = 16M
join_buffer_size = 1024M

# *** thread ***
thread_cache_size = 64
# thread_concurrency = 8
thread_stack = 512K

# *** query cache ***
query_cache_size = 128M
query_cache_limit = 8M

# *** index ***
ft_min_word_len = 8

#memlock #默认没有开启
default-storage-engine=MYISAM
innodb=OFF
default-tmp-storage-engine=MYISAM
transaction_isolation = REPEATABLE-READ

# *** tmp table ***
tmp_table_size = 1024M

# *** bin log ***
#log-bin=mysql-bin
binlog_cache_size = 4M
binlog_format=mixed
#log_slave_updates #默认没有开启
#log #默认没有开启，此处是查询日志，开启会影响服务器性能
log_warnings #开启警告日志

# *** slow query log ***
slow_query_log
long_query_time = 10
# *** Replication related settings
#server-id = 1
#server-id = 2
#master-host = <hostname>
#master-user = <username>
#master-password = <password>
#master-port = <port>
#read_only
#*** MyISAM Specific options
#myisam_recover
key_buffer_size = 2048M
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover_options=force,backup

# *** INNODB Specific options ***
#skip-innodb #默认没有开启
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
#loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 10240
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

这篇文章就介绍到这了，需要的朋友可以参考一下。

MySQL5.6基本优化配置

相关阅读

相关文章

相关问答

相关工具

相关文档