MySQL性能调整

Mr. Neo Chen (陈景峯), netkiller, BG7NYT


中国广东省深圳市龙华新区民治街道溪山美地
518131
+86 13113668890


版权声明

转载请与作者联系，转载时请务必标明文章原始出处和作者信息及本声明。
文档出处:
http://netkiller.github.io
http://netkiller.sourceforge.net

微信扫描二维码进入 Netkiller 微信订阅号

QQ群：128659835 请注明“读者”

2017-06-16

摘要
目录

1. 系统部分

max_connections

		
mysql>  show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 4096  |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)

connect_timeout

		
mysql>  show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 30       |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 30       |
+-----------------------------+----------+
12 rows in set (0.00 sec)

1.1. 关于 Sleep 进程

			
mysql> show processlist;
+----+------+-----------------+-----------------+---------+------+-------+------------------+
| Id | User | Host            | db              | Command | Time | State | Info             |
+----+------+-----------------+-----------------+---------+------+-------+------------------+
|  5 | app  | 127.0.0.1:35551 | app.example.com | Sleep   | 4444 |       | NULL             |
|  6 | app  | 127.0.0.1:35552 | app.example.com | Sleep   | 4444 |       | NULL             |
|  7 | app  | 127.0.0.1:35554 | app.example.com | Sleep   | 4444 |       | NULL             |
|  8 | app  | 127.0.0.1:35556 | app.example.com | Sleep   | 4444 |       | NULL             |
|  9 | app  | 127.0.0.1:35555 | app.example.com | Sleep   | 4444 |       | NULL             |
| 10 | app  | 127.0.0.1:35557 | app.example.com | Sleep   | 4444 |       | NULL             |
| 11 | app  | 127.0.0.1:35558 | app.example.com | Sleep   | 4444 |       | NULL             |
| 12 | app  | 127.0.0.1:35559 | app.example.com | Sleep   | 4444 |       | NULL             |
| 13 | app  | 127.0.0.1:35560 | app.example.com | Sleep   | 4444 |       | NULL             |
| 14 | app  | 127.0.0.1:35561 | app.example.com | Sleep   |   35 |       | NULL             |
| 15 | app  | 127.0.0.1:35562 | app.example.com | Sleep   | 4439 |       | NULL             |
| 16 | app  | 127.0.0.1:35563 | app.example.com | Sleep   | 4439 |       | NULL             |
| 17 | app  | 127.0.0.1:35565 | app.example.com | Sleep   | 4439 |       | NULL             |
| 18 | app  | 127.0.0.1:35566 | app.example.com | Sleep   | 4439 |       | NULL             |
| 19 | app  | 127.0.0.1:35567 | app.example.com | Sleep   | 4439 |       | NULL             |
| 20 | app  | 127.0.0.1:35568 | app.example.com | Sleep   | 4439 |       | NULL             |
| 21 | app  | 127.0.0.1:35569 | app.example.com | Sleep   | 4439 |       | NULL             |
| 22 | app  | 127.0.0.1:35570 | app.example.com | Sleep   | 4439 |       | NULL             |
| 23 | app  | 127.0.0.1:35571 | app.example.com | Sleep   | 4439 |       | NULL             |
| 24 | app  | 127.0.0.1:35572 | app.example.com | Sleep   |   38 |       | NULL             |
| 25 | app  | 127.0.0.1:35575 | app.example.com | Sleep   | 4432 |       | NULL             |
| 26 | app  | 127.0.0.1:35576 | app.example.com | Sleep   | 4432 |       | NULL             |
| 27 | app  | 127.0.0.1:35578 | app.example.com | Sleep   | 4432 |       | NULL             |
| 28 | app  | 127.0.0.1:35579 | app.example.com | Sleep   | 4432 |       | NULL             |
| 29 | app  | 127.0.0.1:35580 | app.example.com | Sleep   | 4432 |       | NULL             |
| 30 | app  | 127.0.0.1:54330 | app.example.com | Sleep   |   10 |       | NULL             |
| 31 | app  | 127.0.0.1:1081  | app.example.com | Query   |    0 | init  | show processlist |
+----+------+-----------------+-----------------+---------+------+-------+------------------+
27 rows in set (0.09 sec)

mysql>
			
			
show variables where variable_name in ('wait_timeout','interactive_timeout')

与Sleep线程有关的配置 'wait_timeout'与'interactive_timeout'默认是8小时，也就是说Sleep 8小时才会关闭，如果你的服务器访问量很低，可以适当加快线程回收。

			
mysql>  show variables where variable_name in ('wait_timeout','interactive_timeo
ut');
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
| wait_timeout        | 28800 |
+---------------------+-------+
2 rows in set (0.47 sec)

另外PHP与Java两个群体在数据库处理上不同。PHP主张块连接快释放，让出更多的资源给其他服务。而Java多采用连接池独占数据库资源，连接池设置多少连接数就对应数据库创建多少持久连接，所以你会看到你并没有访问数据库但show processlist;会看到很多Sleep连接。

两个群起所采用的方式，并无好坏，都能达到最终目的。但使用连接池需要注意当MySQL最大连接数等于连接池连接数时来自其他服务器的请求将无法连接。

2. 线程部分

		
mysql> show variables like '%thread_handling%';
+-----------------+---------------------------+
| Variable_name   | Value                     |
+-----------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------+---------------------------+
1 row in set (0.01 sec)


mysql> show status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 16    |
| Threads_connected | 8     |
| Threads_created   | 24    |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

thread_cache_hit=(connections-thread_created)/connections*100%

开启 pool-of-threads 模式

3. 查询缓存部分

查看缓存设置

		
mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.01 sec)

查看缓存状态

		
mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16759680 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 192      |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

4. 缓冲区部分

		
mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
3 rows in set (0.00 sec)

5. 部分

		
mysql> show variables like '%low_priority_updates%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| low_priority_updates | OFF   |
+----------------------+-------+
1 row in set (0.00 sec)


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

mysql> set global low_priority_updates=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

global变量需要退出重新进入才能生效。

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

SESSION 会立即生效

		
mysql> set session low_priority_updates=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'low_priority_updates';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| low_priority_updates | ON    |
+----------------------+-------+
1 row in set (0.00 sec)

6. 默认编码设置

		
character-set-server=utf8
collation_server=utf8_general_ci

7. SECTION