Hatena::ブログ(Diary)

SH2の日記 RSSフィード

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さんです。

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というLGPLJDBCドライバが開発されているのですが、こちらは採用されませんでした。

インストール

mariadb-libsはRHEL 6のmysql-libsと同様postfixとの依存関係があり、最小構成でも初めからインストールされています。データベースサーバとしてはmariadbmariadb-devel、mariadb-server、加えて使用するプログラミング言語にあわせてphp-mysqlperl-DBD-MySQLMySQL-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はmariadbmysql-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 -...

1214 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: The latest information about MariaDB is available...g/.
1214 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: You can find additional information about the MyS...at:
1214 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: http://dev.mysql.com
1214 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: Support MariaDB development by buying support/new...rom
1214 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: Monty Program Ab. You can contact us about this a...om.
1214 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: Alternatively consider joining our community base...rt:
1214 00:56:52 k03rhel7b.local mariadb-prepare-db-dir[11106]: http://kb.askmonty.org/en/contributing-to-the-mar...ct/
1214 00:56:52 k03rhel7b.local mysqld_safe[11179]: 131214 00:56:52 mysqld_safe Logging to '/var/log/mariadb/ma...og'.
1214 00:56:52 k03rhel7b.local mysqld_safe[11179]: 131214 00:56:52 mysqld_safe Starting mysqld daemon with dat...ysql
1214 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エラーログではなくOSsyslogです。

ファイアウォール

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)]>

いやはや、お疲れさまでした。MySQLMariaDBかというよりも、まず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となっています。

プレゼンテーション資料からリンクしているウェブサイトの一覧です。特に、日本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