-
[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 20,000 (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.
日前，作者遇到一个疑似“内存”泄露的问题，一时找不头绪，然后就从mysql的官方上找线索，发现了上面的bug（见上图，bug id 86279 ),查询information_schema下的tables表导致内存溢出。 经动手简单测试，貌似还真有这回事。 同理，该schema下的其他的一下表，可能也存在这个问题。
对于表数量特别多的系统，可能更需要慎重。 但也无须惊慌，上面写的重现现象是重复查询，然后内存逐步增长，最终OOM.
以上信息仅供各位朋友参考！！！
percona 5.7
- https://bugs.launchpad.net/percona–server/+bug/1693511 ––performance占用大量内存––10G左右稳定下来
