MySQL性能优化

关于MySQL性能问题查找及优化的点滴.

另一篇记录：MySQL基础

常用参数

# /etc/my.cnf
[mysqld]
max_connections=1000   # 最大连接数
innodb_buffer_pool_size=4G  # 缓存池大小,建议< 80% 总内存

# 时间超过2秒的SQL记录在慢查询日志
long_query_time=2

# 用以下面响应时间分布的收集
query_response_time_stats = on

innodb_buffer_pool_size 推荐大小

计算RIBPS(Recommended InnoDB Buffer Pool Size)基于所有InnoDB数据大小和额外60%索引大小.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
 FROM information_schema.tables WHERE engine='InnoDB') A;

实际占用大小为:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM (SELECT variable_value PagesData FROM information_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_data') A,(SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name='Innodb_page_size') B;

利用多核CPU

# 无限并发
innodb_thread_concurrency = 0
# 下面两个值最大设置为64
innodb_read_io_threads
innodb_write_io_threads

检查当前慢查询并运行EXPLAIN

mysql> SHOW FULL PROCESSLIST;
# 找到上面经常出现的SQL语句
EXPLAIN SQL-Statement

状态sending data意味着正在等待从磁盘或内存读取数据并发送出去,即 reading and filtering data.

PMM的MySQL Query Response Time仪表盘

PMM(Percona Monitoring and Management)的开源监控工具还是很好用的.

安装PMM服务端

建议使用docker的方式

# 获取镜像
docker pull percona/pmm-server:2
# 创建pmm-data容器以持久化数据
docker create \
   -v /srv \
   --name pmm-data \
   percona/pmm-server:2 /bin/true

# 创建并运行pmm-server容器
docker run -d \
   -p 80:80 \
   -p 443:443 \
   --volumes-from pmm-data \
   --name pmm-server \
   --restart always \
   percona/pmm-server:2

配置客户端 (在运行MySQL的主机)

# CentOS
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install pmm2-client
# Ubuntu 先获取
# wget https://www.percona.com/downloads/pmm2/2.2.0/binary/debian/bionic/x86_64/pmm2-client_2.2.0-6.bionic_amd64.deb

# 配置
pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.100.1:443
# 增加MySQL监控
pmm-admin add mysql --query-source=slowlog --username=pmm --password=pmm
# 查看当前状态
pmm-admin list

pmm-admin使用

  • pmm-admin ping
  • pmm-admin config
  • pmm-admin info
  • pmm-admin stop —all
  • pmm-admin uninstall

设置以记录Response Time分布

安装必要的插件:

mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME 'query_response_time.so';
mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';
mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_READ SONAME 'query_response_time.so';
mysql> INSTALL PLUGIN QUERY_RESPONSE_TIME_WRITE SONAME 'query_response_time.so';

# 检查当前安装插件状态
mysql> SHOW PLUGINS;

开启数据收集:

SET GLOBAL query_response_time_stats = 'ON';
# 检查是否生效
show variables like '%query_response_time%';

资源