mita2 database life

主にMySQLに関するメモです

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。

MySQL 各インデックスごとの容量を確認する

お題

  • 不要そうなインデックスを削除して、どれぐらい容量が減るのか確認したい
  • information_schema.tablesINDEX_LENGTH では合計容量しか見えない
mysql> SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH 
  FROM information_schema.tables WHERE TABLE_NAME = 'sbtest1' \G
*************************** 1. row ***************************
TABLE_NAME< TABLE_ROWS: 1
           DATA_LENGTH: 225132544
          INDEX_LENGTH: 85295104
1 row in set, 1 warning (0.00 sec)
  • 複数のインデックスの合計サイズではなく、個別のインデックスのサイズが知りたい

innodb_index_stats テーブルを見る

v5.6 で試しています。innodb_index_stats テーブル でインデックスのページ数が確認できます。 InnoDBのページサイズ x ページ数 でだいたいの容量がわかりそうです。

mysql > SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb 
FROM innodb_index_stats WHERE index_name = 'pad_1' and stat_name = 'size';
+------------+------------+-------------+
| stat_value | index_name | size_mb     |
+------------+------------+-------------+
|       4341 | pad_1      | 67.82812500 |
+------------+------------+-------------+
1 row in set (0.00 sec)

このpad_1 インデックスの論理容量は67MBぐらいでした。インデックスが断片化している場合は、実際の容量より大きな容量が表示される可能性があります。 今回は断片化してないデータで試したため、正確な値が出ているはずです。

インデックスを削除して減る容量を確認する

innodb_index_stats で見積もった容量が正しいかどうか、実際に削除してみます。

削除前:300MB です。

$  ls -alh ./data/sbtest/sbtest1.ibd
-rw-rw---- 1 samitani users 300M Mar 29 16:18 ./data/sbtest/sbtest1.ibd

DROPし、ALTER TABLE で空いた物理領域を開放します。

mysql > DROP INDEX pad_1 ON sbtest.sbtest1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > ALTER TABLE sbtest.sbtest1 ENGINE=InnoDB;
Query OK, 0 rows affected (7.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

削除後:232MBです。だいたい、見積もり通りに小さくなりました!

$  ls -alh ./data/sbtest/sbtest1.ibd
-rw-rw---- 1 samitani users 232M Mar 29 16:23 ./data/sbtest/sbtest1.ibd