[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 # # 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. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M port=3306 datadir=/data/mysql socket=/data/mysql/mysql.sock pid_file=/data/mysql/mysqld.pid
########### Network ########### # 最大连接数(该参数受到最大文件描述符影响，如果不生效请检查最大文件描述符设置) # refs https://stackoverflow.com/questions/39976756/the-max-connections-in-mysql-5-7 max_connections=1500 # mysql 堆栈内暂存的链接数量 # 当短时间内链接数量超过 max_connections 时，部分链接会存储在堆栈内，存储数量受此参数控制 back_log=256 # 最大链接错误，针对于 client 主机，超过此数量的链接错误将会导致 mysql server 针对此主机执行锁定(禁止链接 ERROR 1129 ) # 此错误计数仅在 mysql 链接握手失败才会计算，一般出现问题时都是网络故障 # refs https://www.cnblogs.com/kerrycode/p/8405862.html max_connect_errors=100000 # mysql server 允许的最大数据包大小 max_allowed_packet=64M # 交互式客户端链接超时(30分钟自动断开) interactive_timeout=1800 # 非交互式链接超时时间(10分钟) # 如果客户端有连接池，则需要协商此参数(refs https://database.51cto.com/art/201909/603519.htm) wait_timeout=600 # 跳过外部文件系统锁定 # If you run multiple servers that use the same database directory (not recommended), # each server must have external locking enabled. # refs https://dev.mysql.com/doc/refman/5.7/en/external-locking.html skip_external_locking=1 # 跳过链接的域名解析(开启此选项后 mysql 用户授权的 host 方式失效) skip_name_resolve=0 # 禁用主机名缓存，每次都会走 DNS host_cache_size=0
# # The Percona Server 5.7 configuration file. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# 配置服务端地址 pmm-admin config --server-url https://admin:admin@pmm.mysql.node 172.16.0.11 generic mysql # 配置当前 mysql 实例 pmm-admin add mysql --username=pmm --password=pmm12345 mysql 172.16.0.11:3306
[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 # # 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. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M port=3306 datadir=/home/mysql/mysql socket=/home/mysql/mysql/mysql.sock pid_file=/home/mysql/mysql/mysqld.pid
########### Network ########### # 最大连接数(该参数受到最大文件描述符影响，如果不生效请检查最大文件描述符设置) # refs https://stackoverflow.com/questions/39976756/the-max-connections-in-mysql-5-7 max_connections=1500 # mysql 堆栈内暂存的链接数量 # 当短时间内链接数量超过 max_connections 时，部分链接会存储在堆栈内，存储数量受此参数控制 back_log=256 # 最大链接错误，针对于 client 主机，超过此数量的链接错误将会导致 mysql server 针对此主机执行锁定(禁止链接 ERROR 1129 ) # 此错误计数仅在 mysql 链接握手失败才会计算，一般出现问题时都是网络故障 # refs https://www.cnblogs.com/kerrycode/p/8405862.html max_connect_errors=100000 # mysql server 允许的最大数据包大小 max_allowed_packet=64M # 交互式客户端链接超时(30分钟自动断开) interactive_timeout=1800 # 非交互式链接超时时间(10分钟) # 如果客户端有连接池，则需要协商此参数(refs https://database.51cto.com/art/201909/603519.htm) wait_timeout=28800 # 跳过外部文件系统锁定 # If you run multiple servers that use the same database directory (not recommended), # each server must have external locking enabled. # refs https://dev.mysql.com/doc/refman/5.7/en/external-locking.html skip_external_locking=1 # 跳过链接的域名解析(开启此选项后 mysql 用户授权的 host 方式失效) skip_name_resolve=0 # 禁用主机名缓存，每次都会走 DNS host_cache_size=0
# # The Percona Server 5.7 configuration file. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # WARN innodb_flush_log_at_trx_commit-1: InnoDB is not configured in strictly ACID mode. # NOTE innodb_max_dirty_pages_pct: The innodb_max_dirty_pages_pct is lower than the default. # WARN max_connections: If the server ever really has more than a thousand threads running, then the system is likely to spend more time scheduling threads than really doing useful work. # NOTE read_buffer_size-1: The read_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE read_rnd_buffer_size-1: The read_rnd_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later. # WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed. # WARN sync_binlog: Binary logging is enabled, but sync_binlog isn't configured so that every transaction is flushed to the binary log for durability.
8.3、死锁诊断
使用 pt-deadlock-logger 工具可以诊断当前的死锁状态，以下为对死锁检测的测试
首先创建测试数据库和表
1 2 3 4 5
# 创建测试库 CREATE DATABASE dbatest CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # 切换到测试库并建立测试表 USE dbatest; CREATE TABLE IF NOT EXISTS test (id INT AUTO_INCREMENT PRIMARY KEY, value VARCHAR(255), createtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=INNODB;
# 插入两条测试数据 INSERT INTO test(value) VALUES('test1'); INSERT INTO test(value) VALUES('test2'); # 在两个终端下进行交叉事务
# 统一关闭自动提交 terminal_1 # SET AUTOCOMMIT = 0; terminal_2 # SET AUTOCOMMIT = 0;
# 交叉事务，终端 1 先更新第一条数据，终端 2 先更新第二条数据 terminal_1 # BEGIN; terminal_1 # UPDATE test set value='x1' where id=1; terminal_2 # BEGIN; terminal_2 # UPDATE test set value='x2' where id=2;
# 此后终端 1 再尝试更新第二条数据，终端 2 再尝试更新第一条数据；造成等待互向释放锁的死锁 terminal_1 # UPDATE test set value='lock2' where id=2; terminal_2 # UPDATE test set value='lock1' where id=1;
# 此时由于开启了 mysql innodb 的死锁自动检测机制，会导致终端 2 弹出错误 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
# 同时 pt-deadlock-logger 有日志输出 server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query 127.0.0.1 2019-12-24T14:57:10 87 0 52 root 127.0.0.1 dbatest test PRIMARY RECORD X w 0 UPDATE test set value='lock2' where id=2 127.0.0.1 2019-12-24T14:57:10 89 0 41 root 127.0.0.1 dbatest test PRIMARY RECORD X w 1 UPDATE test set value='lock1' where id=1
pt-duplicate-key-checker 127.0.0.1 --user root --ask-pass Enter password:
# A software update is available: # ######################################################################## # aaaaaa.aaaaaa_audit # ########################################################################
# index_linkId is a duplicate of unique_linkId # Key definitions: # KEY `index_linkId` (`link_id`) # UNIQUE KEY `unique_linkId` (`link_id`), # Column types: # `link_id` bigint(20) not null comment 'bdid' # To remove this duplicate index, execute: ALTER TABLE `aaaaaa.aaaaaa_audit` DROP INDEX `index_linkId`;
# ######################################################################## # Summary of indexes # ########################################################################
# Size Duplicate Indexes 927420 # Total Duplicate Indexes 3 # Total Indexes 847
