mita2 database life

主にMySQLに関するメモです

スロークエリログをDataDogで可視化するLambda Function を作った

MySQL 徹底入門 第4版が出ましたね! 著者の方々にサインをもらいたいところですが、Stay Homeな昨今なかなかチャンスがありそうにありません。


基本、オンプレ職人なのですが、最近、ちょっとだけ、AWS Auroraを触ったりしています。

Datadog で AWS Aurora のスロークエリログを可視化する Lambda Function を作りました。 Lamda 上でクエリを正規化してから、Datadog に送信しています。

正規化して、どのクエリが多くスロークエリログに出力されているか集計しやすくしています。pt-query-digest がやってるのと同じことです。

SELECT id, name FROM tbl WHERE id = "1000"` 
=> `SELECT id, name FROM tbl WHERE id = ?

SELECT id, name FROM tbl WHERE id IN (10, 20, 30)`
=> `SELECT id, name FROM tbl WHERE id IN (?+)

使い方

zipファイルをlamdaに登録して、Lambdaの環境変数にDatadogのAPIキーを設定するだけで使えます。簡単。 詳しくは、README をご覧ください。

github.com

こんな感じで、スロークエリに頻出しているランキングのグラフが作れます。

f:id:mita2db:20200712135136p:plain
datadog
f:id:mita2db:20200712140252p:plain
datadog

グラフを作るには、Datadog のGrok parser に以下を指定して、スロークエリログから Query_timeRows_examined 等の値をメトリックとして切り出す必要があります。いろいろなケースのログをもれなくパースできるよう、Grok parser を書くのにすごく時間がかかりました・・・

SlowLogRule ^(\# Time: (%{date("yyMMdd  H:mm:ss"):date}|%{date("yyMMdd HH:mm:ss"):date})\n+)?\# User@Host: %{notSpace: user1}\[%{notSpace: user2}\] @ (%{notSpace: host}| ) *\[%{regex("[0-9.]*"): ip}\]  Id:[\x20\t]+%{number: id}\n+\# Query_time: %{number: query_time} *Lock_time: %{number: lock_time} *Rows_sent: %{number: rows_sent} *Rows_examined: %{number: rows_examined}\n(SET timestamp=%{number: timestamp};\n+)?%{regex("[a-zA-Z].*"):query}.

Performance Insights じゃダメなのか

Performance Insights は、待機イベントの多いクエリを見ることはできます。 「何も待機イベントが発生していないけど、遅いクエリ」はPerformance Insights のトップSQLには掲載されてない。 実際、スロークエリログにたくさん出力されているにも関わらず、Performance Insights のトップSQLに掲載されないものがありました。

MySQL max_connections は雑に設定しておけば良い

MySQL 誕生25周年 らしいです。めでたい! 25年、1つのソフトウェアが継続しているってすごい!

max_connections について

データベースを使っている開発者から「最大までどれぐらいコネクション数を増やせるのか」という質問を良くもらいます。 最大コネクション数(max_connections) の設定値を超えてしまい、too many connections エラーが出る。 max_connections を見直すとして、「じゃあどこまで大きくしていいのか?」と不安になるのはわかる。

以下の話は、コネクションプールを使っている前提のお話。

単にコネクション数が増えるだけでは、負荷は増加しない

単にコネクション数が増えるだけでは、DBサーバの負荷はあまり変化しない。 特にMySQLはスレッドモデルで実装されており、(プロセスモデルのデータベースと比較して)大量にコネクションを張った時のリソース消費が少ない。 SQLを実行していないアイドル(Sleep)状態のコネクションは、大量に存在していても、あまりDBサーバのCPUやIOリソースの使用量には関係しない。

# Command が Sleep になっているコネクションは何も実行していない
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |  521 | Waiting on empty queue | NULL             |
| 13 | root            | localhost | NULL | Sleep   |    8 |                        | NULL             |
| 14 | root            | localhost | NULL | Query   |    0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)

apache + PHP のような、HTTPサーバのワーカスレッド(プロセス)数とDBへのコネクション数が連動してしまうような環境で、アイドルコネクションが多く発生しやすい。

このグラフはコネクション数を約1万コネクションまで増やした時の、CPU使用率を表している。 コネクション数は SHOW PROCESSLIST で出力された数をカウントしている。

このように、いくらアイドルのコネクションを増やしたとしても、CPU使用率にほとんど影響しない。

f:id:mita2db:20200531163640p:plain

なお、メモリは多少、必要となる。実験では1万コネクションで2Gぐらい(1コネクションあたり200KByteぐらい) 消費された*1SQLを流していないので実際のワークロードではもう少し消費されるかもしれない)。

f:id:mita2db:20200531163841p:plain

以下のパラメータで指定した値が、コネクションの利用するメモリ量と大きく関係している。 以下のパラメータの値が大きいほど1つのコネクション(スレッド)が必要とするメモリが大きくなる。

  • sort_buffer_size
  • myisam_sort_buffer_size
  • read_buffer_size
  • join_buffer_size
  • read_rnd_buffer_size

大事なのはワークロード

DBサーバの負荷を左右するのは、コネクション数ではなく、アクティブなコネクションの数それぞれのコネクション(スレッド)で実行されるSQL。 アクティブなコネクションは、実際にSQLが流れているコネクションを指す。「重いSQL」 が 「同時にたくさん」流れるとDBサーバの負荷大きくなる。

アクティブなコネクション数がどれぐらいあるかは、Threads_running の値で確認できる。現在のコネクション数は Threads_connected で確認できる。

mysql> SHOW GLOBAL STATUS LIKE 'Threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 2     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

コネクション制限で過負荷を回避するのは困難

DBサーバの能力を超えた大量のリクエストは、データベースのレスポンスタイムを大きく低下させる。 レスポンスタイムの悪化がエンドユーザの再実行やアプリケーションからのリトライを誘発し、さらに負荷が増え、最終的には、一切、レスポンスを返せないハングアップしたような状態になってしまう。

データベースへリクエストの流入量を制限できれば、一部のリクエストは犠牲(エラー)にしつつも、サービスの提供を継続することができる。 リアルの店舗等で混雑時に入場制限をするのと同じだ。

しかし、実際には 過負荷を回避するような値に max_connections をあらかじめ設定しておくのは難しい。 DBの負荷は「SQLの内容」や「アクティブなコネクションの割合」「実行計画」といったコネクション数以外の変数の影響が大きく、コネクション数だけではコントロールできない。

キャップをかける試み自体は否定しないが、変化の激しい、Webサービスでコネクション数の見積もりに工数をかけるのは無駄が大きいように思う*2

なお、いざ過負荷に陥ったときに、max_connections を絞り、過負荷を当座で回避することは出来る。

過負荷を回避するための Thread Pool

過負荷を回避するために役立つ Thread Pool という機能がある。 Thread Pool ではアクティブなスレッド数をコントロールし、過負荷を回避し、スループットを最大に保つことができる。 MySQL では Enterprise Edition(有償) に入っている。 Percona Server (無償) にも同様の機能がある。

(しかし、自分は実運用では使ったことがないので、どこまで、うまく動いてくれるか、わからない。。。)

dev.mysql.com

www.percona.com

どこまで max_connections を増やせますか?と聞かれたら

ワークロードに変化があるのかどうをヒアリングする。現状の負荷と照らし合わせて、心配があるなら、負荷検証したり、もしもの時に備えた準備をする。 max_connections はあまり考えず、雑に大きな値(1000とか2000とか)を設定している。

参考書籍

*1:MySQL 8.0.19で全くデフォルトから設定をへ変更していない環境で試している

*2:アクセスパターンが決まっている業務系のアプリなら、アリかもしれない

MySQL 複数データセンター利用する場合のレプリケーショントポロジー考察

マスター・スレーブ構成ではマスター障害時、保持しているバイナリログが最も進んでいるスレーブから新マスターを選出する。

MySQLでは、どのスレーブが最新のログを持っているかはコントロールできない。 準同期レプリケーションを利用していたとしても、非同期スレーブのほうが先に進んでいるということがあり得る。

複数のデータセンターを利用する場合に、プライマリ側のデータセンターにマスターを固定する運用をするとしたら、 どのような構成パターンが良さそうか考えてみる。セカンダリ側のデータセンターのスレーブが最も進んでしまっている場合にどのように対処するか。

前提条件

  • プライマリデータセンター内にロスレス準同期スレーブが最低一台は存在している前提
  • サーバ障害でのデータロストは許容できない

1. 中間マスターなし

f:id:mita2db:20200524085218p:plain

1-1. DC間フェイルオーバー有り

f:id:mita2db:20200524090611p:plain

セカンダリのデータセンターが新マスターになる可能性がある。

f:id:mita2db:20200524092949p:plain

マスターをプライマリのデータセンターに固定したい場合は、2段回でのフェイルオーバー動作が必要になる。 一旦、セカンダリDCのマスターにフェイルオーバーした後、プライマリDC内のスレーブをマスターに昇格しなおす。

1-2. DC間フェイルオーバー無し

f:id:mita2db:20200524092334p:plain

プライマリのデータセンター内から必ず新マスターを選出するパターン。 プライマリ側に最新のログを持ったスレーブが存在する場合は何も問題はない。 運悪く、セカンダリDC側が先に進んでいた場合は、先に進んでしまったセカンダリDC側は破棄する必要がある。そうしないと、プライマリDC側に存在しないデータがセカンダリDC側に存在してしまう。

ここで破棄されるデータは、プライマリDC側で「まだ準同期スレーブがログを受け取っていないデータ」である。そのため、クライアントにはOKが返っていない段階。 まだ、クライアントがコミットできたことを認識してないので、破棄しても問題ない。

セカンダリDC側のスレーブが破棄される可能性があるため、セカンダリDCのスレーブを参照するような構成は取れなくなる。

2. セカンダリのデータセンターに中間マスターを配置

f:id:mita2db:20200524085537p:plain

中間マスターを配置することで、構成が対象になり、わかりやすい。。。ように思えるが、 実際は、中間マスター死んだときのことも考えないといけないので面倒である。

2-1. DC間フェイルオーバー有り

f:id:mita2db:20200524085904p:plain

セカンダリの中間マスターが先に進んでいた場合、中間マスターが新マスターになる。 中間マスターより、セカンダリDCのスレーブが先に進むということはあり得ないため、セカンダリDCのスレーブが新マスターとして選出されないようにすることが可能。 1-1 同様、マスターをプライマリのデータセンターに固定したい場合は、2段回でのフェイルオーバー動作が必要になる。

2-2. DC間フェイルオーバー無し

f:id:mita2db:20200524090956p:plain

1-2 と同じ。セカンダリDC側が先に進んでしまっていた場合、セカンダリDC側は破棄する必要がある。

3. プライマリのデータセンターに中間マスターを配置

f:id:mita2db:20200524091724p:plain

3-1. DC間フェイルオーバー有り

f:id:mita2db:20200524091859p:plain

セカンダリDCのスレーブが、プライマリDCより先に進むことはあり得ないため、必ず、プライマリDCから新マスターが選出される。 この時、中間マスターが選出されるとは限らない。 セカンダリDC側にフェイルオーバーすることがないのがメリットだが、2 同様に、中間マスターのフェイルオーバーも考える必要がある。

3-2. DC間フェイルオーバー無し

セカンダリDCのスレーブが新マスターとなることはないため、3-1 と同じ。

まとめ

  • マスター・スレーブで冗長化するのむずかしい・・・考えることが多い

  • DC間フェイルオーバーを無しとした場合、ロストスレーブが発生しうるため、DC間F/Oは有り+2段回F/O としたほうが運用上は楽な気がする

    • 1-1 が良さそう

MySQL 8.0 の新機能 デュアルパスワードでパスワード変更時のダウンタイムを回避する

MySQL 8.0で、「デュアルパスワード」がサポートされました。 1つのユーザに対して、新旧2つのパスワード(プライマリとセカンダリ)を設けることができます。

# どっちのパスワードでもログインできる
$ mysql -uapp -h$HOSTNAME -pPassw0rd@init -e 'SELECT CURRENT_USER()'
+----------------+
| CURRENT_USER() |
+----------------+
| app@%          |
+----------------+

$ mysql -uapp -h$HOSTNAME -pPassw0rd@2nd -e 'SELECT CURRENT_USER()'
+----------------+
| CURRENT_USER() |
+----------------+
| app@%          |
+----------------+

DB上でのパスワード変更のオペレーション と アプリケーション側に設定されているパスワードの変更 をまったく同時に行うことは困難です。 そのため、従来はパスワード変更の際は、メンテナンス時間を確保して対応する必要がありました。

もしくは、新しいパスワードを設定した別のユーザを追加し、アプリケーション(やスレーブ)を徐々に追加したユーザに切り替えていくことでダウンタイムを回避していました。 ユーザ名の変更が難しいケースもあると思います(SQL SECURITY DEFINER で指定しているとか、ユーザ名を利用してアクセス監査しているなど)。 デュアルパスワード機能で、ユーザ名を維持しつつ、ダウンタイムを回避してパスワード変更が可能になります。

試します

テスト用にユーザを作ります。

mysql> CREATE USER app@'%' IDENTIFIED BY 'Passw0rd@init';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User, Host, plugin, authentication_string, User_attributes FROM mysql.user Where user = 'app' \G
*************************** 1. row ***************************
                 User: app
                 Host: %
               plugin: caching_sha2_password
authentication_string: $A$005$#]
7J.Y=5F`mN[hZn/xX6nyS2Bmb2m0Vr6WnCH6aQz.kUFO2AdtIuh/.9
      User_attributes: NULL
1 row in set (0.00 sec)

パスワード変更時に RETAIN CURRENT PASSWORD を指定することで、旧パスワードを保持した上で、パスワード変更が可能です。 旧パスワード(セカンダリ)は mysql.user テーブルの User_attributes に記録されてました。

mysql> ALTER USER 'app'@'%' IDENTIFIED BY 'Passw0rd@2nd' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User, Host, plugin, authentication_string, User_attributes FROM mysql.user Where user = 'app' \G
*************************** 1. row ***************************
                 User: app
                 Host: %
               plugin: caching_sha2_password
authentication_string: $A$005$F2.("3Cli9Uq{9
TLFbCcuja.VoDO8FrvhR3Zi92cp.v5Mec/Zjcezww9eq0
      User_attributes: {"additional_password": "$A$005$#]\n\u00077J\u0003\u0002.\u0013Y=5F`m/\bN[hZn/xX6nyS2Bmb2m0Vr6WnCH6aQz.kUFO2AdtIuh/.9"}
1 row in set (0.00 sec)

新旧両方のパスワードでログインできます。

$ mysql -uapp -h$HOSTNAME -pPassw0rd@init -e 'SELECT CURRENT_USER()'
+----------------+
| CURRENT_USER() |
+----------------+
| app@%          |
+----------------+

$ mysql -uapp -h$HOSTNAME -pPassw0rd@2nd -e 'SELECT CURRENT_USER()'
+----------------+
| CURRENT_USER() |
+----------------+
| app@%          |
+----------------+

最終的に、アプリケーション側のパスワード設定を変更し終わったら、DISCARD OLD PASSWORD で旧パスワードを破棄します。

mysql> ALTER USER 'app'@'%' DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.00 sec)

権限まわり

RETAIN CURRENT PASSWORD を実行するには、CREATE USER 権限 or APPLICATION_PASSWORD_ADMIN 権限 が必要。

mysql> ALTER USER 'app'@'%' IDENTIFIED BY 'Password@10th' RETAIN CURRENT PASSWORD;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER or APPLICATION_PASSWORD_ADMIN privilege(s) for this operation

出来ないこと

  • セカンダリパスワードだけを変更する。一方、セカンダリを維持しつつ、プライマリのパスワードだけを変更することは可能。
  • ユーザがどっちのパスワードを利用して接続しているかの判別

(余談) 僕が本当に探しているもの

MySQL 8.0 移行時に、強制的に・短期間で、パスワードを mysql_native_password から caching_sha2_passwordマイグレーションする方法。 いろいろ調べてるけど、初期パスワードを発行しなおして、アプリケーション開発者に再設定してもらう方法に落ち着きそう。

MySQL 8.0.20 でHASH JOINが効くケースが拡大した

TLDR;

  • MySQL 8.0.20 から INNER JOIN だけじゃなくて、Semi joinや Left/Right outer join でも HASH JOIN が使えるようになったよ
  • MySQL 8.0.20 では (EXPLAIN ANALYZEではなく)EXPLAIN でも、HASH JOINが使われているか、表示されるようになったよ
  • MySQL 8.0.19 から、optimizer_switch で HASH JOIN の OFF/ON がコントロールできなくなったよ

MySQL 8.0.20 Release Note

Hash joins are now used any time a nested block loop would be employed. This means that hash joins can be used for the following types of queries:
* Inner non-equi-joins
* Semijoins
* Antijoins
* Left outer joins
* Right outer joins
This builds on work done for MySQL 8.0.18, and removes a limitation in the implementation such that a hash join could be used only with a query having at least one equi-join condition.

MySQL 8.0.20 で HASH JOIN が INNER JOIN 以外にも利用できるようになった。

MySQL 8.0.17

比較のため、HASH JOINが入る前の8.0.17 で試す。 HASH JOINが効きそうな、インデックスを一切貼ってないテーブルどうしをJOINさせる。

mysql>  SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;
Empty set (32.01 sec)
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
16305 rows in set (34.92 sec)

INNER JOIN, LEFT JOIN ともに、30秒ほど実行にかかった。

mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 32538 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 32926 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 32538 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 32926 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

実行計画でも、HASH JOIN ではなく、Block Nested Loop が利用されていることがわかる。

MySQL 8.0.18

HASH JOINがINNER JOINに対してのみサポートされた、8.0.18。

mysql>  SELECT * FROM t1  JOIN t2 ON t1.a = t2.a;
Empty set (0.16 sec)
mysql>  SELECT * FROM t1  LEFT JOIN t2 ON t1.a = t2.a;
16305 rows in set (34.65 sec)

INNER JOINは8.0.17で30秒だったものが、0.16秒とHASH JOINにより高速化されている。 LEFT JOINは変わらず。

mysql> EXPLAIN  SELECT * FROM t1  JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16207 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16207 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN  SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16207 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16207 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

素のEXPLAINではHASH JOINかどうかが、区別できない。。。 EXPLAIN ANALYZE を利用する必要がある。

mysql> EXPLAIN ANALYZE  SELECT * FROM t1  JOIN t2 ON t1.a = t2.a;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t2.a = t1.a)  (cost=26268477.04 rows=26266685) (actual time=174.783..174.783 rows=0 loops=1)
    -> Table scan on t2  (cost=0.02 rows=16207) (actual time=0.026..38.354 rows=16305 loops=1)
    -> Hash
        -> Table scan on t1  (cost=1644.95 rows=16207) (actual time=0.053..37.792 rows=16305 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.18 sec)

Inner hash join と表示され、HASH JOIN が使われたことが、わかる。

MySQL 8.0.20

OUTER JOIN にも HASH JOINが適用されるようになった、8.0.20で試す。

mysql> SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;
Empty set (0.14 sec)
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
16305 rows in set (0.14 sec)

LEFT JOINも早くなった!

mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16207 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16207 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

8.0.20 では、EXPLAIN ANALYZEでなくても、HASH JOINかどうか区別できるようになってる(Using join buffer (hash join))。

HASH JOINをOFFにできなくなってた

optimizer_switch の hash_join を OFF にしても、、、

HASH JOINが使われてしまう!

Beginning with MySQL 8.0.19, the hash_join optimizer switch and the HASH_JOIN and NO_HASH_JOIN optimizer hints no longer have any effect.

8.0.19 から、optimizer_switch や ヒント句でコントロールできなくなってしまったようです。

良く使う pt-query-digest のコマンド メモ

ただのメモです。

pt-query-digest

percona tookit に含まれるツールの1つ。 スロークエリログやtcpdump からクエリを抽出して、クエリを集計するツール。 負荷をかけているクエリを洗い出すのに便利なヤツ。

https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html

いつものコマンド

  • InnoDB の IO の多い順でソート

InnoDB_IO_r_bytes は Vanilla MySQLでは出力されない。Percona でしか動かない。

$ pt-query-digest  --group-by fingerprint --order-by InnoDB_IO_r_bytes:sum --since 'YYYY-MM-DD HH:MM:SS' --until 'YYYY-MM-DD HH:MM:SS' mysqld-slow.log 
  • 実行時間の合計でソート

Query_time の合計でソート

$ pt-query-digest  --group-by fingerprint --order-by Query_time:sum  --since 'YYYY-MM-DD HH:MM:SS' --until 'YYYY-MM-DD HH:MM:SS' mysqld-slow.log 
  • たくさん行を読み取っているヤツ

Rows_examined の合計でソート

pt-query-digest  --group-by fingerprint --order-by Rows_examined:sum  --since 'YYYY-MM-DD HH:MM:SS' --until 'YYYY-MM-DD HH:MM:SS' mysqld-slow.log 
  • 回数でソート

〜:cnt とする。Query_time の部分は何でも良い。

pt-query-digest  --group-by fingerprint --order-by Query_time:cnt  --since 'YYYY-MM-DD HH:MM:SS' --until 'YYYY-MM-DD HH:MM:SS' mysqld-slow.log 

おまけ

MySQL 8 で FLUSH STATUS したら、Bytes_received やら Read_first がありえ無い大きな値でログが出力された。

# Time: 2020-04-18T13:38:45.798080+09:00
# User@Host: root[root] @ localhost []  Id:   162
# Query_time: 0.002119  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0 Thread_id: 162 Errno: 0 Killed: 0 Bytes_received: 18446744073709551112 Bytes_sent: 18446744073709548672
 Read_first: 18446744073709551612 Read_last: 0 Read_key: 18446744073709551597 Read_next: 18446744073709551611 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 18446744073709551609 Sort_me
rge_passes: 0 Sort_range_count: 0 Sort_rows: 18446744073709551608 Sort_scan_count: 18446744073709551613 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2020-04-18T13:38:4
5.795961+09:00 End: 2020-04-18T13:38:45.798080+09:00
SET timestamp=1587184725;
flush status;

MySQL の feature request を眺める

feature request

MySQL のバグは bugs.mysql.com で管理されています。 このサイトにはバグだけでなく、機能改善(feature request) の要望も投稿できます。

bugs.mysql.com

眺めてみる

普段はバグ(不具合)に注目してみているのですが、今日は、FRをみてみようと思います。

Advanced Search から StatusがVerified、Severity が S4(Feature request ) を条件として検索してみます。 ID順でソートし、最近投稿されたものを見てみます。

f:id:mita2db:20200418141732p:plain
bugs.mysql.com

これ確かに欲しい!と思ったら

Affects me というボタンがあります。「このバグやリクエストは自分にも関係がある」ことを示すものです。投票機能のようなものですね。 Affects me の数が多ければ、MySQLの開発者の人たちは、「このリクエストはニーズが高い」とわかるわけです。もしかしたら、優先して実装してくれるかもしれません。

f:id:mita2db:20200418150836p:plain
affectsme

ということで、「これ良さそう!」というFeature request があったら、Affects Me しましょう。

when log_slow_extra is on,add some new status

スロークエリに出力されるステータスを増やして欲しい。具体的には、InnoDB_pages_distinct, InnoDB_IO_r_wait, InnoDB_rec_lock_wait, InnoDB_queue_wait があるとすごい便利!というFR。

MySQL Bugs: #99261: when log_slow_extra is on,add some new status

実は、Perconaサーバにはこの機能があります。Percona ではこのようなスロークエリログが出ます。 InnoDB_IO_r_bytes が大きいものを見れば、DISK IOに負荷をかけているSQLがすぐ特定できます。実際、使っていて、非常に便利です。

# User@Host: mailboxer[mailboxer] @  [192.168.10.165]
# Thread_id: 11167745  Schema: board
# Query_time: 1.009400  Lock_time: 0.000190  Rows_sent: 4  Rows_examined: 1543719  Rows_affected: 0  Rows_read: 4
# Bytes_sent: 278  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 1500
# QC_Hit: No  Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 6415  InnoDB_IO_r_bytes: 105103360  InnoDB_IO_r_wait: 0.001279
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 6430
SET timestamp=1346844943;
SELECT id,title,production_year FROM title WHERE title = 'Bambi';

これは絶対便利なので、みんなで Affects Me しましょう。

JSON Path implement negative offset

MySQL Bugs: #99238: JSON Path implement negative offset

JSONで配列から値を取り出すときに、「後ろから数えて何番目」という指定方法をサポートしてほしい。というFR。

多くのプログラミング言語では、配列の要素を指定するときにマイナスで「後ろから数えて何番目」と指定できますね。

mysql> SELECT JSON_EXTRACT(JSON_ARRAY(1, 2, 3), '$[-1]');

Insufficient information in explain

MySQL Bugs: #99107: Insufficient information in explain

EXPLAINしたときに、エイリアスではなくスキーマ名とテーブル名を出してほしい。というFR。 テーブルが多いと、エイリアスとテーブルのマッピングが覚えきれずに、苦労する。。。 昔からこの仕様なので、過去にも同様のFRは出ていそうですが、、、

-- EXPLAINにはテーブル名が表示されずにエイリアスである「x」が表示される
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 986400
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

これも、ぜひ対応してほしい。Affects Me!

Manage buf blocks for InnoDB temp tablespace separately in buffer pool

MySQL Bugs: #99020: Manage buf blocks for InnoDB temp tablespace separately in buffer pool

InnoDB をテンポラリテーブルとして利用した場合、パフォーマンス劣化してしまうケースがある。internal_tmp_disk_storage_engine = MYISAM を指定して回避できていたが、8.0.16 から internal_tmp_disk_storage_engine が削除され、InnoDBしか選択できなくなってしまた。InnoDB はテンポラリテーブルとして利用するには、まだまだオーバーヘッドがあるので、改善してほしい。というFR。