Reproducible example.
Posted by: Nadir Lloret
Date: November 23, 2010 05:18AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Reproducible example.
2706
November 23, 2010 05:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.