mysql5.5 use 7 times memory after 2 days

MySQL & MariaDB Other MySQL® Questions
1

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.

2

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.

3

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.
4

@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.