Hatena::ブログ(Diary)

SH2の日記 RSSフィード

2018-08-22 JdbcRunner 1.3リリース(データベース負荷テストツール) このエントリーを含むブックマーク

f:id:sh2:20180822193824p:image

かなり昔に紹介したJdbcRunnerを、7年ぶりに更新しました。JdbcRunnerは各種データベースを対象とした負荷テストツールで、スクリプトトランザクションを定義して多重実行し、スループットとレスポンスタイムを測定することができます。レスポンスタイムはきれいにグラフ化もできます。

Oracle Database 18c、MySQL 8.0、PostgreSQL 10といった現行バージョンへの追従がメインです。Oracle Database 18cとPostgreSQL 10は昔のままでも動いたのですが、MySQL 8.0に接続するためにConnector/J 8.0が推奨されていて、Connector/J 8.0を動かすためにはJava SE 8が必須で…、という状況でしたので諸々更新しておきました。また、いくつか細かな不具合も修正しています。

大きく手を入れる機会があればJavaScriptエンジンをMozilla Rhinoからより高速なNashornに入れ替えたかったのですが、意外と非互換があるのと、残念ながらNashornがDuplicatedになってしまうとのことで、見送ることにしました。

このツールを最初に作っていたときはWindows XPEclipse 3.4を使って、ソースコード自宅サーバSubversionで管理していました。それからWindowsは4バージョン、Eclipseは9バージョン上がり、ソースコードGitHubに移行し、動作確認用のRDBMSDockerで動かすようになり、あと最近4Kのモニタを導入しまして(^^;、だいぶ快適になりました。私は普段あまりプログラミングをしていないので何かしようとするたびに開発環境から作り直しているような状態ですが、今後もバージョンアップ追従は続けていこうと思います。

2017-01-30 dstat2graphs(dstatグラフ化ツール)の更新 このエントリーを含むブックマーク

5年前に作ったdstat2graphsを更新しました。

f:id:sh2:20170129235229p:image

  • RHEL 7系に対応し、RHEL 5系の対応を終了しました。
  • dstatのオプション -r(Disk IOPS) と -l(Load Average) に対応しました。
  • 任意の取得間隔秒数に対応しました。
  • X軸に経過時間を表示するか実際の時刻を表示するかを選べるようにしました。

OSのリソース情報を収集する際、本番環境であればZabbix、Elastic Stackなどの監視ツールを使うところですが、試験環境でしたら手軽にdstatで済ませるのも一つの案かと思います。dstatはディストリビューションに付属しているので、導入の壁が低いのもうれしいですね。

dstat2graphsもなかなか自由にソフトウェアの導入ができない開発現場を想定して、ディストリビューション付属のパッケージのみで構築できるようにしています。ぜひ、試してみてください。

(おまけ)iostat2graphsとrstat

dstatはとても便利なのですが、DBエンジニアとしてはディスクI/Oをもう少し詳しく調査したいところです。そこでiostatを使うわけですが、せっかくなのでiostatもグラフ化できるようにしておきました。

iostatはdstatほどmachine-readableなログを出力してくれないので、あらかじめフィルタを挟む設計にしました。リソース情報を収集する際はiostatを直接実行するのではなく、以下のrstatというツールを使用してください。

rstatはdstat、iostatとついでにpidstatを複数のリモートホストで実行し、ログをCSV形式で出力するツールです。出力されたdstatのログはdstat2graphs、iostatのログはiostat2graphsでグラフ化することができます。pidstatに対しては特にツールは用意しておらず、Excelでオートフィルタをかけて見ることを想定しています。

2014-09-14 MySQLのロックについて このエントリーを含むブックマーク

JPOUG> SET EVENTS 20140907 | Japan Oracle User Group (JPOUG)に参加して発表をしてきました。IIJさまのセミナルームは窓からの眺めがすばらしいですね。JPOUGの運営メンバのみなさま、会場を提供してくださったIIJのみなさま、当日お越しいただいたみなさま、どうもありがとうございました。

私のセッションでは「MySQLのロックについて」と題してネクストキーロックなどの説明をしました。プレゼンテーション資料と、調査のために作成したツールを公開します。

プレゼンテーション資料からリンクしているウェブサイトの一覧です。

過去記事の訂正

@kamipoさんから言及がありましたが、私は2009年の記事でネクストキーロックという用語を誤って使用していました。

ところで、ネクストキーロックというとsh2さんのMySQL InnoDBのネクストキーロック おさらい - SH2の日記の記事が有名ですよね。この、ひとつ先のインデックスレコードまでロックするのもネクストキーロックと呼ぶし、レコードロックとその直前のギャップロックの組み合わせもネクストキーロックと書いてるし、議論するときにはどちらの意味で使ってるのか文脈読み取れる社会性が必要そうです(今回はレコードロックとその直前のギャップロックの組み合わせの意味で使います)。

  • 誤:一つ先のレコードまでロックを取得すること。
  • 正:レコードとその手前のギャップに対するロックのこと。

ご指摘ありがとうございました。

訂正前

1つめのセッションではc1 < 30の行だけロックをすればいいのですが、実際にはc1 = 30の行もロックされてしまっています。これはInnoDBアーキテクチャからもたらされている制限事項で、このロックのことをネクストキーロックといいます。ある範囲をロックする際に、一つ先の行までロックをかけることで「範囲」というものを表現する仕組みです。

訂正後

1つめのセッションではc1 < 30の行だけロックをすればいいのですが、実際にはc1 = 30の行もロックされてしまっています。これはInnoDBアーキテクチャからもたらされている制限事項です。InnoDBインデックス上で走査した行に対してロックをかけるアーキテクチャとなっており、このケースではc1 = 30の行まで走査しています。また、走査したそれぞれの行に対してネクストキーロックと呼ばれる特殊なロックをかけています。ネクストキーロックとは行とその手前のギャップに対するロックのことで、現時点で存在しない行に対してロックをかける現実的な仕組みです。InnoDBはこのような仕組みで「範囲」というものを表現し、ファントムリードを防いでいるのです。

MySQL Casual Talks vol.6の復習

今回の資料は、MySQL Casual Talks vol.6での@karupaneruraさんの発表にインスパイアされて作成しました。

じっくり復習すると、P20はセカンダリインデックスでcol1 = 8の手前に対するギャップロックが取得されていること、P21以降で(gap)は最初と最後だけではなく途中にも存在していること、などが分かるかと思います。

Lock Inspector

今回の資料を作るにあたって、簡単なツールを作成しました。以下のようなスクリプトを準備すると、

1:RC
2:RC
1:Q:SELECT * FROM emp WHERE empno = 7788 FOR UPDATE
2:Q:SELECT * FROM emp WHERE empno = 7788 FOR UPDATE
S:10
1:C
2:C

複数のワーカがコマンドを逐次発行してくれます。

Lock Inspector
1:READ_COMMITTED
2:READ_COMMITTED
1:QUERY:SELECT * FROM emp WHERE empno = 7788 FOR UPDATE
(empno      ename      job        mgr        hiredate   sal        comm       deptno     )
(7788       scott      analyst    7566       1987-04-19 3000.00    null       20         )
(1:QUERY)
2:QUERY:SELECT * FROM emp WHERE empno = 7788 FOR UPDATE
SLEEP:10
(2:QUERY)
(2:com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request)
2:ABORT
(SLEEP)
1:COMMIT
1:EXIT

GitHub上は、sandboxというあまりやる気のないリポジトリに入れてあります。

トランザクション処理 概念と技法

参考書籍です。洋書は購入可能です。

和書は中古で手に入れるしかないと思います。こちらは上下巻に分かれています。

トランザクション処理 上

トランザクション処理 上

トランザクション処理 下

トランザクション処理 下

2014-01-10 MySQL 5.1のプロダクトライフサイクルが終了 このエントリーを含むブックマーク

2013年12月31日をもってMySQL 5.1のプロダクトライフサイクルが終了しました。今後MySQL 5.1に対して新たな不具合や脆弱性が見つかっても、開発元による修正は行われません。現在もMySQL 5.1を使用している場合は、MySQL 5.5/5.6へ計画的にバージョンアップをされることをおすすめいたします。

リリースGAPremier Support終了Extended Support終了Sustaining Support終了
MySQL 5.02005年10月2011年12月 Not Available Indefinite
MySQL 5.12008年12月2013年12月 Not Available Indefinite
MySQL 5.52010年12月2015年12月 2018年12月 Indefinite
MySQL 5.62013年2月 2018年2月 2021年2月 Indefinite

(Lifetime Support Policy, Coverage for Oracle Technology Products - November, 2013より引用)

古いバージョンの頒布

2年前MySQL 3.23〜5.0までのバージョンを集めたウェブサイトを作成したのですが、そこにMySQL 5.1を追加しました。

現時点で公式サイトからはMySQL 5.0以降のバージョンをダウンロードすることが可能です。そのため今回追加したMySQL 5.1が今すぐ役に立つわけではありませんが、公開が終了した際にはご利用いただければと思います。

バグ曲線

こちらも2年前に調べたのですが、MySQL 5.6を追加して更新しました。

f:id:sh2:20140110034436p:image

MySQL 5.6はGA後もバグ修正数が勢いよく伸びていて、MySQL 5.0と近い傾向にあるようです。一方MySQL 5.5はかなり安定しています。

2013-12-23 Oracle/MySQL/PostgreSQLにおけるサブクエリを含むDMLの非互換性 このエントリーを含むブックマーク

JPOUG Advent Calendar 2013の23日目です。比較的簡単なDMLでもRDBMS間の非互換性が出てしまうという怖い話をします。

+----+------+
| id | data |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
BEGIN;
-- IDが最も小さいレコードをロックする
SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;

(何らかの処理)

-- 処理が終わったので削除する
DELETE FROM q WHERE id = (1つ目のSQLで取得したID);
COMMIT;

IDが最も小さいレコードをロックし、何らかの処理を行って、最後にそのレコードを削除します。IDが最も小さいレコードを取得するというのはよく見かけるもので、例えばジョブキューを素朴に実装するとこのような流れになるかと思います。なおジョブキューを本当にこのように実装してしまうとトラブルの元になりますので、気になる方はBaron Schwartz氏の以下のエントリをご参照ください。

実行例

最初はOracle Database 12.1.0.1.0の実行例です。

 SESSON1> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
         ID DATA
 ---------- ----------
          1 a

          SESSION2> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
          (待たされる)

 SESSON1> DELETE FROM q WHERE id = 1;
 1 row deleted.
 
 SESSON1> COMMIT;
 Commit complete.

                  ID DATA
          ---------- ----------
                   2 b

次はMySQL 5.6.15の実行例です。トランザクション分離レベルはOracle Databaseに合わせてREAD COMMITTEDとしています。

 SESSON1> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 Query OK, 0 rows affected (0.00 sec)
 
 SESSON1> BEGIN;
 Query OK, 0 rows affected (0.00 sec)
 
 SESSON1> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
 +----+------+
 | id | data |
 +----+------+
 |  1 | a    |
 +----+------+
 1 row in set (0.00 sec)

          SESSION2> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
          Query OK, 0 rows affected (0.00 sec)
          
          SESSION2> BEGIN;
          Query OK, 0 rows affected (0.00 sec)
          
          SESSION2> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
          (待たされる)

 SESSON1> DELETE FROM q WHERE id = 1;
 Query OK, 1 row affected (0.00 sec)
 
 SESSON1> COMMIT;
 Query OK, 0 rows affected (0.00 sec)

          Empty set (8.26 sec)

MySQLの場合、セッション2のSELECT FOR UPDATE文が空振りします。

MySQLはサブクエリでMIN(ID)=1を取得して、外側のクエリでID=1のレコードをロックしようとして空振りする分かりやすい挙動です。一方Oracle Databaseは文レベルの読み取り一貫性を保証するために、SELECT FOR UPDATE文でロックしようとしたレコードが変更されていた場合、クエリ再起動してMIN(ID)=2を取得しなおすという挙動です。この挙動はマニュアルに記載されています。

私はOracle Databaseの挙動の方が好きですが、内部的にクエリを複数回実行することから、デッドロックが発生する、トリガが複数回起動されるといったトラブルを招く場合があります。

PostgreSQL 9.3.2の実行例も見てみましょう。

 SESSION1=> BEGIN;
 BEGIN
 
 SESSION1=> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
  id | data
 ----+------
   1 | a
 (1 行)

          SESSION2=> BEGIN;
          BEGIN
          
          SESSION2=> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
          (待たされる)

 SESSION1=> DELETE FROM q WHERE id = 1;
 DELETE 1
 
 SESSION1=> COMMIT;
 COMMIT

           id | data
          ----+------
          (0 行)

PostgreSQLの場合は、MySQLと同様にセッション2のSELECT FOR UPDATE文が空振りします。

結果一覧

トランザクション分離レベルがREAD COMMITTEDの場合に加え、REPEATABLE READ、SERIALIZABLEの場合も調査しました。セッション2で実行されたSELECT FOR UPDATE文がどのような結果になったのか、一覧を以下に示します。

RDBMS READ COMMITTEDREPEATABLE READ SERIALIZABLE
Oracle 12.1.0.1.0ID=2を取得 (なし) 直列化エラー(※1)
MySQL 5.6.15 空振空振ID=2を取得
PostgreSQL 9.3.2 空振直列化エラー(※2)直列化エラー(※2)
  • ※1 ORA-08177: can't serialize access for this transaction
  • ※2 ERROR: could not serialize access due to concurrent update

三者三様で、どのトランザクション分離レベルでも挙動が揃うということがありません。正直頭を抱えてしまいます。

対処方法

これらのRDBMSで挙動を揃えたい場合は、SELECT FOR UPDATE文でサブクエリを使用することを禁止し、Oracle Databaseが行っているクエリ再起動を自前で実装することになるかと思います。PL/SQLでの実装例を以下に示します。

DECLARE
    v_min_id NUMBER;
    v_id     NUMBER;
    v_data   VARCHAR2(10);
BEGIN
    LOOP
        SELECT MIN(id) INTO v_min_id FROM q;
        BEGIN
            SELECT id, data INTO v_id, v_data FROM q WHERE id = v_min_id FOR UPDATE;
            EXIT;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END LOOP;
END;
/

Oracle Databaseで構築されたシステムをPostgreSQLに移行する際や、複数のRDBMSに対応したアプリケーションを開発する際に注意していただければと思います。明日は@MOTOTAKERさんです。