[MYSQL] query of mysql5.7-bug-information_schema tables may cause memory leaks

[11 May 2017 6:57] Cho-Shing Ma

Description:

Running a query (on Information _ Schema.tables (where clause) to exclude system schema, showing memory growing eventually Out-of-memory. Number of table entries around 20000 (40 databases with 500 tables for each db)

Mysql-uroot-h227.0.0.1-e "select concat_ws ('|, ifnull (TABLE_SCHEMA,''), ifnull (TABLE_NAME,''), ifnull (ENGINE,''), ifnull (TABLE_ROWS,'0'), ifnull (DATA_LENGTH,'0'), ifnull (INDEX_LENGTH,'0'), ifnull (DATA_FREE,'0'), ifnull (TABLE_COLLATION,''), ifnull (CREATE_TIME,''), ifnull (UPDATE_TIME) '') from information_schema.TABLES where TABLE_SCHEMA not in ('mysql','test','information_schema','performance_schema','sys') "

How to repeat:

Please refer to attached document.

Table_definition_cache=10000

Table_open_cache = 10000

Innodb_open_files=10000

Open_files_limit=10000

Run the sql query every second and record the memory growing from OS and the mysqld.

Mysql-uroot-h227.0.0.1-e "select concat_ws ('|, ifnull (TABLE_SCHEMA,''), ifnull (TABLE_NAME,''), ifnull (ENGINE,''), ifnull (TABLE_ROWS,'0'), ifnull (DATA_LENGTH,'0'), ifnull (INDEX_LENGTH,'0'), ifnull (DATA_FREE,'0'), ifnull (TABLE_COLLATION,''), ifnull (CREATE_TIME,''), ifnull (UPDATE_TIME) '') from information_schema.TABLES where TABLE_SCHEMA not in ('mysql','test','information_schema','performance_schema','sys') "

Suggested fix:

No idea but the memory is growing.

A few days ago, the author encountered a suspected "memory" leak, and was confused for a moment, then looked for clues from the official mysql and found the above bug (see figure, bug id 86279). Querying the tables table under information_schema caused a memory overflow. After a simple test, it seems that this is really the case. By the same token, the other tables under this schema may have the same problem.

For systems with a particularly large number of tables, you may need to be more cautious. But there is no need to panic, the recurrence of the above phenomenon is to repeat the query, and then the memory gradually increases, and finally OOM.

The above information is for your reference only!

Percona 5.7

Https://bugs.launchpad.net/percona-server/+bug/1693511-- performance takes up a lot of memory-- stabilizes around 10g.