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

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

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

MyISAMの性能に関係するサーバーシステム変数
1)主なサーバーシステム変数
 

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  |
+------------------------+-------+

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

関連記事の目次

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください