Some help understanding memory usage on information_schema queries

  • Filter
  • Time
  • Show
Clear All
new posts

  • Some help understanding memory usage on information_schema queries

    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

    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