Hi everybody )
I was wondering if somebody could explain this situation on memory allocation… as obviously I was guessing wrong on my calculations… everything has to be said, I’m not any mysql guru… )
With simple 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 | 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 | 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); } }?>Thanks in advance for any clue you could give me on where is mysql using this memory. Possibly this is a expected behaviour, but if that’s the case, I will have to look for a workaround on checking partitions…
Edited: I forgot to post versions of mysql server / os
tried on
percona server 11.1 - revision 51
mysql server 5.1.53
on ubuntu 10.4