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
MySQL 5.6 パラメータ検討会
7月29日にMyNA(日本MySQLユーザ会)会 2013年7月が行われ、Oracle ACE Directorの@sheeriさん、MyNA会長の@tmtmsさんに混ざって発表をしてきました。運営のみなさま、当日お越しいただいたみなさま、いつもありがとうございます。
今回は@yoku0825さん、@yyamasaki1さんがライトニングトークをされました。@yoku0825さんアイスごちそうさまでした。
私は発表内容について懇親会でいろいろ宿題をもらってしまい、しばらく復習をしていました。ようやく修正が終わりましたのでRevision 2として公開します。
- プレゼンテーション資料 (PDF)
- パラメータ一覧表 Excel、Calc、HTML
復習のついでに、パラメータファイルの叩き台を作成しました。
このパラメータファイルは一部のディレクトリ名、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の性能測定をされていて、少し気になったので追試をしました。
- Amazon EBS の性能ベンチマーク その1 (Standard編) - 元RX-7乗りの適当な日々
- Amazon EBS の性能ベンチマーク その2 (Standard-Vol増量編) - 元RX-7乗りの適当な日々
- Amazon EBS の性能ベンチマーク その3 (Provisioned IOPS編) - 元RX-7乗りの適当な日々
- 4,000IOPS EBSのベンチをとってみた - まめ畑
- 今一度Provisoed IOPS 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秒間ずっとバーストしたままとなっていました。他のパターンのグラフをいくつか載せておきます。
書き込みの場合は、ファイルサイズが8GBでもバーストが継続しています。
もっと詳しく調べたいところですが、お財布がどんどん軽くなるのでこのあたりにしておきます。個人的には、データベースの格納先としてはProvisioned IOPSをおすすめします。
チューニンガソン5の復習 MySQL 5.6 新機能編
というわけで、MyNA(日本MySQLユーザ会)会 2013年3月に参加して発表をしてきました。とてもリラックスして話をすることができました。司会進行の坂井さんをはじめ日本MySQLユーザ会のみなさま、日本オラクルのみなさま、当日お越しいただいたみなさま、どうもありがとうございました。
私のセッションでは前回のエントリの続きということで、MySQL 5.6の新機能Optimizer Traceを活用しながら正攻法でのチューニングを行っていきました。とはいえ途中から正攻法ではなくなっていた気もします。MySQL 5.6でRDBMSとしての土台はしっかりしてきたと思いますので、今後は高度な統計情報を使用したSQL実行計画の最適化といったところにも機能強化が施されていくのではないかと期待しています。
プレゼンテーション資料からリンクしているウェブサイトの一覧です。
- チューニンガソン5の復習 MySQL 5.5 チート編&勉強会のお知らせ - SH2の日記
- My MySQL is faster than your MySQL
- Is MySQL 5.6 slower than MySQL 5.5?
- MySQL Bugs: #68413: performance_schema overhead is at least 10%
- MySQL Bugs: #66473: MySQL 5.6.6-m9 has more mutex contention than MySQL 5.1
- INDEX FULL SCANを狙う - MySQL Casual Advent Calendar 2011 - SH2の日記
- MySQL SQLオプティマイザのコスト計算アルゴリズム - SH2の日記
- MySQL :: MySQL 5.6 Reference Manual :: 8.13.14.1 Loose Index Scan
もっとすてきなチューニング方法を見つけたら、ぜひ教えてください。