Description:
On an example MySQL instance with 150K InnoDB tables(each table have only one row or even empty table), a specific query to information_schema.tables leads to memory consumption over 9GB RSS, with table number incress, the memory cost more and more. I have test 5.7.8 and 5.7.44 version, both have the problem.
How to repeat:
Create 150000 tables with sysbench and run the query.
1. prepare 150K tables.
sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=xxxx --mysql-db=sbtest --table_size=1 --tables=150000 --events=0 --time=60000 --threads=16 oltp_read_write prepare
2. run the following query.
select a.TABLE_SCHEMA db,a.Total_Size db_size,ifnull(b.table_size,'') table_size,ifnull(b.data_length,'') data_length,ifnull(b.index_length,'') index_length,ifnull(b.data_free,'') data_free from (select TABLE_SCHEMA, ROUND(sum(data_length+index_length+data_free)/(1024*1024*1024), 3) AS 'Total_Size' from information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema') group by TABLE_SCHEMA) a left join (select TABLE_SCHEMA, group_concat("'",TABLE_NAME,"'",':',ROUND((data_length+index_length+data_free)/(1024*1024*1024), 3)) table_size, group_concat("'",TABLE_NAME,"'",':',ROUND(data_length/(1024*1024*1024), 3)) data_length, group_concat("'",TABLE_NAME,"'",':',ROUND(index_length/(1024*1024*1024), 3)) index_length, group_concat("'",TABLE_NAME,"'",':',ROUND(data_free/(1024*1024*1024), 3)) data_free from information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema') group by TABLE_SCHEMA) b on a.TABLE_SCHEMA=b.TABLE_SCHEMA;
3. watch the mysqld memory costs.
use linux top or ps commands check mysqld process memory costs.
PS:
After executing this query I_S.tables sql, it was observed that the memory was not released quickly, and there was no memory decrease when flushing tables manually.
Suggested fix:
Able to limit memory overhead when the number of tables is large.
Description: On an example MySQL instance with 150K InnoDB tables(each table have only one row or even empty table), a specific query to information_schema.tables leads to memory consumption over 9GB RSS, with table number incress, the memory cost more and more. I have test 5.7.8 and 5.7.44 version, both have the problem. How to repeat: Create 150000 tables with sysbench and run the query. 1. prepare 150K tables. sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=xxxx --mysql-db=sbtest --table_size=1 --tables=150000 --events=0 --time=60000 --threads=16 oltp_read_write prepare 2. run the following query. select a.TABLE_SCHEMA db,a.Total_Size db_size,ifnull(b.table_size,'') table_size,ifnull(b.data_length,'') data_length,ifnull(b.index_length,'') index_length,ifnull(b.data_free,'') data_free from (select TABLE_SCHEMA, ROUND(sum(data_length+index_length+data_free)/(1024*1024*1024), 3) AS 'Total_Size' from information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema') group by TABLE_SCHEMA) a left join (select TABLE_SCHEMA, group_concat("'",TABLE_NAME,"'",':',ROUND((data_length+index_length+data_free)/(1024*1024*1024), 3)) table_size, group_concat("'",TABLE_NAME,"'",':',ROUND(data_length/(1024*1024*1024), 3)) data_length, group_concat("'",TABLE_NAME,"'",':',ROUND(index_length/(1024*1024*1024), 3)) index_length, group_concat("'",TABLE_NAME,"'",':',ROUND(data_free/(1024*1024*1024), 3)) data_free from information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema') group by TABLE_SCHEMA) b on a.TABLE_SCHEMA=b.TABLE_SCHEMA; 3. watch the mysqld memory costs. use linux top or ps commands check mysqld process memory costs. PS: After executing this query I_S.tables sql, it was observed that the memory was not released quickly, and there was no memory decrease when flushing tables manually. Suggested fix: Able to limit memory overhead when the number of tables is large.