|
MYSQL チューニング
|
|
|
mysqlは、落ちるときに カーネルもつれて行きます。
チューニングを施して、最適に利用しましょう。 |
|
|
|
|
/etc/my.cnf でチューニングに必要な箇所は以下の項目です。
メモリー 設定 計算シート(エクセルファイル）
http://www.819410.com/FreeBSD6/conf/mysql.xls
set-variable = key_buffer=256M
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
set-variable = max_connections=120
上記の設定の場合、MySQL は仮想メモリ（実メモリ+スワップメモリ）を最大 856M 必要とします。
key_buffer + ( sort_buffer + record_buffer ) * max_connections = 856M
もしもメモリが足りない場合は、
InnoDB: Fatal error: cannot allocate 966656 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 133447938 bytes. Operating system errno: 12
InnoDB: Cannot continue operation!
こんな感じで mysqld は落ちてしまいます。
ここで注意して欲しいのは使用するメモリの計算式です。
key_buffer は1つのデーモンに対するものですが、sort_buffer と record_buffer は1接続に対する
ものに相当します。つまり仮に sort_buffer=16M， resord_buffer=8M と設定したらどうなるでしょうか。
256M + ( 16M + 8M ) * 120(最大) = 3136M
となります。これは最大で 3G 以上の仮想メモリを必要とすることを意味しますので、
mysqld が落ちる原因となります。
以下は record_buffer について書いてありますが、sort_buffer でも同様です。
--
However， you must be very careful with the size of the record buffer
if you have a lot of clients which run queries that perform full table scans.
Because the record buffer is allocated on a per-thread basis，
you may end up in a situation where individual clients cause record buffers
to be allocated at the same time.
If the remaining memory is limited，
you'll likely encounter swapping and dramatically reduced performance.
--
■ my.cnf の設定例
ソースに含まれる my.cnf の例を上げてみます。
my-huge.cnf
key_buffer = 384M
sort_buffer_size = 2M
read_buffer_size = 2M
my-large.cnf
key_buffer = 256M
sort_buffer_size = 1M
read_buffer_size = 1M
my-medium.cnf
key_buffer = 16M
sort_buffer_size = 512K
read_buffer_size = 0
my-small.cnf
key_buffer = 16K
sort_buffer_size = 64K
read_buffer_size = 0
これらを見る限りでは、huge なサイトでも sort_buffer_size， read_buffer_size 共に
2M までしか確保していません。あまりにもメモリが余っているのであれば良いのですが
特に sort_buffer_size と read_buffer_size の設定には注意して下さい。
実際に負荷テストを行いましたが my.cnf の設定次第で、
同じ負荷を掛けても落ちる落ちないの違いが確認できています。
■ 適切な key_buffer
念の為に key_buffer が適切な値になっているかどうかを確認してみましょう。
mysql> SHOW STATUS LIKE 'Key%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Key_blocks_used | 294 |
| Key_read_requests | 20390 |
| Key_reads | 111 |
| Key_write_requests | 9979 |
| Key_writes | 294 |
+--------------------+-------+
上の値を参照する事で key buffer の影響を計算する事ができます。
Key Efficiency(%) = 100 - ( ( Key_reads / Key_read_requests ) * 100 )
さて、計算してみましょう。
Key Efficiency(%) = 100 - ( ( 111 / 20390 ) * 100 ) = 99.4556...
この影響率が 90% を下回る事が無いように設定されていれば良い状態であるようです。
この値が出来るだけ 100% に近づくよう key_buffer を設定してあげましょう。といっ
もいちいちキーボードを打って、計算機を使うのは面倒ですね。そんなときはこのツー
ルを使ってみます。（導入方法は良くマニュアルを見て下さい。）
これは UNIX の top コマンドの MySQL版 だと考えれば分かり易いと思います。MySQL
の状況をタイムリーに表示してくれるわけです。このツールを実行すると、上の影響率
をタイムリーに計算してくれますので非常に便利です。もちろんそれ以外にも現在のク
エリの状況や、スレッドを kill するようなコマンドも用意されています。
|
|
|
