現在のバッファサイズを次で得ることができます:
> ./mysqld --help
この結果、全ての mysqld オプションと次のようなコンフィグ可能変数のリスト を得られます。
Possibly variables to option --set-variable (-O) are: back_log current value: 5 join_buffer current value: 131072 key_buffer current value: 1048568 max_allowed_packet current value: 65536 max_connections current value: 90 max_join_size current value: 4294967295 max_sort_length current value: 1024 net_buffer_length current value: 8192 record_buffer current value: 131072 sort_buffer current value: 2097144 table_cache current value: 64 tmp_table_size current value: 1048576 thread_stack current value: 65536
back_log | MySQL が持てる未解決の接続要求の数です。これは MySQL スレッドがとても多くの接続要求をとても短い時間に得た時に、働き ます。接続のチェックと新しいスレッドの開始はメインスレッドにすこし時間 (しかしとても短い)がかかります。back_log は、MySQL が瞬間的に新 しい要求への回答を停止する前に、この短い時間の間にスタックできる接続数で す。短い期間に多くの接続を期待する場合にだけ、これを増加する必要がありま す。 他の言葉では、TCP/IP 接続の入力 listne キューのサイズです。UNIX システム コール listen(2) のマニュアルページに、さらに詳細があります。 この変数の最大値は OS のドキュメントをチェックしてください。 |
join_buffer | このバッファは(インデックス無しの)完全な結合に使用されます。それは2つの テーブル間の完全な結合ごとに1回割り当てられます。インデックスの追加がで きない時、より速い完全な結合を得るために、これを増加してください。通常、 速い結合を得る一番言い方法は、インデックスを追加することです。 |
key_buffer |
バッファインデックスブロック。全てのスレッドに共有されます。多くのインデッ
クスを持つテーブルで多くの削除/挿入を行うときに、これを増加させてくださ
い。さらに速度を得るためには LOCK TABLES を使用してください。
「LOCK TABLES 構文」節参照 。
|
max_allowed_packet |
一つのパケットの最大サイズ。これはメッセージバッファを必要なときにこの制
限まで増大させることを許します(net_buffer_length に初期化されます)。
これは主に間違ったパケットを見つけるために、とても大きな値が設定されます。
大きな BLOB を使用している場合は、これを増加する必要があります。使用した
い最大の BLOB と同じくらい大きくするべきです。
|
max_connections | 許される同時クライアントの数。これを増加する場合は、mysqld が持つファイ ルディスクリプタの数を増やす必要があるでしょう。これは OS に依存しますの で、OS のドキュメントを見てください。 |
max_join_size |
max_join_size より多いレコードを触るとエラーが返ります。長い時間をかけて
百万行を返すような WHERE なしの結合を作成するようなユーザを持って
いる場合にこれを設定してください。
|
max_sort_length |
BLOB または TEXT 項目上でソートする時に使用するバイト数。
|
net_buffer_length | 通信バッファがクエリ間でこのサイズにリセットされます。これは通常は変更す べきではありませんが、とても小さなメモリしかない場合は、これを期待される クエリのサイズに設定してください。 |
record_buffer | 順序スキャンを行う各スレッドが、スキャンするテーブル毎に、このサイズのバッ ファを割り当てます。多くの順序スキャンを行う場合は、これを増加させてくだ さい。 |
sort_buffer |
ソートを行う必要がある各スレッドがこのサイズのバッファを割り当てます。よ
り速い ORDER BY または GROUP BY のためにはこれを増やしてく
ださい。 「MySQL が一時ファイルを格納する場所」節参照
|
table_cache | 全てのスレッドについてのオープンテーブルの数。これを増加する場合は、オー プンファイルディスクリプタの数も増加することに注意しないといけません。 MySQL はユニークテーブル毎に2つのファイルディスクリプタを必要と します。 |
tmp_table_size |
一時テーブルがこれよりも大きい場合、The table ### is full エラー
が生成されます。多くの先進的な GROUP BY クエリを行う場合は、これ
を増加してください。
|
thread_stack |
各スレッドの C スタックの大きさ。crash-me によって検出される多く
の制限がこれに依存します。デフォルトで通常は十分です。
|
> safe_mysqld -O key_buffer=16M -O table_cache=128 \ -O sort_buffer=4M -O record_buffer=1M &多くの接続で少ないメモリしかない場合、次のようなものを使用します:
> safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k &または
> safe_mysqld -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 \ -O record_buffer=8k -O net_buffer=1K &
mysqld
へのオプションを変更する場合、そのサーバのインスタンスにだ
けであることに注意して下さい。パラメータ変更の効果を見るには、このように
しますmysqld -O key_buffer=32m --help
。
mysqladmin variables
で実際のパラメータをチェックできます。
とても多くの接続がある場合、各接続にとても少ないメモリを使用するように
mysqld がコンフィグされていなければ、'スワップの問題' が生じます。もちろ
ん全ての接続に十分なメモリがあれば、ちゃんと働きます。
例えば、200 の接続では少なくても 200 * (max_number of tables in join) の
テーブルキャッシュを持つべきです。
次のテストの多くは Linux 上で MySQL ベンチマークで行われました。 しかし、これらは他の OS についてもいくつかの指標を与えます:
Linux では、pgcc と -O6 でコンパイルした時に最速のコードを得られます。こ のオプションで sql_yacc.cc をコンパイルすると、gcc/pgcc は全ての関数をイ ンラインにするために多くのメモリを必要とするので、180M のメモリが必要で す。libstdc++ ライブラリの増加を避けるためには、MySQL の configure 時に CXX=gcc も設定すべきです。
-static でのリンク時に最速の実行形式を得ます。TCP/IP の代わりに Unix ソ ケットをデータベースへの接続に使用することも、より良い性能を与えます。
TCX により配布されている MySQL-linux 配布は pgcc でコンパイルされ、静的 にリンクされています。
net_buffer_length
) を使用します。
mysqld
の起動時にキーバッファを指定できます。これは全テーブル内の全ての
インデックスを FIFO ベースでバッファします (変数 key_buffer)。
BLOB
を含む
テーブルはディスク上に置かれます。現在の問題は、HEAP テーブルが
tmp_table_size
のサイズを越えると、エラー 'The table ### is full'
が出ることです。将来我々は、必要時にメモリ (HEAP) テーブルをディスクベー
ス (NISAM) テーブルに自動的に変更することにより、これを修正します。この
問題を回避するため、mysqld への -O tmp_table_size=#
オプションま
たは SQL オプション SQL_BIG_TABLES
で増加できます。 「SET OPTION 構文」節参照 。MySQL 3.20
では、一時テーブルの最大サイズは
recordbuffer*16
でした。そのため、このバージョンを使用していると、
recordbuffer
を追加する必要があります。mysqld を --big-tables で
開始することで、常に一時テーブルをディスク上に格納できます。しかしこれは
全ての複雑なクエリの速度に影響します。
BLOB
を計算しない最大行長) のサイズのバッファが割り当てら
れます。BLOB
は 5 から 8 バイト + BLOB データの長さを使用します。
BLOB
を持つ各テーブルでは、より大きな BLOB
の読み込みでバッファ
は動的に拡大されます。テーブルのスキャンをする場合、割り当てられたバッファは最
も大きい BLOB
と同じ大きさになります。
mysqladmin refresh
は使用されていない全てのテーブルをクローズし、
使用されている全てのテーブルを、実行中スレッドが終った時にクローズするよ
うにマークします。これは多くの使用メモリを解放するのに有効です。全てのロ
グファイルもクローズと再オープンされます。
mysqld 実行時、ps
や他のプログラムは、それが多くのメモリ
を使用していると報告するでしょう。これは異なったメモリアドレス上のスレッ
ドスタックによって発生します。例えば、Solaris ps はスタック間の未使用メ
モリを使用メモリとして計算します。'swap -s' で有効なスワップをチェックす
ることでこれを確かめられます。我々は市販のメモリリーク検出プログラムで
mysqld
をテストしました。そのため、メモリリークは全くありません。
全てのインデックス, PRIMARY
, UNIQUE
そして INDEX()
は B tree に格納されます。文字列は自動的に始めと終りの空白が圧縮されます。
「CREATE INDEX 構文(互換関数)」節参照
(不完全, MySQL は多くの最適化を行う。)
遅い SELECT ... WHERE
をより速くするための最初の問題は、インデッ
クスを追加できるかどうかをチェックすることです。異なるテーブル間の全ての参照は
通常はインデックスを使用して行なわれます。select
で使用されるイン
デックスをチェックするために、EXPLAIN
コマンドを使用できます。
「EXPLAIN 構文。SELECT についての情報を得る」節参照 。 「MySQL はどのようにインデックスを使用するか?」節参照
((a AND b) AND c OR
(((a AND b) AND (c AND d))))
-> (a AND b) OR (a AND b AND c AND d)
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
(b>=5
AND b=5) OR (b=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
WHERE
がない CONST(*)
は、テーブルから
直接取り出されます。これはまた同じ条件下での任意の NOT NULL
表現
のためにも行われます。
GROUP BY
または group 関数を使用しない場合は、HAVING
は
WHERE
とマージされます。
WHERE
評価を得るために、また、可能な限り
早くレコードをスキップするために、各サブ結合についてより簡単な
WHERE
が構築されます。
=
,
>
, >=
, <
, <=
, BETWEEN
そして、'somthing%'
のように文字が最初にある LIKE
。
AND
レベルにかからないインデックスを削除します。
index = 1 or A = 10
-> NULL
(インデックスを使用できません。)
index = 1 or A = 10 and index=2
-> index = 1 OR index = 2
index_part_1 = const and index_part_3 = const
-> index_part_1 =
const
const_table.index = constant
const_table.index_part_1 = const_table2.column and const_table.index_part_2 = constant
ORDER BY
または GROUP
内の全ての項目が同じテーブルからの場合は、
このテーブルは結合時に最初に優先されます。
HAVING
節に適合するものをスキップします。
オープンテーブルのキャッシュは最大で table-cache
まで大きくなります(デ
フォルトは 64, -O table_cahe=# で変更可能)。キャッシュが一杯になって、他
のスレッドがテーブルのオープンを試みた時、または 'mysqladmin refresh' を使用し
た場合を除いて、テーブルはクローズされません。
制限に達すると、MySQL は、キャッシュサイズに達するか、または未 使用テーブルがそれ以上なくなるまで、可能な限り多くのテーブルをクローズします。これは、 全てのテーブルがいくつかのスレッドによって使用される場合、キャッシュ制限よりも 多くのオープンテーブルがあることを意味します。しかし、拡張テーブルは最後にクローズさ れます。表は最後に使用された順に従ってクローズされます。
テーブルは各同時アクセスに (再び) オープンされます。これは、同じテーブルで2つのスレッ
ドが実行されている場合、または同じクエリで(AS
で)テーブルを2回アクセス
する場合、テーブルは2回オープンする必要があることを意味します。最初のテーブルのオー
プンは2つのファイル記述子を使用し、続くテーブルの各使用は1つだけのファイル記述
子を使用します。
MySQL が、テーブルがシンボリックリンクであることに気づいた場合、 symlink を解析し、代わりにその点のテーブルを使用します。これは realpath() コールをサポートする全てのシステムで働きます(少なくとも Linux と Solaris は realpath() をサポートします!)。realpath() をサポー トしないシステム上では、symlink とテーブルを同時に使用すべきでありません! テーブルはテーブルの更新後に矛盾するでしょう。
MySQL はデフォルトではデータベースのリンクをサポートしません。 データベース間のシンボリックリンクを作成しない限り、これは正常に働きます。 次に示すケースは働きません:
db2->db1 db1/
本当にこれが必要な場合は、mysys/mf_format.c を変更する必要があります:
if (!lstat(to,&stat_buff)) /* Check if it's a symbolic link */ if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff)) to if (realpath(to,buff))
各テーブルは実際には 3 つのファイルです。ディレクトリ中に多くのファイルがある 場合、オープン、クローズそして生成は遅くなります。多くの異なるテーブルで select を行なう場合も、少しオーバヘッドがあります。表キャッシュが一杯に なった時、オープンする必要のある全てのテーブルのため、他のものがクローズされな ければならないからです。テーブルキャッシュを大きくすることで、オーバヘッドをよ り小さくできます。
MySQL の全てのロックはデッドロックフリーです。これは、常にクエ リ開始時に一度ロックを必要とする全てを要求し、常に同じ順でテーブルをロッ クすることで管理されます。
MySQL が使用する WRITE
ロックのロック方法は次のように働
きます:
テーブル上にロックがない場合 write ロックを置き、そうでなければ write ロッ クキューにロックを置きます。
MySQL が使用する READ
ロックのロック方法は次のように働き
ます:
テーブル上に write ロックがない場合 read ロックを置き、そうでなければ read ロックキューにロックを置きます。
ロックが解放されたとき、最初に write ロックキュー内のスレッドに、その後 read ロックキュー内のスレッドにロックを与えます。
これは、同じテーブルで多くの更新をする場合、select ステートメントは update がなくなるまで待たされることを意味します。
同じテーブルで多くの insert と多くの select を行う場合、これを解決するに は、他のテーブルに行を挿入して、たまに、その一時テーブルから全てのレコー ドをもう一方のテーブルに update します。
これは次のコードで行えます:
LOCK TABLES real_table WRITE, insert_table WRITE insert into real_table select * from insert_table delete from insert_table UNLOCK TABLES
一つのキューだけを使用するように mysys/thr_lock.c 内のロックコードを変更 することもできます。この場合、write ロックは read ロックと同じ優先順位に なります。これはいくつかのアプリケーションを助けます。
NOT NULL
を使用してください。これは全てをより速くし、項目
毎に1ビットを節約します。
MEDIUMINT
はしばしば INT
よりも良いです。
VARCHAR
項目を持たない場合は、固定サイズレコード形式が使用されます。
これはかなり速いです。しかしあいにくいくらかの領域を浪費します。
「行形式の種類は? また VARCHAR/CHAR の使用時は?」節参照 。
isamchk --analyze
をテーブルに対して実行してください。これ
は各インデックスの値を更新し、このインデックスに同じ値が平均で何行あるか
を知らせます。もちろん、ユニークインデックスではこれは常に 1 です。
isamchk --sort-index --sort-records=1
を使用してください(あなたが
インデックス 1 でのソートを望む場合)。数値順で全てのレコードを読みたいユ
ニークインデックスをもっていれば、これはそれをより速くさせる良い方法です。
LOAD DATA FROM INFILE
を使用してくださ
い。これは多くの INSERT
の使用よりも通常 20 倍速くなります。テキ
ストファイルがサーバ上にない場合、まずそれをサーバに rcp してください。
「LOAD DATA INFILE 構文」節参照 。
多くのインデックスを持つテーブルへデータをロードする時、次を行うことにより、もっ
と速度を得ることができます:
CREATE TABLE...
で mysql または perl でテーブルを作成します。
mysqladmin refresh
を行ないます。
isamchk --keys-used=0 database/table_name
を使用します。これは全
てのインデックスの使用をテーブルから削除します。
LOAD DATA INFILE...
でデータをテーブルに挿入します。
isamchk -r -q database/table_name
でインデックスを再生成します。
mysqladmin refresh
を行ないます。
LOAD DATA FROM INFILE
と INSERT
の両方について、いくらかの
速度を得るための他の可能性は、キーバッファを増大することです。これは
(safe)mysqld
に対する -O key_buffer=#
オプションで行なうこ
とができます。例えば、多くの RAM を持っている場合、16M は良い値です :)
SELECT ... INTO OUTFILE
を使用してください。 「LOAD DATA INFILE 構文」節参照 。
LOCK TABLE
の使用により、
さらに速度を得ることができます。...FROM INFILE...
と
...INTO OUTFILE...
は原子的ですので、それらの使用時に LOCK
TABLES
を行なう必要はありません。 「LOCK TABLES
構文」節参照 。
あなたがどのように行なっているかをチェックするために、isamchk -evi
を .ISM ファイルに対して実行してください。
レコードを挿入する時間は次からなります:
ここで (数字) は比例時間です。これは、テーブルのオープンにかかる初期オーバーヘッ ド(これは同時に動作する各クエリ毎に1回行なわれます)は考慮されていません。
テーブルのサイズはインデックスの挿入を N log N で遅くします (B-tree)。
挿入の高速化の一つの方法は、挿入中テーブルをロックすることです。
LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23) INSERT INTO a VALUES (2,34) INSERT INTO a VALUES (4,33) INSERT INTO a VALUES (8,26) INSERT INTO a VALUES (6,29) UNLOCK TABLES;
主な速度差は、全ての insert でインデックスバッファが一度だけディスクにフ ラッシュされることです。通常は insert があるのと同じくらい多くのインデッ クスバッファフラッシュがあります。
ロックも複数接続テストの合計時間を低くしますが、いくつかのスレッドの最大 待ち時間は上がります。
例えば:
thread 1 does 1000 inserts thread 2, 3, and 4 does 1 insert thread 5 does 1000 inserts
ロックを使用しない場合、2, 3 そして 4 は 1 と 5 の前に終ります。ロックを 使用する場合、2,3,4 は 1 や 5 の前に終わることはおそらくありませんが、合 計時間は約 40 % 速くなります。
INSERT
, UPDATE
そして DELETE
は MySQL では
とても速いので、1 行内に約 5 以上の inserts/updates を行なう全ての回りに
ロックを追加することにより、全般的により良い性能が得られます。とても多い
insert を行に行なう場合、他のスレッドにそのテーブルへのアクセスを与えるために
UNLOCK TABLES
つづいて LOCK TABLES
を間に一度 (約 1000 行
ごとに) 行ないます。これでもまだ良い性能が得られます。
もちろん LOAD DATA INFILE
はとても速いです。
レコードの削除時間は正確にインデックスの数に比例します。削除の速度を上げ るために、インデックスキャッシュのサイズを増加できます。デフォルトのイン デックスキャッシュは 1M です。より速く削除するためにはいくつかの要因によっ て増やすべきです (十分なメモリがあるなら 16M を試してください)。
mysqld
を開始します。よりメモリを持っている場
合は、より速度を与えます。 「MySQL のバッファサイズの変更方法」節参照 。
SELECT
をより速くするためにインデックスを作成します。
「MySQL はどのようにインデックスを使用するか?」節参照
NOT NULL
を
使用します。
「私のテーブルをできるだけ速く/小さく扱う方法は?」節参照
--skip-locking
は SQL 要求間のファイルロックを無効にします。これ
はより大きな速度を与えますが、次の結果になります:
isamchk
でテーブルのチェック/修復を試みる前に、全てのテーブルを
mysqladmin refresh
でフラッシュ しなければなりません。
(isamchk -d table_name
はいつでも許されます)。
--skip-locking
がデフォルトです。
これは全てのプラットフォームで MIT スレッドが flock() を完全にサポートし
ていないためです。
MySQL は真の SQL VARCHAR() 型を持ちません。
MySQL は代わりにレコードを格納する3つの異なる方法を持ち、これを
VARCHAR()
のエミュレートに使用します:
VARCHAR
, BLOB
または TEXT
項目型のどれも使用しない
場合、固定行サイズが使用されます。そうでなければ、動的行サイズが使用され
ます。CHAR()
と VARCHAR()
は、アプリケーションの観点からは
同じに扱われます; 両方とも、項目がアクセスされた時に、項目から終りの空白
を取り除きます。
テーブルに使用された形式は isamchk -d
でチェックできます。
MySQL は3つの異なったテーブル形式を持ちます:
VARCHAR
, TEXT
または BLOB
項目がテーブル内に存在する時に使
用されます。
isamchk -r
を実行する必要があり
ます。isamchk -ei table_name
をいくつかの統計のために使用します。
isamchk -ed
でチェックできます。全てのリンクは isamchk -r
で削除されます。
(key_length+4)*0.67
(全てのキーがソート順に挿入された時、これは最悪のケースです。)
文字列インデックスは圧縮された空白で、最初のインデックス部が文字列の場合、
それは圧縮された prefix でもあります。項目が 100% に満たない場合または多
くの重複がある場合、これは通常インデックスファイルをより小さくします。
mysqladmin status
を実行した時、次のようなものが得られます:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
これは、あなたが6個しかテーブルを持っていない場合、いくらか惑わせます。
MySQL はマルチスレッドなので、同じテーブルで一度に多くのクエリを持て ます。同じファイル上で異なる状態を持つ2つのスレッドで、問題を最小化する ため、同時に動作する各スレッドのためテーブルを再びオープンします。これはいくつ かのメモリとデータファイルについての一つの拡張ファイル記述子を使用します。 インデックスファイル記述子は全てのスレッド間で共有されます。
Go to the first, previous, next, last section, table of contents.