天天看點
傳回

MySQL性能分析(轉)

第一步：檢查系統的狀态

        通過作業系統的一些工具檢查系統的狀态，比如CPU、記憶體、交換、磁盤的使用率、IO、網絡，根據經驗或與系統正常時的狀态相比對，有時系統表面上看起來看空閑，這也可能不是一個正常的狀态，因為cpu可能正等待IO的完成。除此之外，還應觀注那些占用系統資源(cpu、記憶體)的程序。

1．用vmstat察看關于核心程序,虛拟記憶體,磁盤,cpu的的活動狀态

[root@ks01 ~]# vmstat

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

 0  0    208 811596 326016 2485004    0    0     0     1    0    0  0  0 100  0  0

其中：

        procs--核心程序的狀态

                --r：運作隊列中的程序數，當這個值超過了CPU數目，就可能會出現CPU瓶頸，在一個穩定的工作量下，應該少于5。

                --b：等待隊列中的程序數(等待I/O)（阻塞程序），通常情況下是接近0的，

                --w：被交換出去的可運作的程序數。此數由 linux 計算得出，但 linux 并不耗盡交換空間

        memory--虛拟和真實記憶體的使用資訊

                --swpd：虛拟記憶體使用大小，機關：KB，如果大于0，表示你的機器實體記憶體不足了，如果不是程式記憶體洩露的原因，那麼應該更新記憶體或者把耗記憶體的任務遷移到其他機器。

                -- free：空閑的實體記憶體的大小，機關KB

                -- buff ：被用來做為緩存的記憶體數，機關：KB。Linux/Unix系統用來存儲目錄内容，權限等的緩存

                -- cache：用來儲存打開的檔案，給檔案做緩沖。Linux/Unix将空閑實體記憶體的一部分用于檔案和目錄的緩存，這樣提高程式執行的性能，當程式使用記憶體時，buffer/cached會很快地被使用。

        swap--

                -- si：從磁盤交換到記憶體的交換頁數量，機關：KB/秒，即每秒從磁盤讀入虛拟記憶體的大小。

                -- so：從記憶體交換到磁盤的交換頁數量，機關：KB/秒，即每秒虛拟記憶體寫入磁盤的大小。

        io--

                -- bi：發送到塊裝置的塊數，機關：塊/秒，即塊裝置每秒接收的塊數量，這裡的塊裝置是指系統上所有的磁盤和其他塊裝置，預設塊大小是1024byte，

                -- bo：從塊裝置接收到的塊數，機關：塊/秒，即塊裝置每秒發送的塊數量

          如讀取檔案，bo一般會大于0。bi和bo一般都要接近0，不然就是IO過于頻繁，需要調整。

        system--

                -- in：每秒的中斷數，包括時鐘中斷

                -- cs：每秒的環境（上下文）切換次數，上下文切換次數過多表示CPU大部分浪費在上下文切換，導緻CPU運作任務時間過少，CPU沒有充分利用。例如我們調用系統函數，代碼就會進入核心空間，導緻上下文切換，線程的切換，也要程序上下文切換，這個值要越小越好

        cpu--按 CPU 的總使用百分比來顯示

                -- us：使用者CPU時間

                -- sy：系統CPU時間，如果太高，表示系統調用時間長，例如是IO操作頻繁。

                -- id：空閑 CPU時間（包括IO等待時間），一般來說，id + us + sy = 100,

                -- wa：等待IO CPU時間

虛拟記憶體運作原理

在系統中運作的每個程序都需要使用到記憶體，但不是每個程序都需要每時每刻使用系統配置設定的記憶體空間。當系統運作所需記憶體超過實際的實體記憶體，核心會釋放某些程序所占用但未使用的部分或所有實體記憶體，将這部分資料存儲在磁盤上直到程序下一次調用，并将釋放出的記憶體提供給有需要的程序使用。

在Linux記憶體管理中，主要是通過“調頁Paging”和“交換Swapping”來完成上述的記憶體排程。調頁算法是将記憶體中最近不常使用的頁面換到磁盤上，把活動頁面保留在記憶體中供程序使用。交換技術是将整個程序，而不是部分頁面，全部交換到磁盤上。

分頁(Page)寫入磁盤的過程被稱作Page-Out，分頁(Page)從磁盤重新回到記憶體的過程被稱作Page-In。當核心需要一個分頁時，但發現此分頁不在實體記憶體中(因為已經被Page-Out了)，此時就發生了分頁錯誤（Page Fault）。

當系統核心發現可運作記憶體變少時，就會通過Page-Out來釋放一部分實體記憶體。經管Page-Out不是經常發生，但是如果Page-out頻繁不斷的發生，直到當核心管理分頁的時間超過運作程式的時間時，系統效能會急劇下降。這時的系統已經運作非常慢或進入暫停狀态，這種狀态亦被稱作thrashing(颠簸)。

準測

r，b≈0， 

如果fre，将會出現連續不斷的頁面排程，将導緻系統性能問題。

對于page列，re，pi，po，cy維持于比較穩定的狀态，PI率不超過5，如果有pagin發生，那麼關聯頁面必須先進行pageout

在記憶體相對緊張的環境下pagein會強制對不同的頁面進行steal操作。如果系統正在讀一個大批的永久頁面，你也許可以看到po和pi列

會出現不一緻的增長，這種情景并不一定表明系統負載過重，但是有必要對應用程式的資料通路模式進行見檢查。在穩定的情況下，掃描率和重置率幾乎相等，在

多個程序處理使用不同的頁面的情況下，頁面會更加不穩定和雜亂，這時掃描率可能會比重置率高出。

faults列，in，sy，cs會不斷跳躍，這裡沒有明确的限制，唯一的就是這些值最少大于100

cpu列，us，sys，id和wa也是不确定的，最理想的狀态是使cpu處于100%工作狀态，單這隻适合單使用者的情況下。us的值比較高時，說明使用者程序消耗的cpu時間多，但是如果長期大于50%，需要考慮優化使用者的程式。us + sy的參考值為80%，如果us+sy 大于 80%說明可能存在CPU不足

如果在多使用者環境中us＋sys > 80，程序就會在運作隊列中花費等待時間，響應時間和吞吐量就會下降。wa>40表明磁盤io沒有也許存在不合理的平衡，或者對磁盤操作比較頻繁，

如果 r經常大于 4 ，且id經常少于40，表示cpu的負荷很重。 

如果pi，po 長期不等于0，表示記憶體不足。 

如果disk 經常不等于0， 且在 b中的隊列 大于3， 表示 io性能不好。

wa的參考值為30%，如果wa超過30%，說明IO等待嚴重，這可能是磁盤大量随機通路造成的，也可能磁盤或者磁盤通路控制器的帶寬瓶頸造成的(主要是塊操作)。

swpd 切換到記憶體交換區的記憶體數量(k表示)。如果swpd的值不為0，或者比較大，比如超過了100m，隻要si、so的值長期為0，系統性能還是正常

cache: 作為page cache的記憶體數量，一般作為檔案系統的cache，如果cache較大，說明用到cache的檔案較多，如果此時IO中bi比較小，說明檔案系統效率比較好。

這裡我們設定的bi+bo參考值為1000，如果超過1000，而且wa值較大應該考慮均衡磁盤負載

io bo: 磁盤寫的資料量稍大，如果是大檔案的寫，10M以内基本不用擔心，如果是小檔案寫2M以内基本正常

在Linux下還有很多系統性能分析工具，比較常見的有top、free、ps、time、timex、uptime等。

2．使用sar來檢查作業系統是否存在IO問題

        sar可以顯示CPU、運作隊列、磁盤I/O、分頁（交換區）、記憶體、CPU中斷、網絡等性能資料:

[root@ks01 ~]# sar

Linux 2.6.18-194.el5 (ks01.oss.com)     05/03/2011

12:00:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle

12:10:01 AM       all      0.00      0.00      0.00      0.03      0.00     99.96

12:20:01 AM       all      0.00      0.00      0.00      0.01      0.00     99.98

...

其中:

         -CPU       CPU編号

         --%user    在使用者模式中運作程序所花的時間的百分比(指的是使用者程序使用的cpu資源的百分比)

         --%nice     運作正常程序所花的時間的百分比

         --%system     在核心模式（系統）中運作程序所花的時間的百分比(指的是系統資源使用cpu資源的百分比)

         --%iowait      沒有程序在該CPU上執行時，處理器等待I/O完成的時間的百分比(指的是等待io完成的百分比)   --這個值過高，表示硬碟存在I/O瓶頸

         --%idle         CPU空閑時間百分比 ---如果這個值很高 但是系統響應慢 這時候應該加大記憶體 如果這個值持續太低 說明系統缺少cpu資源

        如果iowait列的值很大，如在35%以上，說明系統的IO存在瓶頸，CPU花費了很大的時間去等待I/O的完成。Idle很小說明系統CPU很忙。

附：sar 指令行的常用格式：

        sar -u 是sar的預設輸出 （CPU 使用情況）

        sar [options] [-A] [-o file] t [n] 在指令行中，n 和t 兩個參數組合起來定義采樣間隔和次數，t為采樣間隔，是必須有 的參數，n為采樣次數，是可選的，預設值是1，-o file表示将指令結果以二進制格式 存放在檔案中，file 在此處不是關鍵字，是檔案名。options 為指令行選項，sar指令 

的選項很多，下面隻列出常用選項：

　　　-A：所有報告的總和。 

　　　-u：CPU使用率 

　　　-v：程序、I節點、檔案和鎖表狀态。 

　　　-d：硬碟使用報告。 

　　　-r：沒有使用的記憶體頁面和硬碟塊。 

　　　-g：序列槽I/O的情況。 

           -b：緩沖區使用情況。 

           -a：檔案讀寫情況。 

           -c：系統調用情況。 

           -R：程序的活動情況。 

           -y：終端裝置活動情況。 

           -w：系統交換活動。

比如 

[root@ks01 ~]# sar -u 2 5  每2秒采集一下資訊 收集5次

Linux 2.6.18-194.el5 (ks01.oss.com)     05/03/2011

03:33:47 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle

03:33:49 PM       all      0.00      0.00      0.00      0.00      0.00    100.00

03:33:51 PM       all      0.00      0.00      0.00      0.00      0.00    100.00

03:33:53 PM       all      0.00      0.00      0.00      0.03      0.00     99.97

03:33:55 PM       all      0.00      0.00      0.00      0.00      0.00    100.00

03:33:57 PM       all      0.00      0.00      0.00      0.00      0.00    100.00

Average:          all      0.00      0.00      0.00      0.01      0.00     99.99

3．使用vmstat監控記憶體 cpu資源

2.1 CPU問題

        下面幾列需要被察看，以确定cpu是否有問題

Processes in the run queue (procs r)

User time (cpu us)

System time (cpu sy)

Idle time (cpu id)

問題情況:

        1)  如果processes in run queue (procs r)的數量遠大于系統中cpu的數量，将會使系統便慢。

        2)  如果這個數量是cpu的4倍的話，說明系統正面臨cpu能力短缺，這将使系統運作速度大幅度降低

        3) 如果cpu的idle時間經常為0的話，或者系統占用時間(cpu sy)是使用者占用時間(cpu us)兩倍的話，系統面臨缺少cpu資源

解決方案 :

        解決這些情況，涉及到調整應用程式，使其能更有效的使用cpu，同時增加cpu的能力或數量

2.2記憶體問題

        主要檢視頁導入的數值（swap中的si），如果該值比較大就要考慮記憶體，大概方法如下：

        1) 最簡單的，加大RAM

        2) 減少RAM的需求

3．磁盤IO問題

        處理方式：做raid10提高性能

使用的作業系統很重要。為了更好地使用多CPU機器，應使用Solaris(因為其線程工作得很好)或Linux(因為2.4和以後的核心有很好的SMP支援)。請注意預設情況舊的Linux核心有一個2GB的檔案大小限制。如果有這樣的一個核心并且需要檔案大于2GB，應得到ext2檔案系統的大檔案支援(LFS)更新檔。其它檔案系統例如ReiserFS和XFS沒有此2GB限制。

4．網絡問題

        telnet一下MySQL對外開放的端口，如果不通的話，看看防火牆是否正确設定了。另外，看看MySQL是不是開啟了skip-networking的選項，如果開啟請關閉。

第二步 檢查mysql參數

1．幾個不被注意的mysql參數

1.1 max_connect_errors

        max_connect_errors預設值為10，如果受信帳号錯誤連接配接次數達到10則自動堵塞，需要flush hosts來解除。如果你得到象這樣的一個錯誤：

              Host ’hostname’ is blocked because of many connection errors.

              Unblock with ’mysqladmin flush-hosts’

         這意味着，mysqld已經得到了大量(max_connect_errors)的主機’hostname’的在中途被中斷了的連接配接請求。在 max_connect_errors次失敗請求後，mysqld認定出錯了(象來字一個黑客的攻擊)，并且阻止該站點進一步的連接配接，直到某人執行指令 mysqladmin flush-hosts。

        内網連接配接的話，建議設定在10000以上，已避免堵塞，并定期flush hosts。

1.2 connect_timeout

        指定MySQL服務等待應答一個連接配接封包的最大秒數，超出該時間，MySQL向用戶端傳回 bad handshake。預設值是5秒，在内網高并發環境中建議設定到10-15秒，以便避免bad hand shake。建議同時關注thread_cache_size并設定thread_cache_size為非0值，大小具體調整。

1.3 skip-name-resolve

        skip-name-resolve能大大加快使用者獲得連接配接的速度，特别是在網絡情況較差的情況下。MySQL在收到連接配接請求的時候，會根據請求包中獲得的ip來反向追查請求者的主機名。然後再根據傳回的主機名又一次去擷取ip。如果兩次獲得的ip相同，那麼連接配接就成功建立了。在DNS不穩定或者區域網路内主機過多的情況下，一次成功的連接配接将會耗費很多不必要的時間。假如MySQL伺服器的ip位址是廣域網的，最好不要設定skip-name- resolve。

1.4 slave-net-timeout=seconds

        參數含義：當slave從主資料庫讀取log資料失敗後，等待多久重建立立連接配接并擷取資料。預設值是3600秒，如果需要保證同步性，如此NC的參數請極力控制在10秒以下。

1.5 master-connect-retry

        參數含義：當重建立立主從連接配接時，如果連接配接建立失敗，間隔多久後重試。預設是60秒，請按照合理的情況去設定參數。

第三步 檢查mysql 相關狀态

        幾個指令：

                show status           顯示系統狀态 

                show variables     顯示系統變量

                show processlist   顯示程序狀态

                show profiles;       收集執行查詢資源資訊 預設是關閉的 開啟 set profiling=1;

        當調節MySQL伺服器時，要配置的兩個最重要的變量是key_buffer_size和table_cache。在試圖更改其它變量前你應先确信已經适當地配置了這些變量。

        下面的例子顯示了部分典型的不同的運作時配置的變量值。

•         如果至少有256MB記憶體和許多表，想要在中等數量的客戶時獲得最大性能，應使用：

               shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \

                           --sort_buffer_size=4M --read_buffer_size=1M &

•         如果隻有128MB記憶體和少量表，但仍然要進行大量的排序，可以使用：

                shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M

        如果有許多并行連接配接，交換問題會發生，除非mysqld已經配置成為每個連接配接配置設定很少的記憶體。如果有足夠的記憶體用于所有連接配接，mysqld會執行得更好。

•         對于少量記憶體和大量連接配接，應使用：

                shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \

                           --read_buffer_size=100K &

        或甚至為：

                shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \

                           --table_cache=32 --read_buffer_size=8K \

                           --net_buffer_length=1K &

         如果正對遠遠大于可用記憶體的表執行GROUP BY或ORDER BY操作，應增加read_rnd_buffer_size的值以加速排序操作後面的行讀取。

         如果已經安裝了MySQL，support-files目錄包含一些不同的my.cnf示例檔案：my-huge.cnf、my-大.cnf、my-medium.cnf和my-small.cnf。可以使用這些檔案來優化系統。

       請注意如果在指令行中為mysqld或mysqld_safe指定一個選項，它隻在該次伺服器調用中保持有效。要想每次伺服器運作時使用該選項，将它放在選項檔案中。

       要想看參數更改的效果，應執行：

                  shell> mysqld --key_buffer_size=32M --verbose ---help

         變量值列于輸出的最後。確定--verbose和---help選項在最後。否則，在指令行中列于它們後面的選項的效果不會反映到輸出中。

1．連接配接數

mysql> show variables like \'max_connections\';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections | 100   |

+-----------------+-------+

1 row in set (0.00 sec)

mysql> show status like \'max_used_connections\';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| Max_used_connections | 1     |

+----------------------+-------+

1 row in set (0.00 sec)

       *  max_connections：最大連接配接數

       *  max_used_connections：響應的連接配接數

        max_connections：允許的并行用戶端連接配接的數量。增大該值會增加mysqld 需要的檔案描述符的數量。預設值為100，這個數字應該增大，否則，會經常看到 Too many connections 錯誤。并且如果連接配接數達到了最大連接配接數，應用程式的通路将會被阻塞。

        一般情況下，max_used_connections的值在max_connections的85%左右是比較合适的，即：

                     max_used_connections / max_connections * 100% （理想值 ≈ 85%）

        如果max_used_connection值過高或與max_connections相同，那麼就是max_connections設定過低，或者系統負荷過高，超過伺服器負載上限了。

        修改mysql最大連接配接數，請根據硬體情況調整到合适的大小，一般經驗值可設為3000。Windows伺服器大概支援量為1500-1800個連接配接，linux伺服器可以支援到8000個左右。

        請将max_user_connections設0——–這個0代表不限制單使用者的最大連接配接數，其最大連接配接值可以等于max_connections值。

        下面是網上看到的兩個公式：

                key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections

        innoDB 的公式比這個複雜點：

        innodb_buffer_pool_size + key_buffer_size + max_connections * ( sort_buffer_size + read_buffer_size + binlog_cache_size ) + max_connections * 2MB

相關參數：

        back_log：back_log值說明MySQL臨時停止響應新請求前在短時間内可以堆起多少請求。如果你需要在短時間内允許大量連接配接，可以增加該數值。當主要MySQL線程在一個很短時間内得到非常多的連接配接請求，這就起作用，然後主線程花些時間(盡管很短)檢查連接配接并且啟動一個新線程。

換句話說，該值為“進”TCP/IP連接配接幀聽隊列的大小。作業系統有該隊列自己的限制值。該變量最大值請查閱OS文檔。企圖将back_log設定為高于你的作業系統限值是徒勞無益的。

        當觀察MySQL主機程序清單，發現大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接配接程序時，就要加大 back_log的值了。預設數值是50。

 1.1 mysqladmin -uroot status

[root@mysql1 ~]# mysqladmin -uroot status

Uptime: 1742276  Threads: 2  Questions: 2538  Slow queries: 0  Opens: 145  Flush tables: 1  Open tables: 23  Queries per second avg: 0.1

1.2 show full processlist

1.  顯示所有程序

mysql> show full processlist;

+—–+——+———–+——+———+——+——-+———————–+

| Id  | User | Host      | db   | Command | Time | State | Info                  |

+—–+——+———–+——+———+——+——-+———————–+

| 629 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist |

| 633 | root | localhost | NULL | Sleep   |   11 |       | NULL                  |

+—–+——+———–+——+———+——+——-+———————–+

2 rows in set (0.00 sec)

 2.   如果正在運作的語句太多，運作時間太長，表示MySQL效率有問題。必要的時候可以将對應的程序kill掉。

         殺死休眠的程序kill ID号

                mysql> kill 633;

                 Query OK, 0 rows affected (0.00 sec)

 3.  關注TIME參數，看看正在運作的使用者程序有多少是長時間占用的，具體分析下。

 1）使用mysqlreport關注Connections，Threads

__ Connections _________________________________________________________

Max used            3 of  200      %Max:   1.50

Total          30.16k     0.7/s

。。。。。。

__ Threads _____________________________________________________________

Running             1 of    2

Cached              1 of  300      %Hit:  99.99

Created             3     0.0/s

Slow                0       0/s

2．連接配接失敗情況

mysql> show status like\'%aborted%\';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| Aborted_clients  | 46    |

| Aborted_connects | 1     |

+------------------+-------+

2 rows in set (0.00 sec)

       參數：

       *  aborted_connects：連接配接mysql失敗次數，如果指過高，那就該檢查一下網絡，錯誤連結失敗會在此記錄。

      *  aborted_clients：用戶端非法中斷連接配接次數。如果随時間而增大，看看mysql的連結是否正常，或者檢查一下網絡，或者檢查一下max_allowed_packet，超過此設定的查詢會被中斷（ show variables like\'%max%\'）。

        連接配接緩存區和結果緩存區可以根據需要動态擴充到max_allowed_packet。當某個查詢運作時，也為目前查詢字元串配置設定記憶體。所有線程共享相同的基本記憶體。

        包消息緩沖區初始化為net_buffer_length位元組，但需要時可以增長到max_allowed_packet位元組。該值預設很小，以捕獲大的(可能是錯誤的)資料包。

mysql> show variables like \'%timeout\';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| connect_timeout            | 10    |

| delayed_insert_timeout     | 300   |

| innodb_lock_wait_timeout   | 50    |

| innodb_rollback_on_timeout | OFF   |

| interactive_timeout        | 28800 |

| net_read_timeout           | 30    |

| net_write_timeout          | 60    |

| slave_net_timeout          | 3600  |

| table_lock_wait_timeout    | 50    |

| wait_timeout               | 28800 |

+----------------------------+-------+

10 rows in set (0.00 sec)

       參數：

        *  wait_timeout：伺服器在關閉非互動連接配接之前等待活動的秒數。線上程啟動時，根據全局wait_timeout值或全局interactive_timeout值初始化會話wait_timeout值，取決于用戶端類型(由mysql_real_connect()的連接配接選項CLIENT_INTERACTIVE定義)

        * interactive_timeout：伺服器關閉互動式連接配接前等待活動的秒數。互動式用戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的用戶端。

        *  connect_timeout：mysqld伺服器用Bad handshake響應前等待連接配接包的秒數。

3．慢查詢（slow query）日志

        日志必然會拖慢系統速度，特别是CPU資源，是以如果CPU資源充分，可以一直打開，如果不充足，那就在需要調整的時候，或者在replication從伺服器上打開（針對select）。

mysql> show variables like ‘%slow%’;

+———————+—————————————-+

| Variable_name       | Value                                  |

+———————+—————————————-+

| log_slow_queries    | OFF                                    |

| slow_launch_time    | 2                                      |

| slow_query_log      | OFF                                    |

| slow_query_log_file | /data/mysql/data/mysql1-slow.log |

+———————+—————————————-+

4 rows in set (0.00 sec)

        參數：

        * log_slow_queries：是否記錄慢日志，用long_query_time變量的值來确定“慢查詢”。

        * slow_launch_time：如果建立線程的時間超過該秒數，伺服器增加Slow_launch_threads狀态變量

        * slow_query_log：是否打開日志記錄

        * slow_query_log_file：日志檔案

        mysql> set global slow_query_log=\'ON\'   注：打開日志記錄

        一旦slow_query_log變量被設定為ON，mysql會立即開始記錄

mysql> show status like \'%slow%\';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| Slow_launch_threads | 0     |

| Slow_queries        | 0     |

+---------------------+-------+

2 rows in set (0.00 sec)

        參數：

        * Slow_launch_threads：The number of threads that have taken more than slow_launch_time seconds to create. 

        * Slow_queries：超過long_query_time時間的查詢數量

相關參數：

        *  long_query_time：慢查詢的時間标準。如果查詢時間超過該值，則該sql會被記錄下來。如果使用--log-slow-queries選項，則查詢記入慢查詢日志檔案。用實際時間測量該值，而不是CPU時間，是以低于輕負載系統門檻值的查詢可能超過重負載系統的門檻值。

        其中，slow_launch_threads 值較大時，說明有些東西正在延遲連結的新線程。

        把數個修改裹進一個事務裡。如果事務對資料庫修改，InnoDB在該事務送出時必須重新整理日志到磁盤。因為磁盤旋轉的速度至多167轉/秒，如果磁盤沒有騙作業系統的話，這就限制送出的數目為同樣的每秒167次。

        如果你可以接受損失一些最近的已送出事務，你可以設定my.cnf檔案裡的參數innodb_flush_log_at_trx_commit為0。 無論如何InnoDB試着每秒重新整理一次日志，盡管重新整理不被許可。

        使用大的日志檔案，讓它甚至與緩沖池一樣大。當InnoDB寫滿日志檔案時，它不得不在一個檢查點把緩沖池已修改的内容寫進磁盤。小日志檔案導緻許多不必要的吸盤寫操作。大日志檔案的缺點時恢複時間更長。

        也讓日志緩沖相當大（與8MB相似的數量)。

3.1關注慢查詢涉及的表的相關狀态

1.       表内記錄數。盡量控制在500萬行以内（有索引），建議控制在200萬行

2.       表内索引的使用。

3.       表如果update，delete，insert頻繁，可以考慮optimize table優化下檔案存放，索引，存儲空間。

4.       表内update，insert，delete查詢的鎖定時間。

5.       select for update如果條件字段無索引的話，會引起的是鎖全表而不是行鎖，請關注。

6.       如果查詢包括GROUP BY但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。

3.2定期分析表

ANALYZE TABLE

文法：

        ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

         本語句用于分析和存儲表的關鍵字分布。在分析期間，使用一個讀取鎖定對表進行鎖定。這對于MyISAM, BDB和InnoDB表有作用。對于MyISAM表，本語句與使用myisamchk -a相當。

CHECK TABLE

 文法：

        CHECK TABLE tbl_name [, tbl_name] … [option] …

        option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

        檢查一個或多個表是否有錯誤。CHECK TABLE對MyISAM和InnoDB表有作用。對于MyISAM表，關鍵字統計資料被更新。

         CHECK TABLE也可以檢查視圖是否有錯誤，比如在視圖定義中被引用的表已不存在。

CHECKSUM TABLE

 文法：

        CHECKSUM TABLE tbl_name [, tbl_name] … [ QUICK | EXTENDED ]

 報告一個表校驗和。

3.3使用optimize table

OPTIMIZE TABLE

文法：

         OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

         如果已經删除了表的一大部分，或者如果您已經對含有可變長度行的表（含有VARCHAR, BLOB或TEXT列的表）進行了很多更改，則應使用OPTIMIZE TABLE。被删除的記錄被保持在連結清單中，後續的INSERT操作會重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來重新利用未使用的空間，并整理資料檔案的碎片。

        OPTIMIZE TABLE隻對MyISAM, BDB和InnoDB表起作用。

4．緩存簇

                show status like \'key_blocks_u%\';             -----使用和未使用緩存簇(blocks)數

                show variables like \'%Key_cache%\';

                show variables like \'%Key_buffer_size%\'; 

mysql> show status like \'key_blocks_u%\';

+-------------------+--------+

| Variable_name     | Value  |

+-------------------+--------+

| Key_blocks_unused | 213839 |

| Key_blocks_used   | 503    |

+-------------------+--------+

2 rows in set (0.00 sec)

        參數：

        *  Key_blocks_unused     未使用的塊數（key_buffer控制）

        *  Key_blocks_used         使用的塊數

mysql> show variables like \'%Key_cache%\';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| key_cache_age_threshold  | 300   |

| key_cache_block_size     | 1024  |

| key_cache_division_limit | 100   |

+--------------------------+-------+

3 rows in set (0.00 sec)

        參數：

        *  key_cache_age_threshold：該值控制将緩沖區從鍵值緩存熱子鍊(sub-chain)降級到溫子鍊(sub-chain)。如果值更低，則降級更快。最小值為100。 預設值是300。

        *  key_cache_block_size：鍵值緩存内塊的位元組大小。

        * key_cache_division_limit：鍵值緩存緩沖區鍊熱子鍊和溫子鍊的劃分點。該值為緩沖區鍊用于溫子鍊的百分比。允許的值的範圍為1到100。 預設值是100。

         這三個參數都與MyISAM鍵高速緩沖相關。可以使用key_cache_block_size變量為具體的 鍵高速緩沖指定塊緩存區的大小。這樣允許為索引檔案調節I/O操作的性能。

        當讀緩存區的大小等于原生作業系統I/O緩存區的大小時，可以獲得I/O操作的最佳性能。但是将關鍵位元組點的大小設定為等于I/O緩存區的大小并不總是能保證最佳整體性能。當讀取大的葉節點時，伺服器讀入大量的不需要的資料，結果防止讀入其它葉子的節點。

         目前，你不能控制表内索引塊的大小。該大小由伺服器在建立.MYI索引檔案時設定，取決于表定義中索引的關鍵字大小。在大多數情況下，它被設定為與I/O緩存區大小相等。

        如果Key_blocks_used * key_cache_block_size  遠小于key_buffer_size，那麼就意味着記憶體被浪費了，應該調大key_buffer_size值。

        預設情況，鍵高速緩沖管理系統采用LRU政策選擇要收回的鍵高速緩沖塊，但它也支援更複雜的方法，稱之為“中點插入政策”。

        當使用中點插入政策時，LRU鍊被分為兩個部分：一條熱子鍊和一條溫子鍊。兩部分之間的劃分點不固定，但 鍵高速緩沖管理系統關注溫部分不“太短”，總是包含至少key_cache_division_limit比例的 鍵高速緩沖塊。key_cache_division_limit是結構式 鍵高速緩沖變量的一個元件，是以其值是一個可以根據每個緩存進行設定的參數。

        當一個索引塊從表中讀入鍵高速緩沖，它被放入溫子鍊的末端。經過一定量的通路後(通路塊)，它被提升給熱子鍊。目前，需要用來提升一個塊(3)的通路次數與所有索引塊的相同。

        提升到熱子鍊的塊被放到子鍊的末端。塊然後在該子鍊中循環。如果塊在子鍊的開頭停留足夠長的時間，它被降到溫鍊。該時間由鍵高速緩沖key_cache_age_threshold元件的值确定。

對于包含N個塊的 鍵高速緩沖，門檻值表示，熱子鍊開頭的沒有在最後N *key_cache_age_threshold/100次通路中被通路的塊将被移動到溫子鍊開頭。該塊然後變為第1個擠出的候選者，因為替換的塊總是來自溫子鍊的開頭。

        中點插入政策允許你将更有價值的塊總是在緩存中。如果你想使用簡單的LRU政策，使key_cache_division_limit值保持其預設值100。

        若執行的查詢要求索引掃描有效推出所有索引塊對應有數值的進階B-樹節點的緩存，中點插入政策可以幫助提高性能。要想避免，必須使用中點插入政策，而key_cache_division_limit設定為遠小于100。然後在索引掃描操作過程中，有數值的經常通路的節點被保留在熱子鍊中。

        鍵高速緩沖可以通過更新其參數值随時重新建構。例如：

           mysql> SET GLOBAL cold_cache。key_buffer_size=4*1024*1024；

        如果你為key_buffer_size或key_cache_block_size鍵高速緩沖元件配置設定的值與元件目前的值不同，伺服器将毀掉緩存的舊結構并根據新值建立一個新的。如果緩存包含任何髒的塊，伺服器在銷毀前将它們儲存到硬碟上并重新建立緩存。如果你設定其它 鍵高速緩沖參數，則不會發生重新建構。

        當重新建構鍵高速緩沖時，伺服器首先将任何髒緩存區的内容重新整理到硬碟上。之後，緩存内容不再需要。然而，重新建構并不阻塞需要使用配置設定給緩存的索引的查詢。相反，伺服器使用原生檔案系統緩存直接通路表索引。檔案系統緩存不如使用 鍵高速緩沖有效，是以盡管查詢可以執行，但速度會減慢。緩存被重新建構後，它又可以緩存配置設定給它的索引了，并且索引不再使用檔案系統緩存。

5．鍵值緩存(索引塊緩沖區)

mysql> show variables like \'key_buffer_size\';

+-----------------+-----------+

| Variable_name   | Value     |

+-----------------+-----------+

| key_buffer_size | 268435456 |

+-----------------+-----------+

1 row in set (0.00 sec)

        參數：

        * key_buffer_size：MyISAM表的索引塊配置設定了緩沖區，由所有線程共享。key_buffer_size是索引塊緩沖區的大小。鍵值緩沖區即為鍵值緩存。

          Key_buffer_size是MyISAM 存儲引擎鍵高速緩存，對MyISAM表性能影響很大大。

mysql> show status like \'key_read%\';

+-------------------+--------+

| Variable_name     | Value  |

+-------------------+--------+

| Key_read_requests | 115144 |

| Key_reads         | 1311   |

+-------------------+--------+

2 rows in set (0.00 sec)

        參數：

        *  Key_read_requests：請求從緩存讀入一個鍵值的次數(磁盤讀取索引的請求次數)

        *  Key_reads：從硬碟讀取鍵的資料塊的次數  

mysql> show status like \'key_write%\';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| Key_write_requests | 0     |

| Key_writes         | 0     |

+--------------------+-------+

2 rows in set (0.00 sec)

        參數：

        *  Key_write_requests：将鍵的資料塊寫入緩存的請求數。

        *  Key_writes：向硬碟寫入将鍵的資料塊的實體寫操作的次數。

        key_buffer_size 參數用來設定用于緩存 MyISAM存儲引擎中索引檔案的記憶體區域大小。索引的緩存區，多線程共享，最大為4G，并且受到作業系統對每個程序使用RAM的限制，最好是設定為記憶體的25%。

        增加該值，達到你可以提供的更好的索引處理(所有讀和多個寫操作)。通常為主要運作MySQL的機器記憶體的25%。但是，如果你将該值設得過大(例如，大于總記憶體的50%)，系統将轉換為頁并變得極慢。MySQL依賴作業系統來執行資料讀取時的檔案系統緩存，是以你必須為檔案系統緩存留一些空間。故注意：該參數值設定的過大反而會是伺服器整體效率降低！

        如果我們有足夠的記憶體，這個緩存區域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引，以盡可能提高性能。

        此外，當我們在使用MyISAM 存儲的時候有一個及其重要的點需要注意，由于 MyISAM 引擎的特性限制了他僅僅隻會緩存索引塊到記憶體中，而不會緩存表資料庫塊。理想情況下，對于這些塊的請求應該來自于記憶體，而不是來自于磁盤。是以，我們的 SQL 一定要盡可能讓過濾條件都在索引中，以便讓緩存幫助我們提高查詢效率。

        隻對MyISAM表起作用。即使你不使用MyISAM表，但是内部的臨時磁盤表是MyISAM表，也要使用該值。

        如果不使用MyISAM存儲引擎，16MB足以，用來緩存一些系統表資訊等。如果使用 MyISAM存儲引擎，在記憶體允許的情況下，盡可能将所有索引放入記憶體。

        合理設定key_buffer_size的方法：

        你可以通過執行SHOW STATUS語句并檢查Key_read_requests、Key_reads、Key_write_requests和Key_writes狀态變量來檢查鍵值緩沖區的性能。

        Key_reads 代表命中磁盤的請求個數， Key_read_requests 是總數。命中磁盤的讀請求數除以讀請求總數就是不中比率。如果每 1,000 個請求中命中磁盤的數目超過 1 個，就應該考慮增大關鍵字緩沖區了。

            索引未命中緩存的機率=Key_reads / Key_read_requests * 100%

        key_reads / key_read_requests的值應該盡可能的低，比如1:100，1:1000 ，1:10000。通常，Key_reads/Key_read_requests比例一般應小于0.01。

        如果你使用更新和删除，Key_writes/Key_write_requests比例通常接近1，但如果你更新時會同時影響到多行或如果你正使用DELAY_KEY_WRITE表選項，可能小得多。

        同時寫多行時要想速度更快，應使用LOCK TABLES。

        注意，不能以Key_read_requests / Key_reads原則來設定key_buffer_size，因為在伺服器剛啟動的時候，大多數請求都要建立緩存，緩存命中比高不起來，需要運作穩定（幾小時後） 再觀察。但可以參考Key_reads 将這個值和系統的i/o做對比。

        設定key_buffer_size值時，有人給出了一個簡單的計算方法：不花很長時間在運作中調試，把資料庫填滿，達到設計時的最大值，看看這時候索引占了多大空間，然後把所有表的索引大小加起來，就是 key_buffer_size 可能達到的最大值，當然，還要留些餘地，乘個 2 或 3 之類的。

        設定key_buffer_size值，最關鍵的名額是key_blocks_unused(未使用的塊數)，隻要還有剩餘，就說明 key_buffer_size 沒用滿。

        用key_buffer_size結合Key_blocks_unused狀态變量和緩沖區塊大小，可以确定使用的鍵值緩沖區的比例。從key_cache_block_size伺服器變量可以獲得緩沖區塊大小。使用的緩沖區的比例為：

                   1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)

        該值為約數，因為鍵值緩沖區的部分空間被配置設定用作内部管理結構。

        可以建立多個MyISAM鍵值緩存。4GB限制可以适合每個緩存，而不是一個組。      

        對于記憶體在4GB左右的伺服器該參數可設定為256M或384M。

        每個連接配接到MySQL伺服器的線程都需要有自己的緩沖，預設為其配置設定256K。事務開始之後，則需要增加更多的空間。運作較小的查詢可能僅給指定的線程增加少量的記憶體消耗，例如存儲查詢語句的空間等。但如果對資料表做複雜的操作比較複雜，例如排序則需要使用臨時表，此時會配置設定大約read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的記憶體空間。不過它們隻是在需要的時候才配置設定，并且在那些操作做完之後就釋放了。

MyISAM鍵高速緩沖

        為了使硬碟I/O最小化，MyISAM存儲引擎使用一個被許多資料庫管理系統使用的政策。它使用一個緩存機制将經常通路的表鎖在記憶體中：

·         對于索引塊，維護一個稱之為鍵高速緩沖(或鍵高速緩沖區)的特殊結構。該結構包含大量塊緩存區，其中放置了最常用的索引塊。

·         對于資料塊，MySQL不使用特殊緩存。而使用原生的作業系統檔案系統的緩存。

        本節首先描述了MyISAM鍵高速緩沖的基本操作。然後讨論了提高 鍵高速緩沖性能并使你更好地控制緩存操作的最新的更改：

·         多個線程可以并行通路緩存。

·         可以設定多個鍵高速緩沖，并将表索引指定給具體緩存。

        可以使用key_buffer_size系統變量控制 鍵高速緩沖的大小。如果該變量設定為零，不使用鍵高速緩沖。如果key_buffer_size值太小不能配置設定最小數量的塊緩存區(8)，也不使用 鍵高速緩沖。

         如果鍵高速緩沖不工作，隻使用作業系統提供的原生檔案系統緩存區通路索引檔案。(換句話說，使用與表資料塊相同的政策表來通路索引塊）。

        索引塊是一個連續的通路MyISAM索引檔案的機關。通常一個索引塊的大小等于索引B-樹節點的大小。(在硬碟上使用B-樹資料結構表示索引。樹底部的節點為葉子節點。葉子節點上面的節點為非葉子節點）。

        鍵高速緩沖結構中的所有塊緩存區大小相同。該大小可以等于、大于或小于表索引塊的大小。通常這兩個值中的一個是另一個的幾倍。

        當必須通路表索引塊中的資料時，伺服器首先檢查是否它可以用于鍵高速緩沖中的某些塊緩存區。如果适用，伺服器通路鍵高速緩沖中的資料而不是硬碟上的資料。也就是說，從緩存讀取或寫入緩存，而不是從硬碟讀寫。否則，伺服器選擇一個包含一個不同的表索引塊的緩存塊緩存區，并用需要的表索引塊的拷貝替換那裡的資料。一旦新的索引塊位于緩存中，可以通路索引資料。

        如果用于替換的塊已經被修改了，塊被視為“髒了”。在這種情況下，在替換前，其内容被重新整理到它來自的表索引。

        通常伺服器遵從LRU(最近最少使用)政策：當選擇一個塊用于替換時，它選擇最近最少使用的索引塊。為了使該選擇更容易， 鍵高速緩沖子產品維護所有使用的塊的專門隊列(LRU鍊)。當通路塊時，它被放到隊列最後。當塊需要替換時，隊列開頭的塊是最近最少使用的塊，并成為第1個候選者。

共享鍵高速緩沖通路

        在以下條件下，線程可以同時通路鍵高速緩沖緩存區：

·         沒有被更新的緩存區可以被多個線程通路。

·         正被更新的緩存區讓需要使用它的線程等待直到更新完成。

·         多個線程可以發起請求替換緩存塊，隻要它們不彼此幹擾(也就是說，隻要它們需要不同的索引塊，并且使不同的緩存塊被替換)。

        對鍵高速緩沖的共享通路允許伺服器大大提高吞吐量。

多鍵高速緩沖

        對鍵高速緩沖的共享通路可以提高性能但不能完全消除線程之間的竟争。它們仍然競争對鍵高速緩沖緩存區的通路進行管理的控制結構。為了進一步降低 鍵高速緩沖通路竟争，MySQL 5.1還提供了多個鍵高速緩沖，允許你為不同的鍵高速緩沖配置設定不同的表索引。

        有多個鍵高速緩沖時，當為給定的MyISAM表處理查詢時，伺服器必須知道使用哪個緩存。預設情況，所有MyISAM表索引被緩存到預設 鍵高速緩沖中。要想為具體鍵高速緩沖配置設定表索引，應使用CACHE INDEX語句(參見13.5.5.1節，“CACHE INDEX文法”)。

        例如，下面的語句将表t1、t2和t3的索引配置設定給名為hot_cache的 鍵高速緩沖：

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;

+---------+--------------------+----------+----------+

| Table   | Op                 | Msg_type | Msg_text |

+---------+--------------------+----------+----------+

| test.t1 | assign_to_keycache | status   | OK       |

| test.t2 | assign_to_keycache | status   | OK       |

| test.t3 | assign_to_keycache | status   | OK       |

+---------+--------------------+----------+----------+

        可以用SET GLOBAL參數設定語句或使用伺服器啟動選項設定在CACHE INDEX語句中引用的鍵高速緩沖的大小來建立鍵高速緩沖。例如：

                mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

        要想删除鍵高速緩沖，将其大小設定為零：

                mysql> SET GLOBAL keycache1.key_buffer_size=0;

        請注意不能删除預設鍵高速緩沖。删除預設鍵高速緩沖的嘗試将被忽略：

mysql> set global key_buffer_size = 0;

mysql> show variables like \'key_buffer_size\';

+-----------------+---------+

| Variable_name   | Value   |

+-----------------+---------+

| key_buffer_size | 8384512 |

+-----------------+---------+

        鍵高速緩沖變量是結構式系統變量，有一個名群組件。對于keycache1.key_buffer_size，keycache1是緩存變量名，key_buffer_size是緩存元件。關于引用結構式 鍵高速緩沖系統變量所使用的文法的描述，參見9.4.1節，“結構式系統變量”

        預設情況下，表索引被配置設定給伺服器啟動時建立的主要(預設)鍵高速緩沖。當 鍵高速緩沖被删除後，所有配置設定給它的索引被重新配置設定給預設鍵高速緩沖。

        對于一個忙的伺服器，我們建議采用使用三個鍵高速緩沖的政策：

·         占用為所有鍵高速緩沖配置設定的空間的20%的“熱”鍵高速緩沖。該緩存用于頻繁用于搜尋但沒有更新的表。

·         占用為所有鍵高速緩沖配置設定的空間的20%的“冷”鍵高速緩沖。該緩存用于中等大小、大量修改的表，例如臨時表。

·         占用鍵高速緩沖空間的20%的“溫”鍵高速緩沖。使用它作為預設 鍵高速緩沖，預設情況被所有其它表使用。

        使用3個鍵高速緩沖有好處的一個原因是對一個鍵高速緩沖結構的通路不會阻擋對其它的通路。通路配置設定給一個緩存的表的查詢不會與通路配置設定給其它緩存的表的查詢競争。由于其它原因也會提高性能：

·         熱緩存隻用于檢索查詢，是以其内容決不會被修改。結果是，無論何時需要從硬碟上拉入索引塊，選擇用于替換的緩存塊的内容不需要先重新整理。

·         對于配置設定給熱緩存的索引，如果沒有查詢需要索引掃描，很有可能對應索引B-樹的非葉子節點的索引塊仍然在緩存中。

·         當更新的節點位于緩存中并且不需要先從硬碟讀入時，為臨時表頻繁執行的更新操作會執行得更快。如果臨時表的索引的大小可以與冷鍵高速緩沖相比較，很可能更新的節點位于緩存中。

        CACHE INDEX在一個表和 鍵高速緩沖之間建立一種聯系，但每次伺服器重新開機時該聯系被丢失。如果你想要每次伺服器重新開機時該聯系生效，一個發辦法是使用選項檔案：包括配置 鍵高速緩沖的變量設定值，和一個init-file選項用來命名包含待執行的CACHE INDEX語句的一個檔案。例如：

                key_buffer_size = 4G

                hot_cache.key_buffer_size = 2G

                cold_cache.key_buffer_size = 2G

                init_file=/path/to/data-directory/mysqld_init.sql

        每次伺服器啟動時執行mysqld_init.sql中的語句。該檔案每行應包含一個SQL語句。下面的例子配置設定幾個表，分别對應hot_cache和cold_cache：

                CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache

                CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache

中點插入政策

        預設情況，鍵高速緩沖管理系統采用LRU政策選擇要收回的鍵高速緩沖塊，但它也支援更複雜的方法，稱之為“中點插入政策”。

        當使用中點插入政策時，LRU鍊被分為兩個部分：一條熱子鍊和一條溫子鍊。兩部分之間的劃分點不固定，但 鍵高速緩沖管理系統關注溫部分不“太短”，總是包含至少key_cache_division_limit比例的 鍵高速緩沖塊。key_cache_division_limit是結構式 鍵高速緩沖變量的一個元件，是以其值是一個可以根據每個緩存進行設定的參數。

        當一個索引塊從表中讀入鍵高速緩沖，它被放入溫子鍊的末端。經過一定量的通路後(通路塊)，它被提升給熱子鍊。目前，需要用來提升一個塊(3)的通路次數與所有索引塊的相同。

        提升到熱子鍊的塊被放到子鍊的末端。塊然後在該子鍊中循環。如果塊在子鍊的開頭停留足夠長的時間，它被降到溫鍊。該時間由鍵高速緩沖key_cache_age_threshold元件的值确定。

        對于包含N個塊的 鍵高速緩沖，門檻值表示，熱子鍊開頭的沒有在最後N*key_cache_age_threshold/100次通路中被通路的塊将被移動到溫子鍊開頭。該塊然後變為第1個擠出的候選者，因為替換的塊總是來自溫子鍊的開頭。

        中點插入政策允許你将更有價值的塊總是在緩存中。如果你想使用簡單的LRU政策，使key_cache_division_limit值保持其預設值100。

        若執行的查詢要求索引掃描有效推出所有索引塊對應有數值的進階B-樹節點的緩存，中點插入政策可以幫助提高性能。要想避免，必須使用中點插入政策，而key_cache_division_limit設定為遠小于100。然後在索引掃描操作過程中，有數值的經常通路的節點被保留在熱子鍊中。

索引預加載

        如果鍵高速緩沖内有足夠的塊以容納整個索引的塊，或者至少容納對應其非葉節點的塊，則在使用前，預裝含索引塊的鍵高速緩沖很有意義。預裝可以以更有效的方式将表索引塊放入 鍵高速緩沖緩存區中：通過順序地從硬碟讀取索引塊。

        不進行預裝，塊仍然根據查詢需要放入鍵高速緩沖中。盡管塊将仍然在緩存中（因為有足夠的緩存區儲存它們），它們以随機方式從硬碟上索取，而不是以順序方式。

        要想将索引預裝到緩存中，使用LOAD INDEX INTO CACHE語句。例如，下面的語句可以預裝表t1和t2索引的節點(索引塊)：

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

+---------+--------------+----------+----------+

| Table   | Op           | Msg_type | Msg_text |

+---------+--------------+----------+----------+

| test.t1 | preload_keys | status   | OK       |

| test.t2 | preload_keys | status   | OK       |

+---------+--------------+----------+----------+

        IGNORE LEAVES修改器隻允許預裝索引非葉節點所用的塊。這樣，上述的語句預裝t1中的所有索引塊，但隻預裝t2中的非葉節點對應的塊。

        如果已經使用CACHE INDEX語句為一個索引配置設定了一個鍵高速緩沖，預裝可以将索引塊放入該緩存。否則，索引被裝入預設鍵高速緩沖。

鍵高速緩沖塊大小

        可以使用key_cache_block_size變量為具體的 鍵高速緩沖指定塊緩存區的大小。這樣允許為索引檔案調節I/O操作的性能。

        當讀緩存區的大小等于原生作業系統I/O緩存區的大小時，可以獲得I/O操作的最佳性能。但是将關鍵位元組點的大小設定為等于I/O緩存區的大小并不總是能保證最佳整體性能。當讀取大的葉節點時，伺服器讀入大量的不需要的資料，結果防止讀入其它葉子的節點。

        目前，你不能控制表内索引塊的大小。該大小由伺服器在建立.MYI索引檔案時設定，取決于表定義中索引的關鍵字大小。在大多數情況下，它被設定為與I/O緩存區大小相等。

重構鍵高速緩沖

        鍵高速緩沖可以通過更新其參數值随時重新建構。例如：

                mysql> SET GLOBAL cold_cache。key_buffer_size=4*1024*1024；

        如果你為key_buffer_size或key_cache_block_size鍵高速緩沖元件配置設定的值與元件目前的值不同，伺服器将毀掉緩存的舊結構并根據新值建立一個新的。如果緩存包含任何髒的塊，伺服器在銷毀前将它們儲存到硬碟上并重新建立緩存。如果你設定其它 鍵高速緩沖參數，則不會發生重新建構。

        當重新建構鍵高速緩沖時，伺服器首先将任何髒緩存區的内容重新整理到硬碟上。之後，緩存内容不再需要。然而，重新建構并不阻塞需要使用配置設定給緩存的索引的查詢。相反，伺服器使用原生檔案系統緩存直接通路表索引。檔案系統緩存不如使用 鍵高速緩沖有效，是以盡管查詢可以執行，但速度會減慢。緩存被重新建構後，它又可以緩存配置設定給它的索引了，并且索引不再使用檔案系統緩存。

6．查詢緩存

        很多應用程式都嚴重依賴于資料庫，但卻會反複執行相同的查詢。每次執行查詢時，資料庫都必須要執行相同的工作 —— 對查詢進行分析，确定如何執行查詢，從磁盤中加載資訊，然後将結果傳回給客戶機。MySQL 有一個特性稱為查詢緩存，查詢緩存會存儲一個 SELECT 查詢的文本與被傳送到用戶端的相應結果。如果之後接收到一個同樣的查詢，伺服器将從查詢緩存中檢索結果，而不是再次分析和執行這個同樣的查詢。在很多情況下，這會極大地提高性能。不過，問題是查詢緩存在預設情況下是禁用的。

        如果你有一個不經常改變的表并且伺服器收到該表的大量相同查詢，查詢緩存在這樣的應用環境中十分有用。對于許多Web伺服器來說存在這種典型情況，它根據資料庫内容生成大量的動态頁面。

        注釋：查詢緩存不傳回舊的資料。當表更改後，查詢緩存值的相關條目被清空。

        注釋：如果你有許多mysqld伺服器更新相同的MyISAM表，在這種情況下查詢緩存不起作用。

        注釋：查詢緩存不适用于伺服器方編寫的語句。如果正在使用伺服器方編寫的語句，要考慮到這些語句将不會應用查詢緩存。

•         如果執行的所有查詢是簡單的(如從隻有一行資料的表中選取一行)，但查詢是不同的，查詢不能被緩存，查詢緩存激活率是13%。這可以看作是最壞的情形。在實際應用中，查詢要複雜得多，是以，查詢緩存使用率一般會很低。

•         從隻有一行的表中查找一行資料時，使用查詢緩存比不使用速度快238%。這可以看作查詢使用緩存時速度提高最小的情況。

        伺服器啟動時要禁用查詢緩存，設定query_cache_size系統變量為0。禁用查詢緩存代碼後，沒有明顯的速度提高。編譯MySQL時，通過在configure中使用--without-query-cache選項，可以從伺服器中徹底去除查詢緩存能力。

mysql> show variables like \'have_query_cache\';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| have_query_cache | YES   |

+------------------+-------+

1 row in set (0.00 sec)

        參數：

        *  hava_query_cache：訓示查詢緩存是否可用

mysql> show variables like \'query_cache%\';

+------------------------------+-----------+

| Variable_name                | Value     |

+------------------------------+-----------+

| query_cache_limit            | 2097152   |

| query_cache_min_res_unit     | 2048      |

| query_cache_size             | 536870912 |

| query_cache_type             | ON        |

| query_cache_wlock_invalidate | OFF       |

+------------------------------+-----------+

5 rows in set (0.00 sec)

        參數：

        * query_cache_limit：指定單個查詢能夠使用的緩沖區大小，預設為1M，不緩存大于該值的結果。即控制可以被緩存的具體查詢結果的最大值。

        *  query_cache_type ：查詢緩存類型。設定GLOBAL值可以設定後面的所有用戶端連接配接的類型。用戶端可以設定SESSION值以影響他們自己對查詢緩存的使用。這個變量可以設定為下面的值：(如果查詢緩存大小設定為大于0，query_cache_type變量影響其工作方式。)

                0 或OFF 不緩存查詢結果。

                1 或ON 将允許緩存，以SELECT SQL_NO_CACHE 開始的查詢語句除外。

                2 或DEMAND ， 僅對以SELECT SQL_CACHE 開始的那些查詢語句啟用緩存。

        如果全部使用innodb存儲引擎，建議為0，如果使用MyISAM 存儲引擎，建議為2

        *  query_cache_min_res_unit：指定配置設定緩沖區空間的最小機關，預設為4K。檢查狀态值Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多，這就表明查詢結果都比較小，此時需要減小query_cache_min_res_unit。

        *  query_cache_size：為緩存查詢結果配置設定的記憶體的數量(以位元組指定) 。如果設定它為 0 ，查詢緩沖将被禁止(預設值為 0 )。請注意即使query_cache_type設定為0也将配置設定此數量的記憶體。

        * query_cache_wlock_invalidate：一般情況，當用戶端對MyISAM表進行WRITE鎖定時，如果查詢結果位于查詢緩存中，則其它用戶端未被鎖定，可以對該表進行查詢。将該變量設定為1，則可以對表進行WRITE鎖定，使查詢緩存内所有對該表進行的查詢變得非法。這樣當鎖定生效時，可以強制其它試圖通路表的用戶端來等待。(當有其他用戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否傳回cache結果還是等寫操作完成再讀表擷取結果。)

相關參數：

        *  query_prealloc_size：用于查詢分析和執行的固定緩沖區的大小。在查詢之間該緩沖區不釋放。如果你執行複雜查詢，配置設定更大的query_prealloc_size值可以幫助提高性能，因為它可以降低查詢過程中伺服器配置設定記憶體的需求。

mysql> show status like \'qcache%\';

+-------------------------+-----------+

| Variable_name           | Value     |

+-------------------------+-----------+

| Qcache_free_blocks      | 5216 |

| Qcache_free_memory      | 14640664 |

| Qcache_hits             | 2581646882|

| Qcache_inserts          | 360210964 |

| Qcache_lowmem_prunes    | 281680433 |

| Qcache_not_cached       | 79740667 |

| Qcache_queries_in_cache | 16927 |

| Qcache_total_blocks     | 47042|

+-------------------------+-----------+

8 rows in set (0.00 sec)

        參數：

        * Qcache_free_blocks：緩存中相鄰記憶體塊的個數。數目大說明可能有碎片。FLUSH QUERY CACHE 會對緩存中的碎片進行整理，進而得到一個空閑塊。

        * Qcache_free_memory：緩存中的空閑記憶體。

        * Qcache_hits：每次查詢在緩存中命中時就增大。

        * Qcache_inserts：每次插入一個查詢時就增大。 未命中然後插入。

        * Qcache_lowmem_prunes：的值非常大，則表明經常出現緩沖不夠的情況。同時Qcache_hits的值非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小，Qcache_hits的值不大，則表明你的查詢重複率很低，這種情況下使用查詢緩沖反而會影響效率，那麼可以考慮不用查詢緩沖。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者記憶體很少。（上面的 free_blocks 和 free_memory 可以告訴您屬于哪種情況）。

        * Qcache_not_cached： 不适合進行緩存的查詢的數量，通常是由于這些查詢不是 SELECT 語句。

        * Qcache_queries_in_cache： 目前緩存的查詢（和響應）的數量。

        * Qcache_total_blocks：緩存中塊的數量。

        Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

        查詢命中率 = Qcache_hits - Qcache_inserts / Qcache_hits

        查詢插入率 = Qcache_inserts / Com_select;

        未插入率 = Qcache_not_cached / Com_select;

        Query cache 作用于整個 MySQL Instance，主要用來緩存 MySQL 中的 ResultSet，也就是一條SQL語句執行的結果集，是以僅僅隻能針對select語句。

         當我們打開了 Query Cache 功能，MySQL在接受到一條select語句的請求後，如果該語句滿足Query Cache的要求(未顯式說明不允許使用Query Cache，或者已經顯式申明需要使用Query Cache)，MySQL 會直接根據預先設定好的HASH算法将接受到的select語句以字元串方式進行hash，然後到Query Cache 中直接查找是否已經緩存。也就是說，如果已經在緩存中，該select請求就會直接将資料傳回，進而省略了後面所有的步驟(如 SQL語句的解析，優化器優化以及向存儲引擎請求資料等)，極大的提高性能。

        Query Cache 也有一個緻命的缺陷，那就是當某個表的資料有任何任何變化，都會導緻所有引用了該表的select語句在Query Cache 中的緩存資料失效。是以，當我們的資料變化非常頻繁的情況下，使用Query Cache 可能會得不償失。

         Query Cache中最為關鍵參數是 query_cache_size 和 query_cache_type ，前者設定用于緩存 ResultSet 的記憶體大小，後者設定在何場景下使用 Query Cache。

        如何合理設定Query_cache_size值：

        query_cache_size 一般 256MB 是一個比較合适的大小。當然，可以通過計算Query Cache的命中率來進行調整。

          Query Cache的合中率：Qcache_hits / (Qcache_hits + Qcache_inserts) * 100)

        一般不建議太大，256MB可能已經差不多了，大型的配置型靜态資料可适當調大。

        當設定query_cache_size變量為非零值時，應記住查詢緩存至少大約需要40KB來配置設定其資料結構。(具體大小取決于系統結構）。如果你把該值設定的太小，将會得到一個警告，如本例所示：

mysql> SET GLOBAL query_cache_size = 40000;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G

*************************** 1. row ***************************

  Level: Warning

   Code: 1282

Message: Query cache failed to set size 39936; new query cache size is 0

        當一個查詢結果（傳回給用戶端的資料）從查詢緩沖中提取期間，它在查詢緩存中排序。是以，資料通常不在大的資料塊中處理。查詢緩存根據資料排序要求配置設定資料塊，是以，當一個資料塊用完後配置設定一個新的資料塊。因為記憶體配置設定操作是昂貴的(費時的)，是以通過query_cache_min_res_unit系統變量給查詢緩存配置設定最小值。當查詢執行時，最新的結果資料塊根據實際資料大小來确定，是以可以釋放不使用的記憶體。根據你的伺服器執行查詢的類型，你會發現調整query_cache_min_res_unit變量的值是有用的：

        query_cache_min_res_unit預設值是4KB。這應該适合大部分情況。

        如果你有大量傳回小結果資料的查詢，預設資料塊大小可能會導緻記憶體碎片，顯示為大量空閑記憶體塊。由于缺少記憶體，記憶體碎片會強制查詢緩存從緩存記憶體中修整（删除）查詢。這時，你應該減少query_cache_min_res_unit變量的值。空閑塊和由于修整而移出的查詢的數量通過Qcache_free_blocks和Qcache_lowmem_prunes變量的值給出。

        如果大量查詢傳回大結果（檢查 Qcache_total_blocks和Qcache_queries_in_cache狀态變量），你可以通過增加query_cache_min_res_unit變量的值來提高性能。但是，注意不要使它變得太大（參見前面的條目）。

        通常，間隔幾秒顯示這些變量就可以看出差別，這可以幫助确定緩存是否正在有效地使用。運作 FLUSH STATUS 可以重置一些計數器，如果伺服器已經運作了一段時間，這會非常有幫助。

使用非常大的查詢緩存，期望可以緩存所有東西，這種想法非常誘人。但如果表有變動時，首先要把Query_cache和該表相關的語句全部置為失效，然後在寫入更新。

        那麼如果Query_cache非常大，該表的查詢結構又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎麼這麼慢了。

        是以在資料庫寫入量或是更新量也比較大的系統，該參數不适合配置設定過大。而且在高并發，寫入量大的系統，建系把該功能禁掉。

        作為一條規則，如果 FLUSH QUERY CACHE 占用了很長時間，那就說明緩存太大了。

查詢高速緩沖狀态和維護

可以使用下面的語句檢查MySQL伺服器是否提供查詢緩存功能：

mysql> SHOW VARIABLES LIKE \'have_query_cache\';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| have_query_cache | YES   |

+------------------+-------+

可以使用FLUSH QUERY CACHE語句來清理查詢緩存碎片以提高記憶體使用性能。該語句不從緩存中移出任何查詢。

RESET QUERY CACHE語句從查詢緩存中移出所有查詢。FLUSH TABLES語句也執行同樣的工作。

為了監視查詢緩存性能，使用SHOW STATUS檢視緩存狀态變量：

mysql> SHOW STATUS LIKE \'Qcache%\';

+-------------------------+--------+

|變量名                   |值 |

+-------------------------+--------+

| Qcache_free_blocks      | 36     |

| Qcache_free_memory      | 138488 |

| Qcache_hits             | 79570  |

| Qcache_inserts          | 27087  |

| Qcache_lowmem_prunes    | 3114   |

| Qcache_not_cached       | 22989  |

| Qcache_queries_in_cache | 415    |

| Qcache_total_blocks     | 912    |

+-------------------------+--------+

SELECT查詢的總數量等價于：

            Com_select + Qcache_hits + queries with errors found by parser

Com_select的值等價于：

            Qcache_inserts + Qcache_not_cached + queries with errors found during columns/rights check

查詢緩存使用長度可變塊，是以Qcache_total_blocks和Qcache_free_blocks可以顯示查詢緩存記憶體碎片。執行FLUSH QUERY CACHE後，隻保留一個空閑塊。

每個緩存查詢至少需要兩個塊（一個塊用于查詢文本，一個或多個塊用于查詢結果）。并且，每一個查詢使用的每個表需要一個塊。但是，如果兩個或多個查詢使用相同的表，僅需要配置設定一個塊。

Qcache_lowmem_prunes狀态變量提供的資訊能夠幫助你你調整查詢緩存的大小。它計算為了緩存新的查詢而從查詢緩沖區中移出到自由記憶體中的查詢的數目。查詢緩沖區使用最近最少使用(LRU)政策來确定哪些查詢從緩沖區中移出。

查詢高速緩沖的工作原理

查詢解析之前進行比較，是以下面的兩個查詢被查詢緩存認為是不相同的：

SELECT * FROM tbl_name

Select * from tbl_name

查詢必須是完全相同的(逐位元組相同)才能夠被認為是相同的。另外，同樣的查詢字元串由于其它原因可能認為是不同的。使用不同的資料庫、不同的協定版本或者不同 預設字元集的查詢被認為是不同的查詢并且分别進行緩存。

從查詢緩存中提取一個查詢之前，MySQL檢查使用者對所有相關資料庫和表的SELECT權限。如果沒有權限，不使用緩存結果。

如果從查詢緩存中傳回一個查詢結果，伺服器把Qcache_hits狀态變量的值加一，而不是Com_select變量。參見5.13.4節，“查詢高速緩沖狀态和維護”。

如果一個表被更改了，那麼使用那個表的所有緩沖查詢将不再有效，并且從緩沖區中移出。這包括那些映射到改變了的表的使用MERGE表的查詢。一個表可以被許多類型的語句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。

COMMIT執行完後，被更改的事務InnoDB表不再有效。

使用InnoDB表時，查詢緩存也在事務中工作，使用該表的版本号來檢測其内容是否仍舊是目前的。

在MySQL 5.1中，視圖産生的查詢被緩存。

SELECT SQL_CALC_FOUND_ROWS ...和SELECT FOUND_ROWS() type類型的查詢使用查詢緩存。即使因建立的行數也被儲存在緩沖區内，前面的查詢從緩存中提取，FOUND_ROWS()也傳回正确的值。

如果一個查詢包含下面函數中的任何一個，它不會被緩存：

BENCHMARK() CONNECTION_ID()          CURDATE()

CURRENT_DATE()           CURRENT_TIME()            CURRENT_TIMESTAMP()

CURTIME()         DATABASE()       帶一個參數的ENCRYPT()

FOUND_ROWS()                GET_LOCK()       LAST_INSERT_ID()

LOAD_FILE()      MASTER_POS_WAIT()    NOW()

RAND() RELEASE_LOCK()            SYSDATE()

不帶參數的UNIX_TIMESTAMP()                USER() 

在下面的這些條件下，查詢也不會被緩存：

•         引用自定義函數(UDFs)。

•         引用自定義變量。

•         引用mysql系統資料庫中的表。

•         下面方式中的任何一種：

SELECT ...IN SHARE MODE

SELECT ...FOR UPDATE

SELECT ...INTO OUTFILE ...

SELECT ...INTO DUMPFILE ...

SELECT * FROM ...WHERE autoincrement_col IS NULL

最後一種方式不能被緩存是因為它被用作為ODBC工作區來擷取最近插入的ID值。參見26.1.14.1節，“如何在ODBC中擷取AUTO_INCREMENT列的值”。

•          被作為編寫好的語句，即使沒有使用占位符。例如，下面使用的查詢：

char *my_sql_stmt = "SELECT a，b FROM table_c";

   /* ...*/

mysql_stmt_prepare(stmt，my_sql_stmt，strlen(my_sql_stmt));

不被緩存。參見25.2.4節，“C API預處理語句”。

•         使用TEMPORARY表。

•         不使用任何表。

•         使用者有某個表的列級權限。

7．線程使用情況

mysql> show status like \'Thread%\';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Threads_cached    | 4     |

| Threads_connected | 1     |

| Threads_created   | 5     |

| Threads_running   | 1     |

+-------------------+-------+

4 rows in set (0.00 sec)

        參數：

        *  Threads_cached：線程緩存内的線程數

        *  Threads_connected：目前打開的連接配接的數量

        *  Threads_created：建立用來處理連接配接的線程數。

        *  Threads_running：激活的（非睡眠狀态）線程數。

mysql> show variables like \'thread%\';

+-------------------+---------------------------+

| Variable_name     | Value                     |

+-------------------+---------------------------+

| thread_cache_size | 8                         |

| thread_handling   | one-thread-per-connection |

| thread_stack      | 196608                    |

+-------------------+---------------------------+

3 rows in set (0.00 sec)

        參數：

        * thread_cache_size：可以複用的儲存在中的線程的數量。如果有，新的線程從緩存中取得，當斷開連接配接的時候如果有空間，客戶的線置在緩存中。

        * thread_handling：

        * thread_stack：每個線程的堆棧大小，用crash-me測試檢測出的許多限制取決于該值。 預設值足夠大，可以滿足普通操作。

        線程緩存：mysqld 在接收連接配接時會根據需要生成線程。在一個連接配接變化很快的繁忙伺服器上，對線程進行緩存便于以後使用可以加快最初的連接配接。

        當用戶端斷開連接配接時，如果線程少于thread_cache_size，則用戶端的線程被放入緩存。當請求線程時如果允許可以從緩存中重新利用線程，并且隻有當緩存空了時才會建立新線程。如果新連接配接很多，可以增加該變量以提高性能。(一般情況，如果線程執行得很好，性能提高不明顯)。檢查Connections和Threads_created狀态變量的差，你可以看見線程緩存的效率。

        此處重要的值是 Threads_created，每次 mysqld 需要建立一個新線程時，這個值都會增加。如果這個數字在連續執行 SHOW STATUS 指令時快速增加，就應該嘗試增大thread_cache_size值。

         緩存通路率的計算方法 Threads_created（建立的線程）/Connections（隻要有線程連接配接，該值就增加）。

         伺服器應緩存多少線程以便重新使用。當用戶端斷開連接配接時，如果線程少于thread_cache_size，則用戶端的線程被放入緩存，一般配置8。

        每個連接配接到MySQL伺服器的線程都需要有自己的緩沖，預設為其配置設定256K。事務開始之後，則需要增加更多的空間。運作較小的查詢可能僅給指定的線程增加少量的記憶體消耗，例如存儲查詢語句的空間等。但如果對資料表做複雜的操作比較複雜，例如排序則需要使用臨時表，此時會配置設定大約read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的記憶體空間。不過它們隻是在需要的時候才配置設定，并且在那些操作做完之後就釋放了。

8．打開的檔案數

mysql> show status like \'%open%file%\';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_files    | 1     |

| Opened_files  | 58    |

+---------------+-------+

2 rows in set (0.00 sec)

        參數：

        * Open_files：打開檔案的個數，這個統計是伺服器打開的正規檔案的個數。不包括socket 及pipe。當打開myisam表資料時，他會增加兩個（資料檔案與索引檔案），當打開innodb表時，該值不增加，當打開的myisam表已另一個别名打開時，Open_files隻會增加一個。flush tables 會清空該值。

        * Opened_files：當增加Open_files同時，他會已同樣大小增加該值。當table_open_cache增加，或者flush tables 時，該值是不會減少，但也不增加的。

相關參數：

        * Open_streams：打開流的數量(主要用于日志記載)

mysql> show  variables like \'%open_file%\';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| innodb_open_files | 300   |

| open_files_limit  | 25000 |

+-------------------+-------+

2 rows in set (0.00 sec)

        參數：

        * open_files_limit：作業系統允許mysqld打開的檔案的數量。這是系統允許的實際值，可能與你在啟動選項中賦給mysqld的值不同。若在系統中MySQL不能更改打開的檔案的數量，則該值為0。

        mysql打開的最大檔案數，受兩個參數的影響：系統打開的最大檔案數（ulimit -n）和 open_files_limit 。

         不過mysql打開的檔案描述符限制都是OS的檔案描述符限制，和配置檔案中open_files_limit的設定沒有關系。

        如果Open_files和open_files_limit接近，就應該增加open_files_limit的大小。

        增加mysql打開的最大檔案數，最好用sysctl或者修改/etc/sysctl.conf檔案，同時還要在配置檔案中把open_files_limit這個參數增大，對于4G記憶體伺服器，open_files_limit至少要增大到4096，非特殊情況，設定成8192就可以了。

-------------------------------------------------------------

在/etc/my.cnf加入open_files_limit=8192

在/etc/security/limits.conf添加

* soft nofile 8192

* hard nofile 8192

--------------------------------------------------------------------

9．打開表情況

mysql> show status like \'open%tables%\'; 

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_tables   | 95    |

| Opened_tables | 0     |

+---------------+-------+

2 rows in set (0.00 sec)

        參數：

        *  Open_tables：目前打開的表的數量。

        *  Opened_tables：已經打開的表的數量。

mysql> show variables like \'table%cache%\';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| table_definition_cache | 256   |

| table_open_cache       | 256   |

+------------------------+-------+

2 rows in set (0.01 sec)

        參數：

        *  table_definition_cache：

        *  table_open_cache：表高速緩存的數目。

mysql> show variables like \'open%\';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| open_files_limit | 622   |

+------------------+-------+

1 row in set (0.00 sec)

        參數：

        *  open_files_limit：作業系統允許mysqld打開的檔案的數量。

表緩存的說明：

        當 Mysql 通路一個表時，如果該表在緩存中已經被打開，則可以直接通路緩存；如果還沒有被緩存，但是在 Mysql 表緩沖區中還有空間，那麼這個表就被打開并放入表緩沖區；如果表緩存滿了，則會按照一定的規則将目前未用的表釋放，或者臨時擴大表緩存來存放，使用表緩存的好處是可以更快速地通路表中的内容。

        每個連接配接進來，都會至少打開一個表緩存。是以， table_open_cache 的大小應與 max_connections 的設定有關。例如，對于 200 個并行運作的連接配接，應該讓表的緩存至少有 200 × N ，這裡N是可以執行的查詢的一個聯接中表的最大數量。

        幾個參數的關系：

                  table_open_cache * 2 + max_connections＝max_open_files

        每個線程會獨自持有一個資料檔案的檔案描述符，而索引檔案的檔案描述符是公用的。當table cache不夠用的時候，MySQL會采用LRU算法踢掉最長時間沒有使用的表。如果table_cache設定過小，MySQL就會反複打開、關閉 frm檔案，造成一定的性能損失。如果table_cache設定過大，MySQL将會消耗很多CPU去做 table cache的算法運算。

        而InnoDB的中繼資料管理是放在共享表空間裡面做的，是以擷取表的結構不需要去反複解析frm檔案，這是比MyISAM強的地方。即使 table_cache設定過小，對于InnoDB的影響也是很小的，因為它根本不需要反複打開、關閉frm檔案去擷取中繼資料。

        table_open_cache是所有線程打開的表的數目(一個表使用2個檔案描述符)，表數量多，就要大一些。增大該值可以增加mysqld需要的檔案描述符的數量。根據資料庫系統中表數量來決定該值，如2048。

        合理設定table_open_cache的大小：

        通過檢視open_tables，opened_tables，flush tables 的值來比較，察看目前的表緩存情況。

       清空表緩存：mysql> flush tables;

        如果 open_tables接近table_open_cache的時候，并且Opened_tables這個值在逐漸增加，說明table_cache不夠用，表緩存沒有完全用上，那就要考慮增加table_cache的大小了。還有就是Table_locks_waited比較高的時候，也需要增加table_cache

        如果發現 open_tables 接近 table_cache 的時候，如果 Opened_tables 随着重新運作 SHOW STATUS 指令快速增加，就說明緩存命中率不夠，并且多次執行FLUSH TABLES(通過shell > mysqladmin -uroot -ppassword variables status )，那就說明可能 table_cache 設定的偏小，經常需要将緩存的表清出，将新的表放入緩存，這時可以考慮增加這個參數的大小來改善通路的效率。

        如果 Open_tables 比 table_open_cache 設定小很多，就說明table_cache 設的太大了。

        table_open_cache的值在2G記憶體以下的機器中的值預設時256到512，如果機器有4G記憶體則預設這個值是2048，但這決意味着機器記憶體越大，這個值應該越大，因為table_open_cache加大後，使得mysql對SQL響應的速度更快了，不可避免的會産生更多的死鎖（dead lock），這樣反而使得資料庫整個一套操作慢了下來，嚴重影響性能。

        注意，不能盲目地把table_open_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，進而造成性能不穩定或者連接配接失敗。

        對于有1G記憶體的機器，推薦值是128－256。

MySQL如何打開和關閉表

        table_cache、max_connections和max_tmp_tables系統變量影響伺服器保持打開的檔案的最大數量。如果你增加這些值其中的一個或兩個，會遇到作業系統為每個程序打開檔案描述符的數量強加的限制。許多作業系統允許你增加打開的檔案的限制，盡管該方法随系統的不同而不同。查閱作業系統文檔以确定是否可以增加限制以及如何操作。

        table_cache與max_connections有關。例如，對于200個并行運作的連接配接，應該讓表的緩存至少有200 * N，這裡N是可以執行的查詢的一個聯接中表的最大數量。還需要為臨時表和檔案保留一些額外的檔案描述符。

        確定作業系統可以處理table_cache設定所指的打開的檔案描述符的數目。如果table_cacheis設得太高，MySQL可能為檔案描述符耗盡資源并拒絕連接配接，不能執行查詢，并且很不可靠。還必須考慮到MyISAM存儲引擎需要為每個打開的表提供兩個檔案描述符。可以在mysqld_safe中使用--open-files-limit啟動選項來增加MySQL适用的檔案描述符的數量。參見A.2.17節，“檔案未找到”。

        打開表的緩存可以保持在table_cache條。 預設為64；可以用mysqld的--table_cache選項來更改。請注意 MySQL可以臨時打開更多的 表以執行查詢。

在下面的條件下，未使用的表将被關閉并從表緩存中移出：

•         當緩存滿了并且一個線程試圖打開一個不在緩存中的表時。

•         當緩存包含超過table_cache個條目，并且緩存中的表不再被任何線程使用。

•         當表重新整理操作發生。當執行FLUSH TABLES語句或執行mysqladmin flush-tables或mysqladmin refresh指令時會發生。

當表緩存滿時，伺服器使用下列過程找到一個緩存入口來使用：

•         目前未使用的表被釋放，以最近最少使用順序。

•         如果緩存滿了并且沒有表可以釋放，但是一個新表需要打開，緩存必須臨時被擴大。

        如果緩存處于一個臨時擴大狀态并且一個表從在用變為不在用狀态，它被關閉并從緩存中釋放。

        對每個并發通路打開一個表。這意味着，如果2個線程通路同一個表或在同一個查詢中通路表兩次(例如，将表連接配接為自身時)，表需要被打開兩次。每個并行的打開要求在表緩存中有一個條目。任何表的第一次打開占2個檔案描述符：一個用于資料檔案另一個用于索引檔案。表的每一次額外使用僅占一個資料檔案的檔案描述符。索引檔案描述符在所有線程之間共享。

        如果你正用HANDLER tbl_name OPEN語句打開一個表，将為該線程專門配置設定一個表。該表不被其它線程共享，隻有線程調用HANDLER tbl_name CLOSE或線程終止後才被關閉。表關閉後，被拉回表緩存中(如果緩存不滿)。參見13.2.3節，“HANDLER文法”。

        可以通過檢查mysqld的狀态變量Opened_tables确定表緩存是否太小：

mysql> SHOW STATUS LIKE \'Opened_tables\';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Opened_tables | 2741  |

+---------------+-------+

        如果值很大，即使你沒有發出許多FLUSH TABLES語句，也應增加表緩存的大小。參見5.3.3節，“伺服器系統變量”和5.3.4節，“伺服器狀态變量”。

MySQL如何計算打開的表

當運作mysqladmin status時，将看見象這樣的一些東西：

Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

如果你僅有6個表，Open tables值為12可能有點令人困惑。

MySQL是多線程的，是以許多客戶可以同時在同一個表上進行查詢。為了使多個客戶線程在同一個表上有不同狀态的問題減到最小，表被每個并發程序獨立地打開。這樣需要額外的記憶體但一般會提高性能。對于MyISAM表，資料檔案需要為每個打開表的客戶提供一個額外的檔案描述符。(索引檔案描述符在所有線程之間共享)。

10．系統鎖（表鎖/行鎖）情況

mysql> show status like ‘%lock%’;

+——————————-+———+

| Variable_name                 | Value   |

+——————————-+———+

| Com_lock_tables               | 0       |

| Com_unlock_tables             | 0       |

| Innodb_row_lock_current_waits | 0       |

| Innodb_row_lock_time          | 0       |

| Innodb_row_lock_time_avg      | 0       |

| Innodb_row_lock_time_max      | 0       |

| Innodb_row_lock_waits         | 0       |

| Table_locks_immediate         | 2667760 |

| Table_locks_waited            | 0       |

        變量：

        * Table_locks_immediate：     産生表級鎖定的次數(立即獲得的表的鎖的次數)；

        * Table_locks_waited：           出現表級鎖定争用而發生等待的次數(不能立即獲得的表的鎖的次數)。

        這兩個狀态變量記錄MySQL内部表級鎖定的情況，兩個狀态值都是從系統啟動後開始記錄，每出現一次對應的事件則數量加1。如果這裡的Table_locks_waited狀态值比較高，那麼說明系統中表級鎖定争用現象比較嚴重，就須要進一步分析為什麼會為有較多的鎖定資源争用了。由于Table_locks_waited顯示了多少表被鎖住并導緻了mysql的鎖等待，可以開啟慢查詢看一下。

          對于InnoDB所使用的行級鎖定，系統是通過另外一組更為詳細的狀态變量來記錄的InnoDB的行級鎖定狀态變量不僅記錄了鎖定等待的次數，還記錄了鎖定總時長、每次平均時長、以及最大時長，此外還有一個非累計狀态量顯示了目前正在等待的數量。對各個狀态的說明如下：

        * Innodb_row_lock_current_waits：目前正在等待鎖定的數量

        * Innodb_row_lock_time ：             從系統啟動到現在鎖定的總時間長度(行鎖定用的總時間(ms))

        * Innodb_row_lock_time_avg ：     每次等待所花平均時間(行鎖定的平均時間(ms))，該值大，說明鎖沖突大

        * Innodb_row_lock_time_max：     從系統啟動到現在等待最長的一次所花的時間(行鎖定的最長時間(ms))；

        * Innodb_row_lock_waits ：            從系統啟動到現在總共等待的次數(行鎖定必須等待的時間(ms))，該值大，說明鎖沖突大

          對于這5個狀态變量，比較重要的是 Innodb_row_lock_time_avg，Innodb_row_lock_waits 以及Innodb_row_lock_time這三項。尤其是當等待次數很高，而且每次等待時長也不小的時候，就須要分析系統中為什麼又如此多的等待，然後根據分析結果着手制定優化計劃。

2．使用mysqlreport關注Table Locks，InnoDB Lock

__ Questions ___________________________________________________________

Total           3.38M    81.4/s

DMS           2.88M    69.3/s  %Total:  85.11

QC Hits     382.70k     9.2/s           11.32

Com_         90.50k     2.2/s            2.68

COM_QUIT     30.15k     0.7/s            0.89

+Unknown         18     0.0/s            0.00

Slow 1 s           92     0.0/s            0.00  %DMS:   0.00  Log: OFF

。。。。。。

__ Table Locks _________________________________________________________

Waited              0       0/s  %Total:   0.00

Immediate       2.67M    64.2/s

。。。。。。

__ InnoDB Lock _________________________________________________________

Waits               0       0/s

Current             0

Time acquiring

Total             0 ms

Average           0 ms

Max               0 ms

。。。。。。

        如果wait過多，平均時間過長，那就是查詢設計的有問題，仔細關注下超長時間的查詢，并打開slow_query_log。

鎖定事宜

1．鎖定方法

        MySQL 5.1支援對MyISAM和MEMORY表進行表級鎖定，對BDB表進行頁級鎖定，對InnoDB表進行行級鎖定。

        在許多情況下，可以根據教育訓練猜測應用程式使用哪類鎖定類型最好，但一般很難說出某個給出的鎖類型就比另一個好。一切取決于應用程式，應用程式的不同部分可能需要不同的鎖類型。

        為了确定是否想要使用行級鎖定的存儲引擎，應看看應用程式做什麼并且混合使用什麼樣的選擇和更新語句。例如，大多數Web應用程式執行許多選擇，而很少進行删除，隻對關鍵字的值進行更新，并且隻插入少量具體的表。基本MySQL MyISAM設定已經調節得很好。

        在MySQL中對于使用表級鎖定的存儲引擎，表鎖定時不會死鎖的。這通過總是在一個查詢開始時立即請求所有必要的鎖定并且總是以同樣的順序鎖定表來管理。

        對WRITE，MySQL使用的表鎖定方法原理如下：

•              如果在表上沒有鎖，在它上面放一個寫鎖。

•              否則，把鎖定請求放在寫鎖定隊列中。

        對READ，MySQL使用的鎖定方法原理如下：

•              如果在表上沒有寫鎖定，把一個讀鎖定放在它上面。

•              否則，把鎖請求放在讀鎖定隊列中。

        當一個鎖定被釋放時，鎖定可被寫鎖定隊列中的線程得到，然後是讀鎖定隊列中的線程。

這意味着，如果你在一個表上有許多更新，SELECT語句将等待直到沒有更多的更新。

        可以通過檢查table_locks_waited和table_locks_immediate狀态變量來分析系統上的表鎖定争奪：

mysql> SHOW STATUS LIKE \'Table%\';

+-----------------------+---------+

| Variable_name         | Value   |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited    | 15324   |

+-----------------------+---------+

        如果INSERT語句不沖突，可以自由為MyISAM表混合并行的INSERT和SELECT語句而不需要鎖定。也就是說，你可以在其它客戶正讀取MyISAM表的時候插入行。如果資料檔案中間不包含空閑塊，不會發生沖突，因為在這種情況下，記錄總是插入在資料檔案的尾部。(從表的中部删除或更新的行可能導緻空洞）。如果有空洞，當所有空洞填入新的資料時，并行的插入能夠重新自動啟用。

        如果不能同時插入，為了在一個表中進行多次INSERT和SELECT操作，可以在臨時表中插入行并且立即用臨時表中的記錄更新真正的表。

        這可用下列代碼做到：

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;

mysql> INSERT INTO real_table SELECT * FROM insert_table;

mysql> TRUNCATE TABLE insert_table;

mysql> UNLOCK TABLES;

        InnoDB使用行鎖定，BDB使用頁鎖定。對于這兩種存儲引擎，都可能存在死鎖。這是因為，在SQL語句處理期間，InnoDB自動獲得行鎖定和BDB獲得頁鎖定，而不是在事務啟動時獲得。

        行級鎖定的優點：

•         當在許多線程中通路不同的行時隻存在少量鎖定沖突。

•         復原時隻有少量的更改。

•         可以長時間鎖定單一的行。

        行級鎖定的缺點：

•         比頁級或表級鎖定占用更多的記憶體。

•         當在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須擷取更多的鎖。

•         如果你在大部分資料上經常進行GROUP BY操作或者必須經常掃描整個表，比其它鎖定明顯慢很多。

•         用進階别鎖定，通過支援不同的類型鎖定，你也可以很容易地調節應用程式，因為其鎖成本小于行級鎖定。

        在以下情況下，表鎖定優先于頁級或行級鎖定：

•         表的大部分語句用于讀取。

•         對嚴格的關鍵字進行讀取和更新，你可以更新或删除可以用單一的讀取的關鍵字來提取的一行：

                UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

                DELETE FROM tbl_name WHERE unique_key_col=key_value;

•         SELECT 結合并行的INSERT語句，并且隻有很少的UPDATE或DELETE語句。

•         在整個表上有許多掃描或GROUP BY操作，沒有任何寫操作。

        不同于行級或頁級鎖定的選項：

•         版本(例如，為并行的插入在MySQL中使用的技術)，其中可以一個寫操作，同時有許多讀取操作。這說明資料庫或表支援資料依賴的不同視圖，取決于通路何時開始。其它共同的術語是“時間跟蹤”、“寫複制”或者“按需複制”。

•         按需複制在許多情況下優先于頁級或行級鎖定。然而，在最壞的情況下，它可能比使用正常鎖定使用更多的記憶體。

•         除了行級鎖定外，你可以使用應用程式級鎖定，例如在MySQL中使用GET_LOCK()和RELEASE_LOCK()。這些是建議性鎖定，它們隻能在運作良好的應用程式中工作。

2．表鎖定事宜

        為達到最高鎖定速度，除InnoDB和BDB之外，對所有存儲引擎，MySQL使用表鎖定(而不是頁、行或者列鎖定)。

        對于InnoDB和BDB表，如果你用LOCK TABLES顯式鎖定表，MySQL隻使用表鎖定。對于這些表類型，我們建議你根本不要使用LOCK TABLES，因為InnoDB使用自動行級鎖定而BDB使用頁級鎖定來保證事務隔離。

        對于大表，對于大多數應用程式，表鎖定比行鎖定更好，但存在部分缺陷。

        表鎖定使許多線程同時從一個表中進行讀取操作，但如果一個線程想要對表進行寫操作，它必須首先獲得獨占通路。更新期間，所有其它想要通路該表的線程必須等待直到更新完成。

        表更新通常情況認為比表檢索更重要，是以給予它們更高的優先級。這應確定更新一個表的活動不能“餓死”，即使該表上有很繁重的SELECT活動。

        表鎖定在這種情況下會造成問題，例如當線程正等待，因為硬碟已滿并且線上程可以處理之前必須有空閑空間。在這種情況下，所有想要通路出現問題的表的線程也被設定成等待狀态，直到有更多的硬碟空間可用。

        表鎖定在下面的情況下也存在問題：

•         一個客戶發出長時間運作的查詢。

•         然後，另一個客戶對同一個表進行更新。該客戶必須等待直到SELECT完成。

•         另一個客戶對同一個表上發出了另一個SELECT語句。因為UPDATE比SELECT優先級高，該SELECT語句等待UPDATE完成，并且等待第1個SELECT完成。

        下面描述了一些方法來避免或減少表鎖定造成的競争：

•         試圖使SELECT語句運作得更快。你可能必須建立一些摘要(summary)表做到這點。

•         用--low-priority-updates啟動mysqld。這将給所有更新(修改)一個表的語句以比SELECT語句低的優先級。在這種情況下，在先前情形的第2個SELECT語句将在UPDATE語句前執行，而不需要等候第1個SELECT完成。

•         可以使用SET LOW_PRIORITY_UPDATES=1語句指定具體連接配接中的所有更新應使用低優先級。參見13.5.3節，“SET文法”

•         可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先級。

•         可以用HIGH_PRIORITY屬性給與一個特定的SELECT語句較高優先級。參見13.2.7節，“SELECT文法”。

•         為max_write_lock_count系統變量指定一個低值來啟動mysqld來強制MySQL在具體數量的插入完成後臨時提高所有等待一個表的SELECT語句的優先級。這樣允許在一定數量的WRITE鎖定後給出READ鎖定。

•         如果你有關于INSERT結合SELECT的問題，切換到使用新的MyISAM表，因為它們支援并發的SELECT和INSERT。

•         如果你對同一個表混合插入和删除，INSERT DELAYED将會有很大的幫助。參見13.2.4.2節，“INSERT DELAYED文法”。

•         如果你對同一個表混合使用SELECT和DELETE語句出現問題，DELETE的LIMIT選項可以有所幫助。參見13.2.1節，“DELETE文法”。

•         對SELECT語句使用SQL_BUFFER_RESULT可以幫助使表鎖定時間變短。參見13.2.7節，“SELECT文法”。

•         可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊列。在這種情況下，寫鎖定和讀鎖定将具有相同的優先級，對一些應用程式會有幫助。

這裡是一些MySQL中表鎖定相關的技巧：

•         如果不混合更新與需要在同一個表中檢查許多行的選擇，可以進行并行操作。

•         可以使用LOCK TABLES來提高速度，因為在一個鎖定中進行許多更新比沒有鎖定的更新要快得多。将表中的内容切分為幾個表也可以有所幫助。

•         如果在MySQL中表鎖定時遇到速度問題，可以将你的表轉換為InnoDB或BDB表來提高性能。參見15.2節，“InnoDB存儲引擎”和15.5節，“BDB (BerkeleyDB)存儲引擎”。

11．表掃描情況

 mysql> show status like \'handler_read%\';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Handler_read_first    | 0     |

| Handler_read_key      | 0     |

| Handler_read_next     | 0     |

| Handler_read_prev     | 0     |

| Handler_read_rnd      | 0     |

| Handler_read_rnd_next | 20    |

+-----------------------+-------+

6 rows in set (0.00 sec)

        參數：

        * Handler_read_first：使用全索引掃描的次數。

        * Handler_read_key：使用索引次數，該值越高越好。

        * Handler_read_next：按照鍵順序讀下一行的請求數。使用索引描述時，從資料檔案取資料的次數

        * Handler_read_prev：使用索引描述時，按索引倒序從資料檔案取資料的次數。一般是order by/desc查詢

        * Handler_read_rnd：查詢直接操作資料檔案的次數，有可能未使用索引

        * Handler_read_rnd_next：在資料檔案中讀下一行的請求數。若該值非常大，說明使用了大量的表掃描，索引使用率不高或沒有使用索引。

mysql> show status like \'com_select\';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_select    | 318243|

+---------------+-------+

1 row in set (0.00 sec)

        參數：

        * Com_select：執行select操作次數 

相關參數：

        * read_buffer_size：MySql讀入緩沖區大小

        * read_rnd_buffer_size：MySql的随機讀緩沖區大小

        * handler_read_rnd_next：在資料檔案中讀下一行的請求數。若該值非常大，說明使用了大量的表掃描，索引使用率不高或沒有使用索引。

        MySQL 會配置設定一些記憶體來讀取表。理想情況下，索引提供了足夠多的資訊，可以隻讀入所需要的行，但是有時候查詢（設計不佳或資料本性使然）需要讀取表中大量資料。要了解這種行為，需要知道運作了多少個 SELECT 語句，以及需要讀取表中的下一行資料的次數（而不是通過索引直接通路）。

       表掃描比率：Handler_read_rnd_next / Com_select

       如果表掃描比率值超過 4000，就應該調優靜态參數read_buffer_size。如read_buffer_size=1M，若超過8M，那麼就要優化SQL了。

        當某個查詢運作時，MySQL需要為目前查詢字元串配置設定記憶體。

       * 對表進行順序掃描的請求将配置設定一個緩存區(變量read_buffer_size)。

       * 當按任意順序讀取行時(例如，按照排序順序)，将配置設定一個随機讀 緩存區(變量read_rnd_buffer_size)以避免硬碟搜尋。

        每個線程對表進行連續掃描時都會為掃描的每個表配置設定緩沖區，read_buffer_size變量控制這一緩沖區的大小。如果進行多次連續掃描，可能需要增加該值， 預設值為131072。和sort_buffer_size一樣，該參數對應的配置設定記憶體也是每連接配接獨享。

         當排序後按排序後的順序讀取行時，則通過随機讀緩沖區讀取行，避免搜尋硬碟。read_rnd_buffer_size是控制這一緩沖區的大小，将該變量設定為較大的值可以大大改進ORDER BY的性能。如果需要排序大量資料，可适當調高該值。但是，這是為每個用戶端配置設定的緩沖區(即該參數對應的配置設定記憶體是每連接配接獨享的)，是以你不應将全局變量設定為較大的值。相反，隻為需要運作大查詢的用戶端更改會話變量。

12．排序情況

mysql> show variables like \'sort_buffer_size\';

+------------------+--------+

| Variable_name    | Value  |

+------------------+--------+

| sort_buffer_size | 262144 |

+------------------+--------+

1 row in set (0.00 sec)

        參數：

        * sort_buffer_size：每個排序線程配置設定的緩沖區的大小。增加該值可以加快ORDER BY或GROUP BY操作。

mysql> show  status like \'sort%\';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Sort_merge_passes | 0     |

| Sort_range        | 0     |

| Sort_rows         | 0     |

| Sort_scan         | 0     |

+-------------------+-------+

4 rows in set (0.00 sec)

        參數：

        * Sort_merge_passes：排序算法已經執行的合并的數量。

        *  Sort_range：

        *  Sort_rows：

        * Sort_scan：

        一般的，查詢sort都會經曆三個步驟

        1.  查找where條件的值

        2. 排序值

        3. 讀排序後的行

        如果在第一步時增加Select_scan，則第三步就會是增加Sort_scan；如果第一步是增加 Select_range，則第三步就是 增加Sort_range。

        Sort_merge_passes 包括兩步：MySQL 首先會嘗試在記憶體中做排序，使用的記憶體大小由系統變量 Sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到記憶體中，MySQL 就會把每次在記憶體中排序的結果存到臨時檔案中，這時候會增加Sort_merge_passes。等 MySQL 找到所有記錄之後，再把臨時檔案中的記錄做一次排序。實際上，MySQL 會用另一個臨時檔案來存再次排序的結果，是以通常會看到 Sort_merge_passes 增加的數值是建臨時檔案數的兩倍。因為用到了臨時檔案，是以速度可能會比較慢，增加 Sort_buffer_size 會減少 Sort_merge_passes 和 建立臨時檔案的次數。但盲目的增加 Sort_buffer_size 并不一定能提高速度。

        如果 sort_merge_passes 狀态變量很大，這就訓示了磁盤的活動情況，表示需要注意 sort_buffer_size(說明排序緩沖區太小)。

        sort_buffer_size表示每個排序線程配置設定的緩沖區的大小。增加該值可以加快ORDER BY或GROUP BY操作。當 MySQL 必須要進行排序時，就會在從磁盤上讀取資料時配置設定一個排序緩沖區來存放這些資料行。如果要排序的資料太大，那麼資料就必須儲存到磁盤上的臨時檔案中，并再次進行排序。

        注意：該參數對應的配置設定記憶體是每個連接配接獨享，如果有100個連接配接，那麼實際配置設定的總共排序緩沖區大小為100 × 6 ＝ 600MB。是以，對于記憶體在4GB左右的伺服器推薦設定為6-8M。

相關參數：

        * myisam_sort_buffer_size：當在REPAIR TABLE或用CREATE INDEX建立索引或ALTER TABLE過程中排序 MyISAM索引配置設定的緩沖區。

        *  max_length_for_sort_data：filesort算法的索引值大小的限值

        *  max_seeks_for_key:限制根據鍵值尋找行時的最大搜尋數。

參見A.4.4節，“MySQL将臨時檔案儲存在哪裡”。

13．全聯接

mysql> show  status like \'%select_full__%\';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Select_full_join       | 0     |

| Select_full_range_join | 0     |

+------------------------+-------+

2 rows in set (0.00 sec)

        參數：

        * Select_full_joing：全聯接

        * Select_full_range_join：範圍查詢聯接

相關變量：

        * join_buffer_size：聯接查詢操作所能使用的緩沖區大小

        一般情況獲得快速聯接的最好方法是添加索引。當增加索引時不可能通過增加join_buffer_size值來獲得快速完全聯接。将為兩個表之間的每個完全聯接配置設定聯接緩沖區。對于多個表之間不使用索引的複雜聯接，需要多聯接緩沖區。join_buffer_size用于完全聯接的緩沖區的大小(當不使用索引的時候使用聯接操作)。和sort_buffer_size一樣，該參數對應的配置設定記憶體也是每個連接配接獨享。

         一般應避免無索引的完全聯接操作，如果 Select_full_range_join過高，則說明系統運作了很多範圍查詢聯接。

相關參數：

        * max_join_size：通過設定該值，你可以捕獲鍵使用不正确并可能花很長時間的SELECT語句。如果使用者想要執行沒有WHERE子句的花較長時間或傳回數百萬行的聯接，則設定它。

14．臨時表情況

mysql>  show  status like \'created_tmp%\';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0     |

| Created_tmp_files       | 5     |

| Created_tmp_tables      | 0     |

+-------------------------+-------+

3 rows in set (0.00 sec)

        參數：

        * Created_tmp_disk_tables：伺服器執行語句時在硬碟上自動建立的臨時表的數量

        * Created_tmp_files：           mysqld建立的臨時檔案個數

        *  Created_tmp_tables：        伺服器執行語句時在記憶體上自動建立的臨時表的數量，如果Created_tmp_disk_tables較大，你可能要增加tmp_table_size值使臨時表基于記憶體而不基于硬碟。

        Create_tmp_disk_tables為0表示不需要使用磁盤上的臨時表

mysql> show  variables like \'tmp_table%\';

+----------------+-----------+

| Variable_name  | Value     |

+----------------+-----------+

| tmp_table_size | 257949696 |

+----------------+-----------+

1 row in set (0.00 sec)

        變量：

        * tmp_table_size：臨時表容量

mysql> show  variables like \'max_heap%\';

+---------------------+-----------+

| Variable_name       | Value     |

+---------------------+-----------+

| max_heap_table_size | 257949696 |

+---------------------+-----------+

1 row in set (0.00 sec)

        變量：

        * max_heap_table_size：記憶體表容量

        臨時表可以在更進階的查詢中使用，其中資料在進一步進行處理（例如 GROUP BY 字句）之前，都必須先儲存到臨時表中；理想情況下，在記憶體中建立臨時表。但是如果臨時表變得太大，就需要寫入磁盤中。

        每次使用臨時表都會增大 Created_tmp_tables；基于磁盤的表也會增大 Created_tmp_disk_tables。對于這個比率，并沒有什麼嚴格的規則，因為這依賴于所涉及的查詢。長時間觀察 Created_tmp_disk_tables 會顯示所建立的磁盤表的比率，您可以确定設定的效率。 tmp_table_size 和 max_heap_table_size 都可以控制臨時表的最大大小，是以請確定在 my.cnf 中對這兩個值都進行了設定。

         由于當資料超過臨時表的最大值設定時，自動轉為磁盤表，此時因需要進行IO操作，性能會大大下降，而記憶體表不會，記憶體表滿後，會提示資料滿錯誤。

        如果Created_tmp_disk_tables值較高，則有可能是因為：tmp_table_size或者max_heap_table_size太小或者是選擇blob、text屬性的時候建立了臨時表；如果Created_tmp_tables 過高的話，那麼就需要優化查詢。

        變量tmp_table_size訓示臨時表的容量，如果記憶體内的臨時表超過該值，MySQL自動将它轉換為硬碟上的MyISAM表。如果你執行許多進階GROUP BY查詢并且有大量記憶體，則可以增加tmp_table_size的值。

        變量max_heap_table_size訓示記憶體表的容量，該變量設定MEMORY (HEAP)表可以增長到的最大空間大小。該變量用來計算MEMORY表的MAX_ROWS值。在已有的MEMORY表上設定該變量沒有效果，除非用CREATE TABLE或TRUNCATE TABLE等語句重新建立表。

相關變量：

        * tmpdir：儲存臨時檔案和臨時表的目錄。該變量可以設定為幾個路徑，按round-robin模式使用。在Unix中應該用冒号(‘:’)間隔開路徑，在Windows、NetWare和OS/2中用分号(‘；’)。用來将負荷分散到幾個實體硬碟上。如果MySQL伺服器為複制從伺服器，你不應将tmpdir設定為指向基于記憶體的檔案系統上的目錄或當伺服器主機重新開機時聲明的目錄。複制從伺服器需要部分臨時檔案來在機器重新開機後仍可用，以便它可以複制臨時表或執行LOAD DATA INFILE操作。如果伺服器重新開機時臨時檔案夾中的檔案丢失了，則複制失敗。但是，如果你使用MySQL 4.0.0或更新版本，你可以使用 slave_load_tmpdir變量設定從伺服器的臨時目錄。在這種情況下，從伺服器不再使用正常tmpdir，說明你可以将tmpdir設定到一個非固定位置。

15．二進制日志緩存

mysql> show status like\'%binlog%\';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Binlog_cache_disk_use  | 0     |

| Binlog_cache_use       | 69166 |

| Com_binlog             | 0     |

| Com_show_binlog_events | 0     |

| Com_show_binlogs       | 0     |

+------------------------+-------+

5 rows in set (0.00 sec)

        參數：

        * Binlog_cache_disk_use：在硬碟上建立的臨時檔案數量

        * Binlog_cache_use：        在記憶體中建立的臨時檔案數量

        * Com_binlog：                 二進制日志數量

        * Com_show_binlog_events：伺服器執行show binlog event語句的次數

        * Com_show_binglogs：

mysql> show variables like\'%binlog%\';

+-----------------------------------------+------------+

| Variable_name                           | Value      |

+-----------------------------------------+------------+

| binlog_cache_size                       | 4194304    |

| binlog_direct_non_transactional_updates | OFF        |

| binlog_format                           | MIXED      |

| innodb_locks_unsafe_for_binlog          | OFF        |

| max_binlog_cache_size                   | 8388608    |

| max_binlog_size                         | 1073741824 |

| sync_binlog                             | 0          |

+-----------------------------------------+------------+

7 rows in set (0.00 sec)

        變量：

        * binlog_cache_size：同時開啟事務和二進制日志時，每個用戶端日志的緩存大小

        * binlog_direct_non_transactional_update：

        * binlog_format：

        * innodb_locks_unsafe_for_binlog：

        * max_binlog_cache_size：多語句事務需要的記憶體

        * max_binlog_size：二進制日志檔案的大小限制，預設值1G

        * sync_binlog：控制二進制日志到硬碟的同步。

        mysql-bin.000001、mysql-bin.000002等檔案是資料庫的記錄檔，例如UPDATE一個表，或者DELETE一些資料，即使該語句沒有比對的資料，這個指令也會存儲到日志檔案中，還包括每個語句執行的時間，也會記錄進去的。

        Binlog Cache 用于在打開了二進制日志(binlog)記錄功能的環境，是 MySQL 用來提高binlog的記錄效率而設計的一個用于短時間内臨時緩存binlog資料的記憶體區域。

         一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特别頻繁，2MB～4MB是一個合适的選擇。但是如果我們的資料庫大事務較多，寫入量比較大，可與适當調高binlog_cache_size。

        同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由于記憶體大小不夠而使用臨時檔案(binlog_cache_disk_use)來緩存了。如果Binlog_cache_disk_use 和 Binlog_cache_use 比例很大，那麼就應該增加binlog_cache_size的值。

        binlog_cache_size訓示了在事務過程中容納二進制日志SQL語句的緩存大小。如果你經常使用大的，多語句事務，你可以增加binlog_cache_size的值以獲得更有的性能。Binlog_cache_use和Binlog_cache_disk_use狀态變量可以用來調整該變量的大小。

       Max_binlog_cache_size顯示了多語句事務需要更大的記憶體，如果多語句事務需要更大的記憶體，你會得到錯誤Multi-statement transaction required more than \'max_binlog_cache_size\' bytes of storage。

        如果二進制日志寫入的内容超出Max_binlog_size給定值，日志就會發生滾動。你不能将該變量設定為大于1GB或小于4096位元組。 預設值是1GB。

        請注意如果你正使用事務：事務以一個塊寫入二進制日志，是以不不能被幾個二進制日志拆分。是以，如果你有大的事務，二進制日志可能會大于max_binlog_size。

        如果max_relay_log_size為0, max_binlog_size的值也适用于中繼日志。

         sync_binlog值如果為正，當每個sync_binlog\'th寫入該二進制日志後，MySQL伺服器将它的二進制日志同步到硬碟上(fdatasync())。請注意如果在autocommit模式，每執行一個語句向二進制日志寫入一次，否則每個事務寫入一次。

         sync_binlog預設值是0，不與硬碟同步。值為1是最安全的選擇，因為崩潰時，你最多丢掉二進制日志中的一個語句/事務；但是，這是最慢的選擇(除非硬碟有電池備份緩存，進而使同步工作較快)。

相關參數：

       * max_relay_log_size如果複制從伺服器寫入中繼日志時超出給定值，則滾動中繼日志。通過該變量你可以對中繼日志和二進制日志設定不同的限制。但是，将該變量設定為0，MySQL可以對二進制日志和中繼日志使用max_binlog_size。max_relay_log_size必須設定在4096位元組和1GB(包括)之間，或為0。 預設值是0。

MySQL如何使用記憶體

        下面的清單中列出了mysqld伺服器使用記憶體的一些方法。在适用的地方，給出了記憶體相關的系統變量名：

•         鍵緩存(變量key_buffer_size)被所有線程共享；伺服器使用的其它緩存則根據需要配置設定。參見7.5.2節，“調節伺服器參數”。

•         每個連接配接使用具體線程的空間：

o        堆棧(預設64KB，變量thread_stack)

o        連接配接緩存區(變量net_buffer_length)

o        結果緩存區(變量net_buffer_length)

        連接配接緩存區和結果緩存區可以根據需要動态擴充到max_allowed_packet。當某個查詢運作時，也為目前查詢字元串配置設定記憶體。

•         所有線程共享相同的基本記憶體。

•         隻有壓縮MyISAM表映射到記憶體。這是因為4GB的32位記憶體空間不足以容納大多數大表。當64位位址空間的系統變得越來越普遍後，我們可以增加正常的記憶體映射支援。

•         對表進行順序掃描的請求将配置設定一個緩存區(變量read_buffer_size)。

•         當按任意順序讀取行時(例如，按照排序順序)，将配置設定一個随機讀 緩存區(變量read_rnd_buffer_size)以避免硬碟搜尋。

•         所有聯合在一個令牌内完成，并且大多數聯合甚至可以不用臨時表即可以完成。大多數臨時表是基于記憶體的(HEAP)表。具有大的記錄長度的臨時表 (所有列的長度的和)或包含BLOB列的表存儲在硬碟上。

        如果某個内部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動将記憶體中的heap表改為基于硬碟的MyISAM表。還可以通過設定mysqld的tmp_table_size選項來增加臨時表的大小，或設定客戶程式中的SQL選項SQL_BIG_TABLE。參見13.5.3節，“SET文法”。

•         進行排序的大多數請求将配置設定一個排序緩存區，并根據結果集的大小為兩個臨時檔案配置設定零。參見A.4.4節，“MySQL将臨時檔案儲存在哪裡”。

•         幾乎所有解析和計算在局部記憶體中完成。小項目不需要記憶體，是以避免了普通的慢記憶體配置設定和釋放。隻為不期望的大字元串配置設定記憶體；使用函數malloc()和free()來完成。

•         對于每個打開的MyISAM表，索引檔案打開一次；資料檔案為每個并行運作的線程打開一次。對于每個并行線程，将配置設定一個表結構、一個每個列的列結構和大小為3 * N的緩存區(其中N是最大行的長度，而不是計算BLOB列)。一個BLOB列需要5至8個位元組加上BLOB資料的長度。MyISAM 存儲引擎維護一個額外的行緩存區供内部應用。

•         對于每個具有BLOB列的表，将對緩存區進行動态擴大以讀入大的BLOB 值。如果你掃描一個表，則配置設定一個與最大的BLOB值一樣大的緩存區。

•         所有使用的表的句柄結構儲存在高速緩存中并以FIFO管理。預設情況，高速緩存有64個入口。如果某個表同時被兩個運作的線程使用，高速緩存則為該提供兩個入口。參見7.4.9節，“MySQL如何打開和關閉表”。

•         當并行執行的線程結束時，FLUSH TABLE語句或mysqladmin flush-table指令可以立即關閉所有不使用的表并将所有使用中的表标記為已經關閉。這樣可以有效釋放大多數使用中的記憶體。FLUSH TABLE在關閉所有表之前不傳回結果。

        ps和其它系統狀态程式可以報導mysqld使用很多記憶體。這可以是在不同的記憶體位址上的線程棧造成的。例如，Solaris版本的ps将棧間未用的記憶體算作已用的記憶體。你可以通過用swap -s檢查可用交換區來驗證它。我們用商業記憶體漏洞探查器測試了mysqld，是以應該有沒有記憶體漏洞。

16．InnoDB相關狀态

關于InnoDB的性能分析，MySQL官方文檔中有一節作專門分析：InnoDB性能調節揭示。

變量：

mysql> show variables like \'innodb%\';

+---------------------------------+------------------------+

| Variable_name                   | Value                  |

+---------------------------------+------------------------+

| innodb_adaptive_hash_index      | ON                     |

| innodb_additional_mem_pool_size | 2097152                |

| innodb_autoextend_increment     | 8                      |

| innodb_autoinc_lock_mode        | 1                      |

| innodb_buffer_pool_size         | 17825792               |

| innodb_checksums                | ON                     |

| innodb_commit_concurrency       | 0                      |

| innodb_concurrency_tickets      | 500                    |

| innodb_data_file_path           | ibdata1:10M:autoextend |

| innodb_data_home_dir            |                        |

| innodb_doublewrite              | ON                     |

| innodb_fast_shutdown            | 1                      |

| innodb_file_io_threads          | 4                      |

| innodb_file_per_table           | OFF                    |

| innodb_flush_log_at_trx_commit  | 1                      |

| innodb_flush_method             |                        |

| innodb_force_recovery           | 0                      |

| innodb_lock_wait_timeout        | 50                     |

| innodb_locks_unsafe_for_binlog  | OFF                    |

| innodb_log_buffer_size          | 8388608                |

| innodb_log_file_size            | 16777216               |

| innodb_log_files_in_group       | 2                      |

| innodb_log_group_home_dir       | .\                     |

| innodb_max_dirty_pages_pct      | 90                     |

| innodb_max_purge_lag            | 0                      |

| innodb_mirrored_log_groups      | 1                      |

| innodb_open_files               | 300                    |

| innodb_rollback_on_timeout      | OFF                    |

| innodb_stats_on_metadata        | ON                     |

| innodb_support_xa               | ON                     |

| innodb_sync_spin_loops          | 20                     |

| innodb_table_locks              | ON                     |

| innodb_thread_concurrency       | 8                      |

| innodb_thread_sleep_delay       | 10000                  |

+---------------------------------+------------------------+

34 rows in set (0.05 sec)

參數：

mysql> show status like \'innodb%\';

+-----------------------------------+---------+

| Variable_name                     | Value   |

+-----------------------------------+---------+

| Innodb_buffer_pool_pages_data     | 96      | 配置設定出去，正在被使用頁的數量，包括髒頁。機關page

| Innodb_buffer_pool_pages_dirty    | 0       |髒頁但沒有被flush除去的頁面數。機關page

| Innodb_buffer_pool_pages_flushed  | 795       |已經flush的頁面數。機關page

| Innodb_buffer_pool_pages_free     | 992     |目前空閑頁面數。機關page

| Innodb_buffer_pool_pages_misc     | 64       |緩存池中目前已經被用作管理用途或hash index而不能用作為普通資料頁的數目。機關page

| Innodb_buffer_pool_pages_total    | 4096    |緩沖區總共的頁面數。機關page

| Innodb_buffer_pool_read_ahead_rnd | 8       |随機預讀的次數

| Innodb_buffer_pool_read_ahead_seq | 1       |順序預讀的次數

| Innodb_buffer_pool_read_requests  | 1725871    |從緩沖池中讀取頁的次數

| Innodb_buffer_pool_reads          | 2108      |從磁盤讀取頁的次數。緩沖池裡面沒有， 就會從磁盤讀取

| Innodb_buffer_pool_wait_free      | 0       |緩沖池等待空閑頁的次數，當需要空閑塊而系統中沒有時，就會等待空閑頁面

| Innodb_buffer_pool_write_requests | 2296       |緩沖池總共發出的寫請求次數

| Innodb_data_fsyncs                | 695       |總共完成的fsync次數

| Innodb_data_pending_fsyncs        | 0       | innodb目前等待的fsync次數

| Innodb_data_pending_reads         | 0       | innodb目前等待的讀的次數

| Innodb_data_pending_writes        | 0       | innodb目前等待的寫的次數

| Innodb_data_read                  | 44044288 |總共讀入的位元組數

| Innodb_data_reads                 | 2191     | innodb完成的讀的次數

| Innodb_data_writes                | 1296      | innodb完成的寫的次數

| Innodb_data_written               | 26440192    |總共寫出的位元組數

| Innodb_dblwr_pages_written        | 795       |

| Innodb_dblwr_writes               | 90       |

| Innodb_log_waits                  | 0       |因日志緩存太小而必須等待其被寫入所造成的等待數。機關是次

| Innodb_log_write_requests         | 0       |

| Innodb_log_writes                 | 1       |

| Innodb_os_log_fsyncs              | 3       |

| Innodb_os_log_pending_fsyncs      | 0       |

| Innodb_os_log_pending_writes      | 0       |

| Innodb_os_log_written             | 512     |

| Innodb_page_size                  | 16384   |

| Innodb_pages_created              | 0       |

| Innodb_pages_read                 | 96      |

| Innodb_pages_written              | 0       |

| Innodb_row_lock_current_waits     | 0       |

| Innodb_row_lock_time              | 0       |

| Innodb_row_lock_time_avg          | 0       |

| Innodb_row_lock_time_max          | 0       |

| Innodb_row_lock_waits             | 0       |

| Innodb_rows_deleted               | 0       |

| Innodb_rows_inserted              | 0       |

| Innodb_rows_read                  | 0       |

| Innodb_rows_updated               | 0       |

+-----------------------------------+---------+

42 rows in set (0.00 sec)

innodb_buffer_pool_size：

        這是InnoDB最重要的設定，對InnoDB性能有決定性的影響。innodb_buffer_pool_size 定義了 InnoDB 存儲引擎的表資料和索引資料的最大記憶體緩沖區大小。即用來設定用于緩存 InnoDB 索引及資料塊的記憶體區域大小，類似于 MyISAM 存儲引擎的 key_buffer_size 參數。和 MyISAM 存儲引擎不同， MyISAM 的 key_buffer_size 隻能緩存索引鍵，而 innodb_buffer_pool_size 卻可以緩存資料塊和索引鍵。簡單來說，當我們操作一個 InnoDB 表的時候，傳回的所有資料或者去資料過程中用到的任何一個索引塊，都會在這個記憶體區域中走一遭。适當的增加這個參數的大小，可以有效的減少 InnoDB 類型的表的磁盤 I/O 。為Innodb加速優化首要參數。預設值8M。

        如果不使用InnoDB存儲引擎，可以不用調整這個參數，如果需要使用，在記憶體允許的情況下，盡可能将所有的InnoDB資料檔案存放如記憶體中。這個參數不能動态更改，是以配置設定需多考慮。配置設定過大，會使Swap占用過多，緻使Mysql的查詢特慢。

在隻有InnoDB存儲引擎的資料庫伺服器上面，可以設定60-80%的記憶體。更精确一點，在記憶體容量允許 的情況下面設定比InnoDB tablespaces大10%的記憶體大小。即如果你的資料量不大，并且不會暴增，那麼可配置設定是你的資料大小＋１０％左右做為這個參數的值。例如：資料大小為50M,那麼給這個值配置設定innodb_buffer_pool_size＝64M

        命中率=innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_read_ahead + innodb_buffer_pool_reads)

        也可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算緩存命中率，并根據命中率來調整 innodb_buffer_pool_size 參數大小進行優化。

innodb_additional_mem_pool_size：

        作用：設定了InnoDB存儲引擎用來存放資料字典資訊以及一些内部資料結構的記憶體空間大小

        這個值不用配置設定太大，系統可以自動調。不用設定太高。通常比較大資料設定16Ｍ夠用了，如果表比較多，可以适當的增大。如果這個值自動增加，會在error log有中顯示的。20M足夠了。

innodb_log_file_size

        作用：指定日志的大小，該參數決定了recovery speed。太大的話recovery就會比較慢，太小了影響查詢性能，一般取256M可以兼顧性能和recovery的速度。

        配置設定原則：幾個日志成員大小加起來差不多和你的innodb_buffer_pool_size相等。在高寫入負載尤其是大資料集的情況下很重要。這個值越大則性能相對越高，但是要注意到可能會增加恢複時間。

        說明：這個值配置設定的大小和資料庫的寫入速度，事務大小，異常重新開機後的恢複有很大的關系。

innodb_log_buffer_size：

        這是 InnoDB 存儲引擎的事務日志所使用的緩沖區。由于磁盤速度是很慢的，直接将log寫道磁盤會影響InnoDB的性能，該參數設定了log buffer的大小，一般4M。如果有大的blob操作，可以适當增大。

        類似于 Binlog Buffer，InnoDB 在寫事務日志的時候，為了提高性能，也是先将資訊寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 參數所設定的相應條件(或者日志緩沖區寫滿)之後，才會将日志寫到檔案(或者同步到磁盤)中。可以通過 innodb_log_buffer_size 參數設定其可以使用的最大記憶體空間。

        作用：事務在記憶體中的緩沖。

        配置設定原則：控制在2-8M.這個值不用太多的。他裡面的記憶體一般一秒鐘寫到磁盤一次。具體寫入方式和你的事務送出方式有關。一般最大指定為4M比較合适。

        參考：Innodb_os_log_written(show global status 可以拿到)

        如果這個值增長過快，可以适當的增加innodb_log_buffer_size

        另外如果你需要處理大理的text，或是blog字段，可以考慮增加這個參數的值。

        預設的設定在中等強度寫入負載以及較短事務的情況下，伺服器性能還可以。如果存在更新操作峰值或者負載較大，就應該考慮加大它的值了。如果它的值設定太高了，可能會浪費記憶體 -- 它每秒都會重新整理一次，是以無需設定超過1秒所需的記憶體空間。通常 8-16MB 就足夠了。越小的系統它的值越小。

innodb_flush_log_at_trx_commit：

        作用：控制事務的送出方式；innodb_flush_log_trx_commit 參數對 InnoDB Log 的寫入性能有非常關鍵的影響。

        配置設定原則：這個參數隻有3個值，0，1，2請确認一下自已能接受的級别。預設為1，主庫請不要更改了。性能更高的可以設定為0或是2，但會丢失一秒鐘的事務。

        值為1時：innodb 的事務LOG在每次送出後寫入日志檔案，并對日志重新整理到磁盤。這個可以做到不丢任何一個事務。Truly ACID.速度慢。

        值為2時，也就是不把日志重新整理到磁盤上，而隻重新整理到作業系統的緩存上。日志仍然會每秒重新整理到磁盤中去，是以通常不會丢失每秒1-2次更新的消耗。隻有作業系統崩潰或掉電才會删除最後一秒的事務，不然不會丢失事務。

        值為 0時 就快很多了，不過也相對不安全了：日志緩沖每秒一次地被寫到日志檔案，并且對日志檔案做到磁盤操作的重新整理。任何mysqld程序的崩潰會删除崩潰前最後一秒的事務。設定為 2 隻會丢失重新整理到作業系統緩存的那部分事務。

          MySQL文檔中提到，這幾種設定中的每秒同步一次的機制，可能并不會完全確定非常準确的每秒就一定會發生同步，還取決于程序排程的問題。實際上，InnoDB 能否真正滿足此參數所設定值代表的意義正常 Recovery 還是受到了不同 OS 下檔案系統以及磁盤本身的限制，可能有些時候在并沒有真正完成磁盤同步的情況下也會告訴 mysqld 已經完成了磁盤同步。

        transaction-isolation=READ-COMITTED: 如果應用程式可以運作在READ-COMMITED隔離級别，做此設定會有一定的性能提升。

innodb_file_per_table：

        作用：使每個Innodb的表，有自已獨立的表空間。可以存儲每個InnoDB表和它的索引在它自己的檔案中。如删除檔案後可以回收那部分空間。

        配置設定原則：隻有使用不使用。但DB還需要有一個公共的表空間。

        InnoDB 預設會将所有的資料庫InnoDB引擎的表資料存儲在一個共享空間中：ibdata1，增删資料庫的時候，ibdata1檔案不會自動收縮，單個資料庫的備份也将成為問題。通常隻能将資料使用mysqldump 導出，然後再導入解決這個問題。

        檢視是否開啟：

               mysql> show variables like ‘%per_table%’;

        開啟

               innodb_file_per_table=1

innodb_open_files：

        作用：限制Innodb能打開的表的資料。

        配置設定原則：如果庫裡的表特别多的情況，請增加這個。這個值預設是300。這個值必須超過你配置的innodb_data_file_path個數。請适當的增加innodb_open_files。

innodb_flush_method：

        Innodb和系統打交道的一個IO模型。

        作用：設定InnoDB同步IO的方式

        配置設定原則：Windows不用設定。UNIX可以設定：Default，O_DIRECT，和O_DSYNC

        1） Default – 使用fsync（）。

        2） O_DSYNC – 以sync模式打開檔案，通常比較慢。

        3） O_DIRECT – 在Linux上使用Direct IO.可以顯着提高速度，特别是在RAID系統上。避免額外的資料複制和double buffering（mysql buffering 和OS buffering）。O_DIRECT跳過了作業系統的檔案系統Disk Cache，讓MySQL直接讀寫磁盤。 有資料表明，如果是大量随機寫入操作，O_DIRECT會提升效率。但是順序寫入和讀取效率都會降低。

innodb_max_dirty_pages_pct：

        這個參數和上面的各個參數不同，他不是用來設定用于緩存某種資料的記憶體大小的一個參數，而是用來控制在 InnoDB Buffer Pool 中可以不用寫入資料檔案中的Dirty Page 的比例(已經被修但還沒有從記憶體中寫入到資料檔案的髒資料)。這個比例值越大，從記憶體到磁盤的寫入操作就會相對減少，是以能夠一定程度下減少寫入操作的磁盤IO。

        但是，如果這個比例值過大，當資料庫 Crash 之後重新開機的時間可能就會很長，因為會有大量的事務資料需要從日志檔案恢複出來寫入資料檔案中。同時，過大的比例值同時可能也會造成在達到比例設定上限後的 flush 操作“過猛”而導緻性能波動很大。

        作用：控制Innodb的髒頁在緩沖中在那個百分比之下，值在範圍1-100,預設為90.

O_DIRECT的flush_method更适合于作業系統記憶體有限的情況下（可以避免不必要的對交換空間的讀寫操作），否則，它會由于禁用了os的緩沖降低對資料的讀寫操作的效能。

        使用memlock可以避免MySQL記憶體進入swap

innodb_data_file_path:

        作用：指定表資料和索引存儲的空間，可以是一個或者多個檔案。最後一個資料檔案必須是自動擴充的，也隻 有最後一個檔案允許自動擴充。這樣，當空間用完後，自動擴充資料檔案就會自動增長（以8MB為機關）以容納額外的資料。例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend兩 個資料檔案放在不同的磁盤上。資料首先放在ibdata1中，當達到900M以後，資料就放在ibdata2中。一旦達到50MB,ibdata2将以 8MB為機關自動增長。如果磁盤滿了，需要在另外的磁盤上面增加一個資料檔案。

innodb_autoextend_increment:

        預設是8M, 如果一次insert資料量比較多的話， 可以适當增加。

innodb_data_home_dir:

        作用：放置表空間資料的目錄，預設在mysql的資料目錄，設定到和MySQL安裝檔案不同的分區可以提高性能。

innodb_thread_concurrency：

        作用：InnoDB kernel最大的線程數。

　　1） 最少設定為（num_disks+num_cpus）*2.

　　2） 可以通過設定成1000來禁止這個限制

注： MySQL 中為了減少磁盤實體IO而設計的幾個主要參數，對 MySQL 的性能起到了至關重要的作用，參數包括：query_cache_size/query_cache_type(global)、binlog_cache_size(global)、key_buffer_size(global)、bulk_insert_buffer_size(thread)、innodb_buffer_pool_size(global)、innodb_log_buffer_size(global)、innodb_max_dirty_pages_pct(global).

        如果Unix頂層工具或者Windows任務管理器顯示，你的資料庫的工作負荷的CPU使用率小于70%，則你的工作負荷可能是磁盤綁定的，可能你正生成太多的事務和送出，或者緩沖池太小。使得緩沖池更大一些會有幫助的，但不要設定緩沖池等于或超過實體記憶體的80%.

         把數個修改裹進一個事務裡。如果事務對資料庫修改，InnoDB在該事務送出時必須重新整理日志到磁盤。因為磁盤旋轉的速度至多167轉/秒，如果磁盤沒有騙作業系統的話，這就限制送出的數目為同樣的每秒167次。

         如果你可以接受損失一些最近的已送出事務，你可以設定my.cnf檔案裡的參數innodb_flush_log_at_trx_commit為0。 無論如何InnoDB試着每秒重新整理一次日志，盡管重新整理不被許可。

         使用大的日志檔案，讓它甚至與緩沖池一樣大。當InnoDB寫滿日志檔案時，它不得不在一個檢查點把緩沖池已修改的内容寫進磁盤。小日志檔案導緻許多不必要的吸盤寫操作。大日志檔案的缺點時恢複時間更長。

         也讓日志緩沖相當大（與8MB相似的數量)。

         如果你存儲變長度字元串，或者列可能包含很多NULL值，則使用VARCHAR列類型而不是CHAR類型。一個CHAR(N)列總是占據N 位元組來存儲，即使字元串更短或字元串的值是NULL。越小的表越好地适合緩沖池并且減少磁盤I/O。

當使用row_format=compact （MySQL 5.1中預設的InnoDB記錄格式）和可變長度字元集，比如UTF-8或sjis，CHAR(N)将占據可變數量的空間，至少為N 位元組。

         在一些版本的GNU/Linux和Unix上，用Unix的fsync()（InnoDB預設使用的）把檔案重新整理到磁盤，并且其他相似的方法是驚人的慢。如果你不滿意資料庫的寫性能，你可以試着設定my.cnf裡的innodb_flush_method為O_DSYNC，雖然O_DSYNC在多數系統上看起來更慢。

         當在Solaris 10上，為x86_64架構（AMD Opteron)使用InnoDB存儲引擎，重要的是使用forcedirectio選項來安裝任何為存儲與InnoDB相關的檔案而使用的資料系統。（預設在Solaris 10/x86_64上不使用這個檔案系統安裝選項）。使用forcedirectio 失敗會導緻InnoDB在這個平台上的速度和性能嚴重退化。

         當導入資料到InnoDB中之時，請确信MySQL沒有允許autocommit模式，因為允許autocommit模式會需要每次插入都要重新整理日志到磁盤。要在導入操作規程中禁止autocommit模式，用SET AUTOCOMMIT和COMMIT語句來包住導入語句：

                SET AUTOCOMMIT=0;

                /* SQL import statements ... */

                COMMIT;

如果你使用mysqldump 選項--opt，即使不用SET AUTOCOMMIT和COMMIT語句來包裹，你也使得快速的轉儲檔案被導入到InnoDB表中。

         小心大宗插入的大復原：InnoDB在插入中使用插入緩沖來節約磁盤I/O，但是在相應的復原中沒有使用這樣的機制。一個磁盤綁定的復原可以用相應插入花費時間的30倍來執行。殺掉資料庫程序沒有是幫助的，因為復原在伺服器啟動時會再次啟動。除掉一個失控的復原的唯一方法是增大緩沖池使得復原變成CPU綁定且跑得快，或者使用專用步驟，請參閱15.2.8.1節，“強制恢複”。

         也要小心其它大的磁盤綁定操作。用DROP TABLE或CREATE TABLE來清空一個表，而不是用DELETE FROM tbl_name。

         如果你需要插入許多行，則使用多行插入文法來減少用戶端和伺服器之間的通訊開支：

                INSERT INTO yourtable VALUES (1,2), (5,5), ...;

這個提示對到任何表類型的插入都是合法的，不僅僅是對InnoDB類型。

         如果你在第二個鍵上有UNIQUE限制，你可以在導入會話中暫時關閉唯一性檢查以加速表的導入：

                SET UNIQUE_CHECKS=0;

對于大表，這節約了大量磁盤I/O，因為InnoDB可以使用它的插入緩沖來在一批内寫第二個索引記錄。

         如果你對你的表有FOREIGN KEY限制，你可以在導入會話過程中通過關閉外鍵檢查來提速表的導入：

                SET FOREIGN_KEY_CHECKS=0;

對于大表，這可以節約大量的磁盤I/O。

         如果你經常有對不經常更新的表的重發查詢，請使用查詢緩存：

                [mysqld]

                query_cache_type = ON

                query_cache_size = 10M

其它參數

1．bulk_insert_buffer_size (thread)

        和key_buffer_size一樣，這個參數同樣也僅作用于使用 MyISAM存儲引擎，用來緩存批量插入資料的時候臨時緩存寫入資料。當我們使用如下幾種資料寫入語句的時候，會使用這個記憶體區域來緩存批量結構的資料以幫助批量寫入資料檔案：

        insert … select …

        insert … values (…) ,(…),(…)…

        load data infile… into… (非空表)

        MyISAM 使用專用樹狀緩存來使INSERT ... SELECT、INSERT ... VALUES (...)、(...)、 ...和LOAD DATA INFILE的大塊插入更快。該變量用每線程的位元組數限制緩存樹的大小。将它設定為0禁用優化。

2．delay_key_write / delayed_insert_limit

mysql> show variables like \'delay%\';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| delay_key_write        | ON    |

| delayed_insert_limit   | 100   |

| delayed_insert_timeout | 300   |

| delayed_queue_size     | 1000  |

+------------------------+-------+

4 rows in set (0.00 sec)

        變量：

        * delay_key_write：

        * delayed_insert_limit：

        * delayed_insert_timeout：

        * delayed_queue_size：

mysql> show status like \'%delay%\';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| Delayed_errors           | 0     |

| Delayed_insert_threads   | 0     |

| Delayed_writes           | 0     |

| Not_flushed_delayed_rows | 0     |

+--------------------------+-------+

4 rows in set (0.06 sec)

        參數：

        * Delayed_errors：用INSERT DELAYED寫入的發生某些錯誤(可能重複鍵值)的行數

        * Delayed_insert_threads：正在使用的延遲插入處理器線程的數量

        * Delayed_writes：用INSERT DELAYED寫入的行數

        * Not_flushed_delayed_rows：在INSERT DELAY隊列中等待寫入的行的數量

        對資料的插入，如果可以容忍結果幾秒以後再生效的，就可以用 INSERT DELAYED INTO語句。DELAYED調節符應用于INSERT和REPLACE語句。當DELAYED插入操作到達的時候，伺服器把資料行放入一個隊列中，并立即給用戶端傳回一個狀态資訊，這樣用戶端就可以在資料表被真正地插入記錄之前繼續進行操作了。如果讀取者從該資料表中讀取資料，隊列中的資料就會被保持着，直到沒有讀取者為止。接着伺服器開始插入延遲資料行（delayed-row）隊列中的資料行。在插入操作的同時，伺服器還要檢查是否有新的讀取請求到達和等待。如果有，延遲資料行隊列就被挂起， 允許讀取者繼續操作。當沒有讀取者的時候，伺服器再次開始插入延遲的資料行。 這個過程一直進行，直到隊列空了為止。

         使用SHOW PROCESSLIST，可以看到使用者名為 DELAYED 的程序，程序數量等于 INSERT DELAYED 的表的數量，因為表級鎖的存在，每個表一條以上的 DELAYED 程序是沒有意義的。

        注意事項：

       * INSERT DELAYED應該僅用于指定值清單的INSERT語句。伺服器忽略用于INSERT DELAYED...SELECT語句的DELAYED。 

        * 伺服器忽略用于INSERT DELAYED...ON DUPLICATE UPDATE語句的DELAYED。 

        * 因為在行被插入前，語句立刻傳回，是以您不能使用LAST_INSERT_ID()來擷取AUTO_INCREMENT值。AUTO_INCREMENT值可能由語句生成。 

        * 對于SELECT語句，DELAYED行不可見，直到這些行确實被插入了為止。 

         * DELAYED在從屬複制伺服器中被忽略了，因為DELAYED不會在從屬伺服器中産生與主伺服器不一樣的資料。 

          注意，目前在隊列中的各行隻儲存在存儲器中，直到它們被插入到表中為止。這意味着，如果您強行中止了mysqld（例如，使用kill -9） 或者如果mysqld意外停止，則所有沒有被寫入磁盤的行都會丢失。

         關于Delay插入的功能，相關參數為：

        * delay_key_write：該選項隻适用MyISAM表。如果啟用了DELAY_KEY_WRITE，說明使用該項的表的鍵緩沖區在每次更新索引時不被清空，隻有關閉表時才清空。遮掩蓋可以大大加快鍵的寫操作，但如果你使用該特性，你應用--myisam-recover選項啟動伺服器，為所有MyISAM表添加自動檢查(例如，--myisam-recover=BACKUP,FORCE)。

          請注意--external-locking不為使用延遲鍵寫入的表提供索引破壞保護

         本參數具有下述值可以影響CREATE TABLE語句使用的DELAY_KEY_WRITE表選項的處理。

            OFF：DELAY_KEY_WRITE被忽略。

            ON：MySQL在CREATE TABLE中用DELAY_KEY_WRITE選項。這是 預設值。

            ALL：用啟用DELAY_KEY_WRITE選項建立表的相同方法對所有新打開表的進行處理。

        * delayed_insert_timeout：INSERT DELAYED處理器線程終止前應等待INSERT語句的時間。

        * delayed_queue_size：這是各個表中處理INSERT DELAYED語句時隊列中行的數量限制。如果隊列滿了，執行INSERT DELAYED語句的用戶端應等待直到隊列内再有空間。

3．low_priority_updates

        MySQL允許改變語句排程的優先級，它可以使來自多個用戶端的查詢更好地協作，這樣單個用戶端就不會由于鎖定而等待很長時間。改變優先級還可以確定特定類型的查詢被處理得更快。

        MySQL的預設的排程政策可用總結如下：

        * 寫入操作優先于讀取操作。

        * 對某張資料表的寫入操作某一時刻隻能發生一次，寫入請求按照它們到達的次序來處理。

        * 某張資料表的多個讀取操作可以同時地進行。

        MySQL提供了幾個語句調節符，允許你修改它的排程政策：

        * LOW_PRIORITY關鍵字應用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。

        * HIGH_PRIORITY關鍵字應用于SELECT和INSERT語句。

        * DELAYED關鍵字應用于INSERT和REPLACE語句。

        LOW_PRIORITY和HIGH_PRIORITY調節符影響那些使用資料表鎖的存儲引擎（例如MyISAM和MEMORY）。DELAYED調節符作用于MyISAM和MEMORY資料表。

        LOW_PRIORITY關鍵字影響DELETE、INSERT、LOAD DATA、REPLACE和UPDATE語句的執行排程。通常情況下，某張資料表正在被讀取的時候，如果有寫入操作到達，那麼寫入者一直等待讀取者完成操作（查詢開始之後就不能中斷，是以允許讀取者完成操作）。如果寫入者正在等待的時候，另一個讀取操作到達了，該讀取操作也會被阻塞（block），因為預設的排程政策是寫入者優先于讀取者。當第一個讀取者完成操作的時候，寫入者開始操作，并且直到該寫入者完成操作，第二個讀取者才開始操作。

        如果寫入操作是一個LOW_PRIORITY（低優先級）請求，那麼系統就不會認為它的優先級高于讀取操作。在這種情況下，如果寫入者在等待的時候，第二個讀取者到達了，那麼就允許第二個讀取者插到寫入者之前。隻有在沒有其它的讀取者的時候，才允許寫入者開始操作。理論上，這種排程修改暗示着，可能存在LOW_PRIORITY寫入操作永遠被阻塞的情況。如果前面的讀取操作在進行的過程中一直有其它的讀取操作到達，那麼新的請求都會插入到LOW_PRIORITY寫入操作之前。

        SELECT查詢的HIGH_PRIORITY（高優先級）關鍵字也類似。它允許SELECT插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優先級更高。另外一種影響是，高優先級的SELECT在正常的SELECT語句之前執行，因為這些語句會被寫入操作阻塞。

        如果你希望所有支援LOW_PRIORITY選項的語句都預設地按照低優先級來處理，那麼請使用--low-priority-updates選項來啟動伺服器。通過使用INSERT HIGH_PRIORITY來把INSERT語句提高到正常的寫入優先級，可以消除該選項對單個INSERT語句的影響。

        讀為主可以設定low_priority_updates=1，寫的優先級調低，告訴MYSQL盡量先處理讀求

        * --low-priority-updates啟動mysqld。這将給所有更新(修改)一個表的語句以比SELECT語句低的優先級。在這種情況下，在先前情形的第2個SELECT語句将在UPDATE語句前執行，而不需要等候第1個SELECT完成。

        * 可以使用SET LOW_PRIORITY_UPDATES=1語句指定具體連接配接中的所有更新應使用低優先級。參見13.5.3節，“SET文法”

        *  可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先級。

        * 可以用HIGH_PRIORITY屬性給與一個特定的SELECT語句較高優先級。參見13.2.7節，“SELECT文法”。

        *  為max_write_lock_count系統變量指定一個低值來啟動mysqld來強制MySQL在具體數量的插入完成後臨時提高所有等待一個表的SELECT語句的優先級。這樣允許在一定數量的WRITE鎖定後給出READ鎖定。

        * 如果你有關于INSERT結合SELECT的問題，切換到使用新的MyISAM表，因為它們支援并發的SELECT和INSERT。

        * 如果你對同一個表混合插入和删除，INSERT DELAYED将會有很大的幫助。參見13.2.4.2節，“INSERT DELAYED文法”。

         參數：low_priority_updates，預設值為OFF，如果設定為1，所有INSERT、UPDATE、DELETE和LOCK TABLE WRITE語句将等待直到受影響的表沒有挂起的SELECT或LOCK TABLE READ。

相關參數的建議值

        這裡列一下根據以往經驗得到的相關參數的建議值：

        *   query_cache_type : 如果全部使用innodb存儲引擎，建議為0，如果使用MyISAM 存儲引擎，建議為2，同時在SQL語句中顯式控制是否是喲你gquery cache

　　*   query_cache_size: 根據 命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進行調整，一般不建議太大，256MB可能已經差不多了，大型的配置型靜态資料可适當調大

　　*   binlog_cache_size: 一般環境2MB～4MB是一個合适的選擇，事務較大且寫入頻繁的資料庫環境可以适當調大，但不建議超過32MB

　　*   key_buffer_size: 如果不使用MyISAM存儲引擎，16MB足以，用來緩存一些系統表資訊等。如果使用 MyISAM存儲引擎，在記憶體允許的情況下，盡可能将所有索引放入記憶體，簡單來說就是“越大越好”

　　*   bulk_insert_buffer_size: 如果經常性的需要使用批量插入的特殊語句(上面有說明)來插入資料，可以适當調大該參數至16MB～32MB，不建議繼續增大，某人8MB

　　*   innodb_buffer_pool_size: 如果不使用InnoDB存儲引擎，可以不用調整這個參數，如果需要使用，在記憶體允許的情況下，盡可能将所有的InnoDB資料檔案存放如記憶體中，同樣将但來說也是“越大越好”

　　*   innodb_additional_mem_pool_size: 一般的資料庫建議調整到8MB～16MB，如果表特别多，可以調整到32MB，可以根據error log中的資訊判斷是否需要增大

　　*   innodb_log_buffer_size: 預設是1MB，系的如頻繁的系統可适當增大至4MB～8MB。當然如上面介紹所說，這個參數實際上還和另外的flush參數相關。一般來說不建議超過32MB

　　*   innodb_max_dirty_pages_pct: 根據以往的經驗，重新開機恢複的資料如果要超過1GB的話，啟動速度會比較慢，幾乎難以接受，是以建議不大于 1GB/innodb_buffer_pool_size(GB)*100 這個值。當然，如果你能夠忍受啟動時間比較長，而且希望盡量減少記憶體至磁盤的flush，可以将這個值調整到90，但不建議超過90

InnoDB Monitors

        InnoDB包含InnoDB Monitors，它列印有關InnoDB内部狀态的資訊。你可以使用SQL語句SHOW INNODB STATUS來取标準InnoDB Monitor的輸出送到你的SQL用戶端。這個資訊在性能調節時有用。

                mysql> SHOW INNODB STATUS\G

        另一個使用InnoDB Monitors的方法時讓它們不斷寫資料到mysqld伺服器的标準輸出。在這種情況下，沒有輸出被送到用戶端。當被打開的時候，InnoDB Monitors每15秒列印一次資料。伺服器輸出通常被定向到MySQL資料目錄裡的.err日志。這個資料在性能調節時有用。在Windows上，如果你想定向輸出到視窗而不是到日志檔案，你必須從控制台視窗的指令行使用--console選項來啟動伺服器。

        螢幕輸出包括下列類型的資訊：

        •         被每個激活事務持有的表和記錄鎖定

        •         事務的鎖定等待

        •         線程的信号等待

        •         未解決的檔案I/O請求

        •         緩沖池統計資料

        •         主InnoDB線程的淨化和插入緩沖合并活動

        要讓标準InnoDB Monitor寫到mysqld的标準輸出，請使用下列SQL語句：

                CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

        螢幕可以通過發出下列語句來被停止：

                DROP TABLE innodb_monitor;

        CREATE TABLE文法正是通過MySQL的SQL解析程式往InnoDB引擎傳遞指令的方法：唯一有問題的事情是表名字innodb_monitor及它是一個InnoDB表。對于InnoDB Monitor, 表的結構根本不相關。如果你在螢幕正運作時關閉伺服器，并且你想要再次啟動螢幕，你必須在可以發出新CREATE TABLE語句啟動螢幕之前移除表。這個文法在将來的發行版本中可能會改變。

        你可以以相似的方式使用innodb_lock_monitor。除了它也提供大量鎖定資訊之外，它與innodb_monitor相同。一個分離的 innodb_tablespace_monitor列印存在于表空間中已建立檔案片斷的一個清單，并且确認表空間配置設定資料結構。此外，有innodb_table_monitor，用它你可以列印InnoDB内部資料詞典的内容。

        關于這個輸出一些要注意的：

•         如果TRANSACTIONS節報告鎖定等待，你的應用程式可能有鎖定競争。輸出也幫助跟蹤事務死鎖的原因。

•         SEMAPHORES節報告等待一個信号的線程和關于線程對一個互斥體或rw-lock信号需要多少次復原或等待的統計資料。大量等待信号的線程可能是磁盤I/O的結果或者InnoDB内競争問題的結果。競争可能是因為查詢的嚴重并行，或者作業系統線程計劃的問題。設定innodb_thread_concurrency小于預設值在這種情況下是有幫助的。

•         BUFFER POOL AND MEMORY節給你關于頁面讀寫的統計資料。你可以從這些數計算你目前的查詢正做多少檔案資料I/O操作。

•         ROW OPERATIONS節顯示主線程正在做什麼。

        InnoDB發送診斷輸出到stderr或檔案，而不是到stdout或者固定尺寸記憶體緩沖，以避免底層緩沖溢出。作為一個副效果，SHOW INNODB STATUS的輸出每15秒鐘寫到一個狀态檔案。這個檔案的名字是innodb_status.pid，其中pid 是伺服器程序ID。這個檔案在MySQL資料目錄裡建立。正常關機之時，InnoDB删除這個檔案。如果發生不正常的關機， 這些狀态檔案的執行個體可能被展示，而且必須被手動删除。在移除它們之前，你可能想要檢查它們來看它們是否包含有關不正常關機的原因的有用資訊。僅在配置選項innodb_status_file=1被設定之時，innodb_status.pid檔案被建立。

附：SHOW STATUS的一些參數：

參數 說明
基本情況
Aborted_clients 由于客戶沒有正确關閉連接配接已經死掉，已經放棄的連接配接數量
Aborted_connects 嘗試已經失敗的MySQL伺服器的連接配接的次數。
Connections 連接配接伺服器(不管是否成功)的次數
Uptime 伺服器工作時間
Max_used_connections 同時使用的最大連接配接數量
Open_files 打開檔案的數量。
Open_tables 目前打開的表的數量。
Opened_tables

已經打開的表的數量。調優靜态變量表緩存數table_cache：如果open_tables接近table_cache，并且opened_tables不斷增長，就需要增加table_cache的值。

table_cache是所有線程打開的表的數目(一個表使用2個檔案描述符)，表數量多，就要大一些。增大該值可以增加mysqld需要的檔案描述符的數量。根據資料庫系統中表數量來決定該值，如2048。
線程使用情況
Threads_cached 線程緩存内的線程數
Threads_connected 目前打開的線程數
Threads_created

建立過的線程數。調優靜态變量線程緩存數thread_cache：如果該值增加很快，目前thread_cache_size的值可能太小。緩存通路率是Threads_created/Connections。

伺服器應緩存多少線程以便重新使用。當用戶端斷開連接配接時，如果線程少于thread_cache_size，則用戶端的線程被放入緩存，一般配置8。
Threads_running 運作(非睡眠)狀态的線程數
查詢緩存
Qcache_free_blocks 緩存中相鄰記憶體塊的個數。數目大說明可能有碎片。調優方法：FLUSH QUERY CACHE;會對緩存中的碎片進行整理，進而得到一個空閑塊，如果flush運作的時間很長，說明緩存太大了，可以适當調小靜态變量query_cache_size的值。
Qcache_free_memory 緩存中剩餘的記憶體。調優靜态參數query_cache_size：如果剩餘記憶體不足，可以增加該值，如設定query_cache_size=64M
Qcache_hits 查詢緩存命中次數，該值越大越好
Qcache_inserts 插入查詢緩存的次數。緩存命中率 = 1 – Qcache_hits/ Qcache_inserts。80%以上的查詢緩存命中率就算合格。
Qcache_lowmem_prunes 查詢緩存過低的次數。緩存出現記憶體不足并且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者記憶體很少。（上面的free_blocks 和 free_memory 可以告訴您屬于哪種情況）。
Qcache_not_cached 不适合進行緩存的查詢的數量，通常是由于這些查詢不是 SELECT 語句。
Qcache_queries_in_cache 目前緩存的查詢（和響應）的數量。
Qcache_total_blocks 緩存中塊的數量。
SQL執行頻率
Com_select 執行select操作次數
Com_insert 執行insert操作次數
Com_update 執行update操作次數
Com_delete 執行delete操作次數
Com_commit 事務執行commit操作次數
Comm_rollback 事務執行rollback操作次數。如果復原頻繁，就說明程式存在某些問題。
Slow_queries 慢查詢的次數。調優SQL性能：如果該值增加很快，需要分析慢查詢日志，針對查詢SQL優化。
Innodb_rows_read 執行select傳回的行數。以下幾個InnoDB的。
Innodb_rows_inserted 執行insert操作的行數。通過這幾個參數，可以知道資料庫是查詢為主還是插入為主。
Innodb_rows_updated 執行update操作的行數
Innodb_rows_deleted 執行delete操作的行數
Sort_merge_passes

排序算法已經執行的合并的數量。調優靜态變量sort_buffer_size：如果該值很大，說明排序緩沖區太小，如設定sort_buffer_size = 5M

當 MySQL 必須要進行排序時，就會在從磁盤上讀取資料時配置設定一個排序緩沖區來存放這些資料行。如果要排序的資料太大，那麼資料就必須儲存到磁盤上的臨時檔案中，并再次進行排序。
索引使用情況
Handler_read_first 使用全索引掃描的次數。如SELECT col1 FROM foo，假定col1有索引
Handler_read_key 使用索引次數，該值越高越好。
Handler_read_next 按照鍵順序讀下一行的請求數。使用索引描述時，從資料檔案取資料的次數
Handler_read_prev 使用索引描述時，按索引倒序從資料檔案取資料的次數。一般是order by/desc查詢
Handler_read_rnd 查詢直接操作資料檔案的次數，有可能未使用索引
Handler_read_rnd_next 在資料檔案中讀下一行的請求數。若該值非常大，說明使用了大量的表掃描，索引使用率不高或沒有使用索引。Handler_read_rnd_next/Com_select是表掃描比率，如果該值超過 4000，就應該調優靜态參數read_buffer_size。如read_buffer_size=1M，若超過8M，那麼就要優化SQL了。
鎖使用情況
Innodb_row_lock_current_waits 目前等待行鎖的行數
Innodb_row_lock_time 行鎖定用的總時間(ms)
Innodb_row_lock_time_avg 行鎖定的平均時間(ms)。該值大，說明鎖沖突大
Innodb_row_lock_time_max 行鎖定的最長時間(ms)
Innodb_row_lock_waits 行鎖定必須等待的時間(ms)。該值大，說明鎖沖突大

Created_tmp_tables 當執行語句時，已經被創造了的隐含臨時表的數量。

Delayed_insert_threads 正在使用的延遲插入處理器線程的數量。

Delayed_writes 用INSERT DELAYED寫入的行數。

Delayed_errors 用INSERT DELAYED寫入的發生某些錯誤(可能重複鍵值)的行數。

Flush_commands 執行FLUSH指令的次數。

Handler_delete 請求從一張表中删除行的次數。

Handler_update 請求更新表中一行的次數。

Handler_write 請求向表中插入一行的次數。

Key_blocks_used 用于關鍵字緩存的塊的數量。

Key_read_requests 請求從緩存讀入一個鍵值的次數。

Key_reads 從磁盤實體讀入一個鍵值的次數。

Key_write_requests 請求将一個關鍵字塊寫入緩存次數。

Key_writes 将一個鍵值塊實體寫入磁盤的次數。

Not_flushed_key_blocks 在鍵緩存中已經改變但是還沒被清空到磁盤上的鍵塊。

https://www.cnblogs.com/jevo/p/3314361.html

上一篇: ssm整合druid監控功能入門純注解
下一篇: springboot 整合 hive druid jdbcTemplate環境準備pom依賴application.ymljava配置處理使用jdbcTemplatedruid監控