2011年8月のkazeburoさんのエントリに対する解説記事です。結論から言うとkazeburoさんの案に賛成なのですが、本日はどうしてそうなったのかというところを確認していきたいと思います。本記事はMySQL Casual Advent Calendar 2011の17日目のエントリです。16日目はakira1908jpさんでした。 当時の内容を覚えていない方は、先にkazeburoさんのエントリをご一読ください。また、テストケースがGitHubに公開されていますのでカジュアルに再現試験をすることも可能です。 Covering Index と self-join と MySQL - blog.nomadscafe.jp kazeburo's gist: 1150842 - Gist 問題のSQLをチューニングするには、MySQLがインデックスに対してどのようにアクセスするかという点につ

先日CentOS 6.0がリリースされたので、色々試している方も多いと思います。MySQLについて、Scientific Linux/CentOS 6.0では派生元のRed Hat Enterprise Linux 6.0と同様、バージョン5.1.52が採用されています。このディストリビューション付属版のMySQL 5.1.52ですが、残念なことにInnoDB Pluginが無効化されてしまっています。 * Fri Jan 8 2010 Tom Lane <tgl@redhat.com> 5.1.42-4 - Sync with current Fedora build, including: - Update to MySQL 5.1.42, for various fixes described at http://dev.mysql.com/doc/refman/5.1/en/new

MySQLでテーブルへのカラム追加やテーブルの再編成を行うには、ALTER TABLE文を使用します。MySQLのALTER TABLE文は、変更後の定義にもとづく作業用テーブルを作成し、変更前のテーブルから作業用テーブルへデータをコピーして、最後に二つのテーブルを入れ替えるという仕組みになっています。テーブルへのインデックス追加についても、現在のところ大半のケースで内部的にALTER TABLE文が実行されています。 ALTER TABLE文の怖いところは、処理がもうすぐ終わるのかどうかが分からないところです。テーブルサイズが1GBを超えるあたりから分単位の時間がかかるようになり、100GBともなると本当に終わるのか？と見ていて不安になります。メンテナンス時間が限られている場合は、作業を中断すべきかどうか難しい判断を迫られることもあります。 実は、というほどではありませんが、ALTER

MySQLでテーブルへのカラム追加、インデックス追加やテーブルの再編成などを行うと、その間テーブルに共有ロックがかかってしまいます。そのためこれらのメンテナンス処理は、通常利用者の少ない深夜早朝帯にサービスを止めて実施する必要があります。本日はそれを無停止、オンラインのままでできないかという話題です。 基本的なアイデア メンテナンス対象の元テーブルをコピーして、作業用の仮テーブルを作ります 仮テーブルに対して、カラム追加などの変更を加えます その間、元テーブルに対して行われる更新処理について差分を記録しておきます 仮テーブルの変更が終わったら、記録しておいた差分データを仮テーブルに反映します 差分データの反映が終わったら、元テーブルと仮テーブルを入れ替えます これと似たようなことを考えた方は結構いらっしゃるのではないでしょうか。ただ、言うは易し、行うは難しです。整合性がきちんと取れるかどう

データベース負荷テストツールまとめの第4回です。 データベース負荷テストツールまとめ(1) TPC-B、TPC-Wベースのツールを6つ紹介 データベース負荷テストツールまとめ(2) TPC-Cベースのツールを6つ紹介 データベース負荷テストツールまとめ(3) TPC-Hベースのツールを2つ紹介 今回はTPC-Eベースのツールを見ていきたいと思います。 TPC-Eとは TPC-EはRDBMSベンチマーク仕様の一つで、オンライントランザクション(OLTP)の性能を測定するものです。証券会社の業務をモデルとして取引や市場の監視、メンテナンス処理を行い、1秒あたりに行った取引件数を性能の指標値とします。 OLTPのベンチマークとしてはこれまでTPC-Cがよく用いられてきました。しかしTPC-Cは1992年の策定から実に18年が経過しており、その間コンピュータのCPU性能はムーアの法則にしたがって伸

DTIの仮想専用サーバServersMan@VPSを借りてみました。 Entryプランはメモリが256MBでまあ足りるだろうと思っていたのですが、ServersMan@VPSではOpenVZという仮想化ソフトウェアを使っていて、なんとスワップの利用が禁止されているのだそうです。つまりなにがなんでも総メモリ使用量を256MB以下に抑える必要があります。ちょっと難しそうです。 とりあえずMySQL 5.1.45をインストールして、すべてデフォルトで起動するとこんな感じです。 # free -m total used free shared buffers cached Mem: 256 23 232 0 0 0 -/+ buffers/cache: 23 232 Swap: 0 0 0 # service mysql start Starting MySQL. SUCCESS! # free

MySQLのパフォーマンスチューニングに欠かせないスロークエリログですが、このログは特別にlogrotateなどの設定をしない限り一つのファイルに延々と追記される形になっています。先日仕事で3年分ものスロークエリログを見ることがあって大変疲れたので、次回から楽をするためにスロークエリログから一部を切り出して分析するmysqldumpslowのパッチを書いてみました。 オリジナル版のmysqldumpslowはスロークエリログ全体を読み込みます。 $ mysqldumpslow -s t sandbox03-slow.log Reading mysql slow query log from sandbox03-slow.log Count: 2 Time=9223372036854.76s (-1s) Lock=0.00s (0s) Rows=0.0 (0), tpcc[tpcc]@loc

実験エントリです。 動機 OracleのStatspack/AWRで取れるファイル単位のDisk I/O情報を、MySQLでも採取したい。これは次に示すようなデータです。 File IO Stats DB/Inst: ORA112/ORA112 Snaps: 6-7 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Wai

データベース負荷テストツールまとめの第3回です。 データベース負荷テストツールまとめ(1) TPC-B、TPC-Wベースのツールを6つ紹介 データベース負荷テストツールまとめ(2) TPC-Cベースのツールを6つ紹介 かなり期間が空いてしまいましたが、今回はTPC-Hベースのツールを見ていきたいと思います。 TPC-Hとは TPC-HはRDBMSベンチマーク仕様の一つで、意思決定支援システム(DSS)としての性能を測定するものです。大規模なデータを対象にアドホックなクエリを実行します。クエリは全部で22種類定義されています。 TPC-HはTPC-B/W/Cなどと異なり、実行するクエリそのものやテストデータ生成ツールがTPCから提供されています。試しに、一番負荷が高い9番のクエリを確認してみましょう。 -- $ID$ -- TPC-H/TPC-R Product Type Profit Me

出ました。今回は仕様変更が3件と、バグ修正が48件あります。 このバージョンから、InnoDBのパラメータについていくつかデフォルト値が変更されました。デフォルト値をMySQL 5.1、MySQL 5.1のInnoDB向けサンプルファイル(my-innodb-heavy-4G.cnf)、MySQL 5.4.0と比較してみると以下のようになります。 パラメータ 5.1 5.1(heavy) 5.4.0 5.4.3 innodb_autoextend_increment 8MB 8MB 64MB 8MB innodb_buffer_pool_size 8MB 2,048MB 1,024MB 128MB innodb_log_buffer_size 1MB 8MB 16MB 8MB innodb_log_file_size 5MB 256MB 128MB 5MB innodb_log_files

前回の例が思いのほかSQLの実行計画をまともに表現できているようなので、3つだけピックアップして詳しく図解しておきます。解説なしだと自分でもあまり読む気がしなかったというのもあります(^^; 1つ目はSQL 04、索引のない状態で、従業員番号(empno)が7,788である従業員の名前(ename)を表示するSQLです。 2つ目はSQL 06、SQL自体はSQL 04と同じですが、従業員番号(empno)に索引を付与しています。 3つ目はSQL 10、従業員全員の名前(ename)と所属する部門名(dname)を表示するSQLです。 ここで、rowsはあくまで推定値であることに注意してください。特にテーブル結合が何段にもなると、rowsはほとんどあてになりません。 MySQLは元々あまり複雑な実行計画は作れないので、あとはサブクエリを使用したときのEXPLAINが読めればほぼ十分ではないか

ときどき「Plugin 'FEDERATED' is disabled.」で検索してくる方がいらっしゃるようなので。 MySQL 5.1.26以降、FEDERATEDストレージエンジンはデフォルトで無効化されるようになりました。そのためMySQL 5.1のバイナリディストリビューションを使用している場合、mysqldの起動時に以下のようなNoteがエラーログに出力されます。これは正常な動作です。 090925 11:11:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 090925 11:11:38 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Neither

はてなブックマークの人気エントリで「SQLを使うなら理解しておきたいアルゴリズム？…」という記事を眺めていて、実際にSQLのアルゴリズムを説明するにはどうすればよいか考えてみました。そこで、試しにSQLの実行計画をJavaで再現してみることにしました。 今回は索引アクセスとテーブル結合に関する7種類のSQLについて、MySQLのEXPLAIN結果とそれに対応するJavaのコードを記述しました。EXPLAINの読み方を言葉で説明するのは非常に難しいのですが、プログラムを読める方ならこうした説明が一番分かりやすいのではないでしょうか。 import java.util.ArrayList; import java.util.List; import java.util.SortedMap; import java.util.TreeMap; public class Study { publi

MySQLのInnoDBストレージエンジンは行ロックをサポートしています。しかしOracleと同じ感覚でアプリケーションを作っていると、思わぬところでデッドロックに出くわすことがあります。これはInnoDBのロック範囲がOracleよりも微妙に広いためです。 実際の例で確認してみましょう。 mysql> select * from t; +----+------+ | c1 | c2 | +----+------+ | 10 | a | | 15 | a | | 20 | a | | 25 | a | | 30 | a | | 35 | a | | 40 | a | | 45 | a | | 50 | a | +----+------+c1列は主キーになっています。1つめのセッションで以下のSQLを実行します。 mysql> set tx_isolation = 'repeatable-r

少し前に話題になった、MySQLのRethinkDBストレージエンジンを試してみました。 RethinkDBはSSD向けに最適化されたMySQL用ストレージエンジン SSDに最適化したデータベース「RethinkDB」、ロックもログも使わずにトランザクション実現 − Blog on Publickey RethinkDBは、SSD向けに最適化されているところが特長です。 インストール時の注意事項 RethinkDBはMySQL 5.1のプラグイン形式を採用しているので、インストール自体はinstall pluginコマンドを打つだけです。ただし、プラグインがglibcのバージョン2.7以上を要求しているため、使用できるディストリビューションがかなり限られています。 例えば、Red Hat Enterprise Linux 5(CentOS 5)のglibcは2.5なので動かすことができませ

出ました。 今回は37件のバグ修正があり、そのうち7件がレプリケーション関連、さらにそのうち2件がImportant Changeになっています。Important Changeの2件について、内容を簡単にご紹介します。 RESET MASTER文とRESET SLAVE文が、SHOW SLAVE STATUSにおける以下の値をリセットするようになりました。Last_IO_Error、Last_IO_Errno、Last_SQL_Error、Last_SQL_Errno。(Bug#44270) レプリケーションのSLAVE側において、以下のエラーが無視されるようになりました。ER_LOCK_WAIT_TIMEOUT、ER_LOCK_DEADLOCK、ER_XA_RBDEADLOCK。これらのエラーはMASTER側において同時並行性の問題によって起こるものであり、SLAVE側で同じエラーを再

Webシステム開発において性能試験を行う場合、hp LoadRunnerやApache JMeterといったウェブブラウザをエミュレーションしてくれる負荷テストツールを用いるのが定番だと思います。そんななか、たまにデータベース単体での性能を測ってほしいと頼まれることがあるので、そうした便利なツールはあるのかなと思って調べてみました。 データベースに対する負荷テストツールは探すとたくさん出てくるのですが、案件で使用しているRDBMSに対応していなかったり、トランザクション仕様が希望と異なっていたり、微妙に作りが悪かったりと、ニーズに合致したツールはすぐには見つかりません。そんなときにこのエントリがツール探しの参考になればと思います。 pgbench 対応RDBMS：PostgreSQL 対応OS：Linuxなど 言語：C 作者：石井達夫氏 ライセンス：独自(BSDライセンスに近い) トランザ