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


10 MySQL の最大性能をひきだすには

Optimization is a complicated task since it ultimately requires understanding of the whole system. While it may be possible to do some local optimizations with small knowledge of your system/application, the more optimal you want your system to become the more you will have to know about it.

So this chapter will try to explain and give some examples of different ways to optimize MySQL. But remember that there are always some (increasingly harder) ways to make the system even faster left to do.

10.1 最適化 概要

The most important part for getting a system fast is of course the basic design. You also need to know that kinds of things your system will be doing. That is your bottlenecks are.

The most common bottlenecks are.

10.2 システム、コンパイル時、スタートパラメターのチューニング

We start with the system level things sine some of these decisions have to be made very early. In other cases a fast look at this part may suffice since it not that important for the big gains. But it is always nice to have a feeling about how much one gould gain by chancing things at this level.

使うべき OS はとても重要です! 複数の CPU を持つマシンでは、 Solaris (なぜなら、Solaris のスレッドはとても素晴らしく動作するから)、 あるいは、Linux ( kernel 2.2 は良い SMP をサポートしているから) を 使用すべきです。 32bit マシンの Linux では、2G bytes のファイルサイズの 制限があります。 これは新しいファイルシステム (XFS) のリリース時に なくなって欲しいものです。

Since we have not run production MySQL on that many platforms we advice you to test your intended platform before choosing it if possible.

Other tips:

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

次のテストの多くは Linux 上で MySQL ベンチマークで行われました。 しかし、これらは他の OS についてもいくつかの指標を与えます。

-static でのリンク時に最速の実行形式を得ます。 TCP/IP の代わりに Unix ソケットをデータベースへの 接続に使用することも、より良い性能を与えます。

Linux では、pgcc-O6 でコンパイルした時に最速のコードを得られます。 このオプションで `sql_yacc.cc' をコンパイルすると、gcc/pgcc は 全ての関数をインラインにするために多くのメモリを必要とするので、200M のメモリが必要です。 libstdc++ ライブラリの増加を避けるためには、 MySQL の configure 時に CXX=gcc も設定すべきです。

より良いコンパイラー、そしてコンパイラーのオプションは、10-30% の 速度の向上をもたらします。 これはあなた自身が MySQL をコンパイルする 時に重要なことです!

Intel では、例えば pgcc か Cygnus CodeFusion compiler で最速となります。 我々は 新しい Fujitsu コンパイラーでもテストしましたが、 MySQL を最適化オプションでコンパイルするには、 まだ完全なバグ・フリーではありませんでした。

Here is a list of some mesurements that we have done:

TCX により配布されている MySQL-Linux 配布は pgcc でコンパイルされ、 静的にリンクされています。

10.2.2 Disk の問題

10.2.2.1 データベースとテーブルにシンボリックリンクを使用する

テーブルとデータベースのファイルを MySQL のデータベースディレクトリーから 違う場所に移動し、 それに対してシンボリックリンクを張ることができます。 これは例えばディスク容量が少なくなって、データベースを移動したい場合にそうしたくなるでしょう。

MySQL が、テーブルがシンボリックリンクであることに気づいた場合、 symlink を解析し、代わりにその実体のテーブルを使用します。 これは realpath() コールをサポートする全てのシステムで働きます(少 なくとも Linux と Solaris は realpath() をサポートします!)。 realpath() をサポー トしないシステム上では、symlink とテーブルを 同時に使用すべきでありません! テーブルはテーブルの更新後に矛盾する事になるでしょう。

MySQL はデフォルトではデータベース同士のリンクをサポートしません。 が、データベース間のシンボリックリンクを作成しない限り、これは正常に働くでしょう。 MySQL データディレクトリに db1 データベースがあるとして、 仮に db2db1 のシンボリックリンクにしたとします:

shell> cd /path/to/datadir
shell> ln -s db1 db2

すると、db1 中の tbl_a テーブルは db2tbl_a テーブルとして見えます。 もしあるスレッドが db1.tbl_a を更新し、他のスレッドが db2.tbl_a を 更新した場合、 問題が発生します。

こういった使用をどうしてもしたい場合、`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))

Change the code to this:

if (realpath(to,buff))

10.2.3 サーバーパラメーターのチューニング

mysqld サーバーが使用している現在のバッファサイズを次で得ることができます:

shell> mysqld --help

この結果、全ての mysqld オプションと次のようなコンフィグ可能変数のリスト を得られます。出力結果にはデフォルト値が含まれ、以下のように表示されます:

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
join_buffer_size      current value: 131072
key_buffer_size       current value: 1048540
lower_case_table_names  current value: 0
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
max_write_lock_count  current value: 4294967295
net_buffer_length     current value: 16384
query_buffer_size     current value: 0
record_buffer         current value: 131072
sort_buffer           current value: 2097116
table_cache           current value: 64
thread_concurrency    current value: 10
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

もし mysqld サーバーを走らせているなら、以下のコマンドでも変数の値を見ることができます:

shell> mysqladmin variables

これらのオプションは以下のように決めてください。 buffer size, buffer length, stack size は byte 単位で与えます。 これらの値の後ろに `K'`M' を追加すると、キロバイト、メガバイトになります。 例えば、16M は16メガバイトを示します。大文字小文字の区別はなく、 16M16m は同じ意味になります。

稼働中のサーバーの統計情報は SHOW STATUS で参照できます. 「7.21 SHOW 構文 (テーブルやフィールドなどについての情報を得る)」節参照.

back_log
MySQL が持てる未解決の接続要求の数です。これは MySQL スレッドがものすごく多くの接続要求をとても短い時間に得た時に、働き ます。接続のチェックと新しいスレッドの開始はメインスレッドにすこし時間 (しかしほんのわずか)がかかります。back_log は、MySQL が瞬間的に新 しい要求への回答を停止する前に、この短い時間の間にスタックできる接続数で す。短い期間に多くの接続を期待する場合にだけ、これを増加する必要がありま す。 いいかえるなら、これは TCP/IP 接続の入力 listen キューのサイズです。 オペレーティングシステムはこのキューの大きさを制限しています。 UNIX システムコール listen(2) のマニュアルページに、さらに詳細があります。 この値を最大限にしたい場合、お使いのOSのドキュメントを見てください。 back_log をこのOSの制限値より多く取ってもは全く効果ありません。
connect_timeout
mysqld サーバーが接続パケットを待つ秒数。 (Bad handshake を返すまでの秒数)
delayed_insert_timeout
どれぐらい INSERT DELAYED スレッドが INSERT 文を待つべきか。
delayed_insert_limit
delayed_insert_limit 個のレコードを挿入した後、 INSERT DELAYED ハンドラーは 待たされている SELECT 文がないかチェックします。 もしあるなら、 挿入を続ける前にそれらの実行を許します。
delayed_queue_size
How big a queue (in rows) should be allocated for handling INSERT DELAYED. If the queue becomes full, any client that does INSERT DELAYED will wait until there is room in the queue again.
flush_time
これがもし、非ゼロにセットされたなら、毎 flush_time 秒ごとに 全てのテーブルが閉じられます。 (リソースの解放とDiskへのsyncのために)
join_buffer_size
このバッファは(インデックス無しの)完全な結合に使用されます。それは2つの テーブル間の完全な結合ごとに1回割り当てられます。インデックスの追加がで きない時、より速い完全な結合を得るために、これを増加してください。通常、 速い結合を得る一番良い方法は、インデックスを追加することです。
key_buffer_size
インデックス・ブロックはバッファされ、そして全てのスレッドに共有されます。 key_buffer_size はインデックス・ブロックのためのバッファ・サイズです。 Increase this get better index handling (for all reads and multiple writes) to as much as you can afford. If you make this too big the system will starte to page and go REAL slow. Remember that since MySQL does not cache data read that you will have to leave some room for the OS filesystem cache. To get even more speed when writing many rows at the same time use LOCK TABLES. 「7.24 LOCK TABLES/UNLOCK TABLES 構文」節参照.
long_query_time
もしあるクエリがこの値(秒)より時間がかかれば、Slow_queries カウンター が増やされます。
max_allowed_packet
一つのパケットの最大サイズ。メッセージバッファは net_buffer_length バイトに初期化されますが、 max_allowed_packetまで大きくすることができます。このデフォルト値は、 大きなパケット、間違ったパケットをを受けるには小さい値です。 大きな BLOB を使用している場合は、これを増加する必要があります。使用した い最大の BLOB と同じくらい大きくするべきです。
max_connections
許される同時クライアントの数。これを増加する場合は、mysqld が持つファイ ルディスクリプタの数を増やす必要があるでしょう。 後述のファイルデスクリプターの制限の説明を参照のこと。 これは OS に依存しますので、OS のドキュメントを見てください。
max_connect_errors
もしあるホストからの接続中断がこの値を以上になった場合、これ以後、 そのホストからの接続を拒絶します。FLUSH HOSTS コマンドで ホストの拒否を解除できます。
max_delayed_threads
この値を超えて INSERT DELAYED を扱うスレッドを起動できません。 もし全ての INSERT DELAYED スレッドが使用されていて、さらに新しいテーブルに データを挿入しようとすると、そのレコードは DELAYED が与えられていない 場合と同様に挿入されます。
max_join_size
max_join_size より多いレコードを触るとエラーが返ります。長い時間をかけて 百万行を返すような WHERE なしの結合を作成するようなユーザを持って いる場合にこれを設定してください。
max_sort_length
BLOB または TEXT 項目上でソートする時に使用するバイト数。 (最初の max_sort_length バイトだけがそれぞれの値で使用でき、残りは無視されます)
max_tmp_tables
(このオプションはまだなにも行いません). Maximum number of temporary tables a client can keep open at the same time.
net_buffer_length
通信バッファがクエリ間でこのサイズにリセットされます。これは通常は変更す べきではありませんが、とても小さなメモリしかない場合は、これを期待される クエリのサイズに設定してください。 ( これは、クライアントから送られてくるSQL文の長さ分あればいいでしょう。 もし構文がこの値をこえた場合、バッファは自動的に大きくなります。 ただし max_allowed_packet バイトまでです)
record_buffer
順序スキャンを行う各スレッドが、スキャンするテーブル毎に、このサイズのバッ ファを割り当てます。多くの順序スキャンを行う場合は、これを増加させてくだ さい。
sort_buffer
ソートを行う必要がある各スレッドがこのサイズのバッファを割り当てます。よ り速い ORDER BY または GROUP BY のためにはこれを増やしてく ださい。 「18.4 MySQL が一時ファイルを格納する場所」節参照.
table_cache
全てのスレッドについてのオープンテーブルの数。これを増加する場合は、 mysqldが要求するオープンファイルディスクリプタの数も 増加することに注意しないといけません。 MySQL はユニークテーブル毎に2つのファイルディスクリプタを必要と します。 後述のファイルデスクリプターの制限の説明を参照のこと。 テーブルキャッシュがどのように働くかはこちらを参照 「10.2.4 MySQL はどのようにテーブルのオープン & クローズを行なうか?」節.
tmp_table_size
一時テーブルがこれよりも大きい場合、MySQLThe table ### is full エラーを生成します。 多くの先進的な GROUP BY クエリを行う場合は、この値を増加してください。
thread_stack
各スレッドのスタックの大きさ。crash-me によって検出される多く の制限がこれに依存します。デフォルトでは、通常のオペレーションに対して 十分とってあります。 「10.8 Using your own benchmarks」節参照.
wait_timeout
サーバーがコネクションを閉じるまでにアクティブなコネクションを待つ秒数。

MySQL はとてもスケーラブルなアルゴリズムを使用します。そのため 通常はとても少ないメモリで動作し、またより良い性能を得るために MySQL に多くのメモリを与えることができます。

多くのメモリと多くのテーブルを持っていて、適度のクライアント数で最大性能を得た い場合、次のようなものを使用します:

shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
           -O sort_buffer=4M -O record_buffer=1M &

多くの接続で少ないメモリしかない場合、次のようなものを使用します:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &

or even:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

もしとても多くの接続があり、mysqld が各接続に対して少ないメモリーで 動作するように設定されていれば、スワップの読み書きによるパフォーマンスの低下がおきます。 もちろん十分なメモリーがあれば、全ての接続にたいして mysqld のパフォーマンス良くなります。

mysqld へのオプションを変更する場合、そのサーバのインスタンスにだけに 有効であることに注意して下さい。

パラメータ変更の効果を見るには、このようにします:

shell> mysqld -O key_buffer=32m --help

--help オプションは最後につけてください。 その他のオプションを --help の後につけると、そのオプションは反映されません。

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

table_cache, max_connections , max_tmp_tables は サーバーが開いた状態にしておく事が出来るファイルの最大数に影響します。 If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. However, you can increase the limit on many systems. Consult your OS documentation to find out how to do this, because the method for changing the limit varies widely from system to system.

table_cachemax_connections に関連します。 例えば 200 のコネクションを同時に開けるなら、少なくとも 200 * n の テーブル・キャッシュが必要です。 ここで n は join におけるテーブルの 最大数です。

オープンテーブルのキャッシュは最大で table_cache まで大きくなります(デ フォルトは 64, mysqld のオプション -O table_cache=# で変更可能)。 キャッシュが一杯になって、他のスレッドがテーブルのオープンを試みた時、 または mysqladmin refresh mysqladmin flush-tables を使用した場合を除いて、テーブルはクローズされません。

キャッシュがいっぱいになった場合、サーバーは以下の処置を取って キャッシュを使用できるように配置します:

テーブルは各同時アクセスに (再び) オープンされます。これは、同じテーブルで2つのスレッ ドが実行されている場合、または同じクエリで(AS で)テーブルを2回アクセス する場合、テーブルは2回オープンする必要があることを意味します。最初のテーブルのオー プンは2つのファイル記述子を使用し、続くテーブルの各使用は1つだけのファイル記述 子を使用します。

10.2.5 Drawbacks of creating large numbers of tables in the same database

もしたくさんのファイルが一つのディレクトリにある場合、オープン、クローズ、 作成操作は遅くなるでしょう、 もし SELECT 文を多くのテーブルに対し 実行した場合、テーブルキャッシュが一杯ならば、このオーバーヘッドは多くなるでしょう。 なぜなら、それぞれのテーブルにつきオープンし、クローズしなくてはならないからです。 このオーバーヘッドを緩和するには、テーブルキャッシュを大きくします。

10.2.6 何故そんなに多くのテーブルをオープンするのか?

mysqladmin status を実行すると、次のようなものが得られます:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

確かにたった6個しかテーブルがないのに、このような結果が出るので少々戸惑われるかもしれません。

MySQL はマルチスレッドなので、同じテーブルで一度に多くのクエリを持て ます。同じファイル上で異なる状態を持つ2つのスレッドで、問題を最小化する ため、同時に動作する各スレッドのためテーブルを再びオープンします。これはいくつ かのメモリとデータファイルについての一つの拡張ファイル記述子を使用します。 インデックスファイル記述子は全てのスレッド間で共有されます。

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

以下に mysqld サーバーがどのようにメモリーを使用するか、いくつか示します。 サーバーに与える変数名は、サーバーのメモリーの使用方に関連した名前となっています。

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

10.2.8 MySQL はどのようにテーブルをロックするか

MySQL の全てのロックはデッドロックフリーです。これは、常にクエ リ開始時に一度ロックを必要とする全てを要求し、常に同じ順でテーブルをロッ クすることで管理されます。

MySQL が使用する WRITE ロックは次のように働きます:

MySQLREAD ロックは次のように動きます:

ロックが解放されたとき、最初に write ロックキュー内のスレッドに、その後 read ロックキュー内のスレッドにロックを与えます。

これは、同じテーブルで多くの更新をする場合、SELECT 構文は update がなくなるまで待たされることを意味します。

同じテーブルで多くの INSERT と多くの SELECT を行う場合、これを解決するに は、他のテーブルに行を挿入して、たまに、その一時テーブルから全てのレコー ドをもう一方のテーブルに update します。

これは次のコードで行えます:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;
mysql> UNLOCK TABLES;

決まりきった検索に優先権を与えるため、INSERT 構文で LOW_PRIORITYHIGH_PRIORITY オプションが使用できます。 「7.14 INSERT構文」節参照.

一つのキューだけを使用するように `mysys/thr_lock.c' 内のロックコードを変更 することもできます。この場合、いくつかのアプリケーションのために、 write ロックは read ロックと同じ優先順位にします。

10.2.9 テーブル・ロッキングの問題

MySQL のテーブル・ロッキングのコードはデッドロック・フリーです。

MySQL はとても速いロックスピードを得るために、 (レコードのロックやフィールドのロックの代わりに) テーブルのロックを使用します。 大きなテーブルには、 テーブルのロックはレコードのロックよりはるかに良いですが、 いくつかの落とし穴があります。

In MySQL 3.23.7 and above, one can insert rows into MyISAM tables at the same time as other threads are reading on the table. Note that currently this only works if there is no deleted rows in the table.

テーブルのロックはたくさんのスレッドが同時にあるテーブルから読み出すのを可能にしますが、 もしあるスレッドがあるテーブルに対し書込み要求を出す場合、 それはまず最初に排他的なアクセスを得なければなりません. その更新の間、更新が完了するまで、この特定のテーブルにアクセスしようとする 他の全てのスレッドが待たされることになります。

通常データベースの更新は SELECT よりも重要とされるため、 テーブルを更新する全てのスレッドが、テーブルから情報を検索するスレッドよりも 高い優先順位を持ちます。 これは ある特定のテーブルに対して、多くの重いクエリが発行され た場合に、 更新が不完全に終わらないことを確実にするためです。

Starting from MySQL 3.23.7 one can use the max_write_lock_count variable to force MySQL to issue a SELECT after a specific number of inserts on a table.

この1つの主な問題が以下です:

この問題のいくつかの可能な解決は以下の通りです:

10.3 可能な限りデータを小さくする

One of the most basic optimization is to get your data (and indexes) to take as little space on the disk (and in memory) as possible. This can give huge improvements since disk reads are faster and normally less main memory will also be used. Indexing also takes less resources if done on smaller columns.

You can get better performance on a table and minimize storage space using the techniques listed below:

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

Indexes are used to find find a row with a specific calue on one column fast. Without a index MySQL has to start with the first record and then read through the whole table until it find the relevent rows. The bigger the table the more this costs. If the table has a index for the colums in question MySQL can get fast a possition to seek to in the middle of the data file without having to look at all data. If a table have 1000 rows this is at least 100 times faster than reading sequentially. Note that is you need to access almost all 1000 rows it is faster to read sequentially since we when avoid disk seeks.

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

Indexes are used to:

以下のような SELECT 文を発行したとします:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

もし col1col2 に複数フィールドインデックスが定義されている場合、 すぐに思ったとおりの結果が得られます。 If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows.

もしテーブルが複数フィールドインデックスを持つなら、インデックスの接頭部一番左の部分 がレコードを見つけるための最適化に使用されます。例えば、3つのフィールド (col1,col2,col3) に対して一つのインデックスを持っていたとします。 すると、これは (col1), (col1,col2) , (col1,col2,col3) でインデックスがサーチされます。

もし一番左に指定しているフィールドがインデックスを作成していないなら、 MySQL は部分的なインデックスを使用しません。

以下のような SELECT 文を発行したとします:

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

もしインデックスが (col1,col2,col3) にあるならば、 上に示した最初のクエリだけがインデックスを使用します。二番目三番目のクエリは インデックスを持つフィールドを発動しますが、 (col2)(col2,col3) は、(col1,col2,col3) の 接頭部一番左に指定されていません。

MySQL は、もし LIKE がワイルドカードから始まっていなくて、 ある種固定された文字で始まっているなら、 LIKE の評価にインデックスを使用します。 例えば、以下の SELECT 文はインデックスを使用します:

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

一つ目の文は、"Patrick" <= key_col < "Patricl" となるレコードだけ、考慮されます。 二つ目の文は、"Pat" <= key_col < "Pau" となるレコードだけ、考慮されます。

以下の SELECT 文はインデックスを使用しません:

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

最初の文は、LIKE がワイルドカード文字で始まっています。 二つ目の文は、LIKE が定数ではありません。

Searching using column_name IS NULL will use indexes if column_name is a index.

MySQL は通常、一番少ないレコード数を見つけるインデックスを使用します。 インデックスは、以下に示す演算子を用いて比較できるフィールドに対して、使用されます: =, >, >=, <, <=, BETWEEN そして 'something%' の様に頭にワイルドカードがない LIKE

WHERE 節ないの全ての AND にかからないインデックスは、 全くクエリの最適化に使用されません。

以下の WHERE 節はインデックスを使用します:

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */

以下の WHERE 節はインデックスを使用しません

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

10.5 Speed of querys that access or update data

First one thing that affect all querys. The more compex permission systen setup you have, the more overhead you get.

If you do not have any GRANT statements done MySQL will optimize the permission checking somewhat. So if you have a very high vokume it may be worth the time to avoid grants. Otherwise more permission check gives a larger overhead.

もし MySQL 関数のあるものが確実に問題を引き起こしているのならば、 常に MySQL クライアント側は以下のようになります:

mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

上の例では MySQL は 1,000,000 + 式を 0.32 秒で実行できています (たんなる PentiumII 400MHz マシンで)。

全ての MySQL 関数は最適化されていますが、 いくつかは例外があるかもしれません。 benchmark(loop_count,expression)はあなたのクエリの 問題を見つけるためのとてもよいツールです。

10.5.1 Estimating query performance

ほとんどの場合、ディスク・シークを数えることでだいたいのパフォーマンスを予測できます。 小さなテーブルでは、通常、1回のディスク・シークでレコードを見つけれるでしょう (インデックスがたぶんキャッシュされるので)。 大きなテーブルでは、 おおよその予測として、(B++ ツリーインデックスを使用している場合)、 log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1 シーク、1レコードを見つけるのに必要となるでしょう。

MySQL では、インデックス・ブロックは通常 1024 バイトで、 データ・ポインターは通常 4 バイトです。 これは、 インデックス長が 3 (medium integer) 、データが 500,000 レコードあるテーブルでは、 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 シークとなります。

As the above index would require about 500,000 * 7 * 3/2 = 5.2M, (assuming that the index buffers are filled to 2/3 (which is typical) you will probably have much of the index in memory and you will probably only need 1-2 calls to read data from the OS to find the row.

For writes you will however need 4 seek requests (as above) to find where to place the new index and normally 2 seeks to update the index and write the row.

Note that the above doesn't mean that your application will slowly degenerate by N log N! As long as everything is cached by the OS or SQL server things will only go marginally slower while the table gets bigger. After the data gets too big to be cached, things will start to go much slower until your applications is only bound by disk-seeks (which increase by N log N). To avoid this increase the index cache as the data grows. 「10.2.3 サーバーパラメーターのチューニング」節参照.

10.5.2 SELECT クエリの速度

通常、遅い SELECT ... WHERE を速くするには、 まず最初にインデックスがあるかどうかをチェックします。 「10.4 MySQL はどのようにインデックスを使用するか?」節参照. 違うテーブルを参照する場合には、普通はインデックスをともなうべきです。 EXPLAIN コマンドを使用すれば、SELECT でどのインデックスが 使用されているか確認できます。 「7.22 EXPLAIN 構文 (SELECTについての情報を得る)」節参照.

Some general tips:

10.5.3 MySQL はどのように WHERE 節を最適化するか?

The where optimizes are put in the SELECT part here since they are mostly used there. But the same optimizations are used for there in DELETE and UPDATE statements.

Also note that this section is incomplete. MySQL does many optimizations and we have not had time to document them all.

MySQLによる最適化のいくつかを以下に示します:

以下はとても速いクエリの例です:

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
           WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

以下のクエリはインデックスツリーのみを使用します(インデックス化されているフィールドは 数値型と仮定します):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
           WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

以下のクエリは、ソートされた行の検索にインデックスを使用します:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...

10.5.4 How MySQL optimizes LEFT JOIN

A LEFT JOIN B is in MySQL implemented as follows

10.5.5 How MySQL optimizes LIMIT

In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:

10.5.6 INSERT クエリの速度

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

ここで (数字) は比例時間です。これは、テーブルのオープンにかかる初期オーバーヘッ ド(これは同時に動作する各クエリ毎に1回行なわれます)は考慮されていません。

テーブルのサイズはインデックスの挿入を N log N で遅くします (B-tree)。

Some ways to speed up inserts:

To get some more speed for both LOAD DATA INFILE and INSERT, enlarge the key buffer. 「10.2.3 サーバーパラメーターのチューニング」節参照.

10.5.7 UPDATE クエリの速度

UPDATE クエリは SELECT クエリのように最適化されますが、 書き込みのオーバーヘッドが加わります。 書き込み速度は、更新されるデータの大きさ、更新されるインデックス数に 依ります。

Also another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table.

Not that with dynamic record format updating a record with to a longer total length may split the record. So if you do this often it is very important to OPTIMIZE TABLE sometimes. 「7.9 OPTIMIZE TABLE構文」節参照.

10.5.8 DELETE クエリの速度

1レコードを削除する時間は、ちょうどインデックスの数に比例します。 より速くレコードを削除したいなら、インデックス・キャッシュを 増やします。 「10.2.3 サーバーパラメーターのチューニング」節参照.

Its also much faster to remove all rows than to remove a big part of the rows from a table.

10.6 table 型の選択

MySQL では、現在 (version 3.23.5)、4つのテーブルフォーマットを、 スピードの観点から選ぶことが出来ます。

MyISAM Static
このフォーマットは、最も単純、かつ、安全なフォーマットです。 これは最も速いフォーマットでもあります。 The speed comes from the easy way data can be found on disk. When looking up something with a index and static format it very simple, just multiply the row number with the row length. Also when scanning a table it is very easy to read a constant number of records with each disk read. 安全、というのは、次の様なことです。 もし仮に、静的(固定長) MyISAM ファイルに書き込み中に、 あなたのコンピュータがクラッシュした場合、 myisamchk は、それぞれのレコードの開始点と終了点を安易に見つけることが出来ます。 So it can usually reclaim all records except the partially written one. Not that in MySQL all indexes can always be reconstructed.
MyISAM Dynamic
This format is a litte more comples since each row has to have a header that says how long it is. One record can also end up at more that one location when it is made longer at a update. OPTIMIZE tablemyisamchk を使用して、テーブルの フラグメンテーションを修正することが可能です。 If you have static data that you acess/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation.
MyISAM compressed
これは読み込み専用の型で、オプションツールの myisampack で作成されます。
In memory (HEAP)
This table format is extramly usefull for small/medium sized lookup tables. It is possible to copy/create a frequently used lookup table (in joins) table to a (maybe temporary) HEAP table to speed up many joins. Suppose we want to do the following join may times with the same data.
SELECT tab1.a, tab3.a FROM tab1, tab2, tab3
        WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;
これをスピードアップするために、我々は、 tab2 と tab3 を join した一時テーブルを作成することができます。 なぜならこれらは同じフィールド(tab1.a) に参照されるからです。 以下に一時テーブルの作成と SELECT を示します。
CREATE TEMPORARY TABLE test TYPE=HEAP
        SELECT
                tab2.a as a2, tab3.a as a3
        FROM
                tab2, tab3
        WHERE
                tab2.a = tab3.a and c = 0;
SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;
SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;

10.6.1 Static (Fixed-length) table characteristics

10.6.2 Dynamic table characteristics

10.6.3 Compressed table characteristics

MySQL can support different index types, but the normal type is ISAM. This is a B-tree index and you can roughly calculate the size for the index file as (key_length+4)*0.67, summed over all keys. (This is for the worst case when all keys are inserted in sorted order.)

String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression helps if there are many strings with an identical prefix.

10.6.4 In memory table characteristics

HEAP テーブルはメモリー内にのみ存在することが出来ます。 そのため、もし mysqld が止められたりクラッシュしたりした場合、 このデータはなくなります。 しかしこれは とても 速いので、とにかく便利です。

The MySQL internal HEAP tables uses 100% dynamic hashing without overflow areas and don't have problems with delete.

You can only access things by equality using a index (usually by the = operator) whith a heap table.

HEAP のもつマイナス面は:

  1. 同時に使用したいと思っている HEAP テーブルを全て記憶できるような、 十分おおきなメモリが必要。
  2. インデックスのパートをサーチできません。
  3. You can't search for the next entry in order (that is to use the index to do a ORDER BY).
  4. MySQL also cannot find out how approximately many rows there are between two values. This is used by the optimizer to chose which index to use. But on the other hand no disk seeks are even needed.

10.7 Other optimization tips

Unsorted tips for faster systems:

10.8 Using your own benchmarks

You should definitely benchmark your application and database to find out where is the bottlenecks. By fixing it (or by replacing the bottleneck with a 'dummy module') you can then easily identify the next bottleneck (and so on). Even if the overall performance for your application is 'good enough' you should at least make a 'plan', for each bottleneck, how to solve it if you someday 'really need it fix it'.

For some example portable becnchmark programs look at the MySQL benchmark suite. 「11 MySQL ベンチマークスイート」節参照. You can take any program this suite and modify it for your needs. By doing this, you can try different solutions to your problem and test which is really the fastest solution for you.

It is very common that some problems only occur then the system is very heavily loaded. And we have had many customer who contacts us then they have a (tested) system in production and have have got load problems. In every on these cases so far it has been problems with basic design (table scans are NOT good at high load) or OS/Library issues. Most of this would be a LOT easier to fix if the system where not already in production.

To avoid probles like this you should put some effort into benchmarking your whole appliction under the worst possible load!

10.9 Design choices

MySQL keeps row data and index data in separate files. Many (almost all) other databases mix row and index data in the same file. We belive that the MySQL choice is better for a very wide range of modern systems.

Another way to store the row data is to keep the information for each column in a separate area (examples are SDBM and Focus). This will get a performance hit for every query that access more than one column. Since this degenerates so quickly when more that when one columns are accessed we believe that this model is not good for general purpose databases.

The more common case is there the index and data are stored together (like in Oracle/Sybase at all). In this case you will find the row information at the leaf page of the index. The good thing with this layout is that it in many cases (depends on how well the index is cached) saves a disk read. The bad things with this layout is:

*
Table scanning is much slower since you have to read through the indexes to get at the data.
*
You loose a lot of space as you must duplicate indexes from the nodes (as you can't store the row in the nodes)
*
Deletes will degenerate the table over times (as indexes in nodes are usually not updated on delete).
*
You can't use only the index table to retrieve data for a query.
*
The index data is harder to cache.

10.10 MySQL design limitations/tradeoffs

Since MySQL uses extremely fast table locking (multiple readers / single writers) the biggest remaining problem is a mix of a steady stream of inserts and slow selects on the same table.

We belive that for a huge number of system the extreamy fast performance in other cases make this choice a win. This case is usually also possible to solve by having multiple copies of the table. But it takes more effort and hardware.

We are also working on some extension to solve this problem for some common application niches.

10.11 Portability

Since all SQL servers implement different parts of SQL it takes work to write portable SQL applications. For very simple selects/inserts it is very easy but the more you need the harder it gets. And if you want a application that is fast with many databases it becomes even harder!

To make a complex application portable you need to choose a number of SQL server that it should work with.

When you can use the MySQL crash-me program/web-page http://www.mysql.com/crash-me-choose.htmy to find functions, types and limits you can use with a selection of database servers. Crash-me now test a long way from everything possible but it still is vエcomprehensive with about 450 things tested.

For example, you shouldn't have longer column names than 18 characters if you want to be able to use Informix or DB2.

Both the MySQL benchmarks and Crash-me programs are very database independent. By taking a look of how we have handled this, you can get a feeling of what you have to do to write your application database independent. The benchmark themselves can be found in the `sql-bench' directory in the MySQL source distribution. They are written in Perl with DBI database interface (which solves the access part of the problem.

See http://www.mysql.com/benchmark.html the results from this benchmark.

As you can see in these results all databases has some weak points. That is they have different design compromises that lead to different behavior.

If you strive for database independence you need to get a good feeling of each SQL servers bottlenecks. MySQL is VERY fast in retrieving and updating things, but will have a problem in mixing slow readers/writers on the same table. Oracle on the other hand has a big problem when you try to access rows that you have recently updated (until they are flushed to disk). Transaction databases in general are not very good in generating summary tables from log tables as in this case row locking is almost useless.

To get your application 'really database independent' you need to define a easy extendable interface through which you manipulate your data. As C++ is available on most systems, it makes sense to use a C++ classes interface to the databases.

If you use some specific feature for some database (like the REPLACE command in MySQL), you should code a method for the other SQL servers to implement the same feature (but slower). With MySQL you can use the /*! */ syntax to add MySQL specific keywords to a query. The code inside /**/ will be treated as a comment (ignored) by most other SQL servers.

If REAL high performance is more important than exactness, like in some web applications. A possibility is to create a application layer that caches all results to give you even higher performance. By just letting old results 'expire' after a while you can keep the cache reasonable fresh. This is quite nice in case of extremely high load, in which case you can dynamicly increase the cache to be bigger and set the expire timeout higher until things gets back to normal.

In this case the table creating information should contain information of the initial size of the cache and how often the table should normally be refreshed.

10.12 What have we used MySQL for?

MySQL の初期の開発期には、MySQL は我々のもっとも大口の顧客に合うように 機能が作成されました。 彼らは、いくつかのスウェーデン最大手の小売り業者 のために、倉庫に入れている(商品)データを取り扱います。

我々は、すべての店から、全ボーナス・カード取扱高の、その週間まとめを得ます。 そして、その店のオーナにとって有益な情報、その店の広告キャンペーンが お客にどの程度影響を及ぼすか、を提供することが、我々に求められています。

そのデータは、とても大きくて (約700万/月 回の取り扱い)、 我々はその顧客に提供する必要のあるデータを 4〜10年分、持っています。 我々は、カスタマーから、彼らがこのデータからできる新しいレポートに ”即時に”アクセスしたいという、要求を受けました。

我々はこれを、全ての月ごとの情報を圧縮した 'transaction' テーブルに 保存することで解決しました。 We have a set of simple macros/script that generate summary tables grouped by different criterias (product group, customer id, store ...) from the transaction tables. そのレポートは Web ページで、これは小さな perl スクリプトで動的に 作成されます。 この perl script は Web Page を分析し、SQL 文を 実行し、結果を挿入します。 Now we would have used PHP or mod_perl instead but they where not available at that time.

画像データのために、我々は簡単なツールを C でかきました。 これは SQL のクエリの結果を元に(結果にいくつか処理をして) GIF を提供します。 これも動的に perl スクリプト(HTML ファイルを分析する)から実行されます。

In most cases a new report can simple by done by copying a existing script and modifying the SQL query in it. In some cases we will need to add more fields to an existing summary table or generate a new one, but this is also quite simply as we keep all transactions tables on disk. (Currently we have at least 50G of transactions tables and 200G of other customer data).

We also let our customers access the summary tables directly with ODBC so that the advanced users can themselves experiment with the data.

我々はこれらを Sun Ultra sparcstation (2x200 Mz) で扱っていますが、 なんの問題もありません。 We recently upgrade one of our servers to a 2 CPU 400 Mz Ultra sparc and we are now planing to start handling transactions on the product level, which would mean a 10 fold increase of data. We think we can keep up with this by just adding more disk to our systems.

We are also experimenting with Intel-Linux to be able to get more cpu power cheaper. 現在、我々はバイナリ互換のデータベースフォーマットを持っており (new in 3.23) 、我々はこれをアプリケーションのいくつかの部分に使用しはじめる事でしょう。

Our initial feelings are that Linux will perform much better on low to medium load but Solaris will perform better when you start to get a a high load because of extrema disk IO, but we don't yet have anything conclusive about this. After some discussion with a Linux Kernel developer this might be a side effect of Linux giving so much resources to the batch job that the interactive performance gets very low. This make the machine feel very slow and unresponsive while big batches are going. Hopefully this will be better handled in future Linux Kernels.


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