VPSサーバーでWebサイト公開　備忘録　~Linux、MySQLからAJAXまで

MySQL MyISAMの性能関連の設定値とステータス確認

wp-admin

MySQL MyISAMの性能に関係する設定値とステータス確認方法についてです。

※目次をクリックすると目次の下部にコンテンツが表示されます。

MyISAMの性能に関係するサーバーシステム変数
１）主なサーバーシステム変数
　 
mysql> show variables like 'key_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| key_buffer_size          | 8384512 |
| key_cache_age_threshold  | 300     |
| key_cache_block_size     | 1024    |
| key_cache_division_limit | 100     |
+--------------------------+---------+

mysql> show variables like 'myisam_%';
+---------------------------+----------------------+
| Variable_name             | Value                |
+---------------------------+----------------------+
| myisam_data_pointer_size  | 6                    |
| myisam_max_sort_file_size | 9223372036853727232  |
| myisam_mmap_size          | 18446744073709551615 |
| myisam_recover_options    | OFF                  |
| myisam_repair_threads     | 1                    |
| myisam_sort_buffer_size   | 8388608              |
| myisam_stats_method       | nulls_unequal        |
| myisam_use_mmap           | OFF                  |
+---------------------------+----------------------+

mysql> show variables like 'delay_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| delay_key_write        | ON    |
| delayed_insert_limit   | 100   |
| delayed_insert_timeout | 300   |
| delayed_queue_size     | 1000  |
+------------------------+-------+

　
２）変数の内容とチューニングの注意点
　
①key_buffer_size
　
・MyISAMテーブルのインデックスブロックはバッファーされ、すべてのスレッドによって共有される。
　
key_buffer_sizeは、インデックスブロックに使用されるバッファーのサイズ。
　
・MyISAMのみを利用していれば、メインメモリの25-33%を割り当てる。
　
②myisam_sort_buffer_size
　
・REPAIR TABLE実行時のMyISAMインデックスのソートやインデックス作成時に割り当てられるバッファーのサイズ。
　
・インデックス作成時には大きくしておく。

起動時のオプション
①–myisam-recover=
　
・MyISAMストレージエンジンのリカバリーモード。
　
・DEFAULT, BACKUP, FORCE, QUICKから指定。
　
・リカバリーが有効になっている場合、mysqldがMyISAMテーブルをオープンするたびにそのテーブルがクラッシュとしてマークされていないか、または正常にクローズされなかったかどうかチェックする。
　
異常があった場合はmysqldはそのテーブルをリペアする。
　
②–delay-key-write
　
・インデックスをディスクに反映させるのをテーブルが閉じられたタイミングのみにする。
　
・クラッシュ時にデータが破損する可能性が増大するため注意。
ステータス確認
mysql> show status like 'key%';
+------------------------+------------+
| Variable_name          | Value      |
+------------------------+------------+
| Key_blocks_not_flushed | 0          |
| Key_blocks_unused      | 3471       |
| Key_blocks_used        | 6694       |
| Key_read_requests      | 1315669686 |
| Key_reads              | 4786586    |
| Key_write_requests     | 153324571  |
| Key_writes             | 12481575   |
+------------------------+------------+

　
●Key_read_requests、Key_reads
　
・Key_read_requests：キャッシュからキーブロックを読んだリクエスト数
　
・Key_reads：ディスクからキーブロックの物理レコードを読んだ数。
　
・Key_reads/Key_read_requestsでキャッシュミス率
　
●Key_write_requests,Key_writes
　
・キャッシュミス率は高いことが多い
　
●Key_blocks_unused
　
・キーキャッシュ内の使用されていないブロックの数。
　
・値が大きい場合はキャッシュサイズが大きすぎる可能性大。

low_priority_updatesとdelay_key_write
１）low_priority_updates（サーバーシステム変数または各クエリ）
　
・新規挿入、更新、削除などのテーブルデータの変更を伴うクエリの優先度を読み出しより下げる。
　
・各クエリ単位に指定したい場合は、下記のように”LOW_PRIORITY”キーワードを指定する。
　
INSERT LOW_PRIORITY INTO テーブル名・・・
　
上記クエリでは、他のクライアントがテーブルからReadし終わるのを待って挿入を行う。
　
２）delay_key_write（サーバーシステム変数またはcreate tableのオプション）
　
・デフォルトでは、インデックスやキーが更新されるとその更新内容をディスクに書き戻す。
　
更新の頻度が高いテーブルの場合は、これによって多量のディスク書き込みが発生する。
　
“delay_key_write”システム変数をallに指定、またはcreate tableでdelay_key_writeオプションを指定すると、更新されたインデックスはメモリ内のバッファーに保持され、テーブルがクローズされるまでディスクへの書き出しが遅延される。
　
・クリーンに終了されなかった場合は、メモリ内の更新インデックスデータがディスクに書き戻されず、インデックスが不正となる可能性がある。
　
この場合は、myisamchkを実行して修復する事ができる。ただし、データ量の多いテーブルでは実行時間が長くなる。
モバイルバージョンを終了