JdbcRunner 1.3リリース(データベース負荷テストツール)

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

JdbcRunner - 汎用データベース負荷テストツール - dbstudy.info
sh2/jdbcrunner - GitHub

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

2017-01-30

dstat2graphs(dstatグラフ化ツール)の更新

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

sh2/dstat2graphs: Draw graphs from a dstat CSV file. - GitHub リポジトリ
dstat2graphs - dbstudy.info - デモサイト
k01sl6.local 2017/01/29 17:54:14 - dstat2graphs - サンプルレポート

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もグラフ化できるようにしておきました。

sh2/iostat2graphs: Draw graphs from an iostat CSV file. - GitHub リポジトリ
iostat2graphs - dbstudy.info - デモサイト
k01sl6.local 2017/01/29 17:54:14 - iostat2graphs - サンプルレポート

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

sh2/rstat: Run dstat, iostat and pidstat on multiple remote hosts and outputs the log files in CSV format. - GitHub リポジトリ

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のロックについて」と題してネクストキーロックなどの説明をしました。プレゼンテーション資料と、調査のために作成したツールを公開します。

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

MySQL Lists: mysql: Re: InnoDB's inner workings + checkpoints

過去記事の訂正

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

InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーる

ところで、ネクストキーロックというと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というあまりやる気のないリポジトリに入れてあります。

sandbox/Lock.java at master · sh2/sandbox

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

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

Transaction Processing: Concepts and Techniques (The Morgan Kaufmann Series in Data Management Systems)

作者: Jim Gray,Andreas Reuter
出版社/メーカー: Morgan Kaufmann
発売日: 1993
メディア: ハードカバー
クリック: 8回
この商品を含むブログ (1件) を見る

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

トランザクション処理上

作者: ジムグレイ,アンドレアスロイター,Jim Gray,Andreas Reuter,喜連川優
出版社/メーカー: 日経BP社
発売日: 2001/10/20
メディア: 単行本
購入: 7人クリック: 145回
この商品を含むブログ (21件) を見る

トランザクション処理下

作者: ジムグレイ,アンドレアスロイター,Jim Gray,Andreas Reuter,喜連川優
出版社/メーカー: 日経BP社
発売日: 2001/10/20
メディア: 単行本
購入: 7人クリック: 47回
この商品を含むブログ (13件) を見る

2014-01-10

MySQL 5.1のプロダクトライフサイクルが終了

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

ライフタイム・サポート

リリース	GA日	Premier Support終了	Extended Support終了	Sustaining Support終了
MySQL 5.0	2005年10月	2011年12月	Not Available	Indefinite
MySQL 5.1	2008年12月	2013年12月	Not Available	Indefinite
MySQL 5.5	2010年12月	2015年12月	2018年12月	Indefinite
MySQL 5.6	2013年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 Archives - dbstudy.info

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

バグ曲線

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

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氏の以下のエントリをご参照ください。

MySQL をキューに使用する 5 つの微妙な方法とその落とし穴 (翻訳版) | Engine Yard Blog JP

実行例

最初は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開発ガイド 12cリリース1 (12.1) 6.4.4 明示的な行ロック

私は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 COMMITTED	REPEATABLE READ	SERIALIZABLE
Oracle 12.1.0.1.0	ID=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さんです。

2013-12-14

Red Hat Enterprise Linux 7 BetaにおけるMariaDB 5.5の構成

MySQL Casual Advent Calendar 2013の14日目です。

2013年12月11日にRed Hat Enterprise Linux 7 Betaがリリースされました。以前から噂されていたとおり、RHEL 7ではMySQLではなくMariaDBが採用されています。本日は、RHEL 7 BetaにおいてMariaDBがどのような構成になっているのかを簡単に確認していきたいと思います。

パッケージ構成

MariaDB関連パッケージは13個用意されています。

mariadb-bench.x86_64 : MariaDB benchmark scripts and data
mariadb-devel.i686 : Files for development of MariaDB/MySQL applications
mariadb-devel.x86_64 : Files for development of MariaDB/MySQL applications
mariadb-embedded-devel.i686 : Development files for MariaDB as an embeddable library
mariadb-embedded-devel.x86_64 : Development files for MariaDB as an embeddable library
mariadb-embedded.i686 : MariaDB as an embeddable library
mariadb-embedded.x86_64 : MariaDB as an embeddable library
mariadb-libs.i686 : The shared libraries required for MariaDB/MySQL clients
mariadb-libs.x86_64 : The shared libraries required for MariaDB/MySQL clients
mariadb-server.x86_64 : The MariaDB server and related files
mariadb-test.x86_64 : The test suite distributed with MariaD
mariadb.i686 : A community developed branch of MySQL
mariadb.x86_64 : A community developed branch of MySQL

現在のバージョンは5.5.33aです。

名前                : mariadb-server
アーキテクチャー    : x86_64
エポック            : 1
バージョン          : 5.5.33a
リリース            : 3.el7
容量                : 55 M
リポジトリー        : installed
提供元リポジトリー  : rhel-7-public-beta-rpms
要約                : The MariaDB server and related files
URL                 : http://mariadb.org
ライセンス          : GPLv2 with exceptions and LGPLv2 and BSD

以下は比較のために用意したRHEL 6のMySQL関連パッケージです。構成はほぼ同じです。

mysql-bench.x86_64 : MySQL benchmark scripts and data
mysql-connector-java.noarch : Official JDBC driver for MySQL
mysql-connector-odbc.x86_64 : ODBC driver for MySQL
mysql-devel.i686 : Files for development of MySQL applications
mysql-devel.x86_64 : Files for development of MySQL applications
mysql-embedded-devel.i686 : Development files for MySQL as an embeddable library
mysql-embedded-devel.x86_64 : Development files for MySQL as an embeddable library
mysql-embedded.i686 : MySQL as an embeddable library
mysql-embedded.x86_64 : MySQL as an embeddable library
mysql-libs.i686 : The shared libraries required for MySQL clients
mysql-libs.x86_64 : The shared libraries required for MySQL clients
mysql-server.x86_64 : The MySQL server and related files
mysql-test.x86_64 : The test suite distributed with MySQL
mysql.x86_64 : MySQL client programs and shared libraries

ポイントとして、RHEL 7においてもJDBCドライバとODBCドライバについては本家のドライバがバンドルされている点があります。

名前                : mysql-connector-java
アーキテクチャー    : noarch
エポック            : 1
バージョン          : 5.1.25
リリース            : 2.el7
容量                : 1.3 M
リポジトリー        : rhel-7-public-beta-rpms
要約                : Official JDBC driver for MySQL
URL                 : http://dev.mysql.com/downloads/connector/j/
ライセンス          : GPLv2 with exceptions

名前                : mysql-connector-odbc
アーキテクチャー    : x86_64
バージョン          : 5.2.5
リリース            : 2.el7
容量                : 141 k
リポジトリー        : rhel-7-public-beta-rpms
要約                : ODBC driver for MySQL
URL                 : http://dev.mysql.com/downloads/connector/odbc/
ライセンス          : GPLv2 with exceptions

MariaDBではMariaDB Java ClientというLGPLのJDBCドライバが開発されているのですが、こちらは採用されませんでした。

インストール

mariadb-libsはRHEL 6のmysql-libsと同様postfixとの依存関係があり、最小構成でも初めからインストールされています。データベースサーバとしてはmariadb、mariadb-devel、mariadb-server、加えて使用するプログラミング言語にあわせてphp-mysql、perl-DBD-MySQL、MySQL-pythonなどをインストールすることになります。特にハマりどころはなかったので詳細は割愛します。

設定ファイル

インストール直後の/etc/my.cnfは以下のようになっています。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

最後の行で/etc/my.cnf.d以下の設定ファイルを読み込んでいます。追加の設定ファイルを読み込む機能はもともとMySQLにもあるものですが、このような構成でパッケージングされている点はMariaDBの独自方針となります。/etc/my.cnf.dには以下の3ファイルが配置されています。

client.cnf
mysql-clients.cnf
server.cnf

これらはそれぞれ提供元のパッケージが異なっており、client.cnfはmariadb、mysql-clients.cnfはmariadb-libs、server.cnfはmariadb-serverからのものとなっています。RHEL 6のMySQL 5.1ではmysql-libsが提供するmy.cnfにサーバの設定も記述していたわけですが、今回パッケージごとに設定ファイルが分離されている点は、一度覚えてしまえばまあ合理的かなと思います。

サービスの起動と停止

ご存知の方も多いと思いますが、RHEL 7からはサービスをsystemdで管理するようになりました。

# systemctl start mariadb.service

# systemctl status mariadb.service
mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled)
   Active: active (running) since 土 2013-12-14 00:56:54 JST; 46s ago
  Process: 11180 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 11106 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 11179 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           tq11179 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           mq11342 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin -...

12月 14 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: The latest information about MariaDB is available...g/.
12月 14 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: You can find additional information about the MyS...at:
12月 14 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: http://dev.mysql.com
12月 14 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: Support MariaDB development by buying support/new...rom
12月 14 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: Monty Program Ab. You can contact us about this a...om.
12月 14 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: Alternatively consider joining our community base...rt:
12月 14 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: http://kb.askmonty.org/en/contributing-to-the-mar...ct/
12月 14 00:56:52 k03rhel7b.local mysqld_safe[11179]: 131214 00:56:52 mysqld_safe Logging to '/var/log/mariadb/ma...og'.
12月 14 00:56:52 k03rhel7b.local mysqld_safe[11179]: 131214 00:56:52 mysqld_safe Starting mysqld daemon with dat...ysql
12月 14 00:56:54 k03rhel7b.local systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

systemdではcgroupsを用いてサービスごとにリソースを管理できるようになっています。RDBMSは特にリソース消費量の多いソフトウェアですから、今後ノウハウを蓄積していくことで運用で助かる場面も出てくるかと思います。下半分に出ているログは、MariaDBのエラーログではなくOSのsyslogです。

ファイアウォール

RHEL 7からはfirewalldでファイアウォールを管理するようになりましたので、これも覚える必要があります。初期状態では、MariaDBに接続するためのTCP3306番は閉じられています。

# firewall-cmd --add-service=mysql
success

# firewall-cmd --permanent --add-service=mysql
success

# firewall-cmd --list-services
dhcpv6-client mysql ssh

# firewall-cmd --permanent --list-services
dhcpv6-client mysql ssh

サービスの定義ファイルは/usr/lib/firewalld/servicesに格納されています。

# cat /usr/lib/firewalld/services/mysql.xml
<?xml version="1.0" encoding="utf-8"?>
<service>
  <short>MySQL</short>
  <description>MySQL Database Server</description>
  <port protocol="tcp" port="3306"/>
</service>

SELinux

SELinuxについてはRHEL 7で大きく変わったところはないと思います。ただいつまでもPermissiveやDisabledにしているのも進歩がないですので、この機会に典型的なハマりパターンをご紹介しておきます。SELinuxのポリシー違反をsyslogに出力するために、あらかじめsetroubleshoot、setroubleshoot-serverをインストールしておくことをおすすめします。

datadirの変更

MySQLでもMariaDBでもdatadirのデフォルト値は/var/lib/mysqlです。しかし実際にはストレージを増設して別のディレクトリを使用することが多いと思います。そのときによく遭遇するエラーが以下のものです。

# systemctl start mariadb.service
Job for mariadb.service failed. See 'systemctl status mariadb.service' and 'journalctl -xn' for details.

# less /var/log/messages
Dec 14 02:05:11 k03rhel7b mariadb-prepare-db-dir: Initializing MySQL database
Dec 14 02:05:11 k03rhel7b mariadb-prepare-db-dir: Installing MariaDB/MySQL system tables in '/opt/mysql' ...
Dec 14 02:05:11 k03rhel7b mariadb-prepare-db-dir: 131214  2:05:11 [Warning] Can’t create test file /opt/mysql/k03rhel7b.lower-test
Dec 14 02:05:11 k03rhel7b mariadb-prepare-db-dir: 131214  2:05:11 [ERROR] mysqld: Can’t create/write to file '/opt/mysql/aria_log_control' (Errcode: 13)
…
Dec 14 02:05:11 k03rhel7b setroubleshoot: SELinux is preventing /usr/libexec/mysqld from write access on the directory mysql.
                                          For complete SELinux messages. run sealert -l 678a1882-4c2c-43bd-8d50-b515db579e95

# perror 13
OS error code  13:  Permission denied

ディレクトリの所有者、アクセス権限を確認しても問題が見つからないときは、SELinuxのことを思い出してください。setroubleshoot-serverがインストールされていないと相当ハマると思います。

# sealert -l 678a1882-4c2c-43bd-8d50-b515db579e95
SELinux is preventing /usr/libexec/mysqld from write access on the directory mysql.

*****  Plugin catchall_labels (83.8 confidence) suggests   *******************

If you want to allow mysqld to have write access on the mysql directory
Then mysql のラベルを変更する必要があります
Do
  # semanage fcontext -a -t FILE_TYPE 'mysql'
この FILE_TYPE 以下のどれかです: mysqld_db_t, mysqld_log_t, mysqld_tmp_t, mysqld_var_run_t, tmp_t, var_lib_t, var_log_t, var_run_t.
次にこれを実行してください:
restorecon -v 'mysql'
…

翻訳が少々怪しいですが、SELinuxが有効な場合、MySQL/MariaDBがアクセスするファイルには適切なSELinuxコンテキストを付与しておく必要があります。

# ls -lZ
drwxr-xr-x. mysql mysql unconfined_u:object_r:usr_t:s0   mysql

# semanage fcontext -a -t mysqld_db_t '/opt/mysql(/.*)?'

# cat /etc/selinux/targeted/contexts/files/file_contexts.local
/opt/mysql(/.*)?    system_u:object_r:mysqld_db_t:s0

# restorecon -RFv /opt/mysql
restorecon reset /opt/mysql context unconfined_u:object_r:usr_t:s0->system_u:object_r:mysqld_db_t:s0

# ls -lZ
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql

# systemctl start mariadb.service

# mysql -u root -e "SHOW GLOBAL VARIABLES LIKE 'datadir'"
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| datadir       | /opt/mysql/ |
+---------------+-------------+

TCPポート番号の変更

TCPポート番号もSELinuxの管理下にあります。

# systemctl start mariadb.service
Job for mariadb.service failed. See 'systemctl status mariadb.service' and 'journalctl -xn' for details.

# less /var/log/mariadb/mariadb.log
131214  2:29:12 [ERROR] Can’t start server: Bind on TCP/IP port. Got error: 13: Permission denied
131214  2:29:12 [ERROR] Do you already have another mysqld server running on port: 3307 ?
131214  2:29:12 [ERROR] Aborting

# less /var/log/messages
Dec 14 02:29:13 k03rhel7b setroubleshoot: SELinux is preventing /usr/libexec/mysqld from name_bind access on the tcp_socket .
                                          For complete SELinux messages. run sealert -l e459c5fc-2734-459b-8945-501644f238d5

# sealert -l e459c5fc-2734-459b-8945-501644f238d5
*****  Plugin bind_ports (92.2 confidence) suggests   ************************

If you want to allow /usr/libexec/mysqld to bind to network port 3307
Then you need to modify the port type.
Do
  # semanage port -a -t mysqld_port_t -p tcp 3307
…

# semanage port -a -t mysqld_port_t -p tcp 3307

# cat /etc/selinux/targeted/modules/active/ports.local
portcon tcp 3307 system_u:object_r:mysqld_port_t:s0

# systemctl start mariadb.service

# mysql -u root -e "SHOW GLOBAL VARIABLES LIKE 'port'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

さらに、先ほどご紹介したファイアウォールの設定も必要です。

# cd /etc/firewalld/services

# cp /usr/lib/firewalld/services/mysql.xml mysql3307.xml

# vim mysql3307.xml

# cat mysql3307.xml
<?xml version="1.0" encoding="utf-8"?>
<service>
  <short>MySQL 3307</short>
  <description>MySQL Database Server 3307</description>
  <port protocol="tcp" port="3307"/>
</service>

# firewall-cmd --reload
success

# firewall-cmd --add-service=mysql3307
success

# firewall-cmd --permanent --add-service=mysql3307
success

$ mysql -h k03rhel7b -P 3307 -u scott -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.33a-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

いやはや、お疲れさまでした。MySQLかMariaDBかというよりも、まずRHEL 7の使い方を覚えるのに時間がかかりそうです。明日は@tmtmsさんです。

2013-10-07

MySQL 5.6における大量データロード時の考慮点

ご縁があってAWS User Group - Japanにお誘いいただき、10月4日に第18回 AWS User Group - Japan 東京勉強会で発表をしてきました。運営のみなさま、当日お越しいただいたみなさま、どうもありがとうございます。

今回は「秋のDB祭り」ということで、MySQLに限らずさまざまなデータベースに関する話題が取り扱われていました。その中でもRedshiftのセッションが複数あり、注目度の高さが伺えました。クラスメソッドさん、EnterpriseZineさんが勉強会の様子を詳しくレポートされています。

私のセッションはMySQL 5.6における最適なデータロード手順をまとめたものです。こうしたMySQL 5.6の細かいノウハウは、これからも折を見て書きためていけたらと思います。資料は勉強会後に少し修正してRevision 2となっています。

プレゼンテーション資料 (PDF)

プレゼンテーション資料からリンクしているウェブサイトの一覧です。特に、日本HPさんの資料について内容を理解していることが前提となります。

tmpdirについて少し補足します。MySQLがtmpdirに作成した一時ファイルはlsなどのコマンドでは見ることができません。これはMySQLが一時ファイルを作成したあと、すぐに削除してしまうためです。

LinuxなどのOSでは、ファイルを削除してもそのファイルをオープンしているプロセスがある限り実際の削除は行われません。逆に言えば、こうしておくことでプロセス終了時に一時ファイルが自動的にクリーンアップされるというわけです。lsofコマンドであればMySQLが作成した一時ファイルを確認することができます。

# lsof -p 15260
COMMAND   PID  USER   FD   TYPE             DEVICE   SIZE/OFF    NODE NAME
…
mysqld  15260 mysql   37u   REG             252,33   77594624      17 /opt/mysql56/tmp/ibogf9Oi (deleted)
mysqld  15260 mysql   38u   REG             252,33  264241152      18 /opt/mysql56/tmp/ibflHj2M (deleted)
mysqld  15260 mysql   39u   REG             252,33  264241152      19 /opt/mysql56/tmp/ib3syufh (deleted)

測定に使用したスクリプトを以下に貼り付けておきます。

-- ---------------------------------------------------------
SELECT 'PATTERN 1';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  KEY `order_line_ix1` (`ol_i_id`),
  KEY `order_line_ix2` (`ol_dist_info`),
  CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G

-- ---------------------------------------------------------
SELECT 'PATTERN 2';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  KEY `order_line_ix1` (`ol_i_id`),
  KEY `order_line_ix2` (`ol_dist_info`),
  CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET SESSION foreign_key_checks = OFF;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

SET SESSION foreign_key_checks = ON;

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G

-- ---------------------------------------------------------
SELECT 'PATTERN 3';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET SESSION foreign_key_checks = OFF;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

SET SESSION foreign_key_checks = ON;

ALTER TABLE `order_line`
  ADD KEY `order_line_ix1` (`ol_i_id`);

ALTER TABLE `order_line`
  ADD KEY `order_line_ix2` (`ol_dist_info`);

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G

-- ---------------------------------------------------------
SELECT 'PATTERN 4';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET SESSION foreign_key_checks = OFF;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

SET SESSION foreign_key_checks = ON;

ALTER TABLE `order_line`
  ADD KEY `order_line_ix1` (`ol_i_id`),
  ADD KEY `order_line_ix2` (`ol_dist_info`);

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G

-- ---------------------------------------------------------
SELECT 'PATTERN 5';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

SET SESSION foreign_key_checks = OFF;

ALTER TABLE `order_line`
  ADD KEY `order_line_ix1` (`ol_i_id`),
  ADD KEY `order_line_ix2` (`ol_dist_info`),
  ADD CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  ADD CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`);

SET SESSION foreign_key_checks = ON;

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G