Query to INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS leads to huge memory usage
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Medium
|
Unassigned | ||
5.1 |
Won't Fix
|
Medium
|
Unassigned | ||
5.5 |
Fix Released
|
Medium
|
Unassigned | ||
5.6 |
Fix Released
|
Medium
|
Unassigned |
Bug Description
This upstream bug is hitting PS 5.5.30-rel30.2-log also http://
Description:
On an example MySQL instance with 28k empty InnoDB tables, a specific query to information_
How to repeat:
Create 28000 tables with create_tables.php script attached to the issue.
Then run following query and watch memory utilization:
SELECT concat('Select ''', a.TABLE_
ifnull(
FROM information_
informatio
WHERE (a.TABLE_
AND a.TABLE_
AND a.TABLE_SCHEMA NOT IN ('mysql',
AND b.DATA_TYPE IN ('blob',
UNION
SELECT concat('Select ''', TABLE_SCHEMA,
FROM information_
WHERE concat(
(SELECT concat(
FROM information_
WHERE DATA_TYPE IN ('blob',
AND TABLE_SCHEMA NOT IN ('mysql',
ORDER BY 1;
In peak time of the run, just before the query finishes, mysqld RSS size gets to 38.5032 GB. Notable memory usage growth starts when query state changes from "Opening tables" to "checking permissions".
The same behaviour on 5.5.36, 5.6.17 and 5.7.4. MySQL settings used:
query-cache-type = 0
query-cache-size = 0
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_open_files = 50000
innodb_flush_method = O_DIRECT
Tables present on this instance:
mysql >SELECT engine, count(*) tables, concat(
+------
| engine | tables | rows | data | idx | total_size | idxfrac |
+------
| NULL | 59 | NULL | NULL | NULL | NULL | NULL |
| CSV | 2 | 0.00M | 0.00G | 0.00G | 0.00G | NULL |
| PERFORMANCE_SCHEMA | 75 | 2.17M | 0.00G | 0.00G | 0.00G | NULL |
| MEMORY | 50 | NULL | 0.00G | 0.00G | 0.00G | NULL |
| MyISAM | 31 | 0.00M | 0.00G | 0.00G | 0.00G | 0.16 |
| InnoDB | 28007 | 0.34M | 0.46G | 0.85G | 1.31G | 1.87 |
+------
6 rows in set (1 min 4.41 sec)
Valgrind massif output attached.
In 5.7's ps_helper output we can see:
mysql [localhost] {root} (performance_
+------
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+------
| memory/
| memory/
| memory/sql/TABLE | 10063 | 21.58 MiB | 2.20 KiB | 10138 | 21.98 MiB | 2.22 KiB |
| memory/
| memory/
| memory/
| memory/
| memory/
| memory/
| memory/
+------
10 rows in set (0.00 sec)
On a machine with 32GB of RAM this query triggers OOMkiller, so had to test on bigger one.
Suggested fix:
Fix if this is a memory leak. Or explain why this happens otherwise.
tags: | added: upstream |
Able to confirm with PS 5.6.21. With 12K tables, RSS grows from around 800MB to 5.2 GB and then mysql crashed by OOMKiller before query finish.
mysql> SELECT concat('Select ''', a.TABLE_ SCHEMA, ',',a.TABLE_ NAME,', ', DATA_LENGTH, ',', INDEX_LENGTH, ',', DATA_FREE, ','', count(*),', ''','', ifnull( max(ifnull( length( ',b.COLUMN_ NAME,') ,0)),0) ,', ''','',' ' ifnull( avg(ifnull( length( ', b.COLUMN_ NAME,') ,0)),0) From ', a.TABLE_ SCHEMA, '.',a.TABLE_ NAME,'; ') FROM information_ schema. tables a,information_ schema. columns b WHERE (a.TABLE_ NAME=b. TABLE_NAME AND a.TABLE_ SCHEMA= b.TABLE_ SCHEMA) AND a.TABLE_SCHEMA NOT IN ('mysql' ,'wdmon' ,'information_ schema' ,'performance_ schema' ) AND b.DATA_TYPE IN ('blob' ,'longblob' ,'longtext' ,'mediumblob' ,'mediumtext' ,'text' ) UNION SELECT concat('Select ''', TABLE_SCHEMA, ',',TABLE_ NAME,', ', DATA_LENGTH, ',', INDEX_LENGTH, ',', DATA_FREE, ','', count(*),', ''','', 0,', ''','',' ' 0 From ', TABLE_SCHEMA, '.',TABLE_ NAME,'; ') FROM information_ schema. tables a WHERE concat( TABLE_SCHEMA, TABLE_NAME) NOT IN (SELECT concat( TABLE_SCHEMA, TABLE_NAME) FROM information_ schema. columns WHERE DATA_TYPE IN ('blob' ,'longblob' ,'longtext' ,'mediumblob' ,'mediumtext' ,'text' )) AND TABLE_SCHEMA NOT IN ('mysql' ,'wdmon' ,'information_ schema' ,'performance_ schema' ) ORDER BY 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
mysql> SELECT engine, count(*) tables, concat( round(sum( table_rows) /1000000, 2),'M') rows, concat( round(sum( data_length) /(1024* 1024*1024) ,2),'G' ) data, concat( round(sum( index_length) /(1024* 1024*1024) ,2),'G' ) idx, concat( round(sum( data_length+ index_length) /(1024* 1024*1024) ,2),'G' ) total_size, round(sum( index_length) /sum(data_ length) ,2) idxfrac FROM information_ schema. TABLES GROUP BY engine ORDER BY sum(data_ length+ index_length) ; ------- ------- +------ --+---- ---+--- ----+-- -----+- ------- ----+-- ------- + ------- ------- +------ --+---- ---+--- ----+-- -----+- ------- ----+-- ------- + ------- ------- +------ --+---- ---+--- ----+-- -----+- ------- ----+-- ------- +
+------
| engine | tables | rows | data | idx | total_size | idxfrac |
+------
| MEMORY | 60 | NULL | 0.00G | 0.00G | 0.00G | NULL |
| PERFORMANCE_SCHEMA | 52 | 0.07M | 0.00G | 0.00G | 0.00G | NULL |
| CSV | 2 | 0.00M | 0.00G | 0.00G | 0.00G | NULL |
| MyISAM | 31 | 0.00M | 0.00G | 0.00G | 0.00G | 0.18 |
| InnoDB | 12345 | 9.33M | 0.65G | 0.71G | 1.36G | 1.09 |
+------
5 rows in set (10.38 sec)
mysql>
nilnandan@ Dell-XPS: ~$ ps -aux | grep mysql mysqld_ safe /var/lib/ mysql --plugin- dir=/usr/ lib/mysql/ plugin --user=mysql --log-error= /var/log/ mysql/error. log --open- files-limit= 50000 --pid-file=/v...
root 16431 0.0 0.0 4444 756 pts/15 S 14:40 0:00 /bin/sh /usr/bin/
mysql 16815 28.0 10.3 1559480 835540 pts/15 Sl 14:40 0:15 /usr/sbin/mysqld --basedir=/usr --datadir=