2018年12月6日木曜日

マイエスキューエルにはPerl Mongerが必要かもしれないはなし

この記事は


みなさんは innotop をご存知でしょうか?
そう、 SHOW PROCESSLISTSHOW ENGINE INNODB STATUS の結果を top ライクに表示してくれる、みんなだいすき Perlで書かれた スクリプトです。
わたしはこのツールが随分気に入っていて、今までも何度もブログを書いていました。
息してないなーエントリの時もTwitterでちょくちょく「使ってたのに」的な反応があったので、案外(?)使っている人も多いのかも知れません。
最初は2012年だから本当にMySQLを真面目にやり始めた頃、MySQL 5.7対応でプルリクを出したりしたのは2016年だったんですね。今年はMySQL 8.0対応の Pull-Request を書きました。これでinnotopはMySQL 8.0でも使えます。安心ですね。
さて、ここ数年のinnotopへのコントリビュートを経て思ったことが、「マイエスキューエルにはPerl Mongerが必要かもしれない」ということです。
まずこの スクリプト は「如何にも昔から動き今も動くPerlスクリプト」という感じで、1ファイルで1万2千行以上、ファイルサイズも447kBもあります。
たとえばセマフォの情報を示すセクションをパースしているコードはこんな感じになっていて、
Perl Mongerでない限り心が折れてしまうんではないでしょうか。
ちなみにPerl Mongerでも心は折れると思います。俺は何度か折れた。
加えて、このバッファプールのセクションをパースしているあたりなんかは、Perlが問題なく読めたとしても「そもそも SHOW ENGINE INNODB STATUS が読めないとこれで合ってるのかどうか判断できない」というなかなかアレな状態です(新しいバージョンが出た時に自分自身でパースできないとテストケースの作りようがなくなるのでな…)
という訳で現在、innotopにパッチを当てられるのはおおむね (“Perl Mongerである” || “Perl Mongerではないが心が強い”) && “InnoDBと友達” な人に限られるのではないでしょうか…と言いつつ、OSSにパッチを投稿するには限りなく

自分が使っている = 不具合は自分に不都合

というモチベーションが必要です。
Perl MongerではないけどPerlが好きで、InnoDBと友達ではないけれど言いたいことはわかって、innotopをガシガシ本番で使っている俺がこのソフトウェアにパッチを当てるようになったのは自然なことなのかも知れません。

自分で使っているのを差っ引いても、innotopはおおむね便利なのでみなさまも使っていただけると(俺ではなくて中の人の)励みになると思います。


ちなみにここまでの文書、 s/innotop/pt-query-digest/g とか s/innotop/MHA for MySQL/g とかにしても似たようなことが言えます。

Perl製のMySQLのツールはそれなりにあるので、PerlでMySQLな人は そのツールが消えてなくなる前に パッチなどご協力いただければ幸いです。





( ´-`).oO(レプリケーションと友達なPerl Mongerが捕まらなかったのか、使っているユーザーがいなかったのか、 MMM for MySQL …いやううんなんでもない

明日の

です。
明日もおたのしみに。

2018年12月4日火曜日

ConoHaの上でひたすらMySQLをビルドする簡単なお仕事 in 2018


一昨年去年 とひたすらMySQLをビルドしてきましたが、今年もやっぱりMySQLをビルドします。
今年はもうサポートの切れたMySQL 5.0, 5.1のことは忘れることにして、5.5, 5.6, 5.7, 8.0をそれぞれビルドしていきたいと思います。
ではまず、普段使いと同じサイズの 1GBプラン のCentOS 7.5なインスタンスをコントロールパネルからポチって起動します。
今年はビルドに必要そうなものは先にインストールすることにしました。
$ sudo yum install -y cmake gcc gcc-c++ ncurses-devel openssl-devel
ではまずは MySQL 5.5.62 からいきます。
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.62.tar.gz
$ tar xf mysql-5.5.62.tar.gz
$ cd mysql-5.5.62/
$ cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql/5.5.62
$ time make
..
real    6m36.723s
user    5m56.500s
sys     0m39.155s

$ sudo make install
はいさくさくとMySQL 5.6.42です。
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.42.tar.gz
$ tar xf mysql-5.6.42.tar.gz
$ cd mysql-5.6.42/
$ cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql/5.6.42
$ time make
..
real    20m41.700s
user    18m43.041s
sys     1m51.097s
5.6のビルドが思ったより時間がかかっていたので、もう1つインスタンスを立ち上げて5.7.24のビルドを始めました。
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.24.tar.gz
$ tar xf mysql-boost-5.7.24.tar.gz
$ cd mysql-5.7.24/
$ cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql/5.7.24 -DWITH_BOOST=./boost
$ time make
..
real    48m57.496s
user    41m11.879s
sys     4m41.679s
せっかちなので更にもう1つ立ち上げて8.0.13のビルドを始めます()
$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.13.tar.gz
$ tar xf mysql-boost-8.0.13.tar.gz
$ cd mysql-8.0.13/
$ cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql/8.0.13 -DWITH_BOOST=./boost
$ time make
..
real    91m43.661s
user    86m0.743s
sys     10m45.967s
だいぶ厳しくなってはきましたが、取り敢えず有限時間では終わるようです(ただし、他のプロセスはほとんど終了させないとダメな場合も)
ものは試し、ConoHaのメモリ1GBプランだと2CPU使えるので、 time make -j2 バージョンも試してみましたが、5.7と8.0はOOMに殺されてビルドできず…。
次のプランの2GB/3CPUでも3パラだとビルドできずに2パラでなんとか、でした。
もうこうなったら最強のインスタンスを試すしかないかと思ったんですが、
だったので、代わりに c3.8xlarge なEC2で試したみた結果。
メモリ CPU指定 5.5.62 5.6.42 5.7.24 8.0.13
1G 1 6m36.723s 20m41.700s 48m57.496s 91m43.661s
1G 2 3m12.228s 9m52.848s N/A N/A
2G 2 未計測 未計測 24m13.637s 42m55.905s
2G 3 2m19.953s 7m7.850s N/A N/A
60G(*) 32(*) 0m35.651s 1m33.203s 3m56.580s 7m8.324s
(*) よく見たら、ストレージがマグネティックだった。ConoHaはSSD。
強いインスタンスは正義ですね…。
ビルド後のサイズはこんなもので、やっぱり年々大きくなってはきている。ストレージのサイズ的にもやっぱり強いインスタンスが正義……。
$ du -sh * ### ビルドのディレクトリー
838M    mysql-5.5.62
2.3G    mysql-5.6.42
5.3G    mysql-5.7.24
8.2G    mysql-8.0.13

$ du -sh /opt/mysql/* ### インストール先のディレクトリー
341M    /opt/mysql/5.5.62
998M    /opt/mysql/5.6.42
1.9G    /opt/mysql/5.7.24
1.6G    /opt/mysql/8.0.13

明日の ConoHa Advent Calendar 2018misato8310 さん、
明日の MySQL Casual Advent Calendar 2018 はまだ空いているのでまた俺かも知れませんし俺でない誰かかも知れません。

2018年12月1日土曜日

スローログ可視化ツールのラッパー、 anemoeater のこの1年を振り返る

この記事は MySQL Casual Advent Calendar 2018 の1日目のエントリーです。

TL;DR


スローログを食わせてグラフ化する box/Anemometer というのがありますが、それ単体だとちょっと使いづらいよね、と思ってそれをガシガシ起動したり停止したりするための anemoeater というのを書いています。
@i_rethi さんは使ってくれてるって言ってた。
あと、今年一年いくつもPull Requestをくれた yokogawa-k さんも使ってくれてるのかな、と思っています。
Dockerとcpanmさえインストールされていればサクッとスローログを食わせてグラフを見て、要らなくなったらすぐに消す…みたいな運用をするためのラッパーです。詳しくはREADME が日本語なので是非そちらをご覧ください。
今年のアップデートは以下の通り。
まだまだ自分で使う機会も多いスクリプトなので、コードをいただいたりヒントをいただけたりしてとてもありがたいです!
何故か、WatchしているのにGitHubから通知が来ないことがしばしば…ごめんなさい(´;ω;`) < なんだろうこれ
Twitterとかでメンションをいただければ気が付くのが早くなるかも知れません…。

それでは、良いSQLチューニングライフを!

2018年11月12日月曜日

mysql_native_password, caching_sha2_password の速度測り比べ

TL;DR


  • まずは mysql_native_password, caching_sha2_password, sha256_password それぞれを指定したアカウントを作ります。
CREATE USER yoku0825_native_password IDENTIFIED WITH mysql_native_password BY 'a';
CREATE USER yoku0825_caching_sha2 IDENTIFIED WITH caching_sha2_password BY 'a';
CREATE USER yoku0825_sha256 IDENTIFIED WITH sha256_password BY 'a';
  • 別のマシンから1000回接続してquitするのにかかった時間の平均値を出します。それを5回繰り返して最大値と最小値を捨て、残った3回分の平均を取ります。
$ for n in $(seq 1 5) ; do time for m in $(seq 1 1000) ; do  MYSQL_PWD="a" mysql -h 172.31.32.165 -uyoku0825_native_password -e "quit"; done ; done
接続オプション mysql_native_password caching_sha2_password sha256
非SSL 7.8ms 7.5ms x
SSL 34.8ms 35.0ms 38.1ms
非SSL + サーバーの公開鍵 7.6ms 7.4ms 11.2ms
値はそこまでアテにしないとして、傾向としては
  • mysql_native_passwordcaching_sha2_password はそこまで大きな違いはない(個人によって感想は異なります)
  • やはり暗号化しないと速い
  • 非SSLで sha256_password を使うとかなりオーバーヘッドが見える
リモートから公開鍵を指定する MYSQL_SERVER_PUBLIC_KEY は少なくともMySQL 8.0のConnector/Cには mysql_options に実装されてるけど、それ以外のコネクターでは使えるのかどうかわからない。
と考えると、平文の速さが欲しければ mysql_native_password が安牌、SSLに切り替えるよって場合は caching_sha2_password でいいやってことになるかしらん。
【2018/11/14 15:22】
ご指摘をいただいたので追試。 @methane = サンありがとうございます。




mysqld はグリーティングの時に default_authentication_plugin のつもりでソルトを送り、そのプラグインを使っていないユーザーを指定された場合はそのプラグイン用のソルトを送りなおす = AuthSwitchRequest の1往復分
以下、全てSSLの場合。
caching_sha2 & default: caching_sha2 -> 34.6646666666667
caching_sha2 & default: native -> 35.148
native & default: caching_sha2 ->35.1036666666667
native & default: native -> 35.0216666666667

2018年11月8日木曜日

MySQL 5.5とそれ以前の古いDATETIME型が残っているとMySQL 8.0が起動できない

mysqldが起動できないバグなのか、「 avoid_temporal_upgrade なんてことすると将来的にサポート対象外だよ」がドキュメントに記載されてないのかどっちだか迷っている。

MySQL 5.6からDATETIME型のデータ構造が新しくなった。
MySQL 5.5とそれ以前で CREATE TABLE したテーブルは8バイトの旧DATETIME型、MySQL 5.6とそれ以降で CREATE TABLE したテーブルは5バイトの新DATETIME型で、SQL上の扱いには互換性があり、これらを区別することはあんまりできない(切り替えるオプションもない)
CREATE TABLE した時のバージョンで決まるので、 mysqldump を取ってリストアした時はリストアした時のバージョンのDATETIME型を使う。
MySQL 5.5で CREATE TABLE しても、ダンプを取って5.6にリストアすると5.6の中で CREATE TABLE されるので、リストア後のデータは新DATETIME型になる。
なので、表題のようなことは「MySQL 5.5またはそれ以前からインプレースアップグレードのみで8.0に到達しようとしているインスタンス」でしか起こらない。
更に、MySQL 5.7の mysql_upgrade の中では、旧DATETIME型を新DATETIME型に書き換える処理が入っているので、これを素直にやっている場合も表題のケースには到達しない。
MySQL 5.7にインプレースアップグレードした時、 mysql_upgrade でこの新旧DATETIMEの変換処理をスキップさせている場合にタイトルのケースに引っ掛かる。
思い付く限りでは、 mysql_upgrade --upgrade-system-tables ( mysql, performance_schema, sys あたりのみアップグレードし、ユーザーが作ったテーブルには触らない)もしくは mysqldavoid_temporal_upgrade オプションを有効にしている場合、くらいか。
エラーログに大した情報もなくいきなり「データディクショナリー(= DD)への引っ越しに失敗した。大佐、中断する( ーдー´)」と言われてmysqldが起動しないので、Table upgrade required. Please do “REPAIR TABLE ..” or dump/reload to fix it!` もへったくれもないのである。
2018-11-06T07:58:54.851647Z 0 [System] [MY-010116] [Server] /usr/mysql/8.0.13/bin/mysqld (mysqld 8.0.13) starting as process 1585
2018-11-06T07:58:58.491844Z 2 [ERROR] [MY-010923] [Server] Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
2018-11-06T07:58:59.067735Z 2 [Warning] [MY-010772] [Server] db.opt file not found for test database. Using default Character set.
2018-11-06T07:58:59.424516Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2018-11-06T07:58:59.424562Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-11-06T07:59:00.712682Z 0 [System] [MY-010910] [Server] /usr/mysql/8.0.13/bin/mysqld: Shutdown complete (mysqld 8.0.13)  Source distribution.
mysqld には —no-dd-upgrade なんていう如何にもこれを回避できそうなオプションがあるんだけど、これは「データディクショナリーのアップグレード後に、必要なら再起動を行うのがデフォルトだけどそれをしない」ためのオプションらしく、これを加えてもやっぱり同じエラーで止まった。
なのでワークアラウンドは5.7で起動しなおして、 ALTER TABLE .. FORCE で新旧DATETIME型の返還を走らせるなり mysqldump とリストアの手順で8.0にアップグレードするなり…といったところか。
ちなみに、MySQL 5.7にアップグレードしていないdatadirをそのままインプレースアップグレードで8.0にしようとすると、「5.7じゃないよ!」というエラーで落ちるのでこっちはわかりやすい。
旧DATETIME型のカラムがあるかどうかはMySQL ShellのUpgrade Checkerを使えばちゃんと検出できるので、良い子はちゃんとこれを使ってからバージョンアップしよう。

2018年11月6日火曜日

MySQL 8.0では「GROUP BYによる暗黙のソート」がされなくなるよ

MySQL 5.7の時点で既に宣言はされていた。
GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators), but relying on implicit GROUP BY sorting in MySQL 5.7 is deprecated.
どういうことが起こるかというと、こう。
$ perl -ML -E 'for (my $n= 1; $n <= 10; $n++) { for (my $m= 1; $m <= 100; $m++) { printf("%d\t%d\n", $n, $m) } }' | sort -R > /tmp/seq

mysql80 165> CREATE TABLE t1 (n int, m int);
Query OK, 0 rows affected (0.05 sec)

mysql80 165> LOAD DATA INFILE '/tmp/seq' INTO TABLE t1;
Query OK, 1000 rows affected (0.09 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

mysql80 165> SELECT * FROM t1 LIMIT 10;
+------+------+
| n    | m    |
+------+------+
|    2 |   18 |
|    4 |    7 |
|    6 |   85 |
|    1 |   69 |
|    4 |   66 |
|    7 |   52 |
|    3 |   25 |
|    2 |   19 |
|    1 |   61 |
|    5 |   80 |
+------+------+
10 rows in set (0.01 sec)
こんなデータに対して、 SELECT n, COUNT(m) FROM t1 GROUP BY n とか打つ。
今まではこうだった。
mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n;
+------+----------+
| n    | COUNT(m) |
+------+----------+
|    1 |      100 |
|    2 |      100 |
|    3 |      100 |
|    4 |      100 |
|    5 |      100 |
|    6 |      100 |
|    7 |      100 |
|    8 |      100 |
|    9 |      100 |
|   10 |      100 |
+------+----------+
10 rows in set (0.00 sec)
これからはこうなる。
mysql80 165> SELECT n, COUNT(m) FROM t1 GROUP BY n;
+------+----------+
| n    | COUNT(m) |
+------+----------+
|    2 |      100 |
|    4 |      100 |
|    6 |      100 |
|    1 |      100 |
|    7 |      100 |
|    3 |      100 |
|    5 |      100 |
|   10 |      100 |
|    9 |      100 |
|    8 |      100 |
+------+----------+
10 rows in set (0.00 sec)
よく見ると n は「見つかった順」にはなっているから、 KEY (n) を追加してそのインデックスを使わせて GROUP BY n すれば n でソート済にはなるんだけど、オプティマイザーのご機嫌とか伺いながらになるようなそんな手は選べない。
おとなしく ORDER BY を足しましょう。
mysql80 165> SELECT n, COUNT(m) FROM t1 GROUP BY n ORDER BY n;
+------+----------+
| n    | COUNT(m) |
+------+----------+
|    1 |      100 |
|    2 |      100 |
|    3 |      100 |
|    4 |      100 |
|    5 |      100 |
|    6 |      100 |
|    7 |      100 |
|    8 |      100 |
|    9 |      100 |
|   10 |      100 |
+------+----------+
10 rows in set (0.00 sec)