Home > MySQLチューニング > DBT-3によるMySQL5.0.26の測定結果に関する考察　で気になったこと

DBT-3によるMySQL5.0.26の測定結果に関する考察
というIPA　OSSセンターのMySQLのパフォーマンス測定記事を読んでいて気になったことです。

Load前にINDEXを作成すべき

MySQLではインデックスを作成してからデータを投入した方がデータ投入処理が高速になる。
この違いは ALTER TABLE の処理方法に起因すると推定される。
＝＝＝＞
Oracleの常識的な運用とは逆ですね。

IN句とサブクエリを利用したクエリ

MySQL5.0.26ではサブクエリの最適化に一部問題があり、特にIN句とサブクエリを組み合わせた場合のパフォーマンスに問題が発生する可能性が高いこと・・・・。

修正前

PLAIN TEXT
CODE:
  1. SELECT …
  2. FROM customer, orders, lineitem
  3. WHERE o_orderkey in
  4. (SELECT l_orderkey FROM lineitem
  5. GROUP BY l_orderkey
  6. HAVING sum(l_quantity)> :1)
  7. AND …
  8. GROUP BY …
  9. ORDER BY …;

修正後

PLAIN TEXT
CODE:
  1. CREATE TEMPORARY TABLE
  2. tmp_l_orderkey (t_orderkey integer);
  3. INSERT INTO tmp_l_orderkey
  4. SELECT l_orderkey FROM lineitem
  5. GROUP BY l_orderkey
  6. HAVING sum(l_orderkey)> :1;
  7.  
  8. SELECT …
  9. FROM customer, orders, lineitem, tmp_l_orderkey
  10. WHERE o_orderkey = t_orderkey
  11. AND …
  12. GROUP BY …
  13. ORDER BY …;

IN 句のかわりに EXISTS 句を利用する。
テンポラリテーブルを利用し、IN句とサブクエリの組み合わせ利用をやめる
＝＝＝＞
解消されているの確認しないと。

二重のサブクエリを含むクエリのEXPLAIN実施時間

二重のサブクエリを持つSQLのEXPLAINが1.5時間もかかった。
MySQLにおいては二重のサブクエリを含むような複雑なクエリのEXPLAIN処理に問題が発生する

クエリ実施時間のばらつきの抑制

インデックス利用ヒントを指定することによりクエリ実施時間が安定すること
INDEX読みはすべてヒントを付けたほうが良い

ハードウェア性能の違いによる影響の考察

同一CPUで
メモリー（MySQLバッファサイズ3GB）少なめでSATA 80GB
と
メモリー（MySQLバッファサイズ14GB）少なめでFiber HP MSA-1000 RAID-10 72GB
とでは、性能に3～26倍くらいの差が出る
要因はiowaitが大幅に減少するため

x86環境とx86-64環境の違いによる影響の考察

RedHat Enterprise Linux AS 4の32bitと64bitで調べると
64bitの方が10～20%程度、クエリー性能が向上する。

MySQLのバッファサイズの違いによる影響の考察

x86-64環境ではMySQLのバッファサイズ(innodb_buffer_pool_size)を2GB以上に設定することができる
元テーブルが24GBの時、バッファサイズを2GBに設定した場合とより大きく14GBに設定した場合は10～100%程度、性能が向上する。

EXPLAIN実施時間に関する考察

サブクエリを使用しているものの、EXPLAINの時間が突出して長い。