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

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

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

MySQL 5.6 パラメータ検討会

7月29日にMyNA(日本MySQLユーザ会)会 2013年7月が行われ、Oracle ACE Directorの@sheeriさん、MyNA会長の@tmtmsさんに混ざって発表をしてきました。運営のみなさま、当日お越しいただいたみなさま、いつもありがとうございます。

今回は@yoku0825さん、@yyamasaki1さんがライトニングトークをされました。@yoku0825さんアイスごちそうさまでした。

私は発表内容について懇親会でいろいろ宿題をもらってしまい、しばらく復習をしていました。ようやく修正が終わりましたのでRevision 2として公開します。


復習のついでに、パラメータファイルの叩き台を作成しました。

このパラメータファイルは一部のディレクトリ名、hostname、server_id、server_uuidとssl_cipherを除き、Amazon RDS t1.smallの設定を再現したものです。これをベースにカスタマイズしていくと時間を節約できるのではないかと思います。インスタンスサイズが異なる場合はinnodb_buffer_pool_sizeとmax_connectionsを増減させます。オンプレミスの環境でしたらdefault_time_zoneはSYSTEMの方がよいかもしれません。

コメントアウトされているところは私が独断で追加したパラメータです。新機能を積極的に活用する、また極力詳細なログを出力するという方針で選んでいます。設定値はあくまでも一例ですが、ご参考になればと思います。リファレンスマニュアルにリンクしたものを以下に貼り付けておきます。

[mysqld]
## File Location
datadir                   = /rdsdbdata/db
innodb_data_home_dir      = /rdsdbdata/db/innodb
pid-file                  = /rdsdbdata/log/mysql-3306.pid
log_bin                   = /rdsdbdata/log/binlog/mysql-bin-changelog
log_error                 = /rdsdbdata/log/error/mysql-error.log
general_log_file          = /rdsdbdata/log/general/mysql-general.log
innodb_log_group_home_dir = /rdsdbdata/log/innodb
relay_log                 = /rdsdbdata/log/relaylog/relaylog
slow_query_log_file       = /rdsdbdata/log/slowquery/mysql-slowquery.log
slave_load_tmpdir         = /rdsdbdata/tmp
tmpdir                    = /rdsdbdata/tmp
secure_file_priv          = /tmp
socket                    = /tmp/mysql.sock

## SSL
ssl_ca   = /rdsdbdata/rds-metadata/ca-cert.pem
ssl_cert = /rdsdbdata/rds-metadata/server-cert.pem
ssl_key  = /rdsdbdata/rds-metadata/server-key.pem

## InnoDB
#innodb_buffer_pool_dump_at_shutdown = ON
#innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_size              = 1092M
#innodb_checksum_algorithm           = crc32
#innodb_file_format                  = Barracuda
#innodb_flush_method                 = O_DIRECT
#innodb_io_capacity                  = 2000
#innodb_io_capacity_max              = 4000
#innodb_large_prefix                 = ON
innodb_log_file_size                 = 128M
#innodb_online_alter_log_max_size    = 1G
#innodb_print_all_deadlocks          = ON

## MyISAM
key_buffer_size = 16M

## Memory
#max_heap_table_size = 64M
#tmp_table_size      = 64M

## Binary Log
binlog_format             = MIXED
#enforce_gtid_consistency = ON
#expire_logs_days         = 14
#gtid_mode                = ON
log_slave_updates         = ON
master_info_repository    = TABLE
#master_verify_checksum   = ON
max_binlog_size           = 128M
relay_log_info_repository = TABLE
relay_log_recovery        = ON
#report_host              = 
#report_port              = 3306
server_id                 = 12345
#slave_net_timeout        = 30
sync_binlog               = 1

## Slow Query Log
log_output                              = TABLE
#log_queries_not_using_indexes          = ON
#log_slow_slave_statements              = ON
#log_throttle_queries_not_using_indexes = 60
#long_query_time                        = 1.0
#slow_query_log                         = ON

## Connection Management
#log_warnings              = 2
#max_allowed_packet        = 16M
#max_connect_errors        = 999999999
max_connections            = 121
#table_open_cache          = 10000
table_open_cache_instances = 16

## Memory Allocation per Connection
read_buffer_size     = 256K
read_rnd_buffer_size = 512K
#sort_buffer_size    = 2M

## SQL Behavior
#character_set_server           = utf8mb4
#collation_server               = utf8mb4_general_ci
default_time_zone               = UTC
explicit_defaults_for_timestamp = ON
#sql_mode                       = TRADITIONAL
#transaction_isolation          = READ-COMMITTED

[client]
#loose_default_character_set = utf8mb4
socket                       = /tmp/mysql.sock

MySQL向けに書かれたアプリケーションをOracle Databaseに接続させる

先日Oracle Database 12c Release 1がリリースされまして、新機能ガイドを眺めていたところ気になる新機能を見つけました。

Oracle Database 12c Release 1にはliboramysql12という共有ライブラリが同梱されていて、これをlibmysqlclientと差し替えるとMySQL向けに書かれたアプリケーションがそのままOracle Databaseに対して動作するのだそうです。ドキュメントから図を引用します。

試してみましょう。MySQLにテスト用のテーブルを作ります。

mysql> CREATE TABLE myora (
    ->     id INT PRIMARY KEY,
    ->     data VARCHAR(100)
    -> ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO myora (id, data) VALUES (1, 'マイエスキューエル');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM myora;
+----+-----------------------------+
| id | data                        |
+----+-----------------------------+
|  1 | マイエスキューエル          |
+----+-----------------------------+
1 row in set (0.00 sec)

Oracle Databaseにも同じものを作ります。

SQL> CREATE TABLE myora (
  2      id NUMBER PRIMARY KEY,
  3      data VARCHAR2(400)
  4  );
Table created.

SQL> INSERT INTO myora (id, data) VALUES (1, 'オラクル');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> COL data FORMAT a40
SQL> SELECT * FROM myora

        ID DATA
---------- ----------------------------------------
         1 オラクル

しばらく試行錯誤していたのですが、PythonとMySQL-Pythonの組み合わせで動作確認がとれました。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import print_function
from __future__ import unicode_literals

import MySQLdb

connection = None
cursor = None

try:
    connection = MySQLdb.connect(
            db='scott', user='scott', passwd='tiger', charset='utf8')
    cursor = connection.cursor()
    cursor.execute("SELECT data FROM myora WHERE id = 1")
    result = cursor.fetchall()

    for record in result:
        print(record[0])
except MySQLdb.Error as e:
    print(repr(e))
finally:
    if cursor:
        cursor.close()
    if connection:
        connection.close()

$ python db1.py
マイエスキューエル

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export LD_PRELOAD=$ORACLE_HOME/lib/liboramysql12.so
$ python db1.py
オラクル

すごいですね。ただし、LD_PRELOADを設定して実行しただけでは以下のエラーが発生していました。

Traceback (most recent call last):
  File "db1.py", line 16, in <module>
    cursor.execute("SELECT data FROM myora WHERE id = 1")
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 156, in execute
    query = query.encode(charset)
LookupError: unknown encoding:

これはMySQL C APIのうちmysql_character_set_name()がliboramysql12には実装されておらず、空文字列を返すことによるものです。今回はMySQL-Pythonに以下のパッチをあててしのぎました。

--- cursors.py_orig     2013-07-01 00:57:30.422669501 +0900
+++ cursors.py  2013-06-30 23:01:36.461905484 +0900
@@ -152,6 +152,8 @@
         del self.messages[:]
         db = self._get_db()
         charset = db.character_set_name()
+        if charset == '':
+            charset = 'utf8'
         if isinstance(query, unicode):
             query = query.encode(charset)
         if args is not None:

それから例外ハンドリングの違いが気になりました。以下のスクリプトでわざと一意制約違反を起こしてみます。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import print_function
from __future__ import unicode_literals

import MySQLdb

connection = None
cursor = None

try:
    connection = MySQLdb.connect(
            db='scott', user='scott', passwd='tiger', charset='utf8')
    cursor = connection.cursor()
    cursor.execute("INSERT INTO myora (id, data) VALUES (1, 'ポストグレス')")
    connection.commit()
except MySQLdb.Error as e:
    print(repr(e))
finally:
    if cursor:
        cursor.close()
    if connection:
        connection.close()

$ python db2.py
IntegrityError(1062, "Duplicate entry '1' for key 'PRIMARY'")

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export LD_PRELOAD=$ORACLE_HOME/lib/liboramysql12.so
$ python db2.py
InternalError(1, 'ORA-00001: unique constraint (SCOTT.SYS_C0010064) violated')

このように、残念ながらエラー番号のマッピングはしてくれないようです。MySQL-Pythonはエラー番号を見て例外クラスのインスタンスを生成しているため、生成されたインスタンスも誤ったものとなっています。

  • 未実装APIやエラー番号の違いがあるため、MySQL-Pythonなどのドライバにかなりの修正が必要
  • SQLの方言を吸収しないため、アプリケーションにもかなりの修正が必要
  • MySQL用のアプリケーションをわざわざ高価なOracle Databaseに移行する動機がない

ということで現時点ではあまり実用性がない気もしますが、Oracle Database本体がMySQLに関連する機能を備えたのは初めてだと思いますので、ご紹介しました。

EBS Standard Volumeの性能特性

最近@namikawaさんと@con_mameさんがEBSの性能測定をされていて、少し気になったので追試をしました。

測定環境です。

  • ap-northeast-1c
  • m1.small (Spot Instance)
  • Amazon Linux AMI 2013.03.1
  • EBS Standard Volume 16GB
  • ext4 (relatime、nobarrier)

測定用スクリプトです。EBS Standard Volumeに対してランダムI/Oの測定のみを行います。

#/bin/bash

for SIZE in 1g 2g 4g 8g; do
    for IODEPTH in 1 2 4 8; do
        rstat/rstat_start.sh localhost

        fio -direct=1 -ioengine=libaio -readwrite=randread -blocksize=4k \
            -filename=/fio/fio.dat -loops=1000 -runtime=180 \
            -size=${SIZE} -iodepth=${IODEPTH} -name=fio_s${SIZE}_q${IODEPTH} | tee -a fio_s${SIZE}.log

        rstat/rstat_stop.sh
        sleep 30
    done

    mkdir fio_${SIZE}
    mv *.log *.csv fio_${SIZE}
done

ポイントは、測定時間を16秒から180秒に伸ばしたところです。ファイルサイズが8GBのときの結果を時系列のグラフにすると、以下のようになります。

数秒から数十秒経過したところで、性能がストンと落ちています。この挙動はドキュメントに記載されているとおりのものなのですが、なかなかダイナミックな制御が行われているようです。

スタンダードボリュームは、I/O 要件が中程度またはバースト性であるアプリケーション用のストレージに適しています。スタンダードボリュームの平均パフォーマンスは約 100 IOPS であり、ベストエフォートで数百 IOPS のバーストも可能です。スタンダードボリュームは、ブートボリュームとしての使用にも適しており、バースト可能であることからインスタンス起動時間が短縮されます。

ファイルサイズが8GB、非同期I/OのQueue Depthが8の場合はバースト時に14,000 IOPS、それが終わると300〜600 IOPSとなりました。Standard Volumeがコンスタントに1,000 IOPS以上出せてしまうとProvisioned IOPSの意味がなくなってしまいますから、1,000 IOPS未満に落ち込むことがあるという点は致し方ないのかもしれません。振れ幅が大きいのでデータベースの格納先として使用すると突然の性能低下に悩まされそうですね。ルートデバイスには適していると思います。

バースト制御のアルゴリズムがどうなっているのかはよく分からないのですが、今回の測定条件ではfioコマンドの終了後に30秒ほど休ませると再びバーストするようになりました。また読み取りはアクセス範囲が狭い場合にバーストが継続する傾向があり、書き込みについてはアクセス範囲が広くても180秒間ずっとバーストしたままとなっていました。他のパターンのグラフをいくつか載せておきます。


ファイルサイズが1GBの場合は、バーストが継続しています。


書き込みの場合は、ファイルサイズが8GBでもバーストが継続しています。

もっと詳しく調べたいところですが、お財布がどんどん軽くなるのでこのあたりにしておきます。個人的には、データベースの格納先としてはProvisioned IOPSをおすすめします。

チューニンガソン5の復習 MySQL 5.6 新機能編

というわけで、MyNA(日本MySQLユーザ会)会 2013年3月に参加して発表をしてきました。とてもリラックスして話をすることができました。司会進行の坂井さんをはじめ日本MySQLユーザ会のみなさま、日本オラクルのみなさま、当日お越しいただいたみなさま、どうもありがとうございました。
私のセッションでは前回のエントリの続きということで、MySQL 5.6の新機能Optimizer Traceを活用しながら正攻法でのチューニングを行っていきました。とはいえ途中から正攻法ではなくなっていた気もします。MySQL 5.6でRDBMSとしての土台はしっかりしてきたと思いますので、今後は高度な統計情報を使用したSQL実行計画の最適化といったところにも機能強化が施されていくのではないかと期待しています。

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

もっとすてきなチューニング方法を見つけたら、ぜひ教えてください。