Mysql 优化

参数优化

  • max_connections

最大连接数，并发链接，默认151,

调整依据看

show status like 'Max_used_connections';

select @@max_connections;
  • back_log

  

暂存的连接数 ，达到最大连接数多出来的连接数，不建议设置

  • wait_timeout 和 interactive_timeout
select @@wait_timeout;   非活动超时时间默认8小时,建议设置60s
select @@interactive_timeout;     活动超时时间默认8小时，比如正
在进行的事务处理，根据事务时间设置
  • key_buffer_size

索引缓存大小 ,临时表会使用

select @@key_buffer_size; 默认8M

设置依据

show status like "created_tmp%";

+-------------------------+-------+  
Variable_name Value  
+-------------------------+-------+  
Created_tmp_disk_tables 0  
Created_tmp_files 6  
Created_tmp_tables 11  
+-------------------------+-------+

通常地，我们习惯以 
Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 

或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 
Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
控制在5%-10%以内
  • query_cache_size

查询缓存

show variables like "query_cache_size";默认1M，默认关闭
show variables like "query_cache%";

判断依据

show status like "%Qcache%";

Qcache_free_blocks：缓存中相邻内存块的个数。 如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 注：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks

Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。

Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。

Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

Qcache_lowmem_prunes： 多少条Query因为内存不足而被清除出QueryCache。通过“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 数量；。

Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)

90/ 10000 0 90 如果出现hits比例过低，其实就可以关闭查询缓存了。

  • max_connect_errors

最大尝试连接错误数，没有什么意义

  • sort_buffer_size

排序查询的线程分配的一个缓冲区，增加这个加速 建议配置1M

sort_buffer_size=1M  如果有500个连接则会立即消耗500M内存

  • max_allowed_packet

 服务端接受的数据包大小 ，建议设置大一点  

max_allowed_packet=512M

  • join_buffer_size

使用join查询使用的缓存大小 ，不需要太大，建议设置1M

  • thread_cache_size

服务器线程缓存个数 

show status like "%threads_%";  
+-------------------+-------+  
Variable_name Value  
+-------------------+-------+  
Threads_cached 1  
Threads_connected 5  
Threads_created 6  
Threads_running 4  
+-------------------+-------+  
4 rows in set (0.00 sec)

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。

Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。

Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

建议1G内存设置8 依次类推，CPU特别繁忙设置

  • innodb_buffer_pool_size

对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%

innodb_buffer_pool_size=2048M

  • innodb_flush_log_at_trx_commit

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；

1， 每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的ACID；

2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。 根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。

  • innodb_thread_concurrency

此参数用来设置innodb线程的并发数量，默认值为0表示不限制。

依据 通过show processlist 查看线程数

在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，

如下： 如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；

如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128， 并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数

  • innodb_log_buffer_size

此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

innodb_log_buffer_size=128M

设定依据：

1、大事务： 存储过程调用 CALL

2、多事务

  • innodb_log_file_size = 100M

设置 ib_logfile0 ib_logfile1

此参数确定redo数据日志文件的大小，以M为单位，更大的设置可以提高性能.默认50M

innodb_log_file_size = 100M
innodb_log_files_in_group = 3

为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

  • read_buffer_size = 1M

MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

  • read_rnd_buffer_size = 1M

MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

  • bulk_insert_buffer_size = 8M

批量插入数据缓存大小，可以有效提高插入效率，默认为8M

  • binary log

log-bin=/data/mysql-bin

binlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是–1M，后者建议是：即 2–4M

max_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,表示“没有自动删除”。

log-bin=/data/mysql-bin

binlog_format=row 

sync_binlog=1

双1标准(基于安全的控制)：

sync_binlog=1 什么时候刷新binlog到磁盘，每次事务commit

innodb_flush_log_at_trx_commit=1

set sql_log_bin=0;

show status like 'com_%';
  • 安全参数

 

Innodb_flush_method=(O_DIRECT, fsync)

1、fsync

（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=fsync

一般情况下，我们更偏向于安全。
“双一标准”

innodb_flush_log_at_trx_commit=1        
sync_binlog=1                                   
innodb_flush_method=O_DIRECT
  • 参数优化结果2G内存
[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock
Mysql
#Linux #Mysql
Mysql 优化
http://www.jcwit.com/article/69/
作者
Carlos
发布于
2019年6月18日
许可协议