每天進步一點點————最佳化MySQL SERVER（1），每天進步一點點

每天進步一點點————最佳化MySQL SERVER（1），每天進步一點點
1.   最佳化MySQL SERVER7組後台進程

         masterthread：主要負責將髒快取頁面重新整理到資料檔案，執行purge操作，觸發檢查點，合并插入緩衝區等。

         insertbuffer thread:主要負責插入緩衝區的合併作業。

         readthread:負責資料庫讀取操作，可配置多個線程

         writethread:負責資料庫寫操作，可配置多個線程。

         logthread:用於將重做日誌重新整理到logfile中。

         purgethread:MySQL5.5之後用於單獨的purge thread 執行purge操作。

         lockthread:負責鎖控制和死結檢測。

         錯誤監控線程：主要負責錯誤監控和錯誤處理。

記憶體管理及最佳化記憶體管理原則

在調整MySQL記憶體配置時，要注意以下幾點。

         將盡量多的記憶體配置給MySQL做緩衝，但是要給作業系統和其他程式的運行預留足夠的記憶體，否則如果產生SWAP頁交換，將嚴重影響系統效能。

         MyISAM的資料檔案讀取依賴作業系統自身IO緩衝，因此，如果有MyISAM表，就要預留更多的記憶體給作業系統做IO緩衝。

         排序區、串連區等緩衝是分配給每個資料庫會話(seesion)專用的，其預設值的設定要根據最大串連數合理分配，如果設定太大，不但浪費記憶體資源，而且在並發串連較高時會導致實體記憶體消耗盡。

MyISAM記憶體最佳化

MyISAM儲存引擎使用 key buffer緩衝索引塊，以加速MyISAM索引的讀寫速度。對於MyISAM變的資料庫塊，MySQL沒有鐵別的緩衝機制，完全依賴作業系統的IO緩衝。

key_buffer_size設定

key_buffer_size決定MyISAM索引塊緩衝區的大小，直接影響到MyISAM表的存取效率。可以在MySQL參數檔案中設定key_buffer_size的值，對於一般MyISAM資料庫，建議至少將1/4可用記憶體分配給key_buffer_size。

我們可以通過檢查key_read_requests、key_reads、key_write_requests和key_writes等MySQL狀態來評估索引緩衝的效率。一般來說，索引塊物理讀比率key_reads/key_read_requests應該小於0.01。索引塊寫比率key_wirte/key_write_requests也應儘可能小。但是這與應用特點有關，對於更新和刪除操作特別多的應用，key_writes/key_wirte_requests可能會接近1，而對於 更新很多行記錄的應用，key_wirtes/key_write_requests就會比較小。

除了通過索引塊的物理讀寫比率衡量key_buffer的效率外，我們也可以通過評估key buffer的使用率來判斷索引緩衝設定是否合理。key buffer使用率計算公式如下：

         1-((key_blocks_unused*key_cache_block_size)/key_buffer_size)

         一般來說，使用率在80%左右筆記哦啊合適，大於80%可能因索引還粗不足而導致效能下降，小於80%會導致記憶體浪費

設定key_buffer_size

mysql>set global key_buffer_size=8088608;

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

永久設定

[root@localhostbinlog]# vim /etc/my.sql

[mysql]

key_buffer_size=8088608

查看key_buffer_size

mysql> show variables like'key_buffer_size';

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

| Variable_name   | Value  |

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

| key_buffer_size | 8388608 |

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

1 row in set (0.00 sec)

 

mysql>show global status like 'key_%';

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

| Variable_name          | Value |

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

| Key_blocks_not_flushed | 0     |

| Key_blocks_unused      | 6452 |

| Key_blocks_used        | 0    |

| Key_read_requests      | 0    |

| Key_reads              | 0     |

| Key_write_requests     | 0    |

| Key_writes             | 0     |

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

7 rows in set (0.00 sec)

使用多個索引緩衝

MySQL通過session共用的key buffer提高了MyISAM索引存取的效能，但是冰不能消除session間對key buffer的競爭。比如一個session如果對某個很大的索引進行掃描，就可能將其他的索引資料基礎索引緩衝區，而這些索引塊可能是其他session要用的熱資料。

建立key buffer

mysql>set global hot_cache.key_buffer_size=128*1024;

Query OK, 0 rows affected (0.01 sec)

hot_cache是建立索引的緩衝名。

刪除key buffer

mysql>set global hot_cache.key_buffer_size=0;

Query OK, 0 rows affected (0.01 sec)

但是我們不能刪除預設的keybuffer

將某個表的索引加入到建立的key buffer中

mysql>cache index t1 in hot_cache;

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

| Table   | Op                 | Msg_type |Msg_text |

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

| test2.t1 | assign_to_keycache |status   | OK       |

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

1 row in set (0.00 sec)

T1表示T1表

 

在設定檔自動建立並載入索引緩衝

[root@localhostbinlog]# vim /etc/my.sql

[mysql]

key_buffer_size=8088608

hot_cache.key_buffer_size=128*1024

cold_cache.key_buffer_size=1G

init_file=/usr/local/mysql/config/mysqld_init.sql

[root@localhostbinlog]# vim /usr/local/mysql/config/mysqld_init.sql
cache index t1 hot_cache

cache index t2 cold_cache;

load index into cache t1,t2;

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。