Linux操作文档——MySQL优化（5.7.26）
文章目录
- 一、内存优化
- 二、参数优化
- 三、锁等待
- 四、死锁监控
- 五、主从优化
一、内存优化
[root@mysql-1 ~]# echo 0 >/proc/sys/vm/swappiness //临时
[root@mysql-1 ~]# vim /etc/sysctl.conf //永久
vm.swappiness=0
[root@mysql-1 ~]# sysctl -p
二、参数优化
| 参数 | 解释 | 用法 |
|---|---|---|
| Max_connections | Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值 | Max_connections=1024 |
| back_log | mysql能暂存的连接数量，只有如果期望在一个短时间内有很多连接的时候需要增加它 | back_log=1024 |
| wait_timeout | 指的是mysql在关闭一个非交互的连接之前所要等待的秒数 | wait_timeout=60 |
| interactive_timeout | 指的是mysql在关闭一个交互的连接之前所需要等待的秒数 | interactive_timeout=1200 |
| key_buffer_size | 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度 | key_buffer_size=64M |
| query_cache_size | 查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。 | query_cache_size=128M |
| query_cache_type | 缓存类型，决定缓存什么样的查询，设置为0，相当于禁用，设置为1，将会缓存所有的结果，设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询 | query_cache_type=1 |
| max_connect_errors | 负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 | max_connect_errors=2000 |
| sort_buffer_size | 每个需要进行排序的线程分配该大小的一个缓冲区 | sort_buffer_size=1M |
| max_allowed_packet | mysql根据配置文件会限制，server接受的数据包大小 | max_allowed_packet=32M |
| join_buffer_size | 用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享 | join_buffer_size=2M |
| thread_cache_size | 服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限)，如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能 | thread_cache_size=32 |
| innodb_buffer_pool_size | InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的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磁盘操作。 | innodb_flush_log_at_trx_commit=1 |
| innodb_thread_concurrency | 设置innodb线程的并发数量，默认值为0表示不限制 | innodb_thread_concurrency=8 |
| innodb_log_buffer_size | 确定日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。 | innodb_log_buffer_size=128M |
| innodb_log_file_size | 确定数据日志文件的大小，以M为单位，更大的设置可以提高性能 | innodb_log_file_size = 100M |
| innodb_log_files_in_group | 以循环方式将日志文件写到多个文件 | nnodb_log_files_in_group = 3 |
| read_buffer_size | MySql读入缓冲区大小 | read_buffer_size = 1M |
| read_rnd_buffer_size | MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时将分配一个随机读缓存区 | read_rnd_buffer_size = 1M |
| bulk_insert_buffer_size | 批量插入数据缓存大小，可以有效提高插入效率，默认为8M | bulk_insert_buffer_size = 8M |
| binary log | 什么时候刷新binlog到磁盘，每次事务commit | sync_binlog=1 |
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
三、锁等待
1、查看锁等待
mysql01 [(none)]>SHOW STATUS LIKE 'innodb_row_lock%';
2、查看事务阻塞情况
mysql01 [(none)]>USE information_schema;
mysql01 [(none)]>SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
| 参数 | 说明 |
|---|---|
| trx_id | 事务ID号 |
| trx_state | 当前事务的状态 |
| trx_mysql_thread_id | 连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id ) |
| trx_query | 当前被阻塞的操作 |
| 3、查看锁源 |
mysql01 [(none)]>SELECT * FROM sys.innodb_lock_waits;
| 参数 | 说明 |
|---|---|
| locked_table | 哪张表出现的等待 |
| waiting_trx_id | 等待的事务(与上个视图trx_id 对应) |
| waiting_pid | 等待的线程号(与上个视图trx_mysql_thread_id) |
| blocking_trx_id | 锁源的事务ID |
| blocking_pid | 锁源的线程号 |
查看锁源的thread_id
mysql01 [(none)]>SELECT * FROM performance_schema.threads WHERE processlist_id=15;
查看锁源的SQL语句
mysql01 [(none)]>SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41; //当前在执行的语句
mysql01 [(none)]>SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41; //执行语句的历史
四、死锁监控
死锁写入错误日志
[root@mysql-1 ~]# vim /etc/my.cnf
innodb_print_all_deadlocks = 1
mysql01 [(none)]>show engine innodb status\G
mysql01 [(none)]>show variables like '%deadlock%';
五、主从优化
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16 //cpu核心数作为标准（50％）
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON