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のモニタを導入しまして(^^;、だいぶ快適になりました。私は普段あまりプログラミングをしていないので何かしようとするたびに開発環境から作り直しているような状態ですが、今後もバージョンアップ追従は続けていこうと思います。
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でオートフィルタをかけて見ることを想定しています。
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
トランザクション処理 概念と技法
参考書籍です。洋書は購入可能です。

- 作者: 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件) を見る
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より引用)
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 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) |
対処方法
これらの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さんです。
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ポート番号の変更
# 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さんです。
MySQL 5.6における大量データロード時の考慮点
ご縁があってAWS User Group - Japanにお誘いいただき、10月4日に第18回 AWS User Group - Japan 東京勉強会で発表をしてきました。運営のみなさま、当日お越しいただいたみなさま、どうもありがとうございます。
今回は「秋のDB祭り」ということで、MySQLに限らずさまざまなデータベースに関する話題が取り扱われていました。その中でもRedshiftのセッションが複数あり、注目度の高さが伺えました。クラスメソッドさん、EnterpriseZineさんが勉強会の様子を詳しくレポートされています。
- 第18回 AWS User Group - Japan 東京勉強会 に参加してきた #jawsug | Developers.IO
- JAWS-UG東京勉強会で濃ゆーい「秋のDB祭り」:企業のIT・経営・ビジネスをつなぐ情報サイト EnterpriseZine (EZ)
私のセッションはMySQL 5.6における最適なデータロード手順をまとめたものです。こうしたMySQL 5.6の細かいノウハウは、これからも折を見て書きためていけたらと思います。資料は勉強会後に少し修正してRevision 2となっています。
- プレゼンテーション資料 (PDF)
プレゼンテーション資料からリンクしているウェブサイトの一覧です。特に、日本HPさんの資料について内容を理解していることが前提となります。
- 日本HP Linux ブループリント
- MySQL :: MySQL 5.6 Reference Manual :: 5.5.1 Overview of Online DDL
- MySQL Bugs: #57583: fast index create not used during "alter table foo engine=innodb"
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