pt-online-schema-change increase mem

Polyglot Projects Other Tools
1

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!

2

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

3

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

4

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?

5

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。

6

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.”


7

Hi，MR DGB

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