Go to the first, previous, next, last section, table of contents.

MySQL の最大性能を得る方法は？

MySQL のバッファサイズの変更方法

現在のバッファサイズを次で得ることができます:

> ./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 によって検出される多く の制限がこれに依存します。デフォルトで通常は十分です。
MySQL はとてもスケーラブルなアルゴリズムを使用します。そのため 通常はとても少ないメモリで動作し、またより良い性能を得るために MySQL に多くのメモリを与えることができます。 多くのメモリと多くのテーブルを持っていて、適度のクライアント数で最大性能を得た い場合、次のようなものを使用します: 
> 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) の テーブルキャッシュを持つべきです。

MySQL の速度に影響するコンパイルとリンク方法

次のテストの多くは 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 でコンパイルされ、静的 にリンクされています。

MySQL はどのようにメモリを使用するのか？

mysqld 実行時、ps や他のプログラムは、それが多くのメモリ を使用していると報告するでしょう。これは異なったメモリアドレス上のスレッ ドスタックによって発生します。例えば、Solaris ps はスタック間の未使用メ モリを使用メモリとして計算します。'swap -s' で有効なスワップをチェックす ることでこれを確かめられます。我々は市販のメモリリーク検出プログラムで mysqld をテストしました。そのため、メモリリークは全くありません。

MySQL はどのようにインデックスを使用するか？

全てのインデックス, PRIMARY, UNIQUE そして INDEX() は B tree に格納されます。文字列は自動的に始めと終りの空白が圧縮されます。 「CREATE INDEX 構文(互換関数)」節参照

どのような最適化が WHERE 節で行なわれるか？

(不完全, MySQL は多くの最適化を行う。)

遅い SELECT ... WHERE をより速くするための最初の問題は、インデッ クスを追加できるかどうかをチェックすることです。異なるテーブル間の全ての参照は 通常はインデックスを使用して行なわれます。select で使用されるイン デックスをチェックするために、EXPLAIN コマンドを使用できます。 「EXPLAIN 構文。SELECT についての情報を得る」節参照 。 「MySQL はどのようにインデックスを使用するか？」節参照

MySQL はどのようにテーブルのオープン & クローズを行なうか？

オープンテーブルのキャッシュは最大で 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 を行なう場合も、少しオーバヘッドがあります。表キャッシュが一杯に なった時、オープンする必要のある全てのテーブルのため、他のものがクローズされな ければならないからです。テーブルキャッシュを大きくすることで、オーバヘッドをよ り小さくできます。

How does MySQL lock tables?

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 ロックと同じ優先順位に なります。これはいくつかのアプリケーションを助けます。

私のテーブルをできるだけ速く/小さく扱う方法は？

あなたがどのように行なっているかをチェックするために、isamchk -evi を .ISM ファイルに対して実行してください。

何が INSERT ステートメントの速度に影響するか？

レコードを挿入する時間は次からなります:

ここで (数字) は比例時間です。これは、テーブルのオープンにかかる初期オーバーヘッ ド(これは同時に動作する各クエリ毎に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 はとても速いです。

何が DELETE ステートメントの速度に影響するか？

レコードの削除時間は正確にインデックスの数に比例します。削除の速度を上げ るために、インデックスキャッシュのサイズを増加できます。デフォルトのイン デックスキャッシュは 1M です。より速く削除するためにはいくつかの要因によっ て増やすべきです (十分なメモリがあるなら 16M を試してください)。

フルスピードで動作する MySQL を得る方法は？

行形式の種類は？ また VARCHAR/CHAR の使用時は？

MySQL は真の SQL VARCHAR() 型を持ちません。

MySQL は代わりにレコードを格納する３つの異なる方法を持ち、これを VARCHAR() のエミュレートに使用します:

VARCHAR, BLOB または TEXT 項目型のどれも使用しない 場合、固定行サイズが使用されます。そうでなければ、動的行サイズが使用され ます。CHAR() と VARCHAR() は、アプリケーションの観点からは 同じに扱われます; 両方とも、項目がアクセスされた時に、項目から終りの空白 を取り除きます。

テーブルに使用された形式は isamchk -d でチェックできます。

MySQL は3つの異なったテーブル形式を持ちます:

  1. 固定長テーブル。
  2. 動的テーブル
  3. 圧縮されたテーブル:
  4. 固定または動的長レコードを操作できます (BLOB や TEXT 項目がない場合)。
  5. isamchk で非圧縮にできます。
MySQL は異なるインデックス型をサポートできますが、通常の一つは NISAM で す。これは B-tree インデックスで、インデックスファイルのサイズを荒く計算 できます: 全てのキーの次の合計: (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.