2018年12月7日金曜日

MySQL 8.0でレスポンスタイムのヒストグラムが見れるようになった

このエントリーはMySQL Casual Advent Calendar 2018の7日目です。
自分は筆が遅く、11月から準備しようしよう、と思ってたにも関わらず、結局、直前になって書いてます。。。

さて、MySQL 8.0になって、performance_schemaが15個増えてました。

5.7では、87個。

mysql> select count(*) from information_schema.TABLES where TABLE_SCHEMA = 'performance_schema';
+----------+
| count(*) |
+----------+
|       87 |
+----------+
1 row in set (0.02 sec)
8.0では102個。
mysql> select count(*) from information_schema.TABLES where TABLE_SCHEMA = 'performance_schema';
+----------+
| count(*) |
+----------+
|      102 |
+----------+
1 row in set (0.04 sec)

一番気になったのが events_statements_histogram_globalです。 このテーブルはクエリのレスポンスタイムの分布(ヒストグラム)を確認することができます。

mysql> SELECT * FROM events_statements_histogram_global;
+---------------+------------------+-------------------+--------------+------------------------+-----------------+
| BUCKET_NUMBER | BUCKET_TIMER_LOW | BUCKET_TIMER_HIGH | COUNT_BUCKET | COUNT_BUCKET_AND_LOWER | BUCKET_QUANTILE |
+---------------+------------------+-------------------+--------------+------------------------+-----------------+
|             0 |                0 |          10000000 |            0 |                      0 |        0.000000 |
|             1 |         10000000 |          10471285 |            0 |                      0 |        0.000000 |
|             2 |         10471285 |          10964781 |            0 |                      0 |        0.000000 |
|             3 |         10964781 |          11481536 |            0 |                      0 |        0.000000 |
|             4 |         11481536 |          12022644 |            0 |                      0 |        0.000000 |
---省略---
|            80 |        380189396 |         398107170 |            0 |                     17 |        0.127820 |
|            81 |        398107170 |         416869383 |            0 |                     17 |        0.127820 |
|            82 |        416869383 |         436515832 |            5 |                     22 |        0.165414 |
|            83 |        436515832 |         457088189 |            4 |                     26 |        0.195489 |
|            84 |        457088189 |         478630092 |            4 |                     30 |        0.225564 |
|            85 |        478630092 |         501187233 |            5 |                     35 |        0.263158 |
|            86 |        501187233 |         524807460 |            8 |                     43 |        0.323308 |
|            87 |        524807460 |         549540873 |            6 |                     49 |        0.368421 |
|            88 |        549540873 |         575439937 |           11 |                     60 |        0.451128 |
|            89 |        575439937 |         602559586 |            6 |                     66 |        0.496241 |
---省略---
例えば、この結果の下から2行目のBUCKET_NUMERが88の行だと、実行時間が、549540873 ピコ秒より大きく、575439937 ピコ秒以下のクエリが11回あったことがわかります。
COUNT_BUCKET_AND_LOWERは該当のバケットより実行時間が短かったクエリの合計。
549540873以下の実行時間だったクエリが60回ということ。

このテーブルをモニタリングしていくと、こういうグラフを作れる。

※PMMの同じ機能のグラフを拝借。グラフを作るのが面倒だったので・・・


べんり!!

各バケットの間隔(factor)はソースコードにハードコードされています。

/**
  Bucket factor.
  histogram_timer[i+1] = BUCKET_BASE_FACTOR * histogram_timer[i]
  The value is chosen so that BUCKET_BASE_FACTOR ^ 50 = 10,
  which corresponds to a 4.7 percent increase for each bucket,
  or a power of 10 increase for 50 buckets.
*/
#define BUCKET_BASE_FACTOR 1.0471285480508996
コメントに書いてあるように、50バケットごとに約10倍になるようなfactorになっている。
人間が見るときは、キリの良い50バケット目ずつを取り出してみるのが良さそうです。
mysql> select BUCKET_NUMBER, BUCKET_TIMER_LOW / 1000 / 1000 / 1000 as BUCKET_TIMER_LOW_MS,
BUCKET_TIMER_HIGH / 1000 / 1000 / 1000 as BUCKET_TIMER_HIGH_MS, COUNT_BUCKET_AND_LOWER 
FROM events_statements_histogram_global where BUCKET_NUMBER % 50  = 1;
+---------------+----------------------+----------------------+------------------------+
| BUCKET_NUMBER | BUCKET_TIMER_LOW_MS  | BUCKET_TIMER_HIGH_MS | COUNT_BUCKET_AND_LOWER |
+---------------+----------------------+----------------------+------------------------+
|             1 |       0.010000000000 |       0.010471285000 |                      0 |
|            51 |       0.100000000000 |       0.104712854000 |                      0 |
|           101 |       1.000000000000 |       1.047128548000 |                    109 |
|           151 |      10.000000000000 |      10.471285480000 |                    134 |
|           201 |     100.000000000000 |     104.712854805000 |                    140 |
|           251 |    1000.000000000000 |    1047.128548050000 |                    141 |
|           301 |   10000.000000000000 |   10471.285480509000 |                    141 |
|           351 |  100000.000000002000 |  104712.854805092000 |                    141 |
|           401 | 1000000.000000030000 | 1047128.548050931000 |                    141 |
+---------------+----------------------+----------------------+------------------------+
9 rows in set (0.00 sec)

2018年11月10日土曜日

MySQL 8.0でrootのSHOW GRANTSの表現が変わった

8.0からGRANT ALLしたユーザをSHOW GRANTS FORすると、ALL PRIVILEGESではなく、 実際に付与されている個別の権限が列挙されるようになってた。
mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%';
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW GRANTS FOR 'super'@'%' \G
*************************** 1. row ***************************
Grants for super@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, 
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, 
CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, 
DROP ROLE ON *.* TO `super`@`%`
*************************** 2. row ***************************
Grants for super@%: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,
GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,
RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `super`@`%`
2 rows in set (0.00 sec)
5.7ではこうだった。
mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'super'@'%';
+--------------------------------------------+
| Grants for super@%                         |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' |
+--------------------------------------------+
1 row in set (0.00 sec)
5.7でも権限として「ALL」というものは存在しない。 全部の権限がGRANTされていると「ALL PRIVILEGES」と表現されてるだけ。
GRANT ALL したユーザから一部の権限をREVOKEしてやると、わかりやすい。

mysql> REVOKE INDEX ON *.* FROM 'super'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'super'@'%' \G
*************************** 1. row ***************************
Grants for super@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, ALTER, SHOW DATABASES, 
SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, 
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, 
CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'super'@'%'
1 row in set (0.00 sec)

mysql> GRANT INDEX ON *.* TO 'super'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'super'@'%';                                                                                                                                                  +--------------------------------------------+
| Grants for super@%                         |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' |
+--------------------------------------------+
1 row in set (0.00 sec)
8.0のほうが自然な仕様に感じる。
特権DBアカウントかどうかを「ALL PRIVILEGES」で判断していると8.0にバージョンアップしたときにハマるかもしれない。

2018年7月28日土曜日

MySQLでワイルドカード証明書が扱えないという話をしたらすぐ扱えるようになった件

07/23日の日本MySQLユーザ会で、MySQLのSSLや透過的暗号化について発表してきました。



発表の中で、 SSLのワイルドカード証明書がうまく扱えず、大量にサーバを管理するときは大変。。。という話をしました。 ところが、発表してから4日後にリリースされた、MySQL 8.0.12でワイルドカード証明書が扱えるようになってました!!

8.0.11 クライアントだとサーバ証明書がワイルドカード証明書だと、--ssl-mode=VERIFY_IDENTITYで通らない
$ mysql --version
mysql  Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)

$ mysql -h db01.example.jp --ssl-ca=ca.pem --ssl-mode=VERIFY_IDENTITY -unativeuser -p
ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure

8.0.12だと、無事通る。
$ mysql --version
mysql  Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)

$ mysql -h db01.example.jp --ssl-ca=ca.pem --ssl-mode=VERIFY_IDENTITY -unativeuser -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1103
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye


booking.com の方がコントリビュートされたようです。
https://github.com/mysql/mysql-server/pull/196

リリースノートにはワイルドカード証明書が扱えるようになったとは書かれておらず、SANが扱えるようになったと記載されています。
Previously, for the --ssl-mode=VERIFY_IDENTITY option, the client checked whether the host name that it used for connecting matched the Common Name value in the certificate but not the Subject Alternative Name value. Now, if the client uses OpenSSL 1.0.2 or higher, the client checks whether the host name matches either the Subject Alternative Name value or the Common Name value in the server certificate. Thanks to Daniël van Eeden for a patch on which this change was based. (Bug #16211011, Bug #68052, Bug #27511233, Bug #89578)

SSL証明書には複数のドメインを扱うための、Subject Alternative Nameという領域があります。 今回の修正で、名前のチェックをMySQL独自の実装からopensslのライブラリを使うように置き換えられました。
これによって、SANだけでなく、ワイルドカード証明書も期待したとおりの動きになりました。

めでたしめでたし。

2018年5月14日月曜日

MySQL 8.0 でクエリーキャッシュはなくなりました

先日、MySQL 8.0 がGAリリースされました。888888!
こないだ、MySQL 5.7がGAになった気がしたんですが、調べてみたら、5.7 GAは2015/10でした。
いつのまにか約2年半もたっていたようです。。。


頂いたMySQL 8.0 Tシャツ。これは背中側。 

さて、MySQL 8.0 でクエリーキャッシュは廃止されます!私は大歓迎です

なぜ、廃止になるのか?



クエリーキャッシュが効果がある場面は限定的です。
テーブルのデータが変更されると、クエリの結果に変化があろうとなかろうと、そのテーブルに関するキャッシュは無効になります。
更新頻度が高いデータであれば、キャッシュヒット率は低くなり、効果を発揮しません。更新頻度が低いデータであれば、キャッシュヒット率は高くなりますが、更新頻度が低いのであれば、データベースに保存せず、静的なファイル等で保存したほうがより良いレスポンスタイムを期待できると思います。
私の経験ではクエリーキャッシュを正しく理解して利用している人はごく一部でした。

また、レスポンスタイムや負荷のバラ付きも問題です。
キャッシュヒットの有無によりレスポンスタイムのバラ付きが大きくなります。
普段は負荷の低いDBサーバが、テーブルに更新が入るタイミングで、急に負荷がスパイクし、
クエリーキャッシュヒットを狙っていなかったクエリも含めてレスポンスが全体的に悪化する・・・という場面を経験したことがあります。
負荷が安定しなかったり、予測がつかないというのは非常に運用しにくいものです。

そして、スケーラビリティの問題もあります。
MySQLのクエリーキャッシュは内部でmutexが競合するため、性能向上を妨げてしまいます。

詳しい経緯は、yakst さんに翻訳記事が上がっています。


ということで、クエリキャッシュはなくなります!

2018年3月22日木曜日

MEMORYエンジンが勝手にGTIDを進めてた

スレーブの gtid_executed がいつのまにかマスターとずれていることがあって、「スレーブに書き込んだうっかりさんは誰・・・?」と思ったらMEMORYエンジンが犯人だった。

MEMORYエンジンはディスクにはデータを保存せず、メモリ上のみデータを保存する。DBを再起動するとデータは消える。

レプリケーションを組んでいて、マスターだけ再起動したとする。 そうすると、マスターではデータが消えて、スレーブではデータが残ったままになる。
これでは、都合が悪い・・・ということで、MEMORYエンジンを利用している場合、再起動するとDELETE文をバイナリログに追記して整合性を取るようになっている。

マスターサーバーがシャットダウンして再起動すると、その MEMORY テーブルは空になります。マスターはこの影響をスレーブに複製するために、起動後に所定の MEMORY テーブルを最初に使用するときに、空にすべきテーブルの
DELETE ステートメントをバイナリログに書き込むことで、そのテーブルを空にする必要があることをスレーブに通知するイベントのログを記録します。
https://dev.mysql.com/doc/refman/5.6/ja/replication-features-memory.html


マニュアルでは「マスターサーバ」としているが、バイナリログがONになっていると、スレーブでもこの挙動になる。
スレーブを再起動すると、スレーブのバイナリログにDELETE文が書かれ、スレーブのUUIDのGTIDが進む。マスターとgtid_executedが一致しなくなる。

元の状態
マスター(UUID:aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb) のgtid_executed:
aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb:1-10
スレーブ(UUID:zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx) のgtid_executed:
aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb:1-10
スレーブ再起動。スレーブのUUID(zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx)のGTIDが進む。
マスター(UUID:aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb) のgtid_executed:
aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb:1-10
スレーブ(UUID:zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx) のgtid_executed:
aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb:1-10, zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx:1
もっかいスレーブ再起動
マスター(UUID:aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb) のgtid_executed:
aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb:1-10
スレーブ(UUID:zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx) のgtid_executed:
aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb:1-10, zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx:1-2

この状態で、ある日、マスターとスレーブを入れ替えるとする。
新スレーブ(旧マスター)は自分に未適用のGTIDのトランザクションを新マスター(旧スレーブ)からレプリケーションしようとする。
上記でいうと、zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx:1-2。
バイナリログが残っていれば、MEMORYエンジンのテーブルがDELETEされる(これはまぁ、いいかもしれない。MEMORYエンジンのデータはいつ消えてもいい前提だし)
もし、バイナリログが残ってないと、「そんなGTIDのイベントのログはもう持ってません~ ┐(´д`)┌ 」とレプリケーションが止まってしまう。。。

新マスターでダミーのコミットを実行し、gtid_executed を無理やり揃えて回避。

SET SQL_LOG_BIN=0;

SET GTID_NEXT='zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx:1';
BEGIN; COMMIT;

SET GTID_NEXT='zzzzzzzz-1111-2222-3333-xxxxxxxxxxxx:2';
BEGIN; COMMIT;

SET GTID_NEXT='AUTOMATIC'

2018年3月17日土曜日

Index column size too large エラーでレプリケーションが停止

マスターが MySQL 5.5 で スレーブが 5.6 の構成で、Index column size too large エラー でCREATE TABLEがコケてレプリケーションが止まった。

はて?5.5で通ったDDLがなぜ5.6でエラー?

mysql> SET GLOBAL innodb_large_prefix=OFF;
Query OK, 0 rows affected (0.00 sec)

-- warning が出てインデックス長は自動的に切り詰められる
mysql> CREATE TABLE t (pk serial, col1 varchar(2000), index(col1));
Query OK, 0 rows affected, 1 warning (0.19 sec)
mysql> SET GLOBAL innodb_large_prefix=ON;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE t2 (pk serial, col1 varchar(2000), index(col1));
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

innodb_large_prefix による挙動の違いでした。
innodb_large_prefix が OFF のときは、warning でクエリは通る(インデックス長は自動で短くされてしまう)が、ONだとエラーになるようだ。
innodb_large_prefix がマスター(5.5)ではOFFで、スレーブ(5.6)ではON になっていた。

「せっかくinndb_large_prefix をONにしてるんだから、ROW_FORMAT=Dynamicを指定すれば、長いインデックス作れますよ!」というMySQLさんのアピールか。

スレーブのinnodb_large_prefixをOFFにしてレプリケーション再開することで復旧。

実はさらに孫スレーブが5.7なのだが、5.7ではinnodb_default_row_format が Dynamic になっててエラーにならなかった&長いインデックスが作成されてた。MySQLの歴史を感じた。

2018年2月17日土曜日

Percona XtraDB Cluster 5.7 のバグたち(修正済)

私はRDBで一番大事なのは、durability (永続性、整合性) だと思っている。

データの整合性は、気づくことが難しい。整合性を疑って、正しいデータと比較して初めて気づける。
気づいたときには影響が広くなっており、すでに取り戻せないデータが発生している・・・ということも有り得る。

Availability (可用性) や Performance (性能) は、問題が出たとしても、いろいろな手段でカバー可能であることは多い。


ということで、踏み抜いておいた(踏み抜いてしまった)PXCのdurabilityに関わるバグたちを紹介。

1. LOAD DATA INFILE 利用時にノード間不整合


Wrong count if LOAD DATA used with GTIDs
https://jira.percona.com/browse/PXC-879

GTID を有効にしている状態で、かつ、LDI中にバイナリログのローテが入ると発動。
PXCにはパフォーマンスをあげるため、LDI時にデータを1万件ごとに小分けにしてコミットする機能が入っている。この機能にバグが潜んでいたようだ。
このバグを踏むと、例えば、31000件ロードしたら、LDIを実行したノードには31000件、正しく書き込まれる、
しかし、その他のノードでは1000件が失われ30000件だけ書き込まれる。

このバグは 5.7.20-29.24 で修正されている。

2. INSERT INTO ~ ON DUPLICATE KEY UPDATE でノード間不整合


Node consistency compromized for simple INSERT INTO ... ON DUPLICATE KEY UPDATE workload
https://jira.percona.com/browse/PXC-2039

複数ノードに同時並列でINSERT INTO ~ ON DUPLICATE KEY UPDATEを行うとノード間データ不整合が発生。

このバグはパッチは出ているものの、まだパッケージとしてはリリースされていない。
次のリリースに取り込まれるであろう。

3. Crash recovery 時にリカバリされないデータが発生


Transaction lost after recovery
https://jira.percona.com/browse/PXC-895

前提として、いわゆる durable (以下)なパラメータでPXCを運用している人のみが気にすべきバグである。
innodb_flush_log_at_trx_commit = 1
innodb_double_write = 1
PXCは複数ノードでデータを複製している。ノードが死んだ場合は、生きてるノードから最新のデータを
コピーする(SST)ため、crash recovery が必要なことは基本的にない。
Crash recovery が必要になるのは、全ノードがダウンした際だけである。

durable なパラメータで運用していない場合、全ノード ダウン時、少々データがロストするのはそもそも「仕様」である。

自分は durable で運用している。
複数のサーバが同時に死ぬことは、ほとんどありえないが、「ドミノ倒し」になることは有り得るからだ。
負荷やバグでノードが次々と死んでいくケースである。

実際、上記の1,2 のバグを踏んだ結果、残り1ノードになってしまうケースがある。
PXCではデータ不整合を検知すると、そのノードは自発的にシャットダウンする。
これ自体はデータ不整合が新たな不整合を生まないための仕組みで、望ましい挙動である。
しかし、結果として、1ノードになってしまうのだ。

このような状態でさらに想定外の事態が起きても、データロストしないよう durableな設定 で保険をかけているのだが、その保険が有効に機能してなかったという・・・
このバグも 5.7.20-29.24 で修正されている。

いずれも報告からすぐ修正がなされており、PXCのサポート体制は評価できる。
PXC5.7を利用している人は、最新バージョンに今すぐバージョンアップしよう。
(2のバグFIXはまだリリースされてないけど・・・)