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が自動調整していました。
