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


5 MySQL Optimization

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 or application, the more optimal you want your system to become the more you will have to know about it.

This chapter will try to explain and give some examples of different ways to optimize MySQL. Remember, however, that there are always some (increasingly harder) additional ways to make the system even faster.

5.1 最適化 概要

システムを速くするための最も重要な部分は、もちろん基礎的な設計です。 You also need to know what kinds of things your system will be doing, and what your bottlenecks are.

主なボトルネックは:

5.1.1 MySQL Design Limitations/Tradeoffs

MySQLは極端に早いテーブルブロックのしくみ(複数のリードと一つのライトによる方式)を 使用するために、一つのテーブルに対して続けざまにインサートを行いながら、同時に 遅いセレクトを行った場合に生じる問題が、最大の難点です。

私たちはこの選択により数多くのシステムにおいて非常に高速なパフォーマンスを実現する ことができると信じています。またこの問題は、若干の手数とハードウェアリソースを必要 とはするものの、通常、テーブルのコピーを複数行うことにより回避できます。

また、我々は、幾つかの共通アプリケーションのために、 この問題を解決しうるための複数の拡張を行っています。

5.1.2 Portability

全てのSQLサーバはそれぞれ異なるSQL処理ルーチンにより構成されています。 したがって色々なSQLサーバ上で同じように動作するような(ポータブルな) SQLアプリケーションを書くのは手間がかかります。 確かに単純なインサートやセレクトを行うようなものは簡単ですが、 必要な機能が増えれば増えるほど難しくなっていきます。 高速動作を要求されるSQLプラットフォームの数が増えれば増えるほど そのようなアプリケーションの開発は大変になっていきます。

ポータブルな多機能アプリケーションを開発するためには 沢山のSQLサーバでの動作を検証する必要があります。

You can use the MySQL crash-me program/web-page http://www.mysql.com/information/crash-me.php to find functions, types, and limits you can use with a selection of database servers. Crash-me now tests far from everything possible, but it is still comprehensive with about 450 things tested.

For example, you shouldn't have column names longer 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 at how we have handled this, you can get a feeling for what you have to do to write your application database-independent. The benchmarks 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/information/benchmarks.html for the results from this benchmark.

As you can see in these results, all databases have 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 for each SQL server's 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 at 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 an 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, as in some Web applications, a possibility is to create an application layer that caches all results to give you even higher performance. By letting old results 'expire' after a while, you can keep the cache reasonably fresh. This is quite nice in case of extremely high load, in which case you can dynamically increase the cache and set the expire timeout higher until things get back to normal.

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

5.1.3 What Have We Used MySQL For?

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

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

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

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

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

In most cases a new report can simply be done by copying an 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 simple, 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 upgraded one of our servers to a 2 CPU 400 Mhz UltraSPARC, and we are now planning to start handling transactions on the product level, which would mean a ten-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 Version 3.23) 、我々はこれをアプリケーションのいくつかの部分に使用しはじ める事でしょう。

Our initial feelings are that Linux will perform much better on low-to-medium load and Solaris will perform better when you start to get a high load because of extreme 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 makes the machine feel very slow and unresponsive while big batches are going. Hopefully this will be better handled in future Linux Kernels.

5.1.4 The MySQL Benchmark Suite

This should contain a technical description of the MySQL benchmark suite (and crash-me), but that description is not written yet. Currently, you can get a good idea of the benchmark by looking at the code and results in the `sql-bench' directory in any MySQL source distributions.

This benchmark suite is meant to be a benchmark that will tell any user what things a given SQL implementation performs well or poorly at.

Note that this benchmark is single threaded, so it measures the minimum time for the operations. We plan to in the future add a lot of multi-threaded tests to the benchmark suite.

For example, (run on the same NT 4.0 machine):

Reading 2000000 rows by index
Seconds Seconds
mysql 367 249
mysql_odbc 464
db2_odbc 1206
informix_odbc 121126
ms-sql_odbc 1634
oracle_odbc 20800
solid_odbc 877
sybase_odbc 17614
Inserting (350768) rows
Seconds Seconds
mysql 381 206
mysql_odbc 619
db2_odbc 3460
informix_odbc 2692
ms-sql_odbc 4012
oracle_odbc 11291
solid_odbc 1801
sybase_odbc 4802

In the above test MySQL was run with a 8M index cache.

We have gather some more benchmark results at http://www.mysql.com/information/benchmarks.html.

Note that Oracle is not included because they asked to be removed. All Oracle benchmarks have to be passed by Oracle! We believe that makes Oracle benchmarks VERY biased because the above benchmarks are supposed to show what a standard installation can do for a single client.

To run the benchmark suite, you have to download a MySQL source distribution, install the perl DBI driver, the perl DBD driver for the database you want to test and then do:

cd sql-bench
perl run-all-tests --server=#

where # is one of supported servers. You can get a list of all options and supported servers by doing run-all-tests --help.

crash-me tries to determine what features a database supports and what its capabilities and limitations are by actually running queries. For example, it determines:

We can find the result from crash-me on a lot of different databases at http://www.mysql.com/information/crash-me.php.

5.1.5 Using Your Own Benchmarks

You should definitely benchmark your application and database to find out where the bottlenecks are. 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 sufficient, you should at least make a plan for each bottleneck, and decide how to solve it if someday you really need the extra performance.

For an example of portable benchmark programs, look at the MySQL benchmark suite. 「5.1.4 The MySQL Benchmark Suite」節参照. You can take any program from 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 when the system is very heavily loaded. We have had many customers who contact us when they have a (tested) system in production and have encountered load problems. In every one of 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 systems were not already in production.

To avoid problems like this, you should put some effort into benchmarking your whole application under the worst possible load! You can use Super Smack for this, and it is available at: http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz. As the name suggests, it can bring your system down to its knees if you ask it, so make sure to use it only on your development systems.

5.2 Optimizing SELECTs and Other Queries

最初に、全てのクエリに影響する一つの事柄をのべます: より複雑な権限の設定を行うと、オーバーヘッドが多くなります。

もしあなたがいかなる 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)はあなたのクエリの 問題を見つけるためのとてもよいツールです。

5.2.1 EXPLAIN 構文 (SELECTについての情報を得る)

    EXPLAIN tbl_name
or  EXPLAIN SELECT select_options

EXPLAIN tbl_name は、 DESCRIBE tbl_nameSHOW COLUMNS FROM tbl_name と同義です。

もし EXPLAIN をともなって SELECT 構文を実行した場合、 MySQL はこの SELECT がいかに動作するかを説明し、 いかにテーブルが結合されるかの情報を与えます。

EXPLAIN の情報を元に、インデックスを使用した速い SELECT を 得るためにテーブルにインデックスを加えなくてはならないという事がわかります。 テーブル結合の最適化もオプションによって見ることができます。 SELECT 構文での結合を強制的に最適化するには STRAIGHT_JOIN 節を加え ます。

単純ではない join のために、EXPLAINSELECT 文で使用されている それぞれのテーブルの情報を返します。 テーブルは読まれる順に表示されます。MySQL は one-sweep multi-join method を用いた全ての join を解決します。これは MySQL は最初のテーブルから 一レコード読み込み、二つ目のテーブルからマッチしたレコードを探し、そして三番目を 探すということです。 全てのテーブルが処理される時、選択されたフィールドを出力し、テーブルの一覧は よりマッチするレコードをもつテーブルを見つけるまで back-track されます。 次のレコードはこのテーブルから読まれ、次のテーブルから処理を続けます。

EXPLAIN の出力は以下のフィールドを含みます:

table
出力レコードが参照されるテーブル
type
join タイプ. 様々なタイプの説明は後述します
possible_keys
possible_keys 項目は、MySQL がテーブルからレコードを見つけるために どのインデックスを使用する事ができたかを示します。 注意: このフィールドはテーブルの順にまったく依存しません。これは、 possible_keys 内のいくつかのキーは、生成されたテーブル順での実行に使用でき ないことを意味します。 この項目が空なら、関連した インデックスは無いということです。この場合、あなたは WHERE 節を 調べることによって、クエリの性能を向上させることができるかもしれません。 もしそれがインデックスに適合したフィールドを参照しているならば。 仮にそうだとすると、適切なインデックスを作成し、 EXPLAIN でクエリを もう一度チェックしてみてください。 テーブルがどんなインデックスを持っているかみるには、SHOW INDEX FROM tbl_name とします。
key
key 項目は、 MySQL が使用すると実際に決めたキーを示します。 どのインデックスも選ばれなかったならば、キーは NULL です。 MySQL が間違ったインデックスを選択する場合、たいていは MySQL に他のインデックスを使用するように強制することができます。 myisamchk --analyze の使用、 「4.4.6.1 myisamchk 起動構文」節参照、または USE INDEX/IGNORE INDEX の使用によって。 「6.4.1.1 JOIN 構文」節参照.
key_len
key_len 項目は、MySQL が使用すると決めたキーの長さを示します。 もし keyNULL なら、長さは NULL です。 注意: これはMySQL がマルチパートキーのいくつのパートを実際に使用 するかを示します。
ref
ref 項目は、テーブルからレコードを select するために、どのフィールドや 定数が key と共に使用されたかを示します。
rows
rows フィールドは、MySQL がクエリを実行するために検査する 必要があると考えているレコードの数を示します。
Extra
このフィールドは、MySQL がどのようにクエリを解決するかの追加情報 を含んでいます。ここで、このフィールドに現れ得る様々なテキスト文字列の説明 をします:
Distinct
MySQL will not continue searching for more rows for the current row combination after it has found the first matching row.
Not exists
MySQL はクエリの LEFT JOIN 最適化を行なうことができて、 LEFT JOIN 判定基準に適合する1つのレコードを見つけた後、前のレコード組 み合わせについてこのテーブルにそれ以上のレコードを検査しません。 Here is an example for this:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Assume that t2.id is defined with NOT NULL. In this case MySQL will scan t1 and look up the rows in t2 through t1.id. If MySQL finds a matching row in t2, it knows that t2.id can never be NULL, and will not scan through the rest of the rows in t2 that has the same id. In other words, for each row in t1, MySQL only needs to do a single lookup in t2, independent of how many matching rows there are in t2.
range checked for each record (index map: #)
MySQL は使用すべき本当に良いインデックスを見つけられませんでし た。先行するテーブル内に各レコードの結合の代わりに、テーブルからレコード を取り出すためにどのインデックスを使用するかチェックを行ないます。これは とても速いというわけではありませんが、もちろんインデックス無しでの結合を 行なわなければならないよりは速いです。
Using filesort
MySQL は、ソート順でレコードを取り出す方法を見つけ出すため、余 計なパスの実行が必要です。ソートは、join type に一致する全てのレ コードを通して行なわれ、WHERE に適合する全てのレコードについて、 ソートキーとレコードへのポインタを格納します。それから、キーがソートされま す。最後にレコードはソートされた順に取り出されます。
Using index
フィールド情報は、実際のレコードから読み込む追加シークを行なうことはせず に、インデックスツリー内の情報だけを使用するテーブルから取り出されます。 テーブルについての使用される全てのフィールドが同じインデックスの一部であ る時にこれが行なわれます。
Using temporary
クエリを解決できるように、MySQL は、結果を保持するための一時テー ブルを作成する必要があります。これは通常 GROUP BY が行なわれたも のとは別のフィールド上で ORDER BY を行なう場合に発生します。
Where used
どのレコードが次のテーブルに対して適合するか、またはクライアントに送られ るかを制限するために、WHERE 節が使用されます。この情報がなくて、 テーブルが型 ALLindex であれば、クエリ内に何か間違った ものがあります(テーブルから全てのレコードを fetch/examine しようとしてい るのでなければ)。
クエリを可能な限り速く得たい場合、Using filesortUsing temporary に注意すべきです。

join type は以下のものがあります。良い物から順に書いています:

system
テーブルが一レコードだけ持っている (= system table). これは const join type の特別な場合です。
const
テーブルは、最もマッチするレコードを1つもっており、これはクエリの 最初に読まれます。 1 つのレコードであるため、このレコード中のフィールドの値は オプティマイザーによって常数としてみなされます。 1回だけ読まれるので、const テーブルはとても速いです!
eq_ref
前のテーブルのそれぞれのレコードと結合する際、このテーブルから1レコード読まれま す。これは join では const よりも良い形です。 インデックスの全てのパートが join で使用され、かつ、インデックスが UNIQUEPRIMARY KEY であるときに、これは使用されます。
ref
インデックスの値に合ったすべてのレコードは、前のテーブルからレコードと結合するた めに、このテーブルから読まれるでしょう。 もしその join がキーの一番左の接頭部分だけを使用するならば、 あるいは、もしそのキーが UNIQUEPRIMARY KEY でなければ (言い換えるなら、もし join がキーの値を元に一つだけの、レコードを選択できなけれ ば)、ref は使用されます。 もしそのキーがいくつかのマッチするレコードに使用されるだけなら、 join は良い形です。
range
示された範囲内にあるレコードのみが検索されます。 key 項目はどのインデックスが使用されているか示します。 The key_len contains the longest key part that was used. The ref column will be NULL for this type.
index
ALL と同じですが、インデックスツリーが走査される場合のみを除きます。 これは、インデックスファイルはデータファイルよりも小さいため、通常 ALL よ り速いです。
ALL
前のテーブルのレコードとのそれぞれの結合において、全テーブルが走査されます。 もしそのテーブルが最初のテーブルで const 状態ではないなら、通常 これは良くありません。他の状態ではとても悪くなります。 これは普通、レコードががより早いテーブルからから定数値に基づいて検索することがで きるように、 インデックスを追加することにより ALL を避けることが可能です。

EXPLAIN 出力の rows フィールド内のすべての値を増やすことによ り、join がどのように良くなるかの良い表示を得ることができます。これは、 MySQL がクエリ実行の検査をする必要があるレコードのおおよその数を 知らせます。この数は max_join_size 変数でのクエリを制限する時にも使 用します。 「5.5.2 サーバーパラメーターのチューニング」節参照.

以下の例は、EXPLAIN が提供する情報を元に、いかに JOIN が最適化 できるかの例です。

以下のような EXPLAIN で検査する SELECT 構文があるとします:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

この例では、以下のように仮定します:

最初、いかなる最適化も行われていない状態では、EXPLAIN 構文は 以下の情報を提示します:

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

それぞれのテーブルで、typeALL になっています。 これは MySQL が全てのテーブルを全結合することを示します! それぞれのテーブル内の行数分から作った物が調べられるので、とても長い時間がかかり ます! この場合、74 * 2135 * 74 * 3872 = 45,268,558,720 行調べることになります。 テーブルが肥大化したときにかかる時間を考えてください....

一つ問題があります。(まだ) MySQL がフィールドのインデックスを効果的に 使用できていません。 この例の場合では、VARCHARCHAR は、それらが同じ長さで定義されて いれば、変わりがありません。 tt.ActualPCCHAR(10) と定義されており、 et.EMPLOYIDCHAR(15) です。これらの長さは違います。

この不釣り合いを修正するにあたり、ALTER TABLE を使って ActualPC の長さを 10 文字から 15 文字にします:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

これで tt.ActualPC and et.EMPLOYID は両方とも VARCHAR(15) になりました。 EXPLAIN 構文を実行し直すと、以下を提示します:

table type   possible_keys   key     key_len ref         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    where used
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

まだ完全ではありませんが、よりよくなっています(rows 値の 生成量は 74 より小さくなります)。この場合、実行は数秒でしょう。

A second alteration can be made to eliminate the column length mismatches for the tt.AssignedPC = et_1.EMPLOYID and tt.ClientID = do.CUSTNMBR comparisons:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

これで EXPLAIN は以下を出力します:

table type   possible_keys   key     key_len ref            rows     Extra
et    ALL    PRIMARY         NULL    NULL    NULL           74
tt    ref    AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

これは ``ほとんど'' 最良に近いです。

残る問題は、デフォルトでは、MySQLtt.ActualPC フィールド内の 値がまんべんなく分布していると想定しており、この tt テーブルの場合には 適合しません。 幸運にも、これを MySQL に教えるのはとても簡単です:

shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

これで join は ``完璧'' です。 EXPLAIN は以下の結果を示します:

table type   possible_keys   key     key_len ref            rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL    3872    where used
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC    1
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

EXPLAIN の出力中の rows 項目は、 MySQL JOIN オプティマイ ザーによる、``推測'' です; クエリの最適化のために、この数値が実際に近いかどうかをチェックすべきです。 そうでなければ、SELECT ステートメントで STRAIGHT_JOIN を使用 し、FROM 節に異なる順でテーブルを並べることで、良いパフォーマンスを 得られます。

5.2.2 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.

書き込み時には、上の場合、新しいインデックスを書き込める場所を 探し出すのに、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. 「5.5.2 サーバーパラメーターのチューニング」節参照.

5.2.3 SELECT クエリの速度

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

Some general tips:

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

WHERE の最適化は、WHERE がほとんど SELECT で使用されるため、 SELECT に置かれています。 しかし、同じ最適化は DELETEUPDATE 文でも使用されます。

このセクションの最適化の説明はまだ不十分です。 なぜなら、 MySQL はとても多くの最適化を行っており、 それら全てについての説明を書ける時間が、我々はとれません。

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,... ;

5.2.5 How MySQL Optimizes DISTINCT

DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.

When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.

If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.

5.2.6 MySQL はどのように LEFT JOINRIGHT JOIN を最適化するか?

MySQLA LEFT JOIN B は以下のように組みこまれています:

RIGHT JOIN is implemented analogously as LEFT JOIN.

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 more 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

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

5.2.7 MySQL はどのように LIMIT を最適化するか?

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

5.2.8 INSERT クエリの速度

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

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

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

INSERT を速くするいくつかの方法:

LOAD DATA INFILEINSERT を共に速くするためには、 キーバッファを増やします。 「5.5.2 サーバーパラメーターのチューニング」節参照.

5.2.9 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.

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

5.2.10 DELETE クエリの速度

If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. 「6.4.6 TRUNCATE 構文」節参照.

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

5.2.11 最適化に関するその他の助言

Unsorted tips for faster systems:

5.3 Locking Issues

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

付録に、異なるロックする方法に関する議論があります。 「G.4 Locking methods」節参照.

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> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

特定の場合での検索を優先的に行いたい場合、 LOW_PRIORITY オプションと共に INSERTUPDATEDELETE 構文、または HIGH_PRIORITY オプションと共に SELECT 構文を使用できます。 また、同様の動作を得るために、 --low-priority-updates オプション と共に mysqld を開始することができます。

SQL_BUFFER_RESULT の使用はさらにテーブル・ロックをより短くするために有効かもしれません。 「6.4.1 SELECT 構文」節参照.

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

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

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

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

BDBInnoDB のテーブルでは、 LOCK TABLES を使用して 明示的にテーブルをロックするか、 ALTER TABLE のようなテーブル中のすべて のレコードを修正するコマンドを実行した場合にのみ、MySQLはテーブル・ロッキングを使用 します。これらのテーブル・タイプについては、 LOCK TABLES を全く使用 しないことを推奨します。

MySQL バージョン 3.23.7 以上では、 あるスレッドが MyISAM テーブルを読んでいる最中に、 同じテーブルにレコードを挿入する事ができます。 現在、これは挿入が行われる時に、テーブルのレコードを削除した後にホールがない場合にのみ 動作することに注意してください。すべてのホールが新しいデータで満たされた時に、 自動的に同時挿入が再び可能になります。

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

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

MySQL バージョン 3.23.7 から、max_write_lock_count 変数が使用で きます。 これは、一つのテーブルに対して、指定された回数だけ INSERT が行われた後に、 SELECT を発行するようにします。

テーブルロッキングは以下の場合にはあまり良くありません:

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

5.4 Optimizing Database Structure

5.4.1 Design Choices

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 cause a performance hit for every query that accesses more than one column. Because this degenerates so quickly when more than one column is accessed, we believe that this model is not good for general purpose databases.

The more common case is that the index and data are stored together (like in Oracle/Sybase et al). 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, depending on how well the index is cached, saves a disk read. The bad things with this layout are:

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

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 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. 「7 MySQL テーブル型」節参照.

以下に挙げるテクニックを使用すれば、テーブルでのパフォーマンスの向上、 保存領域の縮小化が可能でしょう:

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

Indexes are used to find rows with a specific value of one column fast. インデックスがない場合、MySQL は、そのレコードが見つかるまで、 最初のレコードからテーブルをなめていきます。 大きなテーブルでは多くの 手間がかかります。 もし問い合わせの中にインデックスを持ったフィールドが ある場合、MySQL は全てのデータをみることなく、データの途中の位置を速く 得ることができます。 もしあるテーブルが1000レコード持っていたとすると、 順番に頭からなめていくことに比べて、これは少なくとも100倍速いことに なります。 Note that if 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 に格納されます。 文字列は自動的に始めと終りの空白が圧縮されます。 「6.5.7 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 an 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 */

Note that in some cases MySQL will not use an index, even if one would be available. Some of the cases where this happens are:

5.4.4 Column Indexes

MySQL の全てのフィールドはインデックスを持つことができます。 適切なフィールドでのインデックスの使用は、SELECT の 性能を向上する最良の方法です。

キーの最大数と最大インデックス長はテーブルハンドラ毎に定義されます。 「7 MySQL テーブル型」節参照。すべてのテーブルハンドラで少なくとも 16 個のキーと少な くとも合計 256 バイトのインデックス長を持つことができます。

CHARVARCHAR フィールドには接頭部にインデックスを持つことが できます。フィールド全体をインデックス化するより、 これははるかに早く、少ないディスク容量でできます。

CREATE TABLE 構文でフィールドにインデックスを作るには、 以下のようにします:

KEY index_name (col_name(length))

以下の例は name フィールドの最初の10文字にインデックスを創り出します:

mysql> CREATE TABLE test (
           name CHAR(200) NOT NULL,
           KEY index_name (name(10)));

BLOBTEXT フィールドでは、そのフィールドの頭の部分に インデックスを張らなくてはなりません。 フィールドの全体にインデックスは張れません。

MySQL バージョン 3.23.23 以降では、特殊な FULLTEXT イン デックスを生成することもできます。これは全文検索に使用されます。 MyISAM テーブル型だけが FULLTEXT インデックスをサポートしま す。これは VARCHARTEXT フィールドだけに生成できます。イ ンデックスは常にフィールド全体に対して行なわれ、部分インデックスはサポート されません。詳細は 「6.8 MySQL Full-text Search」節.

5.4.5 Multiple-Column Indexes

MySQL は異なるフィールドのセットに一つのインデックスを持つことができます。 インデックスは最大15個のコラムまで許されます。 (CHARVARCHAR フィールドの接頭部をインデックスとして使用できます)

複数フィールドインデックスは、 ソートされた配列(インデックス化されたフィールドの値が結合されている配列) を扱うことができます。

インデックス化されたコラムに対して、既知の値を WHERE 節で指定した時、 たとえ他のフィールドの値を指定しなくとも、 MySQL は複数フィールドインデックスを使用します。

以下のテーブルがあると仮定してください:

mysql> CREATE TABLE test (
           id INT NOT NULL,
           last_name CHAR(30) NOT NULL,
           first_name CHAR(30) NOT NULL,
           PRIMARY KEY (id),
           INDEX name (last_name,first_name));

name インデックスは、last_namefirst_name にまたがるイン デックスです。 このインデックスは、last_name に対するクエリや、 name インデックスは以下のクエリで使われます:

mysql> SELECT * FROM test WHERE last_name="Widenius";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND (first_name="Michael" OR first_name="Monty");

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND first_name >="M" AND first_name < "N";

しかし name インデックスは以下のクエリでは使用されません:

mysql> SELECT * FROM test WHERE first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          OR first_name="Michael";

MySQL がクエリの性能を上げるためにどうインデックスを使用しているか、 より詳しい情報はこちら: 「5.4.3 MySQL はどのようにインデックスを使用するか?」節.

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

table_cache , max_connections , max_tmp_tables は サーバーが開いた状態にしておく事が出来るファイルの最大数に影響します。 もしこれらの変数のうちの一つ、あるいは複数を増加させるなら、 あなたのオペレーティング・システムの、1 プロセスあたりに開かれる ファイル・デスクリプタ の最大値を増やすことになるかもしれません。 多くのシステムではこの制限を増やすことが可能です。 これをどうやって行うかは、あなたの使用している OS のドキュメントを見てください。 制限値の変更方法は、システムによってまちまちだからです。

table_cachemax_connections に関連します。 例えば 200 のコネクションを同時に開けるなら、少なくとも 200 * n の テーブル・キャッシュが必要です。 ここで n は join におけるテーブルの 最大数です。 You also need to reserve some extra file descriptors for temporary tables and files.

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

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

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

You can check if your table cache is too small by checking the mysqld variable opened_tables. If this is quite big, even if you haven't done a lot of FLUSH TABLES, you should increase your table cache. 「4.5.5.3 SHOW STATUS」節参照.

5.4.7 Drawbacks to Creating Large Numbers of Tables in the Same Database

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

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

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

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

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

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

5.5 Optimizing the MySQL Server

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

We start with the system level things since 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. However, it is always nice to have a feeling about how much one could gain by changing things at this level.

使うべき OS はとても重要です! 複数の CPU を持つマシンでは、 Solaris (なぜなら、Solaris のスレッドはとても素晴らしく動作するから)、 あるいは、Linux ( kernel 2.2 は良い SMP をサポートしているから) を 使用すべきです。 32bit マシンの Linux では、2G bytes のファイルサイズの 制限があります。 これは新しいファイルシステム (XFS/ReiserFS) のリリース時に なくなって欲しいものです。 If you have a desperate need for files bigger than 2G on Linux-intel 32 bit, you should get the LFS patch for the ext2 file system.

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

Other tips:

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

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

shell> mysqld --help

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

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
bdb_cache_size        current value: 1048540
binlog_cache_size     current_value: 32768
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_binlog_cache_size current_value: 4294967295
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
myisam_sort_buffer_size  current value: 8388608
net_buffer_length     current value: 16384
net_retry_count       current value: 10
net_read_timeout      current value: 30
net_write_timeout     current value: 60
query_buffer_size     current value: 0
record_buffer         current value: 131072
record_rnd_buffer     current value: 131072
slow_launch_time      current value: 2
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

You can find a full description for all variables in the SHOW VARIABLES section in this manual. 「4.5.5.4 SHOW VARIABLES」節参照.

You can also see some statistics from a running server by issuing the command SHOW STATUS. 「4.5.5.3 SHOW STATUS」節参照.

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 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 &

If you are doing a GROUP BY or ORDER BY on files that are much bigger than your available memory you should increase the value of record_rnd_buffer to speed up the reading of rows after the sorting is done.

When you have installed MySQL, the `support-files' directory will contain some different my.cnf example files, `my-huge.cnf', `my-large.cnf', `my-medium.cnf', and `my-small.cnf', you can use as a base to optimize your system.

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

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

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

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

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

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

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

-static でのリンク時に最速の実行形式を得ます。

Linux では、pgcc-O3 でコンパイルした時に最速のコードを得られます。 このオプションで `sql_yacc.cc' をコンパイルすると、gcc/pgcc は 全ての関数をインラインにするために多くのメモリを必要とするので、200M のメモリが 必要です。 libstdc++ ライブラリの増加を避けるためには、 MySQL の configure 時に CXX=gcc も設定すべきです。 Note that with some versions of pgcc, the resulting code will only run on true Pentium processors, even if you use the compiler option that you want the resulting code to be working on all x586 type processors (like AMD).

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

我々は Cygnus CodeFusion と Fujitsu コンパイラーでもテストしましたが、 MySQL を最適化オプションでコンパイルするには、 どちらもまだ完全なバグ・フリーではありませんでした。

When you compile MySQL you should only include support for the character sets that you are going to use. (Option --with-charset=xxx). The standard MySQL binary distributions are compiled with support for all character sets.

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

The MySQL-Linux distribution provided by MySQL AB used to be compiled with 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 standard MySQL Linux binary is linked statically to get it faster and more portable.

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

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

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

5.5.5 How MySQL uses DNS

When a new thread connects to mysqld, mysqld will span a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.

If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.

You can disable DNS host lookup by starting mysqld with --skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.

If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.

You can disable the hostname cache with --skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.

If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with --skip-networking.

5.5.6 SET 構文

SET [OPTION] SQL_VALUE_OPTION= value, ...

SET OPTION はサーバやクライアントの操作に影響する様々なオプションを設定します. 現在のセッションが終わるか,またはあなたが異なった値にオプションを設定するまで, 設定されたオプション値は残っています.

CHARACTER SET character_set_name | DEFAULT
これは指定されたマッピングに従って、すべての文字列をクライアントからクライアント にマップします. 現在、character_set_name に指定できるオプションは cp1251_koi8 だけですが, MySQL のソース中にある `sql/convert.cc' ファイルを編集することによって, 容易に新しいマッピングを加えることができます. 標準のマッピングに戻すには、 character_set_nameDEFAULT を指定します。 CHARACTER SET オプションを設定するための構文は、 他のオプションを設定する構文と異なっていることに注意してください.
PASSWORD = PASSWORD('some password')
現在のユーザのパスワードを設定します。いかなる非匿名のユーザも、 自分自身パスワードを変えることができます!
PASSWORD FOR user = PASSWORD('some password')
現在ログインしているホストの特定ユーザのパスワードを設定します。 mysql データベースにアクセスができるユーザーだけが実行できます。 ユーザは user@hostname 形式で与えなくてはなりません。 ここで userhostname は、mysql.user テーブルの User, Host フィールドに登録されていなくてはなりません。 例えば、UserHost フィールドが 'bob''%.loc.gov' ならば、以下のようにします:
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");

or

mysql> UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' and host="%.loc.gov";
SQL_AUTO_IS_NULL = 0 | 1
1 (デフォルト) を設定すると、次のようにして、auto_increment レコー ドを持つテーブルで、最後に挿入されたレコードを見つけることができます: WHERE auto_increment_column IS NULL。これは、Access のようないくつ かの ODBC プログラムによって使用されます。
AUTOCOMMIT= 0 | 1
1 を設定すると、テーブルへのすべての変更はすぐに行なわれます。 複数コマンドトランザクションを開始するためには、BEGIN ステートメ ントを使用する必要があります。 「6.7.1 BEGIN/COMMIT/ROLLBACK 構文」節参照。 0 を設定すると、そのトランザクションを許可/破棄するために、 COMMIT / ROLLBACK を使用する必要があります。 「6.7.1 BEGIN/COMMIT/ROLLBACK 構文」節参照。 注意: 非 AUTOCOMMIT モードから AUTOCOMMIT モードに変更する時、 MySQL はすべてのオープントランザクションを自動的に COMMIT します。
SQL_BIG_TABLES = 0 | 1
0 の場合、全ての一時テーブルはメモリーではなくディスクに書き出されます。 これは少し遅くなりますが、多くの一時テーブルを必要とする大きな SELECT を 実行しても、The table tbl_name is full エラーが出なくなります。 新しい接続におけるこの値のデフォルト値は 1 (メモリーに一時テーブルを作る) です。
SQL_BIG_SELECTS = 0 | 1
1 の場合、とても時間のかかる SELECT が実行された場合、 MySQL はそれを中止します。 これはあまり芳しくない(間違った) WHERE 構文が発行されたときに役立ちます。 max_join_size 行以上を検討するような SELECT が 大きなクエリと定義されます。 新しい接続におけるこの値のデフォルト値は 0 です。 (全ての SELECT 構文を許します)
SQL_BUFFER_RESULT = 0 | 1
SQL_BUFFER_RESULT は一時ファイルに置かれる SELECT からの結果 を強制的に置きます。これは MySQL がテーブルロックを早く解放する手 助けをし、クライアントへ結果セットを送信するために長い時間が掛かる場合に役 立ちます。
SQL_LOW_PRIORITY_UPDATES = 0 | 1
1 の場合、全ての INSERT, UPDATE, DELETE, LOCK TABLE WRITE構文は、 対象となるテーブルを処理中の SELECTLOCK TABLE READ がなくなるまで待ちます。
SQL_MAX_JOIN_SIZE = value | DEFAULT
おそらく value よりも多いレコードの組合せを試験する必要があるような SELECT を許可しません。この値を設定することで、キーが使用されないで あろう SELECT と長い時間が掛かるであろう SELECT を捕捉できま す。これを DEFAULT 以外の値に設定すると、SQL_BIG_SELECTS フ ラグをリセットします。SQL_BIG_SELECTS フラグを再び設定すると、 SQL_MAX_JOIN_SIZE 変数は無視されます。-O max_join_size=#mysqld を起動することで、この変数のデフォルト値を設定できます。
SQL_SAFE_UPDATES = 0 | 1
1 に設定すると、WHERE 節内でキーや LIMIT を使用しない で UPDATE または DELETE を行なおうとした場合に MySQL がアボートします。これは、手で SQL コマンドを生成する時の間 違った更新の捕捉を可能にします。
SQL_SELECT_LIMIT = value | DEFAULT
SELECT 構文から返されるレコードの最大値。 もし SELECTLIMIT 節を使用している場合、LIMITSQL_SELECT_LIMIT の値より優先されます。 新しい接続におけるこの値の標準値は ``unlimited''. もしリミットを変えているならば、SQL_SELECT_LIMITDEFAULT を指定することにより、標準値に戻すことができます。
SQL_LOG_OFF = 0 | 1
この値が 1 の場合、もしクライアントが process 権限を持っているならば、 このクライアントのログが行われません。 これは更新ログに影響しません!
SQL_LOG_UPDATE = 0 | 1
0 の場合、もしクライアントが process 権限を持っているならば、 このクライアントの更新ログの記録は行われません。 これは通常のログには影響しません!
SQL_QUOTE_SHOW_CREATE = 0 | 1
If set to 1, SHOW CREATE TABLE will quote table and column names. This is on by default, for replication of tables with fancy column names to work. 「4.5.5.8 SHOW CREATE TABLE」節.
TIMESTAMP = timestamp_value | DEFAULT
クライアントに時間を設定します。 もしレコードのリストアに更新ログを使用する場合、オリジナルのタイムスタンプを得る ために使用します。 timestamp_value should be a UNIX Epoch timestamp, not a MySQL timestamp.
LAST_INSERT_ID = #
LAST_INSERT_ID() からの返り値を設定します。 テーブルを更新するコマンド中に LAST_INSERT_ID() を使用した場合、 これは更新ログに保存されます。
INSERT_ID = #
AUTO_INCREMENT 値を挿入する時、 INSERT , ALTER TABLE コマンドに従って使用される値をセットします。 これは更新ログによって使用されます。

5.6 Disk の問題

5.6.1 シンボリックリンクの使用

テーブルとデータベースのファイルを MySQL のデータベースディレクトリーから 違う場所に移動し、 それに対してシンボリックリンクを張ることができます。例 えば、もっと空き容量のあるファイルシステムへデータベースを移すため、あるい はテーブルを異なるディスクに広げることでシステムのスピードを増加するために これを行ないたくなるでしょう。

The recommended may to do this, is to just symlink databases to different disk and only symlink tables as a last resort.

5.6.1.1 Using Symbolic Links for Databases

The way to symlink a database is to first create a directory on some disk where you have free space and then create a symlink to it from the MySQL database directory.

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test mysqld-datadir

MySQL は一つのディレクトリを複数のデータベースにリンクすることは サポートしていません。シンボリックリンクを持つデータベースディレクトリの複 製は、データベース間のシンボリックリンクを作成しなければ、正常に動作します。 MySQL データディレクトリに db1 データベースがあるとして、 db1 を指すシンボリックリンク db2 を作成します。

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 (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

to

if (1)

On Windows you can use internal symbolic links to directories by compiling MySQL with -DUSE_SYMDIR. This allows you to put different databases on different disks. 「2.6.2.5 Windows 上で、異なるディスクにデータを分割する」節参照.

5.6.1.2 Using Symbolic Links for Tables

Before MySQL 4.0 you should not symlink tables, if you are not very carefully with them. The problem is that if you run ALTER TABLE, REPAIR TABLE or OPTIMIZE TABLE on a symlinked table, the symlinks will be removed and replaced by the original files. This happens because the above command works by creating a temporary file in the database directory and when the command is complete, replace the original file with the temporary file.

You should not symlink tables on system that doesn't have a fully working realpath() call. (At least Linux and Solaris support realpath())

In MySQL 4.0 symlinks is only fully supported for MyISAM tables. For other table types you will probably get strange problems when doing any of the above mentioned commands.

The handling of symbolic links in MySQL 4.0 works the following way (this is mostly relevant only for MyISAM tables).

Things that are not yet supported:


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