2022/06/29

DROP TABLEがどれくらいその他のトラフィックに影響を与えるか……を、手元で測る方法

TL;DR

sysbenchでDROP用のテーブルと、その他トラフィック用のテーブルをprepareして
sysbench runしながらDROP TABLEするだけ

sysbenchは無ければインストール(CentOS 7.xだとepelに入ってる)


$ sudo yum install -y sysbench

ベンチ先がMySQL 8.0の場合、 default_authentication_plugin=mysql_native_password かつ、sysbench用のアカウントがmysql_native_passwordでされてないとエラーになる。


FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed

ちなみにchaching_sha2_passwordは libmysqlcliento.so に埋め込まれているので、どこを探しても caching_sha2_password.so なんてものは無いのでyum providesとかで探す必要はない。

どうしても8.0のcaching_sha2_passwordで通したい場合は最新のmysql-community-develを使ってsysbenchをビルドしなおすことになると思う。

mysql80 16> CREATE USER sysbench@localhost IDENTIFIED WITH mysql_native_password BY '';
Query OK, 0 rows affected (0.02 sec)

mysql80 16> GRANT ALL ON for_drop.* TO sysbench@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 16> GRANT ALL ON sysbench.* TO sysbench@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 21> CREATE DATABASE for_drop;
Query OK, 1 row affected (0.00 sec)

mysql80 21> CREATE DATABASE sysbench;
Query OK, 1 row affected (0.00 sec)

for_dropスキーマの方にデカめのテーブルを作る。

$ sysbench --mysql-socket=/usr/mysql/8.0.29/data/mysql.sock --mysql-user=sysbench --mysql-db=for_drop oltp_common --table-size=10000000 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...

Inserting 10000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

$ ll -h /usr/mysql/8.0.29/data/for_drop/sbtest1.ibd
-rw-r----- 1 yoku0825 yoku0825 2.3G Jun 29 15:21 /usr/mysql/8.0.29/data/for_drop/sbtest1.ibd

1000万行で2.3Gらしいので、このへんを基準(?)にDROPで試したいテーブルサイズを作る。とりあえず10倍の23GBを作っておいた。

今度はsysbenchスキーマの方に小さめのテーブルを複数作っておく。

$ sysbench --mysql-socket=/usr/mysql/8.0.29/data/mysql.sock --mysql-user=sysbench --mysql-db=sysbench oltp_common --tables=100 prepare
..

で、sysbench側にベンチをかける。
oltp_* にどんなスクリプトが用意されているかはこんな感じ。

$ ll /usr/share/sysbench/
total 132
-rwxr-xr-x 1 root root  1452 May  8  2020 bulk_insert.lua
-rw-r--r-- 1 root root 14369 May  8  2020 oltp_common.lua
-rwxr-xr-x 1 root root  1290 May  8  2020 oltp_delete.lua
-rwxr-xr-x 1 root root  2415 May  8  2020 oltp_insert.lua
-rwxr-xr-x 1 root root  1265 May  8  2020 oltp_point_select.lua
-rwxr-xr-x 1 root root  1649 May  8  2020 oltp_read_only.lua
-rwxr-xr-x 1 root root  1824 May  8  2020 oltp_read_write.lua
-rwxr-xr-x 1 root root  1118 May  8  2020 oltp_update_index.lua
-rwxr-xr-x 1 root root  1127 May  8  2020 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1440 May  8  2020 oltp_write_only.lua
-rwxr-xr-x 1 root root  1919 May  8  2020 select_random_points.lua
-rwxr-xr-x 1 root root  2118 May  8  2020 select_random_ranges.lua
drwxr-xr-x 4 root root    49 Apr 13 12:53 tests
-rwxr-xr-x 1 root root 12320 May  8  2020 tpcc_check.lua
-rwxr-xr-x 1 root root 20795 May  8  2020 tpcc_common.lua
-rwxr-xr-x 1 root root  1863 May  8  2020 tpcc.lua
-rwxr-xr-x 1 root root 28512 May  8  2020 tpcc_run.lua

oltp_read_onlyにしてみる。
--report-interval=1 にすると毎秒出力されるので便利。

$ sysbench --mysql-socket=/usr/mysql/8.0.29/data/mysql.sock --mysql-user=sysbench --mysql-db=sysbench oltp_read_only --tables=100 --report-interval=1 --threads=100 --time=1000 run

..
Threads started!

[ 1s ] thds: 100 tps: 1530.75 qps: 25282.12 (r/w/o: 22122.60/0.00/3159.52) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 100 tps: 1585.97 qps: 25346.51 (r/w/o: 22180.57/0.00/3165.93) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 100 tps: 1679.10 qps: 26823.60 (r/w/o: 23465.40/0.00/3358.20) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 100 tps: 1655.01 qps: 26505.23 (r/w/o: 23195.20/0.00/3310.03) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 100 tps: 1673.00 qps: 26725.07 (r/w/o: 23377.06/0.00/3348.01) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 100 tps: 1577.97 qps: 25269.49 (r/w/o: 22112.55/0.00/3156.94) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 100 tps: 1580.93 qps: 25326.88 (r/w/o: 22166.02/0.00/3160.86) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 100 tps: 1593.91 qps: 25551.54 (r/w/o: 22365.73/0.00/3185.82) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 100 tps: 1593.18 qps: 25472.95 (r/w/o: 22282.58/0.00/3190.37) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00               <--- ここで開始
[ 10s ] thds: 100 tps: 1316.81 qps: 21059.97 (r/w/o: 18429.35/0.00/2630.62) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00

..
[ 60s ] thds: 100 tps: 1890.99 qps: 30242.85 (r/w/o: 26459.87/0.00/3782.98) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 61s ] thds: 100 tps: 1783.59 qps: 28572.41 (r/w/o: 25006.23/0.00/3566.18) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 62s ] thds: 100 tps: 1820.78 qps: 29147.51 (r/w/o: 25503.95/0.00/3643.56) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 63s ] thds: 100 tps: 1783.71 qps: 28545.30 (r/w/o: 24977.89/0.00/3567.41) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 64s ] thds: 100 tps: 1943.01 qps: 31046.16 (r/w/o: 27163.14/0.00/3883.02) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 100 tps: 1978.97 qps: 31682.47 (r/w/o: 27721.53/0.00/3960.93) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 66s ] thds: 100 tps: 1768.98 qps: 28216.69 (r/w/o: 24683.73/0.00/3532.96) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00              <-- ここで終わった
[ 67s ] thds: 100 tps: 1559.02 qps: 24944.31 (r/w/o: 21828.27/0.00/3116.04) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 68s ] thds: 100 tps: 1648.94 qps: 26347.03 (r/w/o: 23046.15/0.00/3300.88) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 69s ] thds: 100 tps: 1702.05 qps: 27290.79 (r/w/o: 23887.69/0.00/3403.10) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 1708.01 qps: 27256.18 (r/w/o: 23839.16/0.00/3417.02) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00

というわけで、semisyncなしでSELECTしか来てないサーバーだとデカいテーブルのDROP TABLEでも他のクエリにはほとんど影響は無さそうでした。

……というのを手元で確認するためのやり方でした。

2022/06/28

MySQLが勝手に作るファイルのパーミッションを指定する

TL;DR

UMASK 環境変数で制御する
- MySQL :: MySQL 8.0 リファレンスマニュアル :: B.3.3.1 ファイル権限の問題
ファイルであれば最小パーミッションは 0600, ディレクトリであれば 0710 の値になる
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.9 環境変数
- https://github.com/mysql/mysql-server/blob/mysql-8.0.29/mysys/my_init.cc#L141-L149

UMASK を設定しないと、ファイルは 0640, ディレクトリは 0750
- ただし auto_generate_certs で作られる証明書は 0600 (公開用のは 0644 っぽい

$ env | grep UMASK  ### からっぽ
$ /usr/mysql/8.0.29/bin/mysqld --no-defaults --initialize-insecure --datadir=/tmp/noumask
2022-06-28T03:49:08.901551Z 0 [System] [MY-013169] [Server] /usr/mysql/8.0.29/bin/mysqld (mysqld 8.0.29) initializing of server in progress as process 116145
2022-06-28T03:49:08.921448Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-06-28T03:49:09.938657Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-06-28T03:49:11.335845Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
$ ll /tmp/noumask/
total 176568
-rw-r----- 1 yoku0825 yoku0825       56 Jun 28 12:49 auto.cnf
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 ca-key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 ca.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 client-cert.pem
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 client-key.pem
-rw-r----- 1 yoku0825 yoku0825   196608 Jun 28 12:49 #ib_16384_0.dblwr
-rw-r----- 1 yoku0825 yoku0825  8585216 Jun 28 12:49 #ib_16384_1.dblwr
-rw-r----- 1 yoku0825 yoku0825     5944 Jun 28 12:49 ib_buffer_pool
-rw-r----- 1 yoku0825 yoku0825 12582912 Jun 28 12:49 ibdata1
-rw-r----- 1 yoku0825 yoku0825 50331648 Jun 28 12:49 ib_logfile0
-rw-r----- 1 yoku0825 yoku0825 50331648 Jun 28 12:49 ib_logfile1
drwxr-x--- 2 yoku0825 yoku0825        6 Jun 28 12:49 #innodb_temp
drwxr-x--- 2 yoku0825 yoku0825      143 Jun 28 12:49 mysql
-rw-r----- 1 yoku0825 yoku0825 25165824 Jun 28 12:49 mysql.ibd
drwxr-x--- 2 yoku0825 yoku0825     8192 Jun 28 12:49 performance_schema
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 private_key.pem
-rw-r--r-- 1 yoku0825 yoku0825      452 Jun 28 12:49 public_key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 server-cert.pem
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:49 server-key.pem
drwxr-x--- 2 yoku0825 yoku0825       28 Jun 28 12:49 sys
-rw-r----- 1 yoku0825 yoku0825 16777216 Jun 28 12:49 undo_001
-rw-r----- 1 yoku0825 yoku0825 16777216 Jun 28 12:49 undo_002

UMASK=0600 を押し込むと、ファイルは 0600, ディレクトリは…… 0710 ?
- 証明書類はUMASKの影響を受けてなさそう

$ UMASK=0600 /usr/mysql/8.0.29/bin/mysqld --no-defaults --initialize-insecure --datadir=/tmp/umask0600
2022-06-28T03:52:21.587890Z 0 [System] [MY-013169] [Server] /usr/mysql/8.0.29/bin/mysqld (mysqld 8.0.29) initializing of server in progress as p
rocess 116751
2022-06-28T03:52:21.595297Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-06-28T03:52:22.194497Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-06-28T03:52:23.877843Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off th
e --initialize-insecure option.

$ ll /tmp/umask0600/
total 176568
-rw------- 1 yoku0825 yoku0825       56 Jun 28 12:52 auto.cnf
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:52 ca-key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 ca.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 client-cert.pem
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:52 client-key.pem
-rw------- 1 yoku0825 yoku0825   196608 Jun 28 12:52 #ib_16384_0.dblwr
-rw------- 1 yoku0825 yoku0825  8585216 Jun 28 12:52 #ib_16384_1.dblwr
-rw------- 1 yoku0825 yoku0825     5600 Jun 28 12:52 ib_buffer_pool
-rw------- 1 yoku0825 yoku0825 12582912 Jun 28 12:52 ibdata1
-rw------- 1 yoku0825 yoku0825 50331648 Jun 28 12:52 ib_logfile0
-rw------- 1 yoku0825 yoku0825 50331648 Jun 28 12:52 ib_logfile1
drwx--x--- 2 yoku0825 yoku0825        6 Jun 28 12:52 #innodb_temp
drwx--x--- 2 yoku0825 yoku0825      143 Jun 28 12:52 mysql
-rw------- 1 yoku0825 yoku0825 25165824 Jun 28 12:52 mysql.ibd
drwx--x--- 2 yoku0825 yoku0825     8192 Jun 28 12:52 performance_schema
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:52 private_key.pem
-rw-r--r-- 1 yoku0825 yoku0825      452 Jun 28 12:52 public_key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 server-cert.pem
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:52 server-key.pem
drwx--x--- 2 yoku0825 yoku0825       28 Jun 28 12:52 sys
-rw------- 1 yoku0825 yoku0825 16777216 Jun 28 12:52 undo_001
-rw------- 1 yoku0825 yoku0825 16777216 Jun 28 12:52 undo_002

0710 がもんにょりしたけど、ドキュメントに書いてあった。

UMASK 変数および UMASK_DIR 変数は、その名前にもかかわらず、マスクではなくモードとして使用されます。
UMASK が設定されている場合、mysqld は ($UMASK | 0600) をファイル作成のモードとして使用し、新しく作成されるファイルのモードは 0600 から 0666 の範囲になります (すべて 8 進数の値)。
UMASK_DIR が設定されている場合、mysqld は ($UMASK_DIR | 0700) をディレクトリ作成のベースモードとして使用し、次に ~(~$UMASK & 0666) との AND が取られます。そのため新しく作成されるファイルのモードは 0700 から 0777 の範囲になります (すべて 8 進数の値)。 AND 演算によってディレクトリモードから読み取り/書き込み権が削除されることがありますが、実行権が削除されることはありません。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.9 環境変数

2022/06/22

MySQLだけでWindow関数を使って@rowとか使わずに95%ileを計算したい

やり方があってるかどうかわからないので違ってたら教えてほしい。

サンプルデータこんな感じ。


mysql80 209534> SELECT * FROM t1 LIMIT 3;

+---------------------+-----------+
| dt                  | rows_read |
+---------------------+-----------+
| 2022-05-31 17:16:00 |         0 |
| 2022-05-31 17:16:01 |         6 |
| 2022-05-31 17:16:03 |         0 |
+---------------------+-----------+

3 rows in set (0.00 sec)

まずは全期間でrows_readの95%ileを計算してみたい。

パーセンタイルを一発で求めるなにかは無さそうなので、まずはおとなしくRANK()で並べ替える。

mysql80 209534> SELECT dt, rows_read, RANK() OVER (ORDER BY rows_read) AS _rank FROM t1;
+---------------------+-----------+-------+
| dt                  | rows_read | _rank |
+---------------------+-----------+-------+
| 2022-05-31 17:16:00 |         0 |     1 |
| 2022-05-31 17:16:03 |         0 |     1 |
| 2022-05-31 17:16:04 |         0 |     1 |

..
| 2022-05-31 18:26:54 |   1652200 |  5297 |
| 2022-05-31 18:20:12 |   1656600 |  5298 |
| 2022-05-31 18:40:09 |   1668900 |  5299 |
+---------------------+-----------+-------+
5299 rows in set (0.00 sec)

要はこの _rank / 5299 がパーセンタイルになるので割ってやればいいんだけど、5299の部分はもちろん動的に作りたい……が、COUNT取らないといけないので一旦WITH句に逃がす。

mysql80 209534> WITH
    -> _count AS (
    ->   SELECT COUNT(*) AS c FROM t1
    -> ),
    -> _ranked AS (
    ->   SELECT dt, rows_read, RANK() OVER (ORDER BY rows_read) AS _rank FROM t1
    -> )
    -> SELECT dt, rows_read, _rank, c, (_rank / c) * 100 AS percentile
    -> FROM _ranked JOIN _count;

+---------------------+-----------+-------+------+------------+
| dt                  | rows_read | _rank | c    | percentile |
+---------------------+-----------+-------+------+------------+
| 2022-05-31 17:16:00 |         0 |     1 | 5299 |     0.0189 |
| 2022-05-31 17:16:03 |         0 |     1 | 5299 |     0.0189 |
| 2022-05-31 17:16:04 |         0 |     1 | 5299 |     0.0189 |

..
| 2022-05-31 18:26:54 |   1652200 |  5297 | 5299 |    99.9623 |
| 2022-05-31 18:20:12 |   1656600 |  5298 | 5299 |    99.9811 |
| 2022-05-31 18:40:09 |   1668900 |  5299 | 5299 |   100.0000 |
+---------------------+-----------+-------+------+------------+
5299 rows in set (0.01 sec)

この結果セットの WHERE percentile >= 95 ORDER BY percentile ASC LIMIT 1 が95%ile値ってことで合ってるかしらん。

mysql80 209534> WITH
    -> _count AS (
    ->   SELECT COUNT(*) AS c FROM t1
    -> ),
    -> _ranked AS (
    ->   SELECT dt, rows_read, RANK() OVER (ORDER BY rows_read) AS _rank FROM t1
    -> ),
    -> _percentiled AS (
    ->   SELECT dt, rows_read, (_rank / c) * 100 AS percentile
    ->   FROM _ranked JOIN _count
    -> )
    -> SELECT rows_read, percentile
    -> FROM _percentiled
    -> WHERE percentile >= 95
    -> ORDER BY percentile ASC
    -> LIMIT 1;
+-----------+------------+
| rows_read | percentile |
+-----------+------------+
|   1562614 |    95.0179 |
+-----------+------------+
1 row in set (0.01 sec)

とりあえず出てきた。

mysql80 209534> WITH
    -> _count AS (
    ->   SELECT COUNT(*) AS c FROM t1
    -> ),
    -> _ranked AS (
    ->   SELECT dt, rows_read, RANK() OVER (ORDER BY rows_read) AS _rank FROM t1
    -> )
    -> SELECT dt, rows_read, _rank, c, (_rank / c) * 100 AS percentile
    -> FROM _ranked JOIN _count;

..
| 2022-05-31 18:06:07 |   1562400 |  5031 | 5299 |    94.9424 |
| 2022-05-31 18:22:27 |   1562435 |  5032 | 5299 |    94.9613 |
| 2022-05-31 18:25:30 |   1562506 |  5033 | 5299 |    94.9802 |
| 2022-05-31 18:23:04 |   1562587 |  5034 | 5299 |    94.9991 |
| 2022-05-31 18:44:19 |   1562614 |  5035 | 5299 |    95.0179 |   <--- ちゃんとここの行
| 2022-05-31 18:09:39 |   1562900 |  5036 | 5299 |    95.0368 |
| 2022-05-31 18:34:26 |   1562900 |  5036 | 5299 |    95.0368 |
| 2022-05-31 18:44:26 |   1563000 |  5038 | 5299 |    95.0745 |

..

合ってるかなこれで。

さて、更にこれを「dtを1分単位で丸めて、その枠の中の95%ile値」にしたい。

RANK()を取るところまではいける。

mysql80 209534> SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') AS _dt, rows_read, RANK() OVER (PARTITION BY DATE_FORMAT(dt, '%Y-%m-%d  %H:%i:00') ORDER BY rows_read) AS _rank FROM t1;
+---------------------+-----------+-------+
| _dt                 | rows_read | _rank |
+---------------------+-----------+-------+
| 2022-05-31 17:16:00 |         0 |     1 |
| 2022-05-31 17:16:00 |         0 |     1 |
| 2022-05-31 17:16:00 |         0 |     1 |

..
| 2022-05-31 18:45:00 |   1563137 |    57 |
| 2022-05-31 18:45:00 |   1573000 |    58 |
| 2022-05-31 18:45:00 |   1598500 |    59 |
| 2022-05-31 18:46:00 |         0 |     1 |
+---------------------+-----------+-------+
5299 rows in set (0.02 sec)

さっきはCOUNTで割る数の5299が得られたけど、今度はパーティションごとにCOUNTの値が違うので MAX(_rank) で代用するのをWITHに備え付ける。

mysql80 209534> WITH
    -> _ranked AS (
    ->   SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') AS _dt, rows_read, RANK() OVER (PARTITION BY DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') ORDER BY rows_read) AS _rank FROM t1
    -> ),
    -> _count_max AS (
    ->   SELECT _dt, MAX(_rank) AS c FROM _ranked GROUP BY _dt
    -> )
    -> SELECT _dt, rows_read, _rank, c, (_rank / c) * 100 AS percentile
    -> FROM _ranked JOIN _count_max USING(_dt);
+---------------------+-----------+-------+------+------------+
| _dt                 | rows_read | _rank | c    | percentile |
+---------------------+-----------+-------+------+------------+
| 2022-05-31 17:16:00 |         0 |     1 |   59 |     1.6949 |
| 2022-05-31 17:16:00 |         0 |     1 |   59 |     1.6949 |
| 2022-05-31 17:16:00 |         0 |     1 |   59 |     1.6949 |

..
| 2022-05-31 18:45:00 |   1563137 |    57 |   59 |    96.6102 |
| 2022-05-31 18:45:00 |   1573000 |    58 |   59 |    98.3051 |
| 2022-05-31 18:45:00 |   1598500 |    59 |   59 |   100.0000 |
| 2022-05-31 18:46:00 |         0 |     1 |    1 |   100.0000 |
+---------------------+-----------+-------+------+------------+
5299 rows in set (0.05 sec)

ここからWHEREと…フレームごとにORDER BY percentile ASC LIMIT 1は難しいので、フレームごとのMIN(rows_read)でいけるかしら。

mysql80 209534> WITH
    -> _ranked AS (
    ->   SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') AS _dt, rows_read, RANK() OVER (PARTITION BY DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') ORDER BY rows_re
ad) AS _rank FROM t1
    -> ),
    -> _count_max AS (
    ->   SELECT _dt, MAX(_rank) AS c FROM _ranked GROUP BY _dt
    -> ),
    -> _percentiled AS (
    ->   SELECT _dt, rows_read, _rank, c, (_rank / c) * 100 AS percentile
    ->   FROM _ranked JOIN _count_max USING(_dt)
    -> )
    -> SELECT _dt, MIN(rows_read)
    -> FROM _percentiled
    -> WHERE percentile >= 95
    -> GROUP BY _dt;
+---------------------+----------------+
| _dt                 | MIN(rows_read) |
+---------------------+----------------+
| 2022-05-31 17:16:00 |           1600 |
| 2022-05-31 17:17:00 |            822 |
| 2022-05-31 17:18:00 |           8022 |
| 2022-05-31 17:19:00 |          79000 |

..
| 2022-05-31 18:43:00 |        1594800 |
| 2022-05-31 18:44:00 |        1578900 |
| 2022-05-31 18:45:00 |        1563137 |
| 2022-05-31 18:46:00 |              0 |
+---------------------+----------------+
91 rows in set (0.05 sec)

18:43近辺の前のクエリの結果で調べてみると

| 2022-05-31 18:43:00 |   1586000 |    52 |   59 |    88.1356 |
| 2022-05-31 18:43:00 |   1588800 |    53 |   59 |    89.8305 |
| 2022-05-31 18:43:00 |   1589332 |    54 |   59 |    91.5254 |
| 2022-05-31 18:43:00 |   1594400 |    55 |   59 |    93.2203 |
| 2022-05-31 18:43:00 |   1594600 |    56 |   59 |    94.9153 |
| 2022-05-31 18:43:00 |   1594800 |    57 |   59 |    96.6102 |
| 2022-05-31 18:43:00 |   1610900 |    58 |   59 |    98.3051 |
| 2022-05-31 18:43:00 |   1615500 |    59 |   59 |   100.0000 |
| 2022-05-31 18:44:00 |         0 |     1 |   58 |     1.7241 |
| 2022-05-31 18:44:00 |         0 |     1 |   58 |     1.7241 |
| 2022-05-31 18:44:00 |         0 |     1 |   58 |     1.7241 |

うん、合ってそう。
合ってますかね？

登録: 投稿 ( Atom )

日々の覚書

GA

2022/06/29

DROP TABLEがどれくらいその他のトラフィックに影響を与えるか……を、手元で測る方法

TL;DR

2022/06/28

MySQLが勝手に作るファイルのパーミッションを指定する

TL;DR

2022/06/22

MySQLだけでWindow関数を使って@rowとか使わずに95%ileを計算したい