mysql常用配置参数学习笔记

mysql> show engine innodb status\G
查看线程默认4读4写

show global status like ‘%innodb_dblwr%’\G
写入页数:写入次数大概是64:1 如果业务高峰这个比例小于64:1相对可作为依据说明系统写入压力不大。

binlog_format
statement --二进制日志记录逻辑sql语句
row --记录表行更改情况（主从复制 row+RC）
mixed --statement+row兼容模式，特殊情况使用row（insert delay,临时表）
set @@session.binlog_format=‘ROW’;
set global binlog_format=‘ROW’;
开row日志记录的更多，应考虑空间情况

innodb_print_all_deadlocks = 1
开启后会将所有的死锁记录到 error_log 中 错误日志在 my.cnf 配置为 log-error=/var/log/mysqld.log

show processlist; #显示mysql系统中正在运行的所有线程。可以看到每个客户端正在执行的命令

启用mysql慢查询分析sql语句
vim /etc/my.cnf
[mysqld]
slow_query_log = 1 #开启慢查询日志
slow-query-log-file=/var/lib/mysql/slow.log
long_query_time=5 #查询超过5秒钟的语句记录下来
log-queries-not-using-indexes =1 #没有使用索引的查询

MySQL有一个特性称为查询缓存，他可以将查询的结果保存在内存中，在很多情况下，这会极大地提高性能。
启动查询缓存：
vim /etc/my.cnf
[mysqld]
query_cache_size = 32M

systemctl restart mysqld

如果设置的 query_cache_size 小于 41984，则会设置失败，并自动把它置为 0。如果设置为 0 则表 示不使用缓冲区。

当 query_cache_size 大于 0 的情况下，并不能保证缓冲区会被使用，还必须根据变量 query_cache_type 来决定，该变量有 3 中状态，分别代表 3 中不同缓冲方式：

0 或 OFF--------将阻止缓存或查询缓存结果。
1 或 ON---------将允许缓存，以 SELECT SQL_NO_CACHE 开始的查询语句除外。
2 或 DEMAND--------仅对以 SELECT SQL_CACHE 开始的那些查询语句启用缓存。

have_query_cache 表示是否支持 query cache

mysql> show variables like ‘%have_query_cache%’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| have_query_cache | YES |
±-----------------±------+
1 row in set (0.00 sec)

mysql> show variables like ‘%query_cache_limit%’;
±------------------±--------+
| Variable_name | Value |
±------------------±--------+
| query_cache_limit | 2097152 |
±------------------±--------+
1 row in set (0.00 sec)

如果（查询结果的大小=query_cache_min_res_unit），则直接把结果存入到该缓冲区中。 如果（查询结果的大小<query_cache_min_res_unit），则直接把结果存入到该缓冲区中，然后把多 分配的空间释放掉，这种情况容易产生内存碎片。 如果（查询结果的大小>query_cache_min_res_unit），则需要再分配内存空间来存放结果

查询缓存使用长度可变块，因此 Qcache_total_blocks 和 Qcache_free_blocks 可以显示查询缓存内 存碎片。执行 FLUSH QUERY CACHE 后，只保留一个空闲块

FLUSH QUERY CACHE;
mysql> show variables like ‘%Qcache%’;
Empty set (0.00 sec)

show variables like ‘%query_cache_size%’;

解释如下： 变量名 说明

1. Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。如果数目比较大，可以执行：mysql> flush query
2. Qcache_free_memory 缓存中的空闲内存大小
3. Qcache_hits 每次查询在缓存中命中时就增大。
4. Qcache_inserts 每次插入一个查询时就增大。即没有从缓存中找到数据
5. Qcache_lowmem_prunes #因内存不足删除缓存次数，缓存出现内存不足并且必须要进行清理,以便为更多查询提供空间的次数。返个数字最好长时间来看；如果返个数字在不断增长，就表示可能碎片非常严重，或者缓存内存很少。如果Qcache_free_blocks比较大，说明碎片严重。如果free_memory很小，说明缓存不够用了。
6. Qcache_not_cached # 没有进行缓存的查询的数量，通常是这些查询未被缓存或其类型不允许被缓存
7. Qcache_queries_in_cache # 在当前缓存的查询（和响应）的数量。
8. Qcache_total_blocks #缓存中块的数量。

max_connections=500上限是看硬件配置
wait_timeout=10 终止空闲等待时间
max_connect_errors = 20 如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到执行：mysql> FLUSH HOSTS;

文末推荐个干货链接：
https://www.modb.pro/db/51907