Home | 簡體中文 | 繁體中文 | 雜文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 作品與服務 | Email

MySQL性能調整

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


中國廣東省深圳市龍華新區民治街道溪山美地
518131
+86 13113668890


版權聲明

轉載請與作者聯繫,轉載時請務必標明文章原始出處和作者信息及本聲明。

文檔出處:
http://netkiller.github.io
http://netkiller.sourceforge.net

微信掃瞄二維碼進入 Netkiller 微信訂閲號

QQ群:128659835 請註明“讀者”

2016-03-29

摘要

目錄

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

		

		
		
comments powered by Disqus