Queries on information schema allocating too much memory.
Posted by: Nadir Lloret
Date: November 18, 2010 10:10AM

Hi everybody.

I'm trying to figure out what is allocating so much memory at this situation:

SELECT COUNT(1) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='db_example';

On db_example, we have around ~550 innodb tables with 9 partitions each.
After query execution, I can see 700 Mbytes of memory have been allocated to mysql.

shell> ps axu | grep ^mysql | grep /usr/sbin | awk '{print $6;}'
349036
shell> mysql -uroot -p -e "SELECT COUNT(1) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='db_example'"
+----------+
| COUNT(1) |
+----------+
| 4189 |
+----------+
shell> ps axu | grep ^mysql | grep /usr/sbin | awk '{print $6;}'
1115952

1115952-349036 = 766916 (~700M)


show engine innodb status, discards my first theory... "memory was being used to load tables into data dictionary", but:

"Dictionary cache 65076408 (8851984 + 56224424)"

Dictionary is only about 62 Mbytes.. nothing compared to 700Mbytes used to execute query.

If I run same query again and again, no more memory is allocated, (that was what made me think of data dict)

Could anybody give me a clue on what is memory being used?


SO is Linux Ubuntu 10.4
Mysql version is Percona Server 11.1, based on mysql 5.1.47-rel11.1-log


Thanks in advance

Options: ReplyQuote


Subject
Views
Written By
Posted
Queries on information schema allocating too much memory.
7105
November 18, 2010 10:10AM
2707
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.