MySQL参数优化
2020-08-28 14:02:04 1 举报AI智能生成
登录查看完整内容
mysql参数优化
作者其他创作
大纲/内容
MySQL参数优化
设置
查询MySQL参数
show variables like '%max_connections%';
查询状态值
show global status like '%onnection%';
设置MySQL参数
set global max_connections = 200;
全局内存消耗(共享内存)
MySQL内存计算器：http://www.mysqlcalculator.com/
innodb_buffer_pool_size
innodb buffer pool缓存着InnoDB表，索引，及其它辅助缓冲器中的数据。使用LRU算法(最近最少使用）淘汰过期缓存数据。
innodb_buffer_pool_instances
主要用于将innodb buffer pool进行划分，通过划分innodb buffer pool为多个实例，可以提高并发能力，并且减少了不同线程读写造成的缓冲页。每一页从其中一个buffer pool中使用hash函数随机的读取和写入。每个buffer pool管理和维护各自的信息，包括free lists、flush lists、LRUs等
默认：8 or 1(innodb_buffer_pool_size<1G)
推荐：默认值或者逻辑CPU数量
innodb_additional_mem_pool_size
要用于存放MySQL内部的数据结构和Innodb的数据字典，所以大小主要与表的数量有关，表越多值越大。这个值是可变的，如果不够用的话，MySQL会向操作系统申请的。
innodb_log_buffer_size
redolog的缓冲区，为了提高性能，MySQL每次写日志都将日志先写到一个内存Buffer中，然后将Buffer按照innodb_flush_log_at_trx_commit的配置刷到disk上。
innodb_flush_log_at_trx_commit
0: 最佳性能：每隔一秒，才将Log Buffer中的数据批量write入OS cache，同时MySQL主动fsync。这种策略，如果数据库奔溃，有一秒的数据丢失。1：强一致：每次事务提交，都将Log Buffer中的数据write入OS cache，同时MySQL主动fsync。这种策略，是InnoDB的默认配置，为的是保证事务ACID特性。2：折中：每次事务提交，都将Log Buffer中的数据write入OS cache；每隔一秒，MySQL主动将OS cache中的数据批量fsync。这种策略，如果操作系统奔溃，最多有一秒的数据丢失。
推荐值：高并发场景下，使用折中配置（=2），因为：配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快；配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据。
sync_binlog
0 ：当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。1：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新。n：在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
默认值：1，安全性最高，性能较低
sync_binlog=0，性能最好，风险最大，一旦系统Crash，在文件系统缓存的所有binlog信息都会丢失。
key_buffer_size
MyISAM表的key缓存，这个只对MyISAM存储引擎有效，对系统临时表(MyISAM引擎)也有效
query_cache_size
MySQL对于查询的结果会进行缓存来节省解析SQL、执行SQL的花销，query_cache是按照SQL语句的Hash值进行缓存的，同时SQL语句涉及的表发生更新，该缓存就会失效，所以这个缓存对于特定的读多更新少的库比较有用，对于绝大多数更新较多的库可能不是很适用，比较受限于应用场景。
会话级内存消耗(连接私有内存)
read_buffer_size
每个线程连续扫描时为扫描的每个表分配的缓存区的大小（字节）。如果进行多次连续扫描，可能还需要增加该值。只有当查询需要的时候，才分配read_buffer_size指定的全部内存。
read_rnd_buffer_size
当以任意顺序读取行时，可以分配随机读取缓冲区，通过该缓冲区读取行，以避免磁盘寻找。read_rnd_buffer_size系统变量决定缓冲器大小。
sort_buffer_size
每一个要做排序的请求，都会分到一个sort_buffer_size大的缓存，用于做order by和group by的排序，如果设置的缓存大小无法满足需要，MySQL会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以sort_buffer_size对排序的性能影响很大。由于这部分缓存是即使不用这么大，也会全部分配的，所以对系统内存分配开销是比较大的。
thread_stack
MySQL为每个线程分配的堆栈大小，当线程堆栈太小时，这限制了服务器可以处理的SQL语句的复杂性。
join_buffer_size
每个连接的每次join都分配一个buffer。
binlog_cache_size
类似于innodb_log_buffer_size缓存事务日志，binlog_cache_size缓存Binlog，不同的是这个是每个线程单独一个，主要对于大事务有较大性能提升。
tmp_table_size
用户内存临时表的最大值，如果临时表超过该值，MySQL就会把临时表转换为一个磁盘上mysiam表。如果用户需要做一些大表的groupby的操作，可能需要较大的该值，由于是与连接相关的，同样建议在会话层设置。
慢查询
slow_query_log
是否启用慢查询日志，1为启用，0为禁用
slow_query_log_file
指定慢查询日志文件的路径和名字，可使用绝对路径指定
默认值是'主机名_slow.log'，位于datadir目录
long_query_time
SQL语句运行时间阈值，执行时间大于参数值(单位秒)的语句才会被记录下来
min_examined_row_limit
SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志，即使这个语句执行时间超过了long_query_time的阈值
log_queries_not_using_indexes
将没有使用索引的语句记录到慢查询日志，1为启用，0为禁用
log_throttle_queries_not_using_indexes
5.6版本设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
log-slow-admin-statements
log_slow_slave_statements
5.6版本记录从库上执行的慢查询语句
log_timestamps
5.7版本新增时间戳所属时区参数
默认值：记录UTC时区的时间戳到慢查询日志
推荐值：system，应修改为记录系统时区
log_output
指定慢查询日志的输出方式，从5.5版本开始可以记录到日志文件(FILE，慢查询日志)和数据库表(TABLE，mysql.slow_log)中
连接数
max_connections
MySQL限定的最大连接数。
推荐值：Max_used_connections / max_connections * 100% ≈ 85%
Max_used_connections
MySQL服务自启动之后响应的最大连接数。
interactive_timeout
mysql在关闭一个交互的连接之前所要等待的秒数。
wait_timeout
mysql在关闭一个非交互（空闲）的连接之前所要等待的秒数。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取
默认值：28800，单位秒，即8个小时
max_connect_errors
max_connect_errors 是一个安全的方法。如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到 FLUSH HOSTS 之后才能运行。
默认值：10，10次失败就导致锁定。
推荐值：100，重试100次，会给服务器足够的时间来从问题中恢复。如果重试 100 次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接。
binlog
log_bin
启用binlog功能，并指定路径和名字，可使用绝对路径指定
未指定绝对路径，默认位于datadir目录
log_bin_index
指定二进制索引文件的路径和名字，可使用绝对路径指定
binlog_do_db
只记录指定数据库的二进制日志，建议不要配置
binlog_ignore_db
不记录指定的数据库的二进制日志，建议不要配置
max_binlog_cache_size
binlog使用的内存最大的尺寸
默认值：18446744073709547520
推荐值：4294967296，即4G
表示binlog使用的内存大小
默认值：37268，即32K
推荐值：1M，如果系统事务多可以设置为2到4M
binlog_cache_use
状态值，表示使用内存进行二进制日志缓存的事务数量
binlog_cache_disk_use
状态值，表示使用二进制日志缓存但超binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
max_binlog_size
Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束
binlog_format
可选值有statement（记录逻辑sql语句）、row（记录表的行变动情况）、mixed（混合模式）
expire_logs_days
binlog过期时间
默认是0天，也就是说不自动清理
推荐值：7天，设置之后不是立即清理，触发条件为：binlog大小超过max_binlog_size、手动执行flush logs、重新启动时(MySQL将会new一个新文件用于记录binlog)
手动清除binlog
指定binlog文件之前
purge binary logs to 'bin.000055';
指定时间之前
purge binary logs before '2020-08-04 13:09:51';
MyISAM引擎
MyISAM表缓冲区大小
默认值：536870912，即512MB
Key_reads
命中磁盘的请求个数
Key_read_requests
请求总数
计算索引未命中缓存的概率：key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%
Key_blocks_unused
未使用的缓存簇(blocks)数
Key_blocks_used
曾经用到的最大的blocks数
推荐：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
临时表
Created_tmp_disk_tables
在磁盘上创建的临时表数
Created_tmp_files
创建的临时文件的文件数
Created_tmp_tables
创建的临时表的总数
推荐：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
max_heap_table_size
该值以下的heap临时表才能全部放入内存，超过的就会用到磁盘临时表
该值以下的临时表才能全部放入内存，超过的就会用到磁盘临时表
线程
Threads_cachedThreads_connectedThreads_createdThreads_running
与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接。
thread_cache_size
如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户，而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
查询缓存
Qcache_free_blocks
缓存中相邻内存块的个数，数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理。
Qcache_free_memory
缓存中的空闲内存。
Qcache_hits
每次查询在缓存中命中时就增大。
Qcache_inserts
每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
Qcache_lowmem_prunes
缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached
不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache
当前缓存的查询（和响应）的数量。
Qcache_total_blocks
缓存中块的数量。
query_cache_limit
超过此大小的查询将不缓存。
query_cache_min_res_unit
缓存块的最小大小。
查询缓存碎片率 = 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可能有点小，要不就是碎片太多。
query_cache_type
缓存类型，决定缓存什么样的查询，ON表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate
当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，ON表示返回cache结果，OFF表示等写操作完成再读表获取结果。
排序
Sort_merge_passesSort_rangeSort_rowsSort_scan
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 并不一定能提高速度
文件打开数
Open_files
当前Mysql文件打开数
open_files_limit
文件打开数上限
推荐：Open_files / open_files_limit * 100% <= 75％
表级锁
Table_locks_waited
需要等待的表锁数
Table_locks_immediate
立即释放表锁数
表扫描
Handler_read_firstHandler_read_keyHandler_read_lastHandler_read_nextHandler_read_prevHandler_read_rndHandler_read_rnd_nextCom_select
表扫描率 ＝ Handler_read_rnd_next / Com_select
推荐：如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB
表定义缓存
table_definition_cache
表定义信息缓存，用来存放表定义信息。用来存放表定义信息。当我们的MySQL 中使用了较多的表的时候，此缓存无疑会提高对表定义信息的访问效率。MySQL 提供了table_definition_cache 参数给我们设置可以缓存的表的数量。注意，这里设置的是可以缓存的表定义信息的数目，而不是内存空间的大小。
Open_table_definitionsOpened_table_definitions
打开的表定义信息的数目。
0 条评论
回复 删除
下一页
