Reproducible example.

[Nadir Lloret]

With php script at the end of the post I generate 12 databases with 550 tables partitioned by range on 9 partitions each.

once databases/tables have been created, restart mysql server.
In my case, mysql starts with:
shell> ps axu | grep ^mysql | grep /usr/sbin | awk '{print $6;}'
273308
(266,9 Mb)

If I execute this query and then check memory again...

mysql> SELECT COUNT(1) FROM INFORMATION_SCHEMA.PARTITIONS;
+----------+
| count(1) |
+----------+
| 59475 |
+----------+
1 row in set (1 min 27.97 sec)

shell> ps axu | grep ^mysql | grep /usr/sbin | awk '{print $6;}'
2069336
(2020,8 Mb)

That is 1753,9 Mb of memory usage to satisfy query.

I have my innodb buffer pool limited to 1G. And data dictionary, after query, taken from "show engine innodb status" is:
...
Dictionary cache 369411960 (4427312 + 364984648)
...
(352,2 Mb)

Why memory allocated after query "SELECT count(1) from INFORMATION_SCHEMA.PARTITIONS" has grown ~1700 Mb? Shouldn't it be, as max, innodb_buffer_pool + data_dictionary?

I'm concerned about this, because at our production servers we have a similar structure (few databases with lots of partitioned tables), and every night, an event checks if new partitions have to be created, or old partitions have to be dropped. This nightly events are consuming a lot of memory on the firsts executions that is never freed.




php script to generate partitioned tables
<?php
define("DBHOST","");
define("DBUSER","");
define("DBPASS","");
define("NUMBER_OF_DATABASES","12");
define("TABLES_PER_DATABASE","550");

function executeQuery($q,$link){
$result = mysql_query($q,$link);
if(!$result){
$error = mysql_error($link);
throw New Exception("Error executing query. $error\n");
}
return $result;
}

$dbLink = mysql_connect(DBHOST,DBUSER,DBPASS);
for($j=1;$j<=NUMBER_OF_DATABASES;$j++){
$query = "CREATE DATABASE test".$j;
executeQuery($query,$dbLink);
mysql_select_db('test'.$j,$dbLink);
for($i=1;$i<=TABLES_PER_DATABASE;$i++){
$tableQuery = "CREATE TABLE table_$i (
`idTable_$i` BIGINT NOT NULL,
`month` TINYINT UNSIGNED NOT NULL,
`col1` MEDIUMINT UNSIGNED NOT NULL,
`col2` MEDIUMINT UNSIGNED NOT NULL,
`col3` MEDIUMINT UNSIGNED NOT NULL,
`col4` MEDIUMINT UNSIGNED NOT NULL,
`col5` MEDIUMINT UNSIGNED NOT NULL,
`keyColumn` TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (`idTable_$i`,`month`),
KEY `updated` (`keyColumn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
PARTITION BY RANGE (month)
(PARTITION pBlackHole VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p201006 VALUES LESS THAN (19) ENGINE = InnoDB,
PARTITION p201007 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p201008 VALUES LESS THAN (21) ENGINE = InnoDB,
PARTITION p201009 VALUES LESS THAN (22) ENGINE = InnoDB,
PARTITION p201010 VALUES LESS THAN (23) ENGINE = InnoDB,
PARTITION p201011 VALUES LESS THAN (24) ENGINE = InnoDB,
PARTITION p201012 VALUES LESS THAN (25) ENGINE = InnoDB,
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB)";
executeQuery($tableQuery,$dbLink);
}
}
?>

Also reproduced with mysql 5.1.53 on ubuntu 10.4, just to see if it was percona server issue.