Enabling userstat on 5.5.61-38.13-log Percona Server instance, the table information_schema.table_statistics contains a lot of entries whith table name beginning whith #sql, ( something like #sql-7144_194b2f ). I would like to know why and when these tables are created and why they are not removed. Are some memory is consumed and not freed by these tables?
It seems that the created tables namede #sql-XXXXX_YYYY are related to ALTER TABLE statement. Th exact statement is : ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; The memory used by mysqld is always growing and not released or reused. During the test, only the alter operation is running.
is there any known memory leak on this kind of ALTER ?
Hello Stofa, thanks for the question.
There are some blog posts on troubleshooting memory issues that you migh find useful https://www.percona.com/blog/2012/03/21/troubleshooting-mysql-memory-usage/ and https://www.percona.com/blog/2018/06/28/what-to-do-when-mysql-runs-out-of-memory-troubleshooting-guide/
I didn’t find anything obvious but you are welcome to browse the known issues at https://jira.percona.com to see if any fit your scenario
However, in the MySQL documentation, there is reference to the creation of orphan temporary tables and how to deal with them – it states that these are created during a failed ALTER TABLE statement https://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
If you are in the position to consider upgrading your version of MySQL you could well find there are benefits to that as resource usage and performance are constantly improved as new version of MySQL (and Percona Server for MySQL) are released.