Not the answer you need?
Register and ask your own question!

pt-online-schema-change increase mem

cary90cary90 Current User Role Poster
edited August 19 in Other Tools
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!

Best Answers

Answers

  • cary90cary90 Current User Role Poster
    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
  • cericeri Percona Percona Staff Role
    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?
  • cary90cary90 Current User Role Poster
    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。

  • cary90cary90 Current User Role Poster

    Hi,MR DGB

    We do not have foreign key, if we modify "table_definition_cache" smaller, the qusetion can resolve?

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.