日々の覚書: InnoDB FULLTEXT KEYがメモリを食う件(未解決) の続き。
何がメモリを使ってるのかを performance_schema.memory_summary_global_by_event_name テーブルで(できないだろうなと思いながら)観測してみる。
mysql80> RESTART;
$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_read_write --table_size=1000000 --time=600 --report-interval=1 cleanup
$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_read_write --table_size=1000000 --time=600 --report-interval=1 prepare
$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
$ ps -Ao pid,fname,rss | grep 3591371
3591371 mysqld 498060
↑まずはフルテキストインデックスなしの状態でベンチを流した後の memory_summary_global_by_event_name
mysql80 74> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool | 1 | 0 | 68620288 | 0 | 0 | 1 | 1 | 0 | 68620288 | 68620288 | 65.44 MiB |
| memory/innodb/ut0link_buf | 2 | 0 | 25165888 | 0 | 0 | 2 | 2 | 0 | 25165888 | 25165888 | 89.44 MiB |
| memory/innodb/log_buffer_memory | 1 | 0 | 16778224 | 0 | 0 | 1 | 1 | 0 | 16778224 | 16778224 | 105.44 MiB |
| memory/mysys/KEY_CACHE | 3 | 0 | 8390864 | 0 | 0 | 3 | 3 | 0 | 8390864 | 8390864 | 113.44 MiB |
| memory/sql/TABLE | 6028 | 3948 | 30576081 | 22566230 | 0 | 2080 | 2082 | 0 | 8009851 | 8041086 | 121.08 MiB |
| memory/innodb/sync0arr | 3 | 0 | 7373032 | 0 | 0 | 3 | 3 | 0 | 7373032 | 7373032 | 128.11 MiB |
| memory/sql/log_sink_pfs | 3 | 2 | 5292128 | 49216 | 0 | 1 | 2 | 0 | 5242912 | 5259328 | 133.11 MiB |
| memory/innodb/lock0lock | 33 | 0 | 5086600 | 0 | 0 | 33 | 33 | 0 | 5086600 | 5086600 | 137.97 MiB |
| memory/innodb/ut0pool | 2 | 0 | 4194488 | 0 | 0 | 2 | 2 | 0 | 4194488 | 4194488 | 141.97 MiB |
| memory/temptable/physical_ram | 73 | 69 | 76548384 | 72353952 | 0 | 4 | 4 | 0 | 4194432 | 4194432 | 145.97 MiB |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
mysql80 74> SELECT SUM(current_number_of_bytes_used) FROM performance_schema.memory_summary_global_by_event_name;
+-----------------------------------+
| SUM(current_number_of_bytes_used) |
+-----------------------------------+
| 410046116 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql80 74> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
..
| performance_schema | performance_schema.memory | 242396808 |
+--------------------+-------------------------------------------------------------+-----------+
248 rows in set (0.00 sec)
トータルではまあまあ合ってそうな気がする( memory/temptable/physical_ram はたぶん監視に使ってる pmm-agent だな…失敗したかも )
次にALTER TABLEでFULLTEXT INDEXを足す。
この時点でメモリ使用量が増えている（これ自体は不思議ではない）けど、p_sには計上されず…（これがぐぬぬ）
mysql80 90> ALTER TABLE sbtest.sbtest1 ADD FULLTEXT KEY (c);
Query OK, 0 rows affected, 1 warning (1 min 38.24 sec)
Records: 0 Duplicates: 0 Warnings: 1
$ ps -Ao pid,fname,rss | grep 3591371
3591371 mysqld 659868
mysql80 107> SELECT SUM(current_number_of_bytes_used) FROM performance_schema.memory_summary_global_by_event_name;
+-----------------------------------+
| SUM(current_number_of_bytes_used) |
+-----------------------------------+
| 408419366 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql80 107> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool | 1 | 0 | 68620288 | 0 | 0 | 1 | 1 | 0 | 68620288 | 68620288 | 65.44 MiB |
| memory/innodb/ut0link_buf | 2 | 0 | 25165888 | 0 | 0 | 2 | 2 | 0 | 25165888 | 25165888 | 89.44 MiB |
| memory/innodb/log_buffer_memory | 1 | 0 | 16778224 | 0 | 0 | 1 | 1 | 0 | 16778224 | 16778224 | 105.44 MiB |
| memory/mysys/KEY_CACHE | 3 | 0 | 8390864 | 0 | 0 | 3 | 3 | 0 | 8390864 | 8390864 | 113.44 MiB |
| memory/sql/TABLE | 7177 | 5065 | 37444758 | 29329243 | 0 | 2112 | 2113 | 0 | 8115515 | 8130336 | 121.18 MiB |
| memory/innodb/sync0arr | 3 | 0 | 7373032 | 0 | 0 | 3 | 3 | 0 | 7373032 | 7373032 | 128.22 MiB |
| memory/sql/log_sink_pfs | 3 | 2 | 5292128 | 49216 | 0 | 1 | 2 | 0 | 5242912 | 5259328 | 133.22 MiB |
| memory/innodb/lock0lock | 33 | 0 | 5086600 | 0 | 0 | 33 | 33 | 0 | 5086600 | 5086600 | 138.07 MiB |
| memory/innodb/ut0pool | 2 | 0 | 4194488 | 0 | 0 | 2 | 2 | 0 | 4194488 | 4194488 | 142.07 MiB |
| memory/temptable/physical_ram | 96 | 92 | 100666368 | 96471936 | 0 | 4 | 4 | 0 | 4194432 | 4194432 | 146.07 MiB |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)
一度リスタートして再度600秒ベンチ。
mysql80 107> RESTART;
Query OK, 0 rows affected (0.00 sec)
$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
$ ps -Ao pid,fname,rss | grep 3592261
3592261 mysqld 567808
mysql80 48> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool | 1 | 0 | 68620288 | 0 | 0 | 1 | 1 | 0 | 68620288 | 68620288 | 65.44 MiB |
| memory/innodb/ut0link_buf | 2 | 0 | 25165888 | 0 | 0 | 2 | 2 | 0 | 25165888 | 25165888 | 89.44 MiB |
| memory/innodb/log_buffer_memory | 1 | 0 | 16778224 | 0 | 0 | 1 | 1 | 0 | 16778224 | 16778224 | 105.44 MiB |
| memory/mysys/KEY_CACHE | 3 | 0 | 8390864 | 0 | 0 | 3 | 3 | 0 | 8390864 | 8390864 | 113.44 MiB |
| memory/sql/TABLE | 4204 | 2239 | 19897956 | 12217500 | 0 | 1965 | 895 | 0 | 7680456 | 7680456 | 120.77 MiB |
| memory/innodb/sync0arr | 3 | 0 | 7373032 | 0 | 0 | 3 | 3 | 0 | 7373032 | 7373032 | 127.80 MiB |
| memory/sql/log_sink_pfs | 3 | 2 | 5292128 | 49216 | 0 | 1 | 2 | 0 | 5242912 | 5259328 | 132.80 MiB |
| memory/innodb/lock0lock | 33 | 0 | 5086600 | 0 | 0 | 33 | 33 | 0 | 5086600 | 5086600 | 137.65 MiB |
| memory/innodb/ut0pool | 2 | 0 | 4194488 | 0 | 0 | 2 | 2 | 0 | 4194488 | 4194488 | 141.65 MiB |
| memory/temptable/physical_ram | 37 | 33 | 38798496 | 34604064 | 0 | 4 | 4 | 0 | 4194432 | 4194432 | 145.65 MiB |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.02 sec)
mysql80 48> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
..
| performance_schema | performance_schema.memory | 240987688 |
+--------------------+-------------------------------------------------------------+-----------+
248 rows in set (0.00 sec)
やっぱりperformance_schemaから調べるのは難しそう。
取り敢えずtcmalloc使ったら変わるかどうかの切り分けだけやっておく。
$ grep malloc /etc/my.cnf
malloc-lib=/usr/lib64/libtcmalloc.so
$ lsof -p 3639083 | grep mall
mysqld 3639083 yoku0825 mem REG 252,0 334544 68185695 /usr/lib64/libtcmalloc.so.4.5.3
$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_common --table_size=1000000 cleanup
$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_common --table_size=1000000 prepare
$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
$ ps -Ao pid,fname,rss | grep 3639083
3639083 mysqld 518920
フルテキストインデックス無しのケースは大して変わらず（スコアは気持ち上がったけど複数回測ってないので確かではない）
mysql80 165> ALTER TABLE sbtest.sbtest1 ADD FULLTEXT KEY (c);
Query OK, 0 rows affected, 1 warning (1 min 37.04 sec)
Records: 0 Duplicates: 0 Warnings: 1
$ ps -Ao pid,fname,rss | grep 3639083
3639083 mysqld 704996
mysql80 184> RESTART;
Query OK, 0 rows affected (0.00 sec)
$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
$ ps -Ao pid,fname,rss | grep 3640646
3640646 mysqld 588272
おー、だいぶ穏やかになった。tcmallocに変えて改善するということはメモリのフラグメンテーション関連かな…。
0 件のコメント :
コメントを投稿