# mysql5.5 use 7 times memory after 2 days

**URL:** <https://forums.percona.com/t/mysql5-5-use-7-times-memory-after-2-days/1663>\
**Category:** Other MySQL® Questions\
**Created:** [August 25, 2011, 8:12am UTC](https://forums.percona.com/t/mysql5-5-use-7-times-memory-after-2-days/1663 "2011-08-25T08:12:47Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![diablohsh](https://avatars.discourse-cdn.com/v4/letter/d/51bf81/32.png) [@diablohsh](https://forums.percona.com/u/diablohsh)\
**Post date:** [August 25, 2011, 8:12am UTC](https://forums.percona.com/t/mysql5-5-use-7-times-memory-after-2-days/1663/1 "2011-08-25T08:12:47Z")

</div>

here are the key memory variables i set for the my.cnf

innodb\_buffer\_pool\_size = 2G  
innodb\_additional\_mem\_pool\_size = 64M  
innodb\_log\_file\_size = 256M  
innodb\_log\_buffer\_size = 32M  
innodb\_file\_per\_table = 1  
innodb\_thread\_concurrency = 200  
max\_connections=1000  
read\_buffer\_size = 128k  
sort\_buffer\_size = 128k  
read\_rnd\_buffer\_size = 512k

The mysql server use about 2.7g at startup and after 2 day’s of running, the memory go up to 14g.

It’s a heavy cpu load game server database, always have 40% cpu usage. there are hundreds of innodb table in the database and only 2 memory table which is writen to in 2 time per second. there are simultanous 374 long connections connection to one database.

I have read many article on percona about memory usage about mysql and haven’t figure out what’s wrong with my server.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 25, 2011, 8:20am UTC](https://forums.percona.com/t/mysql5-5-use-7-times-memory-after-2-days/1663/2 "2011-08-25T08:20:57Z")

</div>

The memory table could become fragmented. Could you try ALTER TABLE … ENGINE=Memory; on the memory tables? This should not drop any data, but recreates the table.

---

<div class="post-metadata">

**Author:** ![diablohsh](https://avatars.discourse-cdn.com/v4/letter/d/51bf81/32.png) [@diablohsh](https://forums.percona.com/u/diablohsh)\
**Post date:** [August 25, 2011, 8:37am UTC](https://forums.percona.com/t/mysql5-5-use-7-times-memory-after-2-days/1663/3 "2011-08-25T08:37:27Z")

</div>

Some extra information:

1. i have just switched from mysql 5.1.52 to mysql 5.5.14.
2. i have just turn on per table table space and move some table to another hard disk on the server to scatter the io load.
3. this situation happen on Percona Server 5.5.14 too
4. the server has a middle load of sort query on average of 2000 result rows.
5. the server has totally 2368900 prepared statements for all connections.
6. 1 months ago, the server use 5g memory steadly for months.

---

<div class="post-metadata">

**Author:** ![diablohsh](https://avatars.discourse-cdn.com/v4/letter/d/51bf81/32.png) [@diablohsh](https://forums.percona.com/u/diablohsh)\
**Post date:** [August 25, 2011, 8:40am UTC](https://forums.percona.com/t/mysql5-5-use-7-times-memory-after-2-days/1663/4 "2011-08-25T08:40:06Z")

</div>

@gmouse

i have before try alter the engine of all memory table to innodb and change it back to the memory engine . things doesn’t change.