【MYSQL】mysql5.7-bug -information_schema的表被查詢可能導致記憶體洩漏

小亮520cl發表於2018-01-11
MySqlORM記憶體


  1. [11 May 2017 6:57] Cho-Shing Ma
  2. Description:
  3. 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)

  5. mysql -uroot -h127.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');"

  7. How to repeat:
  8. Please refer to attached document.

  10. table_definition_cache=10000
  11. table_open_cache =10000
  12. innodb_open_files=10000
  13. open_files_limit=10000

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

  17. mysql -uroot -h127.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');"

  19. Suggested fix:
  20. No idea but the memory is growing.


         日前，作者遇到一個疑似“記憶體”洩露的問題，一時找不頭緒，然後就從mysql的官方上找線索，發現了上面的bug（見上圖，bug id 86279 ),查詢information_schema下的tables表導致記憶體溢位。 經動手簡單測試，貌似還真有這回事。 同理，該schema下的其他的一下表，可能也存在這個問題。

         對於表數量特別多的系統，可能更需要慎重。 但也無須驚慌，上面寫的重現現象是重複查詢，然後記憶體逐步增長，最終OOM.

         

         以上資訊僅供各位朋友參考！！！



percona 5.7 

  1. https://bugs.launchpad.net/percona-server/+bug/1693511 --performance佔用大量記憶體--10G左右穩定下來

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-2150034/，如需轉載，請註明出處，否則將追究法律責任。

相關文章