-
[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左右穩定下來
