table_open_cacheに関する実験

TL;DR

• table_open_cache を増やすとメモリ使用量が増えるというのは聞いたことがあるけど実際どれくらい増えるのか調べてみたかった
• 1コネクションで1つずつ SELECT * FROM t で65535個のテーブルを開かせてみた。
• かなりシンプルなテーブルなので複雑なのにするともっと食うかもしれない
• 競合が起こらないと仮定したケースなのでスピードはテーブルキャッシュヒット率に関わらずほぼ一定
• 起動直後が遅いのはデータディクショナリがあったまってないからじゃなかろうか
• 肝心のメモリ使用量は綺麗な結果にはならなかった
  • ↓memory(%)は全部でだいたい15GBなので1%が150MB
  • 雑に、1万増えると270MBくらい？

table_open_cache	count	memory(%)	time(s)
4000	0	1.9	N/A
4000	1	6.6	155
4000	2	7.2	105
4000	3	7.2	107
8000	0	3.1	N/A
8000	1	6.6	139
8000	2	7.4	104
8000	3	7.4	105
12000	0	3.2	N/A
12000	1	6.8	149
12000	2	9.0	109
12000	3	9.5	109
24000	0	3.1	N/A
24000	1	7.2	146
24000	2	9.3	109
24000	3	11.3	110
65535	0	3.1	N/A
65535	1	7.3	146
65535	2	9.1	104
65535	3	9.7	105
100000	0	3.1	N/A
100000	1	7.9	146
100000	2	10.2	104
100000	3	11.1	104

---

準備。
d2スキーマに65535個のテーブルを作る。

$ perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("CREATE TABLE IF NOT EXISTS d2.%05d (num int)", $n); $conn->do($sql); }'

一度シャットダウンして起動しなおす。 innodb_buffer_pool_load_at_startup はOFF。

$ ps auxwwww | grep mysqld

yoku0825 1738789 31.0  1.9 1017124 305532 pts/0  Sl   00:49   0:03 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080

$ mysql80 -sse "SELECT @@table_open_cache"
4000

テスト用のクエリは65535個の各テーブルをSELECT *する（何も入ってないので空っぽ）だけ。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 00:50:48 GMT 2025

real    2m35.310s
user    0m0.989s
sys     0m1.514s
Fri Feb 14 00:53:23 GMT 2025

$ ps auxwwww | grep mysqld 

yoku0825 1738789 14.9  6.6 2896216 1069348 pts/0 Sl   00:49   0:58 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080

1回目の時点では 422 table/sec でオープン完了、メモリの利用率は4.7%増(15.3GBくらいメモリがあるので700MBくらいの増)

テーブルオープンにはデータディクショナリもアクセスするからInnoDBバッファプールもあるだろうしテーブルキャッシュが効かなさそうな数だけど2回目は速くなるはず。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 00:59:08 GMT 2025

real    1m45.284s
user    0m0.906s
sys     0m1.410s
Fri Feb 14 01:00:53 GMT 2025

$ !ps
ps auxwwww | grep mysqld ; date
yoku0825 1738789 11.1  7.2 3027288 1161812 pts/0 Sl   00:49   1:42 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080
yoku0825 1739879  0.0  0.0   9292  1092 pts/0    S+   01:04   0:00 grep --color=auto mysqld
Fri Feb 14 01:04:25 GMT 2025

3回目。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 01:04:47 GMT 2025

real    1m47.792s
user    0m0.776s
sys     0m1.589s
Fri Feb 14 01:06:35 GMT 2025

$ !ps
ps auxwwww | grep mysqld ; date
yoku0825 1738789 13.2  7.2 3027288 1162340 pts/0 Sl   00:49   2:26 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080
yoku0825 1739938  0.0  0.0   9292  1152 pts/0    S+   01:07   0:00 grep --color=auto mysqld
Fri Feb 14 01:07:31 GMT 2025

2回目3回目は 606 table / secくらい。メモリは0.6%増で90MBくらい。

……というのをパラメータを変えながら延々試していた。

1つだけハマったのが、 table_open_cache=100000 と table_open_cache=200000 の時にだけ何故かメモリ使用量が逆転する状況になって、なんでかと頭を捻ってたら

$ grep table_open_cache /usr/mysql/8.0.41/data/error.log
2025-02-14T03:07:36.786684-00:00 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 200000)

MySQLが自動調整していました。

​