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秒間ずっとバーストしたままとなっていました。他のパターンのグラフをいくつか載せておきます。
ファイルサイズが1GBの場合は、バーストが継続しています。
書き込みの場合は、ファイルサイズが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
もっとすてきなチューニング方法を見つけたら、ぜひ教えてください。
チューニンガソン5の復習 MySQL 5.5 チート編＆勉強会のお知らせ
ゼロスタートの@zakiさんを中心に企画、運営されているチューニンガソンというイベントがあります。指定された環境を用いてOSやサーバ周りのチューニングを行い、性能を競うというものです。2013年1月に行われた第5回ではMySQLがテーマだったということで、本日はこれの復習をしていこうと思います。
- 【レポート】いろいろチューニングしてパフォーマンスを競うバトルイベント！「Tuningathon」第5弾！ #tuningathon : ゼロスタートの広報ブログ
- 2013年最初のチューニンガソンはMySQL！――第5弾！ いろいろチューニングしてパフォーマンスを競うバトルイベント「チューニンガソン」レポート：レポート｜gihyo.jp … 技術評論社
- Tuningathon#5 - Togetter
しばらく真面目にチューニングしていたのですが思いのほか難しかったので、今回はちょっとズルをして優勝タイムを出すところまでご紹介します。
計測条件
Dropboxでレギュレーションが公開されています。
また、イベントで用いられたものと同じAmazon Machine Imageが公開されています。
しかし従量課金のm1.xlargeで作業していると、正直私は焦りを隠せません。落ち着いて作業をするために、まずは自宅のPCへ引っ越しをしました。
- データベースのダンプファイル tuningathon5.sql.gz (116MB)
- MySQLの設定ファイル my.cnf
- 計測プログラムの改変版 tgbench_mysql_2.py
計測プログラムのオリジナル版には、結果を集計サーバにPOSTする機能があります。しかしもう集計サーバはありませんので、改変版ではPOST機能を取り除いてあります。またSQLとログを少し見やすく整形しています。@zakiさんに改変版の頒布許可をいただきました。どうもありがとうございます。
データベースはMediaWikiのもので、Wikipedia日本語版が格納されています。
CREATE TABLE `page` ( `page_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `page_namespace` int(11) NOT NULL, `page_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `page_restrictions` tinyblob NOT NULL, `page_counter` bigint(20) unsigned NOT NULL DEFAULT '0', `page_is_redirect` tinyint(3) unsigned NOT NULL DEFAULT '0', `page_is_new` tinyint(3) unsigned NOT NULL DEFAULT '0', `page_random` double unsigned NOT NULL, `page_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `page_latest` int(10) unsigned NOT NULL, `page_len` int(10) unsigned NOT NULL, PRIMARY KEY (`page_id`), UNIQUE KEY `name_title` (`page_namespace`,`page_title`), KEY `page_random` (`page_random`), KEY `page_len` (`page_len`), KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`) ) ENGINE=InnoDB AUTO_INCREMENT=2704666 DEFAULT CHARSET=utf8; CREATE TABLE `revision` ( `rev_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `rev_page` int(10) unsigned NOT NULL, `rev_text_id` int(10) unsigned NOT NULL, `rev_comment` tinyblob NOT NULL, `rev_user` int(10) unsigned NOT NULL DEFAULT '0', `rev_user_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `rev_timestamp` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `rev_minor_edit` tinyint(3) unsigned NOT NULL DEFAULT '0', `rev_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0', `rev_len` int(10) unsigned DEFAULT NULL, `rev_parent_id` int(10) unsigned DEFAULT NULL, `rev_sha1` varbinary(32) NOT NULL DEFAULT '', PRIMARY KEY (`rev_id`), UNIQUE KEY `rev_page_id` (`rev_page`,`rev_id`), KEY `rev_timestamp` (`rev_timestamp`), KEY `page_timestamp` (`rev_page`,`rev_timestamp`), KEY `user_timestamp` (`rev_user`,`rev_timestamp`), KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`), KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`) ) ENGINE=InnoDB AUTO_INCREMENT=45658130 DEFAULT CHARSET=utf8 MAX_ROWS=10000000 AVG_ROW_LENGTH=1024;
mysql> SELECT * -> FROM information_schema.tables -> WHERE table_schema = 'wikipedia' -> AND table_name IN ('page', 'revision')\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: wikipedia TABLE_NAME: page TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Compact TABLE_ROWS: 1670757 AVG_ROW_LENGTH: 98 DATA_LENGTH: 164265984 (156.7MB) MAX_DATA_LENGTH: 0 INDEX_LENGTH: 207159296 (197.6MB) DATA_FREE: 85983232 AUTO_INCREMENT: 2704666 CREATE_TIME: 2013-03-03 20:54:20 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8_general_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: wikipedia TABLE_NAME: revision TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Compact TABLE_ROWS: 1599526 AVG_ROW_LENGTH: 136 DATA_LENGTH: 217808896 (207.7MB) MAX_DATA_LENGTH: 0 INDEX_LENGTH: 431144960 (411.2MB) DATA_FREE: 85983232 AUTO_INCREMENT: 45658130 CREATE_TIME: 2013-03-03 20:55:53 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8_general_ci CHECKSUM: NULL CREATE_OPTIONS: max_rows=10000000 avg_row_length=1024 TABLE_COMMENT:
MySQLの設定ファイルはRed Hat Enterprise Linuxバンドル版のmy.cnfを基本として、キャラクタセットのみ追加指定した状態です。チューニングはまったく施されていません。
[mysqld] # Settings user and group are ignored when systemd is used. # If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-server=utf8 [mysql] default-character-set=utf8
計測プログラムが実行するクエリは以下の6種類です。これらが1回ずつ順番に実行されます。
RESET /* Q0 */ QUERY CACHE; SELECT /* Q1 */ COUNT(*) FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 AND r.rev_user = 0; SELECT /* Q2 */ COUNT(*) FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 AND r.rev_user = 0 AND p.page_touched > DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y0101000000'); SELECT /* Q3 */ p.page_id FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 AND r.rev_user = 0 ORDER BY p.page_touched DESC LIMIT 10; SELECT /* Q4 */ r.rev_user, COUNT(*) AS c FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 GROUP BY r.rev_user ORDER BY c DESC; SELECT /* Q5 */ SUBSTRING(r.rev_timestamp, 1, 6), COUNT(*) AS c FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 GROUP BY SUBSTRING(r.rev_timestamp, 1, 6) ORDER BY c DESC;
ベースライン
手元のPCを使用して、まずはそのまま実行してみます。
- CPU：Intel Core i5-2400S (Quad-Core、2.50GHz、Max 3.30GHz)
- Host OS：Scientific Linux 6.3 x86_64
- Guest OS：Scientific Linux 6.3 x86_64
- Guest CPU Allocation：2 Cores
- Guest Memory Allocation：2 GB
$ python tgbench_mysql_2.py 127.0.0.1 Q0:0.00018 Q1:5.97537 Q2:4.66229 Q3:4.59017 Q4:14.48054 Q5:14.30720 Total:43.97575
1回目は44.0秒、2回目は41.1秒となりました。2回目はOSのページキャッシュ、InnoDBのバッファプールにデータがキャッシュされるため性能が向上します。クエリの単体性能を計測する際は、2回ずつ実施することをおすすめします。
当日は140秒程度の結果が出ていたそうで、m1.xlargeに比べるとCore i5-2400Sの方が3倍程度速いということになります。m1.xlargeはXeon E5645でSPECint_base2006は36.4、一方Core i5-2400SのSPECint_base2006は36.0とCPUコアあたりの性能にはほとんど違いがないのですが、EC2の方で何らかの制限が設けられているのだと考えられます。
チューニングその1：InnoDBバッファプールの拡大
計測プログラムが実行するクエリはpageテーブルとrevisionテーブルのみにアクセスします。pageテーブルのサイズは354.2MB、revisionテーブルのサイズは618.9MBということで、まずはこれらのテーブルがすべて収まるだけのメモリを割り当てることがチューニングの第一歩になるかと思います。
[mysqld] # Settings user and group are ignored when systemd is used. # If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 innodb_buffer_pool_size=1G
8.8秒になりました。m1.xlargeの場合は、これで17秒程度になったようです。
チューニングその2：RESET QUERY CACHEの回避
クエリ0番にRESET QUERY CACHEがあるのを見てしまうと、やはりこれを回避したいと思うのが人情ではないでしょうか。本番で成功した方はいなかったのですが、回避の可能性には数名の方が気づいていたようです。
MySQL Proxyとは、MySQLの通信プロトコルを理解しMySQLクライアントとMySQLサーバとの通信を横取りしてさまざまな操作を行うサーバプログラムです。Luaスクリプトによって処理内容を定義し、ロードバランサとして使用したりクエリのプロファイリングを行ったりすることが想定されています。ライセンスはGPLv2です。なお長い間アルファ版のまま開発が進んでいないため、本番環境で使用することはおすすめしません。
-- tuningathon5_q0.lua -- Q0: Ignoring 'RESET QUERY CACHE' function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local query = string.sub(packet, 2) if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then print("[HIT Q0]") proxy.queries:reset() proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE") return proxy.PROXY_SEND_QUERY end end end
このLuaスクリプトで、RESET QUERY CACHEをFLUSH QUERY CACHEに書き換えてしまいます。RESET QUERY CACHEはクエリキャッシュを削除するコマンド、一方FLUSH QUERY CACHEはクエリキャッシュをデフラグするだけのコマンドです。これら二つのコマンドは処理が成功した際の戻りのパケットが同一ですので、クエリを書き換えていることはそう簡単には発覚しないはずです。
[mysqld] # Settings user and group are ignored when systemd is used. # If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 innodb_buffer_pool_size=1G port=3307 query_cache_size=1M
MySQLサーバのTCP待ち受けポートを変更し、クエリキャッシュの設定を施しておきます。今回のクエリに対しては、クエリキャッシュは1MBあれば足ります。MySQL Proxyを以下のオプションで起動し、MySQLクライアントから送られてくるクエリをMySQL Proxyで中継してMySQLサーバに渡すようにします。
$ /PATH/TO/mysql-proxy --proxy-address=:3306 \ --proxy-backend-addresses=:3307 \ --proxy-lua-script=tuningathon5_q0.lua
1.0秒になりました。優勝タイムは6.2秒とのことで、環境の違いを加味してもこの時点で上回ることができたのではないかと思います。
チューニングその3：クエリ1番の修正
RESET QUERY CACHEを回避することでクエリ3〜5番は速くなったのですが、実はクエリ1番と2番が速くなっていません。
mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 107 | +---------------+-------+ 1 row in set (0.00 sec) (計測プログラム実行) mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 110 | +---------------+-------+ 1 row in set (0.00 sec)
計測プログラムの前後でクエリキャッシュの状態を確認すると、3回しかクエリキャッシュにヒットしていないことが分かります。これはクエリ3〜5番の分です。なぜクエリ1番と2番はクエリキャッシュにヒットしないのでしょうか。まずクエリ1番から確認していきましょう。
mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 110 | +---------------+-------+ 1 row in set (0.00 sec) mysql> SELECT /* Q1 */ COUNT(*) -> FROM page p -> JOIN revision r -> ON p.page_id = r.rev_page -> WHERE p.page_is_redirect = 0 -> AND p.page_namespace = 0 -> AND r.rev_user = 0; +----------+ | COUNT(*) | +----------+ | 273537 | +----------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 111 | +---------------+-------+ 1 row in set (0.00 sec)
クエリ1番を手打ちすると、きちんとクエリキャッシュにヒットしていることが分かります。原因を突き止めることはなかなか難しいと思いますが、これは、残念ながらMySQLの不具合です。
AUTOCOMMITがOFFの場合に、トランザクション開始前の最初のクエリがクエリキャッシュにヒットしないというものです。今も直っていません。PythonのMySQLdbモジュールはPython Database API Specification v2.0にもとづきデフォルトでAUTOCOMMITがOFFになっており、この不具合を必ず踏みます。ワークアラウンドとして「START TRANSACTIONで明示的にトランザクションを開始しておく」という方法がありますので、先ほどのLuaスクリプトを以下のように修正します。
-- tuningathon5_q0q1.lua -- Q0: Ignoring 'RESET QUERY CACHE' -- Q1: Workaround for Bug#42197 function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local query = string.sub(packet, 2) if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then print("[HIT Q0]") proxy.queries:reset() proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE") proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "START /* Q1_workaround */ TRANSACTION", { resultset_is_needed = true }) return proxy.PROXY_SEND_QUERY end end end function read_query_result(inj) if inj.id == 2 then return proxy.PROXY_IGNORE_RESULT end end
クエリ0番を実行した直後にSTART TRANSACTIONを実行します。その際START TRANSACTIONの結果をそのままクライアントに返すと計測プログラムが誤動作してしまいますので、read_query_result()ファンクションで結果を握りつぶしています。
0.56秒になりました。グラフでオレンジ色の部分がなくなったことが分かると思います。
チューニングその4：クエリ2番の修正
クエリ2番がクエリキャッシュにヒットしない理由は明快で、NOW()ファンクションを使用しているためです。
SELECT /* Q2 */ COUNT(*) FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 AND r.rev_user = 0 AND p.page_touched > DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y0101000000');
マニュアルに、クエリキャッシュにヒットしなくなるファンクションの一覧が記載されています。
このクエリをどうやって直すかですが、あらためてDATE_FORMAT()ファンクションのところをよく見ると、出力結果が年月日のうち年にしか依存していないことが分かると思います。ここの部分は2013年1月1日〜12月31日までは「20120101000000」という文字列を、2014年1月1日〜12月31日までは「20130101000000」という文字列を返します。このくらいでしたらLuaスクリプトで同じ文字列を返すようにできます。
-- tuningathon5_q0q1q2.lua -- Q0: Ignoring 'RESET QUERY CACHE' -- Q1: Workaround for Bug#42197 -- Q2: Avoiding NOW() function function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local query = string.sub(packet, 2) if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then print("[HIT Q0]") proxy.queries:reset() proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE") proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "START /* Q1_workaround */ TRANSACTION", { resultset_is_needed = true }) return proxy.PROXY_SEND_QUERY elseif string.find(query, "^%s*SELECT /%* Q2 %*/ COUNT%(%*%)%s*" .. "FROM page p%s*" .. "JOIN revision r%s*ON p%.page_id = r%.rev_page%s*" .. "WHERE p%.page_is_redirect = 0%s*" .. "AND p%.page_namespace = 0%s*" .. "AND r%.rev_user = 0%s*" .. "AND p%.page_touched > DATE_FORMAT%(NOW%(%) %- INTERVAL 1 YEAR, '%%Y0101000000'%)%s*$") then print("[HIT Q2]") proxy.queries:reset() proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "SELECT /* Q2_rewrite */ COUNT(*) " .. "FROM page p " .. "JOIN revision r ON p.page_id = r.rev_page " .. "WHERE p.page_is_redirect = 0 " .. "AND p.page_namespace = 0 " .. "AND r.rev_user = 0 " .. "AND p.page_touched > '" .. os.date('%Y') - 1 .. "0101000000'") return proxy.PROXY_SEND_QUERY end end end function read_query_result(inj) if inj.id == 2 then return proxy.PROXY_IGNORE_RESULT end end
0.067秒になりました。このグラフでは何も見えなくなりました。拡大してみましょう。
最終的にクエリ4番が処理時間の大半を占めるようになりました。これは、クエリ4番の結果セットが39,434レコードと最も大きいためです。
勉強会のお知らせ
MySQL Proxyを使用してクエリキャッシュを無理やり効かせるという今回のチューニング方針ですが、MySQL Proxyがアルファ版であること、クエリキャッシュはCPUスケーラビリティが悪く最近は使われない傾向であることを考慮すると、実際の業務に応用することは難しくあまり筋が良いチューニング方針だとは言えません。
本エントリ執筆時点でまだ上手くできていないのですが、クエリキャッシュをOFFに戻して、ついでにMySQLを5.6にバージョンアップしてどこまで改善できるのかを調べています。調査結果について、今月久しぶりに開催されるMySQLユーザ会のイベントでご報告したいと思います。
- MyNA(日本MySQLユーザ会)会 2013年3月 : ATND
- 日時：2013年03月15日(金) 18:30〜21:00
- 場所：日本オラクル 青山センター
