mysql部署优化

参数之innodb_buffer_pool_size大小设置

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.01 sec)

那么如何设置该参数大小呢？首先查看运行时buffer pool相关数据指标：

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 57278 |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 65528 |
+--------------------------------+-------+
1 row in set (0.01 sec)

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

上述三项指标的含义如下：

• Innodb_buffer_pool_pages_data：The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
• Innodb_buffer_pool_pages_total：The total size of the InnoDB buffer pool, in pages.
• Innodb_page_size：InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be easily converted to bytes

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

• 当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%
• 当结果 < 95% 则减少 innodb_buffer_pool_size， 建议设置大小为： Innodb_buffer_pool_pages_data Innodb_page_size 1.05 / (102410241024)

以上述SQL查询结果为例计算：

1. 57278/65528*100% = 87.4%
2. 87.4% < 95% 则 57278163841.05 = 985364889

命令如：SET GLOBAL innodb_buffer_pool_size= 985364889：单位byte

慢查询排查

1. show processlist 来查看状况

-- 查询非sleep 的线程
select id, db, user, host, command, time, state, info from information_schema.processlist where command != 'Sleep' order by time desc;
-- 相对应的kill,注意不要在生产环境中使用
-- 查询执行时间超过2分钟的线程，然后拼接成 kill 语句
select concat('kill ', id, ';') from information_schema.processlist where command != 'Sleep' and time > 2*60 order by time desc;


-- 查看表使用情况：
show OPEN TABLES where In_use > 0;

2. 开启慢查询日志，分析日志来统计

   1. 查看相应的变量值; show global variables like '%slow%';
      a. slow_query_log 就是是否打开慢查询日志的开关，set global slow_query_log=0/1 来进行开启或关闭；
      b. slow_query_log_file 慢查询的日志文件地址；
      c. long_query_time 慢查询的时间定义；
      d. log_queries_not_using_indexes 记录没有用索引的查询
   2. 使用mysqldumpslow 工具来分析慢查询日志；
      a. /bin/mysqldumpslow -s at -g 'UNIX_TIMESTAMP(update_time) >' -t 50 -a data/slow.log

mysql 用户连接数配置

1. 配置参数
   a. max_connections=5000 总连接数
   b. max_user_connections=1000 每个用户连接数；
2. 具体某个用户的连接数定制
   update mysql.user set max_user_connections=600 where user='zed'; FLUSH PRIVILEGES; 注意一定 FLUSH PRIVILEGES 才会生效，否则只是存入数据库，没有写到内存；