Optimization is a complicated task because 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.
システムを速くするための最も重要な部分は、もちろん基礎的な設計です。 You also need to know that kinds of things your system will be doing. That is your bottlenecks are.
主なボトルネックは：
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 because 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) のリリース時に なくなって欲しいものです。
Because 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:
--skip-locking MySQL オプション
を使用します。
Note that this will not impact MySQL functionality as
long that only run one server.
myisamchk を実行する前に、サーバーを止めることを忘れないでください。
On some system
this switch is mandatory because the external locking does not work in any
case.
MIT スレッドでコンパイルする時は --skip-locking がデフォルトです。
これは全てのプラットフォームで MIT スレッドが flock() を完全にサポートし
ていないためです。
--skip-locking が使用できないただ一つの状態は、
もしあなたが複数の MySQL SERVER (クライアントではないです) を
同じデータに対して走らせている場合です。
Or run
myisamchk on the table without first flushing and locking the
mysqld server tables first.
たとえ --skip-locking を使用していても、
LOCK TABLES / UNLOCK TABLES の使用は可能です。
次のテストの多くは 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:
pgcc を使用して -O6 で全てをコンパイルすると、
gcc 2.95.2 より古いバージョンでは、
mysqld サーバーは 11% 速くなります。
-static) 無し)の場合、結果は 13% 遅くなります。
Note that you still can use a dynamic linked MySQL library. It
is only the server that is critical for performance.
gcc 2.7.3 は Sun Pro C++ 4.2 より 13% 速くなります。
TCX により配布されている MySQL-Linux 配布は pgcc でコンパイルされて
いますが、
but we had to go back to regular gcc because of a bug in pgcc that would
generate the code that does not run on AMD. We will continue using gcc until that bug is resolved.
In the meantime, if you have a non-AMD machine, you can get a faster binary by compiling with pgcc.
The Linux binary is linked statically.
テーブルとデータベースのファイルを MySQL のデータベースディレクトリーから 違う場所に移動し、 それに対してシンボリックリンクを張ることができます。 これは例えばディスク容量が少なくなって、データベースを移動したい場合にそうしたくなるでしょう。
MySQL が、テーブルがシンボリックリンクであることに気づいた場合、
symlink を解析し、代わりにその実体のテーブルを使用します。
これは realpath() コールをサポートする全てのシステムで働きます（少
なくとも Linux と Solaris は realpath() をサポートします！）。
realpath() をサポー トしないシステム上では、symlink とテーブルを
同時に使用すべきでありません！ テーブルはテーブルの更新後に矛盾する事になるでしょう。
MySQL はデフォルトではデータベース同士のリンクをサポートしません。
が、データベース間のシンボリックリンクを作成しない限り、これは正常に働くでしょう。
MySQL データディレクトリに db1 データベースがあるとして、
仮に db2 を db1 のシンボリックリンクにしたとします:
shell> cd /path/to/datadir shell> ln -s db1 db2
すると、db1 中の tbl_a テーブルは db2 の tbl_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))
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 interactive_timeout current value: 28800 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メガバイトを示します。大文字小文字の区別はなく、
16M と 16m は同じ意味になります。
稼働中のサーバーの統計情報は SHOW STATUS で参照できます. 「7.23 SHOW 構文 (テーブルやフィールドなどについての情報を得る)」節参照.
ansi_mode.mysqld が --ansi オプションで起動されているなら、ON.
「5.2 ANSI モードでの MySQL の実行」節参照.
back_loglisten(2) のマニュアルページに、さらに詳細があります。
この値を最大限にしたい場合、お使いのOSのドキュメントを見てください。
back_log をこのOSの制限値より多く取ってもは全く効果ありません。
thread_cache_sizethread_cache_size threads from before. All new
threads are first taken from the cache and only when the cache is empty
a new thread is created. This variable can be increased to improve
performance if you have a lot of new connections (Normally this doesn't
however give a notable performance improvement if you have a good
thread implementation).
concurrent_insertsON (これはデフォルトです) なら、MySQL は、
SELECT クエリが実行されている MyISAM テーブルに対して、
同時に INSERT が使用できるようにします。
このオプションは mysqld のオプションに --safe か --skip-new を
指定することで OFF にできます。
connect_timeoutmysqld サーバーが接続パケットを待つ秒数。 (Bad handshake を返すまでの秒数)
delayed_insert_timeoutINSERT DELAYED スレッドが INSERT 文を待つべきか。
delayed_insert_limitdelayed_insert_limit 個のレコードを挿入した後、 INSERT DELAYED ハンドラーは
待たされている SELECT 文がないかチェックします。 もしあるなら、
挿入を続ける前にそれらの実行を許します。
delay_key_writeCREATE TABLE 文の
DELAY_KEY_WRITE オプションを尊重します。
DELAY_KEY_WRITEオプションを指定されて作られたテーブルのキーバッファは、
毎回のインデックスの更新にはフラッシュされず、ただテーブルが閉じられたときにだけ
フラッシュされます。 これはキーの書き出しを速くしますが、もしこれを使用するなら、
全てのテーブルを myisamchk --fast --force で自動的に検査するようにすべきです。
もし mysqld を --delay-key-write_for_all_tables オプション付きで
起動した場合、これは、全てのテーブルが delay_key_write オプション指定されて
作成されたものとして扱われる事に注意してください。
このフラグは、mysqld を --skip-new や --safe-mode オプション
で起動すれば無効にすることも出来ます。
delayed_queue_sizeINSERT
DELAYED. If the queue becomes full, any client that does INSERT
DELAYED will wait until there is room in the queue again.
flush_timeflush_time 秒ごとに
全てのテーブルが閉じられます。 (リソースの解放とDiskへのsyncのために)
init_file--init-file オプションに与えたファイルの名前です。
このファイルには、サーバー起動時に実行したい SQL 文を書いておきます。
interactive_timeoutmysql_real_connect() に CLIENT_INTERACTIVE オプションを
セットします。 See also wait_timeout.
join_buffer_sizekey_buffer_sizekey_buffer_size はインデックス・ブロックのためのバッファ・サイズです。
Increase this get better index handling (for all reads and multiple
writes) to as much as you can afford; 64M on a 256M machine that mainly
runs MySQL is quite common. If you however make this too big
(more than 50 % of your total memory?) your system may start to page
and become REAL slow. Remember that because MySQL does not cache data
read that you will have to leave some room for the OS filesystem cache.
You can check the performance of the key buffer by doing show
status and examine the variables Key_read_requests,
Key_reads, Key_write_requests and Key_writes. The
Key_reads/Key_read_request ratio should normally be < 0.01.
The Key_write/Key_write_requests is usually near 1 if you are
using mostly updates/deletes but may be much smaller if you tend to
do updates that affect a lot of rows at the same time or if you are
using delay_key_write. 「7.23 SHOW 構文 (テーブルやフィールドなどについての情報を得る)」節参照.
To get even more speed when writing many rows at the same time use
LOCK TABLES. 「7.27 LOCK TABLES/UNLOCK TABLES 構文」節参照.
lower_case_table_nameslong_query_timeSlow_queries カウンター
が増やされます。
max_allowed_packetnet_buffer_length バイトに初期化されますが、
max_allowed_packetまで大きくすることができます。このデフォルト値は、
大きなパケット、間違ったパケットをを受けるには小さい値です。
大きな BLOB を使用している場合は、これを増加する必要があります。使用した
い最大の BLOB と同じくらい大きくするべきです。
max_connectionsmysqld が持つファイ
ルディスクリプタの数を増やす必要があるでしょう。
後述のファイルデスクリプターの制限の説明を参照のこと。 「20.2.4 Too many connections エラー」節参照.
max_connect_errorsFLUSH HOSTS コマンドで
ホストの拒否を解除できます。
max_delayed_threadsINSERT DELAYED を扱うスレッドを起動できません。
もし全ての INSERT DELAYED スレッドが使用されていて、さらに新しいテーブルに
データを挿入しようとすると、そのレコードは DELAYED が与えられていない
場合と同様に挿入されます。
max_join_sizemax_join_size より多いレコードを触るとエラーが返ります。長い時間をかけて
百万行を返すような WHERE なしの結合を作成するようなユーザを持って
いる場合にこれを設定してください。
max_heap_table_sizemax_sort_lengthBLOB または TEXT 項目上でソートする時に使用するバイト数。
(最初の max_sort_length バイトだけがそれぞれの値で使用でき、残りは無視されます)
max_tmp_tablesmax_write_lock_countnet_buffer_lengthmax_allowed_packet バイトまでです)
net_retry_countFreeBSD as
internal interrupts is sent to all threads.
record_bufferquery_buffer_sizeskip_show_databasesPROCESS_PRIV 権限を持っていないユーザーが
SHOW DATABASES する事を阻止します。 もし、他人のデータベースや
テーブルを見ようとする人がいる事を、あなたが心配するならば、
これはセキュリティを強化できます。
sort_bufferORDER BY または GROUP BY のためにはこれを増やしてく
ださい。
「20.5 MySQL が一時ファイルを格納する場所」節参照.
table_cachemysqldが要求するオープンファイルディスクリプタの数も
増加することに注意しないといけません。
MySQL はユニークテーブル毎に2つのファイルディスクリプタを必要と
します。
後述のファイルデスクリプターの制限の説明を参照のこと。
You can check if you
need to increase the table cache by checking the Opened_tables
variable. 「7.23 SHOW 構文 (テーブルやフィールドなどについての情報を得る)」節参照. If this variable is big and you don't do
FLUSH TABLES a lot (which just forces all tables to be closed and
reopenend), then you should increase the value of this variable.
テーブルキャッシュがどのように働くかはこちらを参照 「12.2.4 MySQL はどのようにテーブルのオープン & クローズを行なうか？」節.
tmp_table_sizeMyISAM テーブルに変換します。
多くの先進的な GROUP BY クエリを行う場合は、
この tmp_table_size を増加してください。
thread_stackcrash-me によって検出される多く
の制限がこれに依存します。デフォルトでは、通常のオペレーションに対して
十分とってあります。
「12.7 Using your own benchmarks」節参照.
wait_timeoutinteractive_timeout も参照のこと。
MySQL はとてもスケーラブルなアルゴリズムを使用します。そのため 通常はとても少ないメモリで動作し、またより良い性能を得るために MySQL に多くのメモリを与えることができます。
When tuning a MySQL server, the two most important variables to use
are key_buffer_size and table_cache. You should first feel
confident that you have got these right before trying to change any of the
other variables.
多くのメモリ(>=256M)と多くのテーブルを持っていて、適度のクライアント数で最大性能を得た い場合、次のようなものを使用します:
shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
-O sort_buffer=4M -O record_buffer=1M &
If you have only 128M and only a few tables, but you still do a lot of sorting, you can use something like:
shell> safe_mysqld -O key_buffer=16M -O sort_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 の後につけると、そのオプションは反映されません。
table_cache , max_connections , max_tmp_tables は
サーバーが開いた状態にしておく事が出来るファイルの最大数に影響します。
もしこれらの変数のうちの一つ、あるいは複数を増加させるなら、
あなたのオペレーティング・システムの、1 プロセスあたりに開かれる
ファイル・デスクリプタ の最大値を増やすことになるかもしれません。
多くのシステムではこの制限を増やすことが可能です。
これをどうやって行うかは、あなたの使用している OS のドキュメントを見てください。
制限値の変更方法は、システムによってまちまちだからです。
table_cache は max_connections に関連します。
例えば 200 のコネクションを同時に開けるなら、少なくとも 200 * n の
テーブル・キャッシュが必要です。 ここで n は join におけるテーブルの
最大数です。
オープンテーブルのキャッシュは最大で table_cache まで大きくなります(デ
フォルトは 64, mysqld のオプション -O table_cache=# で変更可能)。
キャッシュが一杯になって、他のスレッドがテーブルのオープンを試みた時、
または mysqladmin refresh mysqladmin flush-tables
を使用した場合を除いて、テーブルはクローズされません。
キャッシュがいっぱいになった場合、サーバーは以下の処置を取って キャッシュを使用できるように配置します：
テーブルは各同時アクセスに (再び) オープンされます。これは、同じテーブルで2つのスレッ
ドが実行されている場合、または同じクエリで(AS で)テーブルを2回アクセス
する場合、テーブルは2回オープンする必要があることを意味します。最初のテーブルのオー
プンは2つのファイル記述子を使用し、続くテーブルの各使用は1つだけのファイル記述
子を使用します。
もしたくさんのファイルが一つのディレクトリにある場合、オープン、クローズ、
作成操作は遅くなるでしょう、 もし SELECT 文を多くのテーブルに対し
実行した場合、テーブルキャッシュが一杯ならば、このオーバーヘッドは多くなるでしょう。
なぜなら、それぞれのテーブルにつきオープンし、クローズしなくてはならないからです。
このオーバーヘッドを緩和するには、テーブルキャッシュを大きくします。
mysqladmin status を実行すると、次のようなものが得られます:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
確かにたった6個しかテーブルがないのに、このような結果が出るので少々戸惑われるかもしれません。
MySQL はマルチスレッドなので、同じテーブルで一度に多くのクエリを持て ます。同じファイル上で異なる状態を持つ2つのスレッドで、問題を最小化する ため、同時に動作する各スレッドのためテーブルを再びオープンします。これはいくつ かのメモリとデータファイルについての一つの拡張ファイル記述子を使用します。 インデックスファイル記述子は全てのスレッド間で共有されます。
以下に mysqld サーバーがどのようにメモリーを使用するか、いくつか示します。
サーバーに与える変数名は、サーバーのメモリーの使用方に関連した名前となっています。
key_buffer) は全てのスレッドで共有され、
残りは必要時に割り当てられます。
「12.2.3 サーバーパラメーターのチューニング」節参照.
thread_stack), 接続バッファ(variable
net_buffer_length), 結果バッファ(variable
net_buffer_length)。接続バッファと結果バッファは必要となった時、
動的に最大 max_allowed_packet まで増えます。
クエリが走っている時、そのクエリの文字のコピーが割り当てられます。
BLOB を含む
テーブルはディスク上に置かれます。
MySQL 3.23.2 以前での問題は、HEAP テーブルが
tmp_table_size のサイズを越えると、エラー The table tbl_name is full
が出ることです。将来我々は、必要時にメモリ (HEAP) テーブルをディスクベー
ス (NISAM) テーブルに自動的に変更することにより、これを修正します。この
問題を回避するため、mysqld への tmp_table_size オプションま
たはクライアント側で SQL オプション SQL_BIG_TABLES で増加できます。
「7.28 SET OPTION 構文」節参照.
MySQL 3.20 では、一時テーブルの最大サイズは
recordbuffer*16 でした。そのため、このバージョンを使用していると、
recordbuffer を追加する必要があります。 mysqld を --big-tables で
開始することで、常に一時テーブルをディスク上に格納できます。しかしこれは
多くの複雑なクエリの速度に影響します。
malloc() と free() で行なわれます)。
3 * n のサイズのバッファが割り当てられます。
( n は BLOB 型のフィールドの長さを含めない、一レコードの最大長です )
BLOB は 5 から 8 バイト + BLOB データの長さを使用します。
BLOB を持つ各テーブルでは、より大きな BLOB の読み込みでバッファ
は動的に拡大されます。テーブルのスキャンをする場合、割り当てられたバッファは最
も大きい BLOB と同じ大きさになります。
mysqladmin refresh は使用されていない全てのテーブルをクローズし、
使用されている全てのテーブルを、実行中スレッドが終った時にクローズするよ
うにマークします。これは多くの使用メモリを解放するのに有効です。全てのロ
グファイルもクローズと再オープンされます。
mysqld 実行時、ps や他のプログラムは、それが多くのメモリ
を使用していると報告するでしょう。これは異なったメモリアドレス上のスレッ
ドスタックによって発生します。例えば、Solaris ps はスタック間の未使用メ
モリを使用メモリとして計算します。'swap -s' で有効なスワップをチェックす
ることでこれを確かめられます。我々は市販のメモリリーク検出プログラムで
mysqld をテストしました。そのため、メモリリークは全くありません。
MySQL の全てのロックはデッドロックフリーです。これは、常にクエ リ開始時に一度ロックを必要とする全てを要求し、常に同じ順でテーブルをロッ クすることで管理されます。
MySQL が使用する WRITE ロックは次のように働きます:
MySQL の READ ロックは次のように動きます:
ロックが解放されたとき、最初に 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_PRIORITY か HIGH_PRIORITY オプションが使用できます。
「7.16 INSERT構文」節参照.
一つのキューだけを使用するように `mysys/thr_lock.c' 内のロックコードを変更 することもできます。この場合、いくつかのアプリケーションのために、 write ロックは read ロックと同じ優先順位にします。
MySQL のテーブル・ロッキングのコードはデッドロック・フリーです。
MySQL はとても速いロックスピードを得るために、 (レコードのロックやフィールドのロックの代わりに) テーブルのロックを使用します。 大きなテーブルには、 テーブルのロックはレコードのロックよりはるかに良いですが、 いくつかの落とし穴があります。
MySQL 3.23.7 以上では、あるスレッドが MyISAM テーブルを読んでいる最中に、
同じテーブルにレコードを挿入する事ができます。
これは現在、削除がない場合にのみ動きます。
テーブルのロックはたくさんのスレッドが同時にあるテーブルから読み出すのを可能にしますが、 もしあるスレッドがあるテーブルに対し書込み要求を出す場合、 それはまず最初に排他的なアクセスを得なければなりません. その更新の間、更新が完了するまで、この特定のテーブルにアクセスしようとする 他の全てのスレッドが待たされることになります。
通常データベースの更新は SELECT よりも重要とされるため、
テーブルを更新する全てのスレッドが、テーブルから情報を検索するスレッドよりも
高い優先順位を持ちます。 これは ある特定のテーブルに対して、多くの重いクエリが発行され
た場合に、 更新が不完全に終わらないことを確実にするためです。
MySQL 3.23.7 から、max_write_lock_count 変数が使用できます。
これは、一つのテーブルに対して、指定された回数だけ INSERT が行われた後に、
SELECT を発行するようにします。
この1つの主な問題が以下です：
SELECT を発行。
UPDATE を発行；
このクライアントは先の SELECT が完了するまで待ちます
SELECT 構文を発行；
UPDATE は SELECT よりも高い優先度を持つので、
この SELECT は先の UPDATE が完了するまで待ちます。
これは一番最初の SELECT が 終了するまで待たされます！
この問題のいくつかの可能な解決は以下の通りです：
SELECT 構文を速く実行できるように試みます； これを行うにあたり、
いくつかのサマリーテーブルを作成しなければならないかもしれません.
mysqld を --low-priority-updates オプションで起動。
これは全てのあるテーブルを更新する構文に対して、 SELECT 構文よりも
低い優先度を与えます。 この場合、前述のシナリオ中の最後の SELECT 構文は
INSERT 構文が完了する前に実行されます。
LOW_PRIORITY 属性指定を用いて、 INSERT,UPDATE,DELETE 構文
に低い優先度を与えることも出来ます。
mysqld with a low value for max_write_lock_count to give
READ locks after a certain number of WRITE locks.
SET SQL_LOW_PRIORITY_UPDATES=1 を使用して、
ある特定のスレッドからの全ての更新を、低い優先度に指定する事ができます。
「7.28 SET OPTION 構文」節参照.
HIGH_PRIORITY 属性を用いて、特定の SELECT がとても重要であると
指定することが可能です。. 「7.14 SELECT構文」節参照.
INSERT と SELECT をあわせて使用した場合に問題が起きた場合、
新しい MyISAM テーブルに切り替えます。 これは SELECT
と INSERT を同時に行えます。
INSERT と SELECT 構文を主に混在させるのなら、
INSERT の DELAYED 属性指定はこの問題を解決させるでしょう。
「7.16 INSERT構文」節参照.
SELECT と DELETE で問題があるなら、
DELETE の LIMIT オプションが助けになるでしょう。
「7.13 DELETE構文」節参照.
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 because disk reads are faster and normally less main memory will also be used. Indexing also takes less resources if done on smaller columns.
MySQL supports a lot of different table types and row formats. Choosing the right table format may give you a big performance gain. 「8 MySQL table types」節参照.
以下に挙げるテクニックを使用すれば、テーブルでのパフォーマンスの向上、 保存領域の縮小化が可能でしょう：
MEDIUMINT は INT より良いでしょう。
NOT NULL に定義してください。これは全てをより速くし、
各フィールド毎に1ビットを節約します。
Note that if you really need
NULL in your application you should definitely use it. Just avoid
haveing it on all columns by default.
VARCHAR, TEXT, BLOB) を持たない場合は、
固定サイズレコード形式が使用されます。
これはかなり速いです。しかしあいにくいくらかの領域を浪費します。
「8.1.2 MyISAM table formats」節参照.
Indexes are used to find find a row with a specific calue on one column fast. インデックスがない場合、MySQL は、そのレコードが見つかるまで、 最初のレコードからテーブルをなめていきます。 大きなテーブルでは多くの 手間がかかります。 もし問い合わせの中にインデックスを持ったフィールドが ある場合、MySQL は全てのデータをみることなく、データの途中の位置を速く 得ることができます。 もしあるテーブルが1000レコード持っていたとすると、 順番に頭からなめていくことに比べて、これは少なくとも100倍速いことに なります。 Note that is you need to access almost all 1000 rows it is faster to read sequentially because we then avoid disk seeks.
全ての MySQL インデックス, PRIMARY, UNIQUE そして INDEX()
は B tree に格納されます。文字列は自動的に始めと終りの空白が圧縮されます。
「7.30 CREATE INDEX 構文」節参照.
Indexes are used to:
WHERE節にマッチした行の素早い検索
MAX() や MIN() 値の割り出し
ORDER BY key_part_1,key_part_2 )。
もし DESC 指定があれば、全てのキーは逆順に読まれます。
The index can also be used even if the ORDER BY doesn't match the index
exactly, as long as all the not used index parts and all the extra
are ORDER BY columns are constants in the WHERE clause. The
following queries will use the index to resolve the ORDER BY part.
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3; SELECT * FROM foo WHERE column=constant ORDER BY column, key_part1; SELECT * FROM foo WHERE key_part1=const GROUP BY key_part2;
以下のような SELECT 文を発行したとします：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
もし col1 と col2 に複数フィールドインデックスが定義されている場合、
すぐに思ったとおりの結果が得られます。
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 にかからないインデックスは、
全くクエリの最適化に使用されません。
In other words: To be able to use an
index, a prefix of the index must be used in every AND group.
以下の WHERE 節はインデックスを使用します：
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... 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 index1=1 and index2=2 or index1=3 and index3=3;
/* Can use index on index1 but not on index2 or index 3 */
以下の WHERE 節はインデックスを使用しません：
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */ ... WHERE index=1 OR A=10 /* Index is not used in both AND parts */ ... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */
最初に、全てのクエリに影響する一つの事柄をのべます： より複雑な権限の設定を行うと、オーバーヘッドが多くなります。
もしあなたがいかなる GRANT 文も行っていなければ、
MySQL はパーミッションの検査を少ししか最適化しないでしょう。
So if you have a very high volume it
may be worth the time to avoid grants. Otherwise more permission check
results in 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)はあなたのクエリの
問題を見つけるためのとてもよいツールです。
ほとんどの場合、ディスク・シークを数えることでだいたいのパフォーマンスを予測できます。
小さなテーブルでは、通常、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.
書き込み時には、上の場合、新しいインデックスを書き込める場所を 探し出すのに、4 シークかかり、さらに、通常、インデックスを更新し、 レコードを書くのに 2 シーク必要です。
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. 「12.2.3 サーバーパラメーターのチューニング」節参照.
SELECT クエリの速度
通常、遅い SELECT ... WHERE を速くするには、
まず最初にインデックスがあるかどうかをチェックします。 「12.4 MySQL はどのようにインデックスを使用するか？」節参照.
違うテーブルを参照する場合には、普通はインデックスをともなうべきです。
EXPLAIN コマンドを使用すれば、SELECT でどのインデックスが
使用されているか確認できます。
「7.24 EXPLAIN 構文 (SELECTについての情報を得る)」節参照.
Some general tips:
myisamchk
--analyze on a table after it has been loaded with relevant data. This
updates a value for each index part that indicates the average number of
rows that have the same value. (For unique indexes, this is always 1,
of course.). MySQL will use this to decide which index to
choose when you connect two tables with 'a not constant expression'.
You can check the result from the analyze run by doing SHOW
INDEX FROM table_name and examining the Cardinality column.
myisamchk --sort-index --sort-records=1 を使用します
(もし １番目のインデックス順にソートするなら)。
If you
have a unique index from which you want to read all records in order
according to that index, this is a good way to make that faster.
しかし、このソート方法は最適状態では書かれず、また大きなテーブルでは
多くの時間がかかることに注意してください！
WHERE 節を最適化するか？
where の最適化は、where がほとんど SELECT で使用されるため、
SELECT に置かれています。 しかし、同じ最適化は
DELETE や UPDATE 文でも使用されます。
このセクションの最適化の説明はまだ不十分です。 なぜなら、 MySQL はとても多くの最適化を行っており、 それら全てについての説明を書ける時間が、我々はとれません。
MySQLによる最適化のいくつかを以下に示します:
((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) 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 表現
のためにも行われます。
SELECT 構文を素早く見つけ、0 行を結果として返します。
GROUP BY または group 関数 (COUNT(), MIN()...) を使用しない場合は、
HAVING は WHERE とマージされます。
WHERE 評価を得るために、また、可能な限り
早くレコードをスキップするために、各サブ結合についてより簡単な
WHERE が構築されます。
WHERE 節で UNIQUE インデックスや PRIMARY KEY が使用されたテーブルで、
かつ、全てのインデックスが定数式で使用されていて、
かつ、インデックスのパートが NOT NULL で定義されているテーブル。
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
ORDER BY または GROUP 内の全てのフィールドが同じテーブルの場合は、
このテーブルは結合時に最初に優先されます。
ORDER BY 節と GROUP BY 節がある場合、または ORDER BY か GROUP BY が結合キュー
内の最初のテーブルではない他のテーブルからの項目を含む場合、一時テーブルが生成されます。
SQL_SMALL_RESULT を使用するなら, MySQL はメモリー内の一次テーブルを
使用します
DISTINCT は、全てのフィールドに対する GROUP BY に変換されるため、
ORDER BY と合わした DISTINCT は多くの場合で一時テーブルが
必要になります。
HAVING 節に適合するものをスキップします。
以下はとても速いクエリの例です:
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,...
LEFT JOIN を最適化するか？
A LEFT JOIN B は MySQL に以下のように組みこまれています：
B is set to be dependent on table A and all tables
that A is dependent on.
A が LEFT JOIN 条件中に使用される全てのテーブル
（ただし B は除く）に依存すると、セットされます。
LEFT JOIN 条件が WHERE 節に移動されます。
WHERE 節の最適化が行われます。
WHERE 節にマッチするレコードが A にあり、
LEFT JOIN 条件にマッチするレコードが B にない場合、
全てのフィールドが NULL にセットされた B のレコードが
作成されます。
LEFT JOIN to find rows that doesn't exist in some
table and you have the following test: column_name IS NULL in the
WHERE part, where column_name is a column that is declared as
NOT NULL, then MySQL will stop searching after more rows
(for a particular key combination) after it has found one row that
matches the LEFT JOIN condition.
The table read order forced by LEFT JOIN and STRAIGHT JOIN will help
the join optimizer (which calculates in which order tables should be joined) to do
its work much quickly as there are fewer table permutations to check.
Note that the above means that if you do a query of type:
SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key
Then MySQL will do a full scan on b as the LEFT JOIN will
force it to be read before d.
The fix in this case is to change the query to:
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key
LIMIT を最適化するか？
In some cases MySQL will handle the query differently when you are
using LIMIT # and not using HAVING:
LIMIT を使用して限定した数だけ select を行うなら、 MySQL は、
ある場合、テーブルを全て走査するよりも、インデックスを使用しようとします。
LIMIT # を ORDER BY と共に使用するなら, MySQL は
最初の # 行を見つけたらすぐに、全テーブルのソートは行わずに、
ソートを終わらせます。
LIMIT # を DISTINCT と共に使用するなら, MySQL は
# 個の一意なレコードを見つけた時点で終わります。
GROUP BY can be resolved by reading the key in order
(or do a sort on the key) and then calculate summaries until the
key value changes. In this case LIMIT # will not calculate any
unnecessary GROUP's.
# 行をクライアントに送ったあと直に、
クエリを中断します。
LIMIT 0 は常に素早く空の結果を返します。
これはクエリのチェックや結果として得られるフィールドの型を得るために利用できます。
LIMIT # を使用します。
INSERT クエリの速度レコードを挿入する時間はおおよそ次からなります:
ここで (数字) は比例時間です。これは、テーブルのオープンにかかる初期オーバーヘッ ド(これは同時に動作する各クエリ毎に1回行なわれます)は考慮されていません。
テーブルのサイズはインデックスの挿入を N log N で遅くします (B-tree)。
INSERT を速くするいくつかの方法：
INSERT 文を使用します。 これは個々に INSERT 文
を発行する場合に比べて、とても速くなります。
INSERT DELAYED 文の使用で、より速くなるでしょう。 「7.16 INSERT構文」節参照.
MyISAM では、複数の SELECT が走っているテーブルに対して、
同時に、レコードを挿入できます。 ただしそのテーブルに対して
レコードの削除がない場合に。
LOAD DATA INFILE を
使用します。 これはたくさんの INSERT 文の発行に比べて通常 20倍 程度
速くなります
「7.18 LOAD DATA INFILE構文」節参照.
LOAD DATA INFILE の
実行を速くすることが可能です。
以下の手順で行います：
CREATE TABLE. For example using
mysql or Perl-DBI.
FLUSH TABLES の実行。 あるいは mysqladmin flush-tables
コマンドの実行。
myisamchk --keys-used=0 -rq /path/to/db/tbl_name の使用。
これは指定されたテーブルから全てのインデックスを削除します。
LOAD DATA INFILE を使用してデータを入れます.
これはいかなるインデックスも更新しないでしょうし、そのため、速くもなるでしょう。
myisampack をお持ちで、テーブルを圧縮したいなら、
myisampack を実行します. 「8.1.2.3 Compressed table characteristics」節参照.
myisamchk -r -q /path/to/db/tbl_name でインデックスを再構築.
これは disk に書き込む前にインデックスをメモリに作成します。
これは多くのディスクシークを避けることが出来るのでとても速くなります。
でき上がったインデックスツリーは、バランスのよいものになります。
FLUSH TABLES の実行。 あるいは mysqladmin flush-tables
コマンドの実行。
LOAD DATA INFILE に組み込むかもしれません。
mysql> LOCK TABLES a WRITE; mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33); mysql> INSERT INTO a VALUES (8,26),(6,29); mysql> 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 つ以上、値を 挿入・更新 をする操作に対して、
ロックを追加すれば、全般的により良い性能が得られます。
とても多い insert を行なう場合、
他のスレッドにそのロックしたテーブルへのアクセスを与えるために 、
だいたい 1000 レコードごとに一度、UNLOCK TABLES した後に
LOCK TABLES を行います。 これでもまだ良い性能が得られます。
もちろん LOAD DATA INFILE はとても速いです。
LOAD DATA INFILE と INSERT を共に速くするためには、
キーバッファを増やします。 「12.2.3 サーバーパラメーターのチューニング」節参照.
UPDATE クエリの速度
UPDATE クエリは SELECT クエリのように最適化されますが、
書き込みのオーバーヘッドが加わります。
書き込み速度は、更新されるデータの大きさ、更新されるインデックス数に
依ります。 変更されないインデックスは更新されません。
Also another way to get fast updates is to delay updates and then do many updates in a row later. 一文で多くの更新を行えば、もしテーブルがロックされていれば、 一文で一個づつ更新するよりも、とても速くなります。
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.10 OPTIMIZE TABLE構文」節参照.
DELETE クエリの速度1レコードを削除する時間は、ちょうどインデックスの数に比例します。 より速くレコードを削除したいなら、インデックス・キャッシュを 増やします。 「12.2.3 サーバーパラメーターのチューニング」節参照.
テーブル中からレコードの大きな部分を消すよりも、 全てのレコードを消すほうが、とても速いです。
Unsorted tips for faster systems:
thread_cache_size variable. 「12.2.3 サーバーパラメーターのチューニング」節参照.
EXPLAIN コマンドでこれが行えれます。
「7.24 EXPLAIN 構文 (SELECTについての情報を得る)」節参照.
SELECT クエリを
避けるように試みます。 これはテーブル・ロックの問題を避けるためです。
MyISAM テーブルでは、あるテーブルにレコードが挿入(削除は無しで)
されている最中に、同時に、同じテーブルからデータを読むことが可能です。
これがあなたにとって重要項目であるなら、
レコードの削除の必要のない方法や、多くのレコードを削除した後に OPTIMIZE TABLE を
実行することを、考慮すべきです。
SELECT * from table where hash='calculated hash on col1 and col2'
and col_1='constant' and col_2='constant' and ..
VARCHAR や BLOB
フィールドを避けるように試みるべきです。
You will get dynamic row length as soon as you
are using a single VARCHAR or BLOB columns. 「8 MySQL table types」節参照.
UPDATE table set count=count+1 where index_column=constant の更新は
とても速いです！
これは、MySQL のようなテーブル・ロッキング
(multiple readers / single writers)をもつデータベースを
使う上で、本当に重要なことです。
This will
also give better performance with most databases as the row locking
manager in this case will have less to do.
INSERT /*! DELAYED */ を使用します。
これは、一回のディスクへの書き込みで、多くのレコードがかかれるので、
速くなります。
INSERT /*! LOW_PRIORITY */ を使用します。
SELECT /*! HIGH_PRIORITY */ を使用します。
これは、たとえ誰かが書き込みを待っていたとしても、その SELECT は
実行されます。
INSERT 文の使用。 これは多くのレコードを一つの SQL コマンドで
挿入することが出来ます。
(多くの SQL サーバーがこれをサポートしています)
LOAD DATA INFILE を使用します。
This if
faster than normal inserts and will be even faster when myisamchk
is integrated in mysqld.
AUTO_INCREMENT を使用します。
OPTIMIZE TABLE を時々使用します。
「7.10 OPTIMIZE TABLE構文」節参照.
HEAP テーブルをスピードアップのために使用します. 「8 MySQL table types」節参照.
customer_name の
かわりに name を使うとか）。 他の SQL サーバーへの移植を考慮するなら、
名前は 18 文字より短くすべきです。
MyISAM を直接アクセスすることは、
SQL インターフェースを介してアクセスするよりも、2～5倍速くなるでしょう。
これを可能にするには、そのデータは、アプリケーションと同じマシン上になくてはならず、
通常、一つのプロセスだけでアクセスされるべきです。
(なぜなら、外部ファイルロックは、とても遅いから)。
上の問題は、MySQL サーバー内の MyISAM のローレベルのコマンドの
導入で解決できるでしょう。 (これはよりよいパフォーマンスを得ることの出来る、
簡単な方法の一つです)。 By carefully designing the database interface
it should be quite easy to support this types of optimisations.
DELAY_KEY_WRITE=1 が定義されたテーブルは、インデックスの更新が速くなります。
これはそのインデックス・ファイルが閉じられるまでディスクに記録されないからです。
したがって、なにかが途中で mysqld をキルしてもテーブルが大丈夫なようにするため、
mysqld を起動する前には、myisamchk をそれらのテーブルに対して実行すべきです。
As the key information can always be generated
from the data you should not lose anything by using DELAY_KEY_WRITE.
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 benchmark programs look at the MySQL benchmark suite. 「13 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!
MySQL は、レコードのデータとインデックスを別のファイルに保存します。
他の多くの(ほとんど全て)データベースでは、同じファイルにデータとインデックスを
混在させて保存します。 我々は、最近のシステムには、MySQL の選択の方が
良いと信じています。
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. Because 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:
Because 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 systems the extremely 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.
Because 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 dynamically 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.
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.