mysql性能優化

cymt 9年前發布 | 42K 次閱讀 MySQL 數據庫服務器

性能優化是通過某些有效的方法來提高MySQL的運行速度，減少占用的磁盤空間。性能優化包含很多方面，例如優化查詢速度，優化更新速度和優化MySQL服務器等。本文介紹方法的主要有：

優化查詢
優化數據庫結構
優化MySQL服務器

數據庫管理人員可以使用SHOW STATUS語句來查詢MySQL數據庫的性能。語法：SHOW STATUE LIKE ‘value’；其中value參數是常用的幾個統計參數。

Connections：連接MySQL服務器的次數

Uptime：MySQL服務器的上線時間；

Slow_queries：慢查詢的次數；

Com_select：查詢操做的次數；

Com_insert：插入操作的次數；

Com_delete：刪除操作的次數；

Com_update：更新操作的次數；

1優化查詢

查詢操作是最頻繁的操作，提高了查詢速度可以有效提高MySQL數據庫的性能。

首先要對查詢語句進行分析，分析查詢語句的命令是EXPLAIN語句和DESCRIBE語句。比如 EXPLAIN SELECT * FROM student \G；

索引可以快速定位表中的某條記錄。使用索引也可以提高數據庫查詢的速度，從而提高數據庫的性能。如果不使用索引，查詢語句將表中的所有字段。這樣查詢的速度會很慢。如果使用了索引，查詢語句只會查詢索引字段。這樣就減少查詢的記錄數，達到提高查詢效率的目的。

現在看一個查詢語句中沒有索引的使用情況：

SELECT * FROM student WHERE name = ‘張三’；這樣會對student表中的所有數據都查詢一下，對比一下name的字段是否是張三。

然后我們在name字段上建立一個名為index_name的索引：

CREATE INDEX index_name ON student（name）；

現在name字段上面已經有索引了，再進行該select語句查詢的速度就非常快了，不需要遍歷整個表。

但是有些時候即使查詢時使用的是索引，但索引并沒有起作用。比如使用了LIKE關鍵字進行查詢時，如果匹配字符串的第一個字符為‘%’，索引不會被使用。如果‘%’不是在第一個位置，索引就會被使用。

另一種情況是在表的多個字段上創建一個索引，比如

CREATE INDEX index ON student（birth，department）；這樣只有查詢語句條件中使用字段name時，索引才會被用到。因為name字段是多列索引的第一個字段，只有查詢條件中使用了name字段才會使索引index起作用。

2優化子查詢

很多查詢中需要使用子查詢。子查詢可以使查詢語句很靈活，但子查詢的執行效率不高。MySQL需要為內層查詢語句的查詢結果建立一個臨時表。然后外層查詢語句在臨時表中查詢記錄。查詢完畢后，MySQL需要插銷這些臨時表。所以在MySQL中可以使用連接查詢來代替子查詢。連接查詢不需要建立臨時表，其速度比子查詢要快。

3優化數據庫結構

1將字段很多的表分解成多個表

有些表在設計時設置了很多的字段。但是這個表中的有些字段的使用頻率很低。當這個表的數據量很大時，查詢數據的速度就會很慢。對于這種字段特別多的并且有字段的使用頻率不高的表，就可以將其分解成多個表。

2增加中間表

有時需要經常查詢某兩個表中的幾個字段。如果經常進行聯表查詢，就會降低MySQL數據庫的查詢速度。對于這種情況可以建立中間表來提高查詢速度。

先分析經常需要同時查詢那幾個表中的那些字段。然后將這些字段建立一個中間表，并將原來那幾個表的數據插入到中間表中，之后就可以使用中間表來進行查詢和統計了。

3增加冗余字段

設計數據庫表時盡量讓表達到三范式。但是，有時為了提高查詢速度，可以有意識的在表中增加冗余字段。表的規范程度越高，表與表之間的關系就越多，查詢時可能經常需要在多個表之間進行連接查詢，而進行連接查詢會降低查詢速度。比如，學生的信息存儲在student表中，院系的信息存儲在department 表中，通過student表中的dept_id字段與department表建立關聯關系。如果要查詢一個學生所在系的名稱，必須從student表中拿到dept_id字段，然后根據這個編號去department表中查找系的名稱。如果需要經常需要進行這個操作的話，連接查詢會浪費很多時間。因此可以在student表中增加一個冗余字段dept_name，這樣就不用每次都進行連接查詢了。其實就是一切以業務為主。

4優化插入記錄的速度

插入記錄時，索引和唯一性校驗都會影響到插入記錄的速度。而且，一次插入多條記錄和多次插入一條記錄所消耗的時間是不一樣的。根據這些情況，分別進行不同的優化。

禁用索引：插入記錄時，MySQL會根據表的索引對插入的記錄進行排序。如果插入大量數據時，這些排序會降低插入的速度。為了解決這種情況，在插入記錄之前先禁用索引。等插入之后再啟用索引。對于新創建的表，可以先不創建索引，等記錄都導入以后再創建索引。這樣可以提高導入數據的速度。

ALTER TABLE 表名 DISABLE KEYS；

ALTER TABLE 表名 ENABLE KEYS；

優化INSERT語句：當大量插入數據時，建議使用一個INSERT語句插入多條記錄，而不是使用多次INSERT語句。這樣可以減少與數據庫之間的連接等操作。

5分析表，檢查表，和優化表

分析表的主要作用是分析關鍵字的分布。檢查表的作用是檢查表是否存在錯誤。優化表主要作用是消除刪除或者更新造成的空間浪費。

分析表 ANALYZE TABLE 表名；使用ANALYZE TABLE分析表的過程中，數據庫系統會對表加一個只讀鎖。在分析表的過程中，只能讀取表的內容，不能插入和更新表的內容。ANALYZE TABLE 語句能夠分析InnoDB和MyISAM類型的表。

檢查表使用CHECK TABLE語句。在執行過程中也會給表加上只讀鎖。

優化表使用OPTIMIZE TABLE語句。只能優化表中的VARCHAR,BLOB,TEXT類型的字段。OPTIMIZE TABLE語句可以消除刪除和更新造成的磁盤碎片，從而減少空間浪費。因為如果一個表使用了TEXT或者BLOB這樣的數據類型，那么更新，刪除等操作就會造成磁盤空間的浪費。因為，更新和刪除操作以后，以前分配的磁盤空間不會自動回收。使用OPTIMIZE TABLE語句可以將這些磁盤碎片整理出來，以便再利用。

4 優化MySQL服務器

硬件上的優化：增加內存和提高磁盤讀寫速度，都可以提高MySQL數據庫的查詢，更新的速度。另一種提高MySQL性能的方式是使用多塊磁盤來存儲數據。因為可以從多塊磁盤上并行讀取數據，這樣可以提高讀取數據的速度。

MySQL參數的優化：內存中會為MySQL保留部分的緩沖區。這些緩沖區可以提高MySQL的速度。緩沖區的大小都是在MySQL的配置文件中進行設置的。

下面對幾個重要的參數進行詳細介紹：

key_buffer_size：表示索引緩存的大小。這個值越大，使用索引進行查詢的速度就越快
table_cache：表示同時打開的表的個數。這個值越大，能同時打開的表的個數就越多。這個值不是越大越好，因為同時打開的表過多會影響操作系統的性能。
query_cache_size：表示查詢緩沖區的大小。使用查詢緩存區可以提高查詢的速度。這個方式只使用與修改操作少且經常執行相同的查詢操作的情況；默認值是0.
Query_cache_type：表示查詢緩存區的開啟狀態。0表示關閉，1表示開啟。
Max_connections：表示數據庫的最大連接數。這個連接數不是越大越好，因為連接會浪費內存的資源。
Sort_buffer_size：排序緩存區的大小，這個值越大，排序就越快。
Innodb_buffer_pool_size：表示InnoDB類型的表和索引的最大緩存。這個值越大，查詢的速度就會越快。這個值太大了就會影響操作系統的性能。

合理配置這些參數可以提高MySQL數據庫的性能。配置完參數后，需要重啟MySQL服務才會生效

1. 要確保有足夠的內存

數據庫能夠高效的運行，最關建的因素需要內存足更大了，能緩存住數據，更新也可以在內存先完成。但不同的業務對內存需要強度不一樣，一推薦內存要占到數據的15-25%的比例，特別的熱的數據，內存基本要達到數據庫的80%大小。

2. 需要更多更快的CPU

MySQL 5.6可以利用到64個核，而MySQL每個query只能運行在一個CPU上，所以要求更多的CPU，更快的CPU會更有利于并發。

3. 要選擇合適的操作系統

在官方建議估計最推薦的是Solaris, 從實際生產中看CentOS, REHL都是不錯的選擇，推薦使用CentOS, REHL 版本為6以后的，當然Oracle Linux也是一個不錯的選擇。雖然從MySQL 5.5后對Windows做了優化，但也不推薦在高并發環境中使用windows.

4. 合理的優化系統的參數

更改文件句柄 ulimit –n 默認1024 太小

進程數限制 ulimit –u 不同版本不一樣

禁掉NUMA numctl –interleave=all

5. 選擇合適的內存分配算法

默認的內存分配就是c的malloc 現在也出現許多優化的內存分配算法:

jemalloc and tcmalloc

從MySQL 5.5后支持聲明內存儲方法。

[mysqld_safe]

malloc-lib = tcmalloc

或是直接指到so文件

[mysqld_safe]

malloc-lib=/usr/local/lib/libtcmalloc_minimal.so

6. 使用更快的存儲設備ssd或是固態卡

存儲介質十分影響MySQL的隨機讀取，寫入更新速度。新一代存儲設備固態ssd及固態卡的出現也讓MySQL 大放異彩，也是淘寶在去IOE中干出了一個漂亮仗。

7. 選擇良好的文件系統

推薦XFS, Ext4,如果還在使用ext2,ext3的同學請盡快升級別。　推薦XFS，這個也是今后一段時間Linux會支持一個文件系統。

文件系統強烈推薦: XFS

8. 優化掛載文件系統的參數

掛載XFS參數：

(rw, noatime,nodiratime,nobarrier)

掛載ext4參數：

ext4 (rw,noatime,nodiratime,nobarrier,data=ordered)

如果使用SSD或是固態盤需要考慮：

? innodb_page_size = 4K

? Innodb_flush_neighbors = 0

9. 選擇適合的IO調度

正常請下請使用deadline 默認是noop

echo dealine >/sys/block/{DEV-NAME}/queue/scheduler

10. 選擇合適的Raid卡Cache策略

請使用帶電的Raid，啟用WriteBack，　對于加速redo log ,binary log, data file都有好處。

11. 禁用Query Cache

Query　Cache在Innodb中有點雞肋，Innodb的數據本身可以在Innodb buffer pool中緩存，Query Cache屬于結果集緩存，如果開啟Query Cache更新寫入都要去檢查query cache反而增加了寫入的開銷。

在MySQL　5.6中Query　cache是被禁掉了。

12. 使用Thread Pool

現在一個數據對應5個以上App場景比較，但MySQL有個特性隨著連接增多的情況下性能反而下降，所以對于連接超過200的以后場景請考慮使用thread pool. 這是一個偉大的發明。

13. 合理調整內存

13.1 減少連接的內存分配

連接可以用thread_cache_size緩存，觀查屬于比較屬不如thread pool給力。數據庫在連上分配的內存如下：

max_used_connections * (

read_buffer_size +

read_rnd_buffer_size +

join_buffer_size +

sort_buffer_size +

binlog_cache_size +

thread_stack +

2 * net_buffer_length …

)

13.2 使較大的buffer pool

要把60-80%的內存分給innodb_buffer_pool_size. 這個不要超過數據大小了，另外也不要分配超過80%不然會利用到swap.

14. 合理選擇LOG刷新機制

Redo Logs:

– innodb_flush_log_at_trx_commit = 1 // 最安全

– innodb_flush_log_at_trx_commit = 2 // 　較好性能

– innodb_flush_log_at_trx_commit = 0 // 　最好的情能

binlog :

binlog_sync = 1 需要group commit支持，如果沒這個功能可以考慮binlog_sync=0來獲得較佳性能。

數據文件：

innodb_flush_method = O_DIRECT

15. 請使用Innodb表

可以利用更多資源，在線alter操作有所提高。目前也支持非中文的full text，同時支持Memcache API訪問。目前也是MySQL最優秀的一個引擎。

如果你還在MyISAM請考慮快速轉換。

16. 設置較大的Redo log

以前Percona 5.5和官方MySQL 5.5比拼性能時，勝出的一個Tips就是分配了超過4G的Redo log ,而官方MySQL5.5 redo log不能超過4G. 從 MySQL 5.6后可以超過4G了，通常建Redo log加起來要超過500M。可以通過觀查redo log產生量，分配Redo log大于一小時的量即可。

17. 優化磁盤的IO

innodb_io_capactiy 在sas 15000轉的下配置800就可以了，在ssd下面配置2000以上。

在MySQL 5.6:

innodb_lru_scan_depth = innodb_io_capacity / innodb_buffer_pool_instances

innodb_io_capacity_max = min(2000, 2 * innodb_io_capacity)

18. 使用獨立表空間

目前來看新的特性都是獨立表空間支持:

truncate table 表空間回收

表空間傳輸

較好的去優化碎片等管理性能的增加，

整體上來看使用獨立表空間是沒用的。

19. 配置合理的并發

innodb_thread_concurrency =并發這個參數在Innodb中變化也是最頻繁的一個參數。不同的版本，有可能不同的小版本也有變動。一般推薦：

在使用thread pool 的情況下：

innodb_thread_concurrency = 0 就可以了。

如果在沒有thread pool的情況下：

5.5 推薦：innodb_thread_concurrency =16 – 32

5.6 推薦innodb_thread_concurrency = 36

20. 優化事務隔離級別

默認是 Repeatable read

推薦使用Read committed binlog格式使用mixed或是Row

較低的隔離級別 = 較好的性能

21. 注重監控

任環境離不開監控，如果少了監控，有可能就會陷入盲人摸象。推薦zabbix+mpm構建監控。

網上有不少mysql 性能優化方案，不過，mysql的優化同sql server相比，更為麻煩與負責，同樣的設置，在不同的環境下，由于內存，訪問量，讀寫頻率，數據差異等等情況，可能會出現不同的結果，因此簡單地根據某個給出方案來配置mysql是行不通的，最好能使用 status信息對mysql進行具體的優化，網上找了一篇文章，分頁分得亂七八糟的，只能轉到博客。

mysql> show global status;

可以列出mysql服務器運行各種狀態值，另外，查詢mysql服務器配置信息語句：

mysql> show variables;

一、慢查詢

mysql> show variables like ‘%slow%‘;
+------------------+-------+
| variable_name　　　　 | value |
+------------------+-------+
| log_slow_queries | on　　　　 |
| slow_launch_time | 2　　　　　 |
+------------------+-------+

mysql> show global status like ‘%slow%‘;
+---------------------+-------+
| variable_name　　　　　　　 | value |
+---------------------+-------+
| slow_launch_threads | 0　　　　　 |
| slow_queries　　　　　　　　 | 4148 |
+---------------------+-------+

配置中打開了記錄慢查詢，執行時間超過2秒的即為慢查詢，系統顯示有4148個慢查詢，你可以分析慢查詢日志，找出有問題的sql語句，慢查詢時間不宜設置過長，否則意義不大，最好在5秒以內，如果你需要微秒級別的慢查詢，可以考慮給mysql打補丁：http://www.percona.com /docs/wiki/release:start，記得找對應的版本。

打開慢查詢日志可能會對系統性能有一點點影響，如果你的mysql是主－從結構，可以考慮打開其中一臺從服務器的慢查詢日志，這樣既可以監控慢查詢，對系統性能影響又小。

二、連接數

經常會遇見”mysql: error 1040: too many connections”的情況，一種是訪問量確實很高，mysql服務器抗不住，這個時候就要考慮增加從服務器分散讀壓力，另外一種情況是mysql配置文件中max_connections值過小：

mysql> show variables like ‘max_connections‘;
+-----------------+-------+
| variable_name　　　 | value |
+-----------------+-------+
| max_connections | 256　　 |
+-----------------+-------+

這臺mysql服務器最大連接數是256，然后查詢一下服務器響應的最大連接數：

mysql> show global status like ‘max_used_connections‘;

mysql服務器過去的最大連接數是245，沒有達到服務器連接數上限256，應該沒有出現1040錯誤，比較理想的設置是

max_used_connections / max_connections * 100% ≈ 85%

最大連接數占上限連接數的85％左右，如果發現比例在10%以下，mysql服務器連接數上限設置的過高了。

三、key_buffer_size

key_buffer_size是對myisam表性能影響最大的一個參數，下面一臺以myisam為主要存儲引擎服務器的配置：

mysql> show variables like ‘key_buffer_size‘;+-----------------+------------+
| variable_name　　　 | value　　　　　　 |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+

分配了512mb內存給key_buffer_size，我們再看一下key_buffer_size的使用情況：

mysql> show global status like ‘key_read%‘;
+------------------------+-------------+
| variable_name　　　　　　　　　　 | value　　　　　　　 |
+------------------------+-------------+
| key_read_requests　　　　　　 | 27813678764 |
| key_reads　　　　　　　　　　　　　　 | 6798830　　　　　 |
+------------------------+-------------+

一共有27813678764個索引讀取請求，有6798830個請求在內存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：

key_cache_miss_rate ＝ key_reads / key_read_requests * 100%

比如上面的數據，key_cache_miss_rate為0.0244%，4000個索引讀取請求才有一個直接讀硬盤，已經很bt 了，key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬盤），如果key_cache_miss_rate在 0.01%以下的話，key_buffer_size分配的過多，可以適當減少。

mysql服務器還提供了key_blocks_*參數：

mysql> show global status like ‘key_blocks_u%‘;
+------------------------+-------------+
| variable_name　　　　　　　　　　 | value　　　　　　　 |
+------------------------+-------------+
| key_blocks_unused　　　　　　 | 0　　　　　　　　　　　 |
| key_blocks_used　　　　　　　　 | 413543　　　　　　 |
+------------------------+-------------+

key_blocks_unused表示未使用的緩存簇(blocks)數，key_blocks_used表示曾經用到的最大的blocks數，比如這臺服務器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設置：

key_blocks_used / (key_blocks_unused + key_blocks_used) * 100% ≈ 80%

四、臨時表

mysql> show global status like ‘created_tmp%‘;
+-------------------------+---------+
| variable_name　　　　　　　　　　　 | value　　　 |
+-------------------------+---------+
| created_tmp_disk_tables | 21197　　　 |
| created_tmp_files　　　　　　　 | 58　　　　　　 |
| created_tmp_tables　　　　　　 | 1771587 |
+-------------------------+---------+

每次創建臨時表，created_tmp_tables增加，如果是在磁盤上創建臨時表，created_tmp_disk_tables也增加,created_tmp_files表示mysql服務創建的臨時文件文件數，比較理想的配置是：

created_tmp_disk_tables / created_tmp_tables * 100% <= 25%比如上面的服務器created_tmp_disk_tables / created_tmp_tables * 100% ＝ 1.20%，應該相當好了。我們再看一下mysql服務器對臨時表的配置：

mysql> show variables where variable_name in (‘tmp_table_size‘, ‘max_heap_table_size‘);
+---------------------+-----------+
| variable_name　　　　　　　 | value　　　　　 |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
| tmp_table_size　　　　　　 | 536870912 |
+---------------------+-----------+

只有256mb以下的臨時表才能全部放內存，超過的就會用到硬盤臨時表。

五、open table情況

mysql> show global status like ‘open%tables%‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| open_tables　　　 | 919　　　 |
| opened_tables | 1951　 |
+---------------+-------+

open_tables表示打開表的數量，opened_tables表示打開過的表數量，如果opened_tables數量過大，說明配置中 table_cache(5.1.3之后這個值叫做table_open_cache)值可能太小，我們查詢一下服務器table_cache值：

mysql> show variables like ‘table_cache‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| table_cache　　　 | 2048　 |
+---------------+-------+

比較合適的值為：

open_tables / opened_tables * 100% >= 85%

open_tables / table_cache * 100% <= 95%

六、進程使用情況

mysql> show global status like ‘thread%‘;
+-------------------+-------+
| variable_name　　　　　 | value |
+-------------------+-------+
| threads_cached　　　　 | 46　　　　 |
| threads_connected | 2　　　　　 |
| threads_created　　　 | 570　　　 |
| threads_running　　　 | 1　　　　　 |
+-------------------+-------+

如果我們在mysql服務器配置文件中設置了thread_cache_size，當客戶端斷開之后，服務器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀（前提是緩存數未達上限）。threads_created表示創建過的線程數，如果發現threads_created值過大的話，表明 mysql服務器一直在創建線程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值，查詢服務器 thread_cache_size配置：

mysql> show variables like ‘thread_cache_size‘;
+-------------------+-------+
| variable_name　　　　　 | value |
+-------------------+-------+
| thread_cache_size | 64　　　　 |
+-------------------+-------+

示例中的服務器還是挺健康的。

七、查詢緩存(query cache)

mysql> show global status like ‘qcache%‘;
+-------------------------+-----------+
| variable_name　　　　　　　　　　　 | value　　　　　 |
+-------------------------+-----------+
| qcache_free_blocks　　　　　　 | 22756　　　　　 |
| qcache_free_memory　　　　　　 | 76764704　 |
| qcache_hits　　　　　　　　　　　　　 | 213028692 |
| qcache_inserts　　　　　　　　　　 | 208894227 |
| qcache_lowmem_prunes　　　　 | 4010916　　　 |
| qcache_not_cached　　　　　　　 | 13385031　 |
| qcache_queries_in_cache | 43560　　　　　 |
| qcache_total_blocks　　　　　 | 111212　　　　 |
+-------------------------+-----------+

mysql查詢緩存變量解釋：

qcache_free_blocks：緩存中相鄰內存塊的個數。數目大說明可能有碎片。flush query cache會對緩存中的碎片進行整理，從而得到一個空閑塊。

qcache_free_memory：緩存中的空閑內存。

qcache_hits：每次查詢在緩存中命中時就增大

qcache_inserts：每次插入一個查詢時就增大。命中次數除以插入次數就是不中比率。

qcache_lowmem_prunes：緩存出現內存不足并且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者內存很少。（上面的 free_blocks和free_memory可以告訴您屬于哪種情況）

qcache_not_cached：不適合進行緩存的查詢的數量，通常是由于這些查詢不是 select 語句或者用了now()之類的函數。

qcache_queries_in_cache：當前緩存的查詢（和響應）的數量。

qcache_total_blocks：緩存中塊的數量。

我們再查詢一下服務器關于query_cache的配置：

mysql> show variables like ‘query_cache%‘;
+------------------------------+-----------+
| variable_name　　　　　　　　　　　　　　　　 | value　　　　　 |
+------------------------------+-----------+
| query_cache_limit　　　　　　　　　　　　 | 2097152　　　 |
| query_cache_min_res_unit　　　　　 | 4096　　　　　　 |
| query_cache_size　　　　　　　　　　　　　 | 203423744 |
| query_cache_type　　　　　　　　　　　　　 | on　　　　　　　　 |
| query_cache_wlock_invalidate | off　　　　　　　 |
+------------------------------+-----------+

各字段的解釋：

query_cache_limit：超過此大小的查詢將不緩存

query_cache_min_res_unit：緩存塊的最小大小

query_cache_size：查詢緩存大小

query_cache_type：緩存類型，決定緩存什么樣的查詢，示例中表示不緩存 select sql_no_cache 查詢

query_cache_wlock_invalidate：當有其他客戶端正在對myisam表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成再讀表獲取結果。

query_cache_min_res_unit的配置是一柄”雙刃劍”，默認是4kb，設置值大對大數據查詢有好處，但如果你的查詢都是小數據查詢，就容易造成內存碎片和浪費。

查詢緩存碎片率 = qcache_free_blocks / qcache_total_blocks * 100%

如果查詢緩存碎片率超過20%，可以用flush query cache整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數據量的話。

查詢緩存利用率 = (query_cache_size - qcache_free_memory) / query_cache_size * 100%

查詢緩存利用率在25%以下的話說明query_cache_size設置的過大，可適當減小；查詢緩存利用率在80％以上而且qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。

查詢緩存命中率 = (qcache_hits - qcache_inserts) / qcache_hits * 100%

示例服務器查詢緩存碎片率＝ 20.46％，查詢緩存利用率＝ 62.26％，查詢緩存命中率＝ 1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。

八、排序使用情況

mysql> show global status like ‘sort%‘;
+-------------------+------------+
| variable_name　　　　　 | value　　　　　　 |
+-------------------+------------+
| sort_merge_passes | 29　　　　　　　　　 |
| sort_range　　　　　　　　 | 37432840　　　 |
| sort_rows　　　　　　　　　 | 9178691532 |
| sort_scan　　　　　　　　　 | 1860569　　　　 |
+-------------------+------------+

sort_merge_passes 包括兩步。mysql 首先會嘗試在內存中做排序，使用的內存大小由系統變量 sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到內存中，mysql 就會把每次在內存中排序的結果存到臨時文件中，等 mysql 找到所有記錄之后，再把臨時文件中的記錄做一次排序。這再次排序就會增加 sort_merge_passes。實際上，mysql 會用另一個臨時文件來存再次排序的結果，所以通常會看到 sort_merge_passes 增加的數值是建臨時文件數的兩倍。因為用到了臨時文件，所以速度可能會比較慢，增加 sort_buffer_size 會減少 sort_merge_passes 和創建臨時文件的次數。但盲目的增加 sort_buffer_size 并不一定能提高速度，見 how fast can you sort data with mysql?（引自http://qroom.blogspot.com/2007/09/mysql-select-sort.html，貌似被墻）

另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的好處，參見：http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/

九、文件打開數(open_files)

mysql> show global status like ‘open_files‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| open_files　　　　 | 1410　 |
+---------------+-------+

mysql> show variables like ‘open_files_limit‘;
+------------------+-------+
| variable_name　　　　 | value |
+------------------+-------+
| open_files_limit | 4590　 |
+------------------+-------+

比較合適的設置：open_files / open_files_limit * 100% <= 75％

十、表鎖情況

mysql> show global status like ‘table_locks%‘;
+-----------------------+-----------+
| variable_name　　　　　　　　　 | value　　　　　 |
+-----------------------+-----------+
| table_locks_immediate | 490206328 |
| table_locks_waited　　　　 | 2084912　　　 |
+-----------------------+-----------+

table_locks_immediate表示立即釋放表鎖數，table_locks_waited表示需要等待的表鎖數，如果 table_locks_immediate / table_locks_waited > 5000，最好采用innodb引擎，因為innodb是行鎖而myisam是表鎖，對于高并發寫入的應用innodb效果會好些。示例中的服務器 table_locks_immediate / table_locks_waited ＝ 235，myisam就足夠了。

十一、表掃描情況

mysql> show global status like ‘handler_read%‘;
+-----------------------+-------------+
| variable_name　　　　　　　　　 | value　　　　　　　 |
+-----------------------+-------------+
| handler_read_first　　　　 | 5803750　　　　　 |
| handler_read_key　　　　　　 | 6049319850　 |
| handler_read_next　　　　　 | 94440908210 |
| handler_read_prev　　　　　 | 34822001724 |
| handler_read_rnd　　　　　　 | 405482605　　　 |
| handler_read_rnd_next | 18912877839 |
+-----------------------+-------------+

各字段解釋參見http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html，調出服務器完成的查詢請求次數：

mysql> show global status like ‘com_select‘;
+---------------+-----------+
| variable_name | value　　　　　 |
+---------------+-----------+
| com_select　　　　 | 222693559 |
+---------------+-----------+

計算表掃描率：

表掃描率＝ handler_read_rnd_next / com_select

如果表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8mb。

后記：

文中提到一些數字都是參考值，了解基本原理就可以，除了mysql提供的各種status值外，操作系統的一些性能指標也很重要，比如常用的top,iostat等，尤其是iostat，現在的系統瓶頸一般都在磁盤io上，關于iostat的使用

一、CPU

首先從CPU說起。
你仔細檢查的話，有些服務器上會有的一個有趣的現象：你cat /proc/cpuinfo時，會發現CPU的頻率竟然跟它標稱的頻率不一樣：

    #cat /proc/cpuinfo 
    processor : 5
    model name : Intel(R) Xeon(R) CPU E5-2620 0 @2.00GHz ...
    cpu MHz : 1200.000

這個是Intel E5-2620的CPU，他是2.00G * 24的CPU，但是，我們發現第5顆CPU的頻率為1.2G。
這是什么原因列？
這些其實都源于CPU最新的技術：節能模式。操作系統和CPU硬件配合，系統不繁忙的時候，為了節約電能和降低溫度，它會將CPU降頻。這對環保人士和抵制地球變暖來說是一個福音，但是對MySQL來說，可能是一個災難。
為了保證MySQL能夠充分利用CPU的資源，建議設置CPU為最大性能模式。這個設置可以在BIOS和操作系統中設置，當然，在BIOS中設置該選項更好，更徹底。由于各種BIOS類型的區別，設置為CPU為最大性能模式千差萬別，我們這里就不具體展示怎么設置了。

二、內存

然后我們看看內存方面，我們有哪些可以優化的。

i)我們先看看numa
非一致存儲訪問結構 (NUMA ： Non-Uniform Memory Access) 也是最新的內存管理技術。它和對稱多處理器結構 (SMP ： Symmetric Multi-Processor) 是對應的。簡單的隊別如下：

如圖所示，詳細的NUMA信息我們這里不介紹了。但是我們可以直觀的看到：SMP訪問內存的都是代價都是一樣的；但是在NUMA架構下，本地內存的訪問和非本地內存的訪問代價是不一樣的。對應的根據這個特性，操作系統上，我們可以設置進程的內存分配方式。目前支持的方式包括：


    --interleave=nodes 
 --membind=nodes 
 --cpunodebind=nodes 
 --physcpubind=cpus 
 --localalloc 
 --preferred=node

簡而言之，就是說，你可以指定內存在本地分配，在某幾個CPU節點分配或者輪詢分配。除非是設置為--interleave=nodes輪詢分配方式，即內存可以在任意NUMA節點上分配這種方式以外。其他的方式就算其他NUMA節點上還有內存剩余，Linux也不會把剩余的內存分配給這個進程，而是采用 SWAP的方式來獲得內存。有經驗的系統管理員或者DBA都知道SWAP導致的數據庫性能下降有多么坑爹。
所以最簡單的方法，還是關閉掉這個特性。
關閉特性的方法，分別有：可以從BIOS，操作系統，啟動進程時臨時關閉這個特性。
a)由于各種BIOS類型的區別，如何關閉NUMA千差萬別，我們這里就不具體展示怎么設置了。
b)在操作系統中關閉，可以直接在/etc/grub.conf的kernel行最后添加numa=off，如下所示：

kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=/dev/mapper/VolGroup-root rd_NO_LUKS LANG=en_US.UTF-8 rd_LVM_LV=VolGroup/root rd_NO_MD quiet SYSFONT=latarcyrheb-sun16 rhgb crashkernel=auto rd_LVM_LV=VolGroup/swap rhgb crashkernel=auto quiet KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM  numa=off

另外可以設置 vm.zone_reclaim_mode=0盡量回收內存。
c)啟動MySQL的時候，關閉NUMA特性：

  numactl --interleave=all  mysqld &

當然，最好的方式是在BIOS中關閉。

ii)我們再看看vm.swappiness。

vm.swappiness是操作系統控制物理內存交換出去的策略。它允許的值是一個百分比的值，最小為0，最大運行100，該值默認為60。vm.swappiness設置為0表示盡量少swap，100表示盡量將inactive的內存頁交換出去。
具體的說：當內存基本用滿的時候，系統會根據這個參數來判斷是把內存中很少用到的inactive 內存交換出去，還是釋放數據的cache。cache中緩存著從磁盤讀出來的數據，根據程序的局部性原理，這些數據有可能在接下來又要被讀取；inactive 內存顧名思義，就是那些被應用程序映射著，但是“長時間”不用的內存。
我們可以利用vmstat看到inactive的內存的數量：

#vmstat -an 1 
 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- 
 r b swpd free  inact  active si so bi bo in cs us sy id wa st 
 1 0 0 27522384 326928 1704644 0 0 0 153 11 10 0 0 100 0 0 
 0 0 0 27523300 326936 1704164 0 0 0 74 784 590 0 0 100 0 0 
 0 0 0 27523656 326936 1704692 0 0 8 8 439 1686 0 0 100 0 0 
 0 0 0 27524300 326916 1703412 0 0 4 52 198 262 0 0 100 0 0

通過/proc/meminfo 你可以看到更詳細的信息：

#cat /proc/meminfo | grep -i inact 
 Inactive: 326972 kB 
 Inactive(anon): 248 kB 
 Inactive(file): 326724 kB

這里我們對不活躍inactive內存進一步深入討論。Linux中，內存可能處于三種狀態：free，active和inactive。眾所周知，Linux Kernel在內部維護了很多LRU列表用來管理內存，比如LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE , LRU_ACTIVE_FILE, LRU_UNEVICTABLE。其中LRU_INACTIVE_ANON, LRU_ACTIVE_ANON用來管理匿名頁，LRU_INACTIVE_FILE , LRU_ACTIVE_FILE用來管理page caches頁緩存。系統內核會根據內存頁的訪問情況，不定時的將活躍active內存被移到inactive列表中，這些inactive的內存可以被交換到swap中去。
一般來說，MySQL，特別是InnoDB管理內存緩存，它占用的內存比較多，不經常訪問的內存也會不少，這些內存如果被Linux錯誤的交換出去了，將浪費很多CPU和IO資源。 InnoDB自己管理緩存，cache的文件數據來說占用了內存，對InnoDB幾乎沒有任何好處。
所以，我們在MySQL的服務器上最好設置vm.swappiness=0。

我們可以通過在sysctl.conf中添加一行：

echo "vm.swappiness = 0" >>/etc/sysctl.conf

并使用sysctl -p來使得該參數生效。

三、文件系統

最后，我們看一下文件系統的優化
i)我們建議在文件系統的mount參數上加上noatime，nobarrier兩個選項。

用noatime mount的話，文件系統在程序訪問對應的文件或者文件夾時，不會更新對應的access time。一般來說，Linux會給文件記錄了三個時間，change time, modify time和access time。
我們可以通過stat來查看文件的三個時間：

stat libnids-1.16.tar.gz 
 File: `libnids-1.16.tar.gz' 
 Size: 72309 Blocks: 152 IO Block: 4096 regular file 
 Device: 302h/770d Inode: 4113144 Links: 1 
 Access: (0644/-rw-r--r--) Uid: ( 0/ root) Gid: ( 0/ root)  Access  : 2008-05-27 15:13:03.000000000 +0800 
 Modify: 2004-03-10 12:25:09.000000000 +0800 
 Change: 2008-05-27 14:18:18.000000000 +0800

其中access time指文件最后一次被讀取的時間，modify time指的是文件的文本內容最后發生變化的時間，change time指的是文件的inode最后發生變化(比如位置、用戶屬性、組屬性等)的時間。一般來說，文件都是讀多寫少，而且我們也很少關心某一個文件最近什么時間被訪問了。
所以，我們建議采用noatime選項，這樣文件系統不記錄access time，避免浪費資源。
現在的很多文件系統會在數據提交時強制底層設備刷新cache，避免數據丟失，稱之為write barriers。但是，其實我們數據庫服務器底層存儲設備要么采用RAID卡，RAID卡本身的電池可以掉電保護；要么采用Flash卡，它也有自我保護機制，保證數據不會丟失。所以我們可以安全的使用nobarrier掛載文件系統。設置方法如下：
對于ext3, ext4和 reiserfs文件系統可以在mount時指定barrier=0；對于xfs可以指定nobarrier選項。

ii)文件系統上還有一個提高IO的優化萬能鑰匙，那就是deadline。

在Flash技術之前，我們都是使用機械磁盤存儲數據的，機械磁盤的尋道時間是影響它速度的最重要因素，直接導致它的每秒可做的IO(IOPS)非常有限，為了盡量排序和合并多個請求，以達到一次尋道能夠滿足多次IO請求的目的，Linux文件系統設計了多種IO調度策略，已適用各種場景和存儲設備。
Linux的IO調度策略包括：Deadline scheduler，Anticipatory scheduler，Completely Fair Queuing(CFQ)，NOOP。每種調度策略的詳細調度方式我們這里不詳細描述，這里我們主要介紹CFQ和Deadline，CFQ是Linux內核2.6.18之后的默認調度策略，它聲稱對每一個 IO 請求都是公平的，這種調度策略對大部分應用都是適用的。但是如果數據庫有兩個請求，一個請求3次IO，一個請求10000次IO，由于絕對公平，3次IO 的這個請求都需要跟其他10000個IO請求競爭，可能要等待上千個IO完成才能返回，導致它的響應時間非常慢。并且如果在處理的過程中，又有很多IO請求陸續發送過來，部分IO請求甚至可能一直無法得到調度被“餓死”。而deadline兼顧到一個請求不會在隊列中等待太久導致餓死，對數據庫這種應用來說更加適用。
實時設置，我們可以通過

echo deadline >/sys/block/sda/queue/scheduler

來將sda的調度策略設置為deadline。

我們也可以直接在/etc/grub.conf的kernel行最后添加elevator=deadline來永久生效。

總結

CPU方面
關閉電源保護模式

內存：
vm.swappiness = 0
關閉numa

文件系統：
用noatime，nobarrier掛載系統
IO調度策略修改為deadline。

本文由用戶 cymt 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1430576333912.html

MySQL 數據庫服務器