Hi, Our MySQL service has about 10000 tables. When we use Pt online schema change to change tables, MySQL memory grows rapidly. Query information_schema.key_ column_ usage can be seen in the slow log,the table is used to check the foreign key constraint SQL,like this:
SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema=‘XXX’ AND referenced_table_name=‘XXX’;
After stopping Pt online schema change, we played back some SQL statements. The memory growth is very fast. Therefore, we think it is the memory growth caused by querying the table . The new version adds the parameter - [no] check foreign keys. We used the – nocheck-foreign-keys parameter to solve this problem, but the memory problem was not solved,why is there still query information_ schema.key_ column_ usage SQL statement in usage? This problem can be easily repeated. You can create tens of thousands of tables in the database. The MySQL version is 5.6, key_ column_ Usage is a MEM engine。
Hello, you can create many tables like 20000 tables, write a small amount of data, and then execute through Pt. The problem can be repeated, I amd looking forward to your conclusion,thanks。
Hi Cary90, the issue is not particular of the tool but a MySQL behavior between INFORMATION_SCHEMA and a huge amount of tables. The issue there is that it becomes slower and slower at the point that something is useless and that’s because it have to load a huge amount of data to memory. Now, the other factor in your case are the Foreign Keys itself. MySQL 5.6 have a variable called “table_definition_cache” that is used to set the limit to the memory used by opened tables, via a LRU mechanism. However, when in presence of Foreign Keys, that limit is not honoured: “The number of table instances with cached metadata could be higher than the limit defined by table_definition_cache, because InnoDB system table instances and parent and child table instances with foreign key relationships are not placed on the LRU list and are not subject to eviction from memory.”