第4回 中国地方DB勉強会 in 岡山 #ChugokuDB に参加した感想、Ubuntu/DebianでMySQL5.5をapt-getでMySQL5.6にアップグレード、あるいはバルクインサートの威力の確認
第4回 中国地方DB勉強会 in 岡山 #ChugokuDB
今週の日曜日に初めて参加しました．
テーマはパフォーマンスチューニング．
ハンズオンのネタとして用いられたのはSQL逆引き大全363の極意．講師の喜田 紘介さんは著者の一人．
- 作者: アシスト
- 出版社/メーカー: 秀和システム
- 発売日: 2013/07
- メディア: 単行本
- この商品を含むブログ (2件) を見る
タイムテーブルの通り、午前MySQL、午後PostgreSQL、ハンズオンと実に濃密な一日でした．
PostgreSQLユーザ会中国支部主催となっておりますが、今後もDBにこだわらず幅広い内容で開催していくとのことでした．
ハンズオンもPostgreSQLだけでなくMySQL、Oracleにも対応しており、MySQL目当てで参加した自分にとっては非常にありがたかったです．
個人的には、この日に向けて急いでMySQLの知識を詰め込んで行ったって感じだったのですが、もちろん知らないことだらけで、その分新たに得られた情報は膨大かつ未だに整理がついていません．
MySQLのお話をいただいた山崎 由章さんはすでに公開されている以下の資料に加えて、更に2つの資料を用いて時間いっぱい話して下さったので、とてもありがたかったです．
お話では、
- EXPLAIN、パフォーマンス・スキーマの機能拡張
- MySQL 5.6のMySQL Workbenchめちゃくちゃ便利になっている
- オプティマイザの改善で5.5と比べてそもそもの性能が向上している
- 何よりリリースから17ヶ月経っており、充分枯れている
という感じで、もう5.6使うしかないな！という感じでした．
合間合間で5.6のMySQL Workbenchのデモがあったのですが、とても高機能かつ情報の見せ方に対する配慮が行き届いている感じでした．
ハンズオンのある勉強会自体初参加だったのですが、勉強会の主題以外にも細かいtipsを見ることができて、普段の利用という意味ではこちらの方がむしろ収穫としては大きかったです．
例えば、mysql -u root -p < ddl-file.sqlで一連のSQL文を実行とか、インデックスの張り方と確認方法、explainの使い方等、超基本ですが凄く勉強になりました．
正直、知識的にはほとんど突撃に近い参加だったんですが、それでも想像以上に有意義でした．
そういう意味で、全然勉強してないから内容についていけないかも、とか考えるくらいなら、とりあえず参加して自分が全然わかってないことを確認するという参加の仕方もありだと思います．
ハンズオン中、細かいトラブルがちょいちょい起こるのですが、意見出し合って数秒で解決してしまうのを目の当たりにして、ハンズオン中ずっとエンジニアかっけー！って感じでした．
突撃の勢い余って懇親会も参加しました．
何者でもない自分は特段喋ることもないので、ひたすらお話を聞いていただけでしたが、DB関連の大小様々な事情を聞けて面白かったです．
一方で、もっと質問内容とかをちゃんと用意していけばもう少しお話できたと思うのですが、扱いにくい感じになってしまって正直申し訳なかったです．
以上、感想．
感想なのに書くの難し過ぎ．PostgreSQLの話なくてごめんなさい．
以下は早速行ったUbuntu/Debianでの5.6移行作業と、ハンズオン後の追加デモで見たバルクインサートが超速くて感動したので、自分で試してみた結果のメモ．
MySQL 5.6 setup
公式にaptから入れる方法が紹介されている．
A Quick Guide to Using the MySQL APT Repository
Ubuntu 12.04とDebian Wheezyで試したが、Ubuntu14.04でも基本的に手順は同じはず．
以下は、すでにMySQL5.5をapt-getでインストール済み、かつ、すでにユーザやDBやテーブルを作ったりしてて、5.6で現在の状態を再現したい場合を想定．
設定ファイル等のバックアップ
$ sudo mkdir backup
$ sudo tar cvfvz backup/mysql_conf.tgz /etc/mysql
バックアップとリストアの方法についてはこちらを参考にした．
データベースとデータのバックアップ
$ sudo mysqldump -u root -p -A --events > backup/backup_db.sql $ sudo tar cvfvz backup/mysql_data.tgz /var/lib/mysql
MySQL5.5を削除
$ sudo service mysql stop
$ sudo apt-get remove mysql-common mysql-client mysql-server
$ sudo apt-get autoremove
$ sudo apt-get autoclean
aptのリポジトリをダウンロード
Download MySQL APT Repositoryで、Ubuntu12.04、14.04とDebian WheezyのaptリポジトリがDLできる．
各リンク先はOracleのWeb accountが必要な感じになっているが、下の方にNo thanks, just start my download.とあって、アカウントがなくてもDLできる．
$ wget https://dev.mysql.com/get/mysql-apt-config_0.2.1-1ubuntu12.04_all.deb
現時点での最新版は0.2.1だった．
リポジトリ登録
$ sudo dpkg -i mysql-apt-config_0.2.1-1ubuntu12.04_all.deb
上記を実行すると、mysql-5.6とmysql-5.7-dmrのどちらかを選ぶように支持されるので、ここではmysql-5.6を選ぶ．
MySQL 5.6 インストール
$ sudo apt-get update
$ sudo apt-get install mysql-server
rootのパスワードを聞かれるので入力する．
my.cnfをカスタムしている場合は、設定ファイルの書き換えをどうするのか聞かれる親切設計なのでそれに答える．
$ sudo apt-get install mysql-client mysql-workbench libmysqlclient-dev
必要に応じてMySQL Workbenchとか、ライブラリも入れちゃう．
バージョン確認
$ mysql --version mysql Ver 14.14 Distrib 5.6.19, for Linux (x86_64) using EditLine wrapper
お手軽．
バックアップをリストア
$ sudo mysql -u root -p < backup/backup_db.sql $ mysql_upgrade -v -u root -p
バックアップには以前のrootのパスワードも含まれているのかわからないが、5.6を入れる時に設定したrootのパスワードは5.5の時と別のパスワードにしたのに、リストアした後は5.5のパスワードを入力しないとrootでログインできない．
バルクインサートの威力を確認する
ハンズオンの中でバルクインサートの威力を確認するデモがあって、その速さに目玉が飛び出る程驚いた*1．
デモでは、400回のinsertを対象に行なっていたので、もっと回数が多いとどうなるか以下の2つのスクリプトを使って試してみた．
実行環境は、Ubuntu 12.04 LTS、Intel® Core™ i7-3770 CPU @ 3.40GHz × 8、メモリ8GB．
insertするデータは、ハンズオンのサンプルコードを流用．
単純なinsertの繰り返しと、バルクインサートのDDLを生成するスクリプト
ただ、実行時間はパスワード決め打って、timeコマンドで無理やり計測しており、平均も取っていないのであくまで参考値として見て頂ければと*2．
単にinsertを繰り返す場合
$ time mysql -u root --password=mysql < many_insert_10000.sql Warning: Using a password on the command line interface can be insecure. mysql -u root --password=mysql < many_insert_10000.sql 0.22s user 0.09s system 6% cpu 5.007 total $ time mysql -u root --password=mysql < many_insert_100000.sql Warning: Using a password on the command line interface can be insecure. mysql -u root --password=mysql < many_insert_100000.sql 2.32s user 2.16s system 9% cpu 49.324 total $ time mysql -u root --password=mysql < many_insert_1000000.sql Warning: Using a password on the command line interface can be insecure. mysql -u root --password=mysql < many_insert_1000000.sql 23.93s user 22.94s system 9% cpu 8:10.91 total
5秒、49秒、8分10秒とどんどん遅くなる．待ってられない．
バルクインサートを用いる場合
$ time mysql -u root --password=mysql < bulk_insert_10000.sql Warning: Using a password on the command line interface can be insecure. mysql -u root --password=mysql < bulk_insert_10000.sql 0.00s user 0.00s system 6% cpu 0.123 total $ time mysql -u root --password=mysql < bulk_insert_100000.sql Warning: Using a password on the command line interface can be insecure. mysql -u root --password=mysql < bulk_insert_100000.sql 0.08s user 0.00s system 7% cpu 1.010 total $ time mysql -u root --password=mysql < bulk_insert_1000000.sql Warning: Using a password on the command line interface can be insecure. mysql -u root --password=mysql < bulk_insert_1000000.sql 0.71s user 0.04s system 8% cpu 9.341 total
0.1秒、1秒、9秒と単純なinsertの繰り返しと比べてめちゃくちゃ速い！バルクインサート超速い！！
max_allowed_packetの設定
デフォルト設定のままinsert100000回分のバルクインサートの実験を行なっていると以下のエラーに見舞われた．
$ bash mk_bulk_insert.bash 100000 Enter password: ERROR 2006 (HY000) at line 3: MySQL server has gone away
3行目、すなわち、100000回分のバルクインサートでサーバがgone awayしている．
どうもmax_allowed_packetに関わるエラーで、バッファサイズが足りてないらしい．
> show variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+
上記の通りmax_allowed_packetのデフォルトが4Mになっていた．
生成したinsert100000回分のバルクインサートのDDLのファイルサイズを確認すると4.7Mだった．なるほど超えてしまっている．
/etc/mysql/my.cnf内の[mysqld]のパラメータとしてmax_allowed_packet=16Mとしてサーバ再起動
$ service mysql restart
これで無事に動いた．
上記のinsert1000000回分のバルクインサートのDDLファイルが47Mになったので、適当にmax_allowed_packet=100Mとして実験した．
バルクインサートの速さはメモリ容量とのトレードオフになっている．
ちなみに、バックアップを取るのに用いたmysqldumpが吐き出すファイルのinsertはバルクインサートになっている．
