# pt-online-schema-change increase mem **URL:** \ **Category:** Other Tools\ **Created:** [August 19, 2020, 4:10am UTC](https://forums.percona.com/t/pt-online-schema-change-increase-mem/7928 "2020-08-19T04:10:25Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![cary90](https://avatars.discourse-cdn.com/v4/letter/c/da6949/32.png) [@cary90](https://forums.percona.com/u/cary90)\ **Post date:** [August 19, 2020, 4:10am UTC](https://forums.percona.com/t/pt-online-schema-change-increase-mem/7928/1 "2020-08-19T04:10:25Z")
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。 I am looking forward to your reply! ---
**Author:** ![Ceri\_Williams](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ceri_williams/32/1218_2.png) [@Ceri\_Williams](https://forums.percona.com/u/Ceri_Williams)\ **Post date:** [September 11, 2020, 3:07am UTC](https://forums.percona.com/t/pt-online-schema-change-increase-mem/7928/2 "2020-09-11T03:07:56Z")
Hi cary90, Please provide an example of some table structures and the full command that you are running pt-online-schema-change to clarify your scenario. Thanks ---
**Author:** ![cary90](https://avatars.discourse-cdn.com/v4/letter/c/da6949/32.png) [@cary90](https://forums.percona.com/u/cary90)\ **Post date:** [September 11, 2020, 6:11am UTC](https://forums.percona.com/t/pt-online-schema-change-increase-mem/7928/3 "2020-09-11T06:11:15Z")
table structer like this: CREATE TABLE `table` ( `id` bigint(20) unsigned NOT NULL AUTO\_INCREMENT COMMENT ‘auto increment id’, `app` bigint(20) unsigned NOT NULL, `uid` bigint(20) unsigned NOT NULL , `type` tinyint(4) unsigned NOT NULL , `fs` bigint(20) unsigned NOT NULL , `path` varchar(1024) NOT NULL COMMENT , `md5` varchar(32) NOT NULL COMMENT ‘file md5’, `server` varchar(32) NOT NULL DEFAULT ‘’ , `size` bigint(20) unsigned NOT NULL , `cate` tinyint(4) unsigned NOT NULL , `status` tinyint(4) unsigned NOT NULL , `sh` int(10) unsigned NOT NULL , `lo` varchar(512) NOT NULL DEFAULT ‘’ ‘, `ta` varchar(512) NOT NULL DEFAULT ‘’ , `info` varchar(1024) NOT NULL DEFAULT ‘’, `poi` varchar(1024) NOT NULL DEFAULT ‘’ , `picset_id` bigint(20) unsigned NOT NULL DEFAULT ‘0’ , `task_status` tinyint(4) unsigned NOT NULL DEFAULT ‘0’ , `extra_info` varchar(1024) NOT NULL DEFAULT ‘’ `ctime` int(10) unsigned NOT NULL , `mtime` int(10) unsigned NOT NULL , `reserved` int(10) NOT NULL DEFAULT ‘0’ , `reserved` varchar(2048) NOT NULL DEFAULT ‘’ , PRIMARY KEY (`id`), UNIQUE KEY `app_id` (`app`,`uid`,`fs`,`picset_id`), KEY `idx_uid_md5` (`uid`,`md5`), KEY `idx_uid_status_mtime` (`uid`,`status`,`mtime`), KEY `idx_uid_status_shoottime` (`uid`,`status`,`mtime`)) ENGINE=InnoDB AUTO\_INCREMENT=650223 DEFAULT CHARSET=utf8mb4 ROW\_FORMAT=COMPRESSED KEY\_BLOCK\_SIZE=8 command: ./bin/pt-online-schema-change --user=$user --password=$password --host=localhost  --socket=/home/mysql/mysql//tmp/mysql.sock  --alter=’$sql’ --nocheck-replication-filters  --max-load Threads\_running=100 --critical-load Threads\_running=500  --max-lag=30 --check-interval=30  --chunk-size=1000  --recursion-method=dsn=D=percona,t=dsns  D=$db,t=$i --execute ---
**Author:** ![Ceri\_Williams](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ceri_williams/32/1218_2.png) [@Ceri\_Williams](https://forums.percona.com/u/Ceri_Williams)\ **Post date:** [September 11, 2020, 7:12am UTC](https://forums.percona.com/t/pt-online-schema-change-increase-mem/7928/4 "2020-09-11T07:12:18Z")
Thanks for the example, but there are no foreign keys in there. Please can you add an example of a table that references this one? ---
**Author:** ![cary90](https://avatars.discourse-cdn.com/v4/letter/c/da6949/32.png) [@cary90](https://forums.percona.com/u/cary90)\ **Post date:** [September 16, 2020, 1:21am UTC](https://forums.percona.com/t/pt-online-schema-change-increase-mem/7928/5 "2020-09-16T01:21:03Z")
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。 ![]() ---
**Author:** ![DGB](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dgb/32/79_2.png) [@DGB](https://forums.percona.com/u/DGB)\ **Post date:** [September 16, 2020, 10:16am UTC](https://forums.percona.com/t/pt-online-schema-change-increase-mem/7928/6 "2020-09-16T10:16:58Z")
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](https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_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.” ---
**Author:** ![cary90](https://avatars.discourse-cdn.com/v4/letter/c/da6949/32.png) [@cary90](https://forums.percona.com/u/cary90)\ **Post date:** [September 17, 2020, 2:00am UTC](https://forums.percona.com/t/pt-online-schema-change-increase-mem/7928/7 "2020-09-17T02:00:57Z")
Hi，MR DGB We do not have foreign key, if we modify “table\_definition\_cache” smaller, the qusetion can resolve?