2012年 1月 22日

MySQLのslow query logのためのfluentdのinput pluginを作った

Fluent input plugin for MySQL slow query log format. — Gist

本家のドキュメントにin_tailプラグインを拡張してオリジナルのプラグインを作る方法が載っていたので、それを応用してMySQLのslow query logを送るプラグインを作った。

slow query logは個々のレコードが複数行にまたがるため、一行単位での処理しかできないin_tailは使えなかった。
余談：fluentd自体について

筋がとても良いなーと使っていて本当に思う。

単にログを集めるだけのソフトウェアなんだけど、コミュニティも活発だし、やっぱりRubyで書かれているというのが大きいのかな。
余談２：to_msgpackがないと怒られるエラーに苦しんだ

in_tailのparse_lineの返り値が time と record の配列な訳だけど、timeはTImeインスタンスじゃなく、Integerじゃないといけないという内部仕様があって、それに気が付かずにTimeインスタンスを返して to_msgpack なんていうメソッドはありません、とfluentの内部の方でエラーが発生して怒られた。

スレッドが走っていて、僕はあまりマルチスレッド環境でのデバッグ経験が無かったので、一体どこでバグが発生しているのか突き止めるのに結構苦労した。結局、to_iをつけるだけで解決したのだけど。

return する前にto_i を忘れないようにしましょう。

MySQLのslow query logのためのfluentdのinput pluginを作った - SELECT * FROM life;

4週間前 | 固定リンク | 2012年 1月 22日 |

2011年 12月 18日

遅延対策のベストプラクティス

エントリのまとめとして、レプリケーションを運用する上で実施するべきことについて列挙しておこう。

まず、巨大なトランザクション（一度に大量の行を更新するもの）は実行しないというのが鉄則である。バッチ処理的なものであれば、可能であればコミットする行数を少しずつに絞り込むようにしよう。大きなテーブルのALTER TABLEのようにいかんともし難い処理は、メンテナンスウィンドウを設けて実施しよう。

スレーブでI/O boundなボトルネックが生じている場合には、innodb_flush_log_at_trx_commit=2の設定を検討しよう。こうすると、スレーブ上ではCOMMIT時にログがディスクへ同期されなくなるが、スレーブはいざとなればバックアップから再構築すれば良いので、特にスレーブが複数あってひとつぐらいダウンしても問題がない場合には、ログの同期をOFFにすることは悪い選択肢ではない。

ワーキングセットがInnoDBバッファプールよりずっと大きい場合、松信さんが紹介していたプリフェッチのテクニックが有効であろう。ただし、そのような状況ではバッファプールを大きくするのも重要である。バッファプールが足りないと、参照の性能にも影響するからだ。

CPUリソースが枯渇している場合には、CPUを増設する（スケールアップ）か、スレーブを追加する（スケールアウト）しかないが、一般的にはスケールアウトのほうが安くつくので好まれる傾向にある。スレーブ数が増えすぎるとNICの帯域が限界に達してネットワークの遅延が生じることになるが、その場合にはマスター上でNICを増設したり、スレーブを多段構成（孫スレーブ）にするなどの工夫が求められる。

漢(オトコ)のコンピュータ道: MySQLにおけるレプリケーション遅延の傾向と対策

2ヶ月前 | 固定リンク | 2011年 12月 18日 |

2011年 12月 18日

innodb_io_capacity を増やそう

本題に入る前に、まだ紹介してないけど1記事にするほどではなかった パラメータを紹介しておきます。

innodb_io_capacity は、 InnoDB に教えるヒントで、 Disk の IO/sec を指定します。 デフォルトでは、通常のHDDでも使えるように中途半端な値(バージョンによって100か200) になっているのですが、BBU付きバッファがあるRAIDカードを使うなどで　IO/sec が 高いマシンでは大き目に設定すると、若干ですが更新クエリ/secの限界値が上がりました。

いくつかの値を試してみたのですが、RAIDカードを使っている場合は 1000～2000 くらいに設定すると性能が上がり、それ以上あげても性能が変わらなかったので、 この値を参考にしてください。

DSAS開発者の部屋:DSAS for Social での MySQL のボトルネックと今後の方針

2ヶ月前 | 固定リンク | 2011年 12月 18日 |

2011年 12月 1日

プリペアドステートメントにはパフォーマンスの利点（同じクエリを何度も発行するときにDBサーバーがクエリの解析を繰り返さないでもすむ）というものと、SQLインジェクション対策になる（正しくSQLを実行できる）という利点がありますが、特にPHPではWebアクセスの度にプリペアし直すのでパフォーマンスの利点は殆ど無くて、基本的には正しくSQLを実行するために使ってることが多いのではないでしょうか？

変数を含むSQLを正しく実行するのに、別にプリペアドステートメントを使う必要はありません。変数の値を、正しくエスケープ処理して、SQLに埋め込めば良いのです。そして、 PDO::ATTR_EMULATED_PREPARE を利用すると、PDOのプリペアドステートメントを普通に使うだけで、PDO内部で正しくエスケープされたSQLを構築してMySQLに投げてくれるので、クエリ実行時に発行するコマンドが1つですむようになり、DBサーバーのCPU,ネットワーク帯域、パケット数を全て削減できます。この設定をするには、PDOのオブジェクトに対して次の1行を実行するだけです。

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

DSAS開発者の部屋:MySQL を PDO で使うときは ATTR_EMULATE_PREPARES を設定しよう

注意しないといけない点として、この設定を使うと一部挙動が変わる可能性があります。たとえば、 “…LIMIT ?” というクエリに ->execute(array(…, 3)); とすると、 “…LIMIT ‘3’” (シングルクォートに注目) に展開されてしまってSQLが不正になってしまうので、型を指定して bindParam() を利用するなり、整数型と判っている部分だけ通常の ($limit が正の整数として “…LIMIT $limit” のような) 文字列処理をする必要があります。

2ヶ月前 | 固定リンク | 2011年 12月 1日 |

2011年 7月 3日

2011年 6月 29日 (水) 09:16:42 JST

groonga 1.2.3がリリースされました！
http://groonga.org/ja/

それぞれの環境毎のインストール方法はこちらを見てください。
http://groonga.org/ja/docs/install.html

○ ハイライト

groongaコマンドには—query-log-path(*)というオプションがあっ
て、このオプションを指定するとクエリの内容や実行時間などをロ
グ（クエリログ）に出力します。

(*) http://groonga.org/ja/docs/execfile.html#cmdoption—query-log-path

今回のリリースでは、このクエリログを解析して、遅いクエリをレ
ポートする簡単なRubyスクリプトを追加しました。まだ、ドキュメ
ントを書いていないのですが、groonga-query-log-analyzerという
コマンドです。configure時にrubyコマンドを検出できるとインス
トールするようになっています。（.debや.rpmではgroonga-tools
というパッケージを作ってそこに入れています。）

次回リリースまでにはドキュメントを書くつもりですが、一応、現
時点でも—helpをすればなんとなくは使えると思いますので、スロー
クエリの発見などに役立ててください。

○ 関連プロジェクト

Webサイトの方ですが、「関連プロジェクト」というページを作っ
てみました。
http://groonga.org/ja/related-projects.html

Rubyからgroongaを使えるrroongaや、MySQLからgroongaを使える
groongaストレージエンジンなどいくつかgroonga関連プロジェクト
がありますが、他にもたくさん増えてきたのでまとめたページがあ
ると便利だろうと思ってまとめてみました。

今のところ、以下のプロジェクトをリストアップしましたが、抜け
ているプロジェクトがある気がむんむんするので、抜けているもの
に気づいたら教えてください。（このメーリングリストでもpull
requestでもこっそりメールでもOKです。）

（詳細は上記URLを見てください）
* groonga + Ruby
* groonga + MySQL
* groonga + PostgreSQL
* groonga + Perl
* AnyEvent-Groonga
* grnwrap
* groonga + Node.js
* App-Groonga-Wrapper

[ANN] groonga 1.2.3 (groonga-dev,00543) - Groonga - fulltext search engine. - SourceForge.JP

7ヶ月前 | 固定リンク | 2011年 7月 3日 |

2011年 6月 22日

insertした場合も、updateした場合も、最後に処理したidを返してもらいたいもの。。

これを実現するには、SQLを下記のように修正します。

INSERT INTO insert_test (test_id,test_name) value(2,"foo") ON DUPLICATE KEY UPDATE test_name = 'foo', id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

INSERT 〜〜 ON DUPLICATE 時における、LAST_INSERT_ID()の挙動 - 雑想空間

8ヶ月前 | 固定リンク | 2011年 6月 22日 |

2011年 6月 22日

もしテーブルが AUTO_INCREMENT カラムを含み INSERT … UPDATE が行を挿入すると、LAST_INSERT_ID() 関数は AUTO_INCREMENT 値を返します。もしステートメントが代わりに行を更新すると、LAST_INSERT_ID() は無意味になります。しかし、LAST_INSERT_ID(expr) を利用する事でこれに対処する事ができます。id が AUTO_INCREMENT カラムだと仮定してください。LAST_INSERT_ID() が更新に意味を持つようにするには、次のように行を挿入してください。

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

ON DUPLICATE KEY UPDATE を利用する時は DELAYED オプションは無視されます。

知りませんでした。。

http://dev.mysql.com/doc/refman/5.1/ja/insert-on-duplicate.html

ON DUPLICATE KEY UPDATE と LAST_INSERT_ID - katano034の日記

8ヶ月前 | 固定リンク | 2011年 6月 22日 |

2011年 6月 21日

This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…” then attempts to produce sane recommendations for tuning server variables. It is compatible with all versions of MySQL 3.23 and above.

Currently the script handles recommendations for the following:
Slow Query Log
Max Connections
Worker Threads
Memory Usage
Key Buffer
Query Cache
Sort Buffer
Joins
Temp Tables
Table (Open & Definition) Cache
Table Scans (read_buffer)
Table Locking
Innodb Status

MySQL Tuning Primer Script in Launchpad

MySQL-5.5対応の性能チューニングツールです
MySQLサーバのパラメータを読み込み、サーバの現状と設定を読み込み、
どれをどのようにチューニングすると良いかアドバイスを出してくれるツールです。
公式に配布されている物はMySQL-5.1までの対応なので、5.5の場合にはこちらが必要です。

8ヶ月前 | 固定リンク | 2011年 6月 21日 |

2011年 6月 18日

COLUMNS PARTITIONING

大規模なデータを扱うときに便利なパーティショニングにも嬉しい改良が加えられている。従来 のRANGEパーティショニングでは評価式が必ずINT型の結果を返すようにする必要があった。具体的に言うと、日付型のカラムを用いる場合には次のよう にTO_DAYS()関数を用いるなどという対処が必要であった。

1 2 3 4 5 6 7 8 9 10 11 12 13

create table t1 (   id int unsigned not null auto_increment,   mydate date not null,   primary key(id,mydate) ) partition by range (to_days(mydate)) (   partition p1 values less than (to_days('2000-04-01')),   partition p2 values less than (to_days('2001-08-01')),   partition p3 values less than (to_days('2004-11-01')),   partition p4 values less than (to_days('2008-01-01')),   partition p5 values less than (to_days('2010-03-01')),   partition p6 values less than (to_days('2011-04-01')),   partition p7 values less than (maxvalue) );

ここでの問題点としては、当然TO_DAYS()を使うのが面倒であるということもあるが、もっと問題なのはSHOW CREATE TABLEでテーブル定義を確認したとき、TO_DAYS()による記述が消失して、TO_DAYS()の計算結果である数値だけが残ってしまうというこ とだ。

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE `t1` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `mydate` date NOT NULL,   PRIMARY KEY (`id`,`mydate`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (to_days(mydate)) (PARTITION p1 VALUES LESS THAN (730576) ENGINE = InnoDB,  PARTITION p2 VALUES LESS THAN (731063) ENGINE = InnoDB,  PARTITION p3 VALUES LESS THAN (732251) ENGINE = InnoDB,  PARTITION p4 VALUES LESS THAN (733407) ENGINE = InnoDB,  PARTITION p5 VALUES LESS THAN (734197) ENGINE = InnoDB,  PARTITION p6 VALUES LESS THAN (734593) ENGINE = InnoDB,  PARTITION p7 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

これは非常に解りづらく、管理上デメリットになるだろう。FROM_DAYS()で日付に変換しなおすことはできるが、面倒である。

MySQL 5.5で追加されたRANGE COLUMNSまたはLIST COLUMNSパーティショニングでは、この問題点が解消されている。カラムの値を直接使ってパーティショニングすることが出来るので、テーブル定義は次 のように記述することが出来るようになったのだ。

1 2 3 4 5 6 7 8 9 10 11 12 13

create table t2 (   id int unsigned not null auto_increment,   mydate date not null,   primary key(id,mydate) ) partition by range columns (mydate) (   partition p1 values less than ('2000-04-01'),   partition p2 values less than ('2001-08-01'),   partition p3 values less than ('2004-11-01'),   partition p4 values less than ('2008-01-01'),   partition p5 values less than ('2010-03-01'),   partition p6 values less than ('2011-04-01'),   partition p7 values less than (maxvalue) );

当然次のようにSHOW CREATE TABLEには日付そのものが表示される。

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE `t2` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `mydate` date NOT NULL,   PRIMARY KEY (`id`,`mydate`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50500 PARTITION BY RANGE  COLUMNS(mydate) (PARTITION p1 VALUES LESS THAN ('2000-04-01') ENGINE = InnoDB,  PARTITION p2 VALUES LESS THAN ('2001-08-01') ENGINE = InnoDB,  PARTITION p3 VALUES LESS THAN ('2004-11-01') ENGINE = InnoDB,  PARTITION p4 VALUES LESS THAN ('2008-01-01') ENGINE = InnoDB,  PARTITION p5 VALUES LESS THAN ('2010-03-01') ENGINE = InnoDB,  PARTITION p6 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,  PARTITION p7 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

解りやすい！

ところで、COLUMNSが複数形になっていることからも分かるように、COLUMNSパーティショニングでは複数のカラムを組み合わせることが出来る。例えば次のような具合に。

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create table t3 (   id int unsigned not null auto_increment,   myyear smallint not null,   mymonth tinyint unsigned not null,   primary key(id,myyear,mymonth) ) partition by range columns (myyear,mymonth) (   partition p1 values less than (2000, 4),   partition p2 values less than (2001, 8),   partition p3 values less than (2004, 11),   partition p4 values less than (2008, 2),   partition p5 values less than (2010, 3),   partition p6 values less than (2011, 4),   partition p7 values less than (maxvalue, maxvalue) );

この例でも分かる通り、COLUMNSパーティショニングでは一つ目のカラムが先に評価され、それでもパーティションが決まらなかった場合には2つ目のカ ラムが評価されることになる。日付の年、月や数値の整数部分、小数部分というように、どちらを先に評価すれば良いかが分かっている場合に使えるテクニック だろう。（年月をわざわざ分けて指定することは少ないと思うが。）

漢(オトコ)のコンピュータ道: MySQL 5.5新機能徹底解説

8ヶ月前 | 固定リンク | 2011年 6月 18日 |

2011年 6月 7日

1. マスタの処理要求を停止する。または mysqladmin を使用して完全にスレーブを停止する。

   shell> mysqladmin stop-slave

   別の方法としては、レプリケーション SQL スレッドを停止してリレー ログ ファイルの処理を停止します。この方法は、バイナリ ログのデータの転送を許可します。この方法を活発なレプリケーション環境で使用すると、スレーブ処理を再開をしたときにキャッチ アップ プロセスをスピードアップする可能性があります。

   shell> mysql -e 'STOP SLAVE SQL_THREAD;'

2. データベースをダンプするために、mysqldump を実行する。ダンプするデータベース選択するか、データベースすべてをダンプするかを決める。詳細は 項7.12. 「mysqldump — データベースバックアッププログラム」 を参照してください。データベースすべてをダンプするには、

   shell> mysqldump --all-databases >fulldb.dump

3. ダンプが完了したら、スレーブのオペレーションを再開する。

   shell> mysqladmin start-slave

MySQL :: MySQL 5.1 リファレンスマニュアル :: 5.3.1.1 mysqldump を使用したバックアップ

レプリケーションを止められるサーバがあることが前提ですが、mysqldumpを行っている間にデータが変化し、不整合データとなることを防ぐことが出来ます。

8ヶ月前 | 固定リンク | 2011年 6月 7日 |

2011年 6月 6日

データ圧縮の手順

データ圧縮はテーブル単位に指定します。CREATE TABLEまたはALTER TABLE文で以下のように定義します。

CREATE TABLE `text` (
  `old_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `old_text` mediumblob NOT NULL,
  `old_flags` tinyblob NOT NULL,
  PRIMARY KEY (`old_id`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

ROW_FORMATにCOMPRESSEDを指定することで、Barracudaフォーマットの圧縮形式になります。InnoDB Pluginにおいて、ROW_FORMATは現在以下の四種類が指定できます。

• REDUNDANT：MySQL 4.1までのフォーマットです。
• COMPACT：MySQL 5.0以降のフォーマットです。UTF-8文字列の最適化などによって、REDUNDANTよりもデータサイズが小さくなっています。
• DYNAMIC：InnoDB PluginのBarracudaフォーマットを用いますが、データ圧縮は行わない形式です。
• COMPRESSED：InnoDB PluginのBarracudaフォーマットを用い、データ圧縮を行う形式です。

次のKEY_BLOCK_SIZEは、圧縮後におけるInnoDBのページサイズを指定するものです。デフォルトは8KBで、1、2、4、8、16KBから選ぶことができます。InnoDBはもともとページサイズが16KB固定になっていてこのページ単位にディスクI/Oなどのデータ管理を行っているのですが、Barracudaフォーマットの圧縮形式ではページサイズがテーブルごとに可変になっています。

MySQL InnoDB Pluginのデータ圧縮機能 - SH2の日記

入るデータサイズが小さいならKEY_BLOCK_SIZEをデフォルトの8KBから2KBにする事で、さらに圧縮効率が高まります。

8ヶ月前 | 固定リンク | 2011年 6月 6日 |

2011年 6月 6日

INSERT INTO tbl (hat, mittens, name)  
VALUES ('yellow','purple','jimmy') 
ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name)  
                                    THEN VALUES(name) ELSE name END 
                      , last_update = CASE WHEN name <> VALUES(name)  
                                      THEN now() ELSE last_update END; 

重複した条件の鍵更新

MySQLで、INSERTしたいがもし、既にあった場合には条件付きでデータを更新したいとき、INSERT INTO 〜〜 ON DUPLICATE KEY UPDATE hoge = CASE WHEN hoge a THEN x ELSE y END;という構文を使います。
しかし、全てのカラムを更新したいなら、REPLACE文を使いましょう。無ければINSERT、既にユニーク制約によって重複データが存在すればUPDATEとなります。

8ヶ月前 | 固定リンク | 2011年 6月 6日 |

2011年 6月 3日

2.8.7. groongaストレージエンジンのインストール¶

mysqldを起動し、mysqlクライアントで接続して”INSTALL PLUGIN”コマンドでインストールします。

mysql> INSTALL PLUGIN groonga SONAME ‘ha_groonga.so’;

以下のように”SHOW ENGINES”コマンドで”groonga”が表示されればgroongaストレージエンジンのインストールは完了です。

mysql> SHOW ENGINES;
+——————+————-+——————————————————————————————+———————+———+——————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————+————-+——————————————————————————————+———————+———+——————+
| groonga | YES | Fulltext search, column base | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+——————+————-+——————————————————————————————+———————+———+——————+
6 rows in set (0.00 sec)

続いてUDF(ユーザ定義関数)をインストールします。

INSERTを行った際にgroongaにより割当てられるレコードIDを取得するためのlast_insert_grn_id関数をインストールします。

以下のようにCREATE FUNCTIONを実行します。

mysql> CREATE FUNCTION last_insert_grn_id RETURNS INTEGER soname ‘ha_groonga.so’;

2. インストールガイド — groonga storage engine v0.6 documentation

yumで入れた後の使い方です

8ヶ月前 | 固定リンク | 2011年 6月 3日 |

2011年 6月 3日

0.6 リリース - 2011/05/29¶

改良¶

• auto_increment機能の追加。#670
• 不必要な”duplicated _id on insert”というエラーメッセージを 抑制。 #910（←は未修正）
• CentOSで利用しているMySQLのバージョンを5.5.10から5.5.12へ アップデート。
• Ubuntu 11.04 Natty Narwhalサポートの追加。
• Ubuntu 10.10 Maverick Meerkatサポートの削除。
• Fedora 15サポートの追加。
• Fedora 14サポートの削除。

修正¶

• ORDER BY LIMITの高速化が機能しないケースがある問題の修正。#845
• デバッグビルド時のメモリリークを修正。
• 提供しているCentOS用パッケージをOracle提供MySQLパッケージ と一緒に使うとクラッシュする問題を修正。

最新ニュース — groonga storage engine v0.6 documentation

めでたくmysql-5.5対応およびauto_inncrement対応となりました

8ヶ月前 | 固定リンク | 2011年 6月 3日 |

2011年 5月 24日

今回は、MySQL-5.0.45のInnoDBで連番を管理するテーブルのパフォーマンス測定をしていたのですが、その際に少し変わったデッドロック問題に遭遇しましたので、そのあたりをネタとして書いてみたいと思います。

まずは、今回使用したデータベースのスキーマは下記のようなものです。

CREATE TABLE num (
    id bigint unsigned NOT NULL default '0'
) Engine=InnoDB;

AUTO_INCREMENTは使用していません。
そこに1レコードだけ登録します。

INSERT INTO num (id) values (1);

そして実際連番を取得する際には、

UPDATE num SET id = LAST_INSERT_ID(id+1);

といったクエリを発行しインクリメントしていき、最新のidはSELECTするのではなくUPDATE時のMySQL応答パケットに含まれるmysql_insertidを参照します。

上記のような内容をベンチマークテストのために同時接続を増やしながらテストしていると、350を超えたあたりで

ERROR 1213 (40001): Deadlock found when trying to get lock

というエラーが発生するという事態に遭遇しました（350という具体的な数値はハードウェアの性能などで変動すると思います）。ちなみに試験環境のOSやMySQLのバージョンは簡単ですが下記のような感じです。

• MySQL-5.0.45
• Linux-2.6.22

まずはWebで検索してみる

筆者の英語力不足という説もありますが、なかなか「コレ！」というものが見つけられませんでした。

パラメータやSQL文等を色々試してみる

• 1カラムしかないのが逆に良くないのかと思い、主キーカラムを追加しWhere句で指定　→　変化なし
• autocommitなのが良くないのかと思い、start transaction(またはbegin)とcommitを発行する　→　変化なし
• トランザクション分離レベルをserializableに変えてみる　→　変化なし
• innodb_table_locksパラメータを0にしてみる　→　変化なし

他にもいくつか試しました。また、それらを組み合わせてみたりもしましたが解決しません。なので、ソースを読んでみることにします。

mixi Engineers’ Blog » MySQLのInnoDBでのデッドロック

結論からいいますと、サーバーのハードのスペックにもよりますが、今回の検証用環境では多少の増加は出来ましたが、さらに負荷を上昇させていくとロック待ち時間が増加し、その結果innodb_lock_wait_timeoutに引っかかり始めました。じゃあinnodb_lock_wait_timeoutも増加させればいいじゃないか、と考えもしましたが、ただ実際にはそれによりパフォーマンスが向上するわけではなく、「90秒や120秒待てばデッドロック扱いもタイムアウト扱いもせずSQLが完了します」というのは、MySQL的にはエラーでなくとも実際のシステム的にはエラーも同然ですのであまり意味がないのではないかと判断しました。というわけで、現在mysqldを動作させる一般的なハードウェア上では200という数字はそれなりに適切な値なのではないかと思いました。

ちなみにMyISAMではこのような問題は起こらず、パフォーマンスも良好な結果でした。システムの要件にもよりますが、MyISAMに変更しても問題ない場合はそちらも検討されるのも良いかと思います。

デッドロックになるはずのないSQL文でデッドロックだとエラーメッセージが返却された場合にこのような例もあったなということを思い出していただけると幸いです。
————-

MySQL5.1や5.5を使えばこの問題は起きないという噂です

9ヶ月前 | 固定リンク | 2011年 5月 24日 |