GA

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_password と caching_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
​
投稿者 時刻: 0 件のコメント :
ラベル: ,

2018/11/08

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 あたりのみアップグレードし、ユーザーが作ったテーブルには触らない）もしくは mysqld の avoid_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を使えばちゃんと検出できるので、良い子はちゃんとこれを使ってからバージョンアップしよう。
​
投稿者 時刻: 0 件のコメント :
ラベル: ,

2018/11/06

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)
​
投稿者 時刻: 0 件のコメント :
ラベル: ,

2018/11/01

ER_OPTION_PREVENTS_STATEMENT(Error: 1290) The MySQL server is running with the .. について 

$ perror 1290
MySQL error code MY-001290 (ER_OPTION_PREVENTS_STATEMENT): The MySQL server is running with the %s option so it cannot execute this statement
要は、「 %s だからそのSQLは実行できないよ」というエラー。
%s の部分に何が入るかは何パターンかあるけど、基本的にオプションの名前が入るので、SQLを成功させたければ %s をOFFにしてやれば上手くいくはず。
MySQL 5.7.24 のコードから ER_OPTION_PREVENTS_STATEMENT を投げるところを引いてみた感じ、あり得る %s のパターンはたぶんこう。
おそらくここまでがよくあるやつら。
ここからはコードをさらって見つけた変なやつら。
  • “—event-scheduler=DISABLED or —skip-grant-tables”
    • これ全部で1区切り
    • 実は event_scheduler は ON, OFF, DISABLED の3通りを取ることができて、 DISABLED で起動すると SET GLOBAL でONにできなくなる（それをやろうとするとこれが出る）
    • skip_grant_tables でもイベントスケジューラーいじれないのね（知らなかった）
  • embedded
    • 組み込み用mysqldである libmysqld を使っている場合に行ベースのバイナリーログイベント（ mysqlbinlog でデコードしたものを含む）を食わせると発生
    • 初めて知った…
あー面白かった。
​
投稿者 時刻: 0 件のコメント :
ラベル:
登録: 投稿 ( Atom )