Innodb 'Free buffers' going to 0 and system crashing

MySQL & MariaDB Other MySQL® Questions
1

Hi,

i have a mysql 5.0. engine running an innodb db with roughly 3GB of data and 2GB of indexes. It has 5GB of memory available, running on Solaris9.

I noticed that the mysql process starts using the amount of memory as the value innodb_buffer_pool_size in my.cnf (ie 2GB), but after some time starts ramping up and eventually hogs all the memory:

PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
18975 mysql 2900M 1025M cpu2 30 0 1:32:12 16% mysqld/18

PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
28266 mysql 4793M 4421M sleep 59 0 41:43:33 2.5% mysqld/19

I also noticed that the ‘Free Buffers’ reduced with time, and when it becomes 0 mysql stops serving queries.

BUFFER POOL AND MEMORY

Total memory allocated 2373583706; in additional pool allocated 19627008
Buffer pool size 131072
Free buffers 1
Database pages 128099
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 870652, created 648834, written 1617115
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000

  1. Why does the memory ramp up? It looks like a memory leak?
  2. What are the buffers in the ‘buffer_pool_size’ used for and how?
  3. How can i clear these buffers?
2

Free pages should be close to zero if database size is large and system is fully warmed up. This is normal.

Buffer pool is only allocated to the size you specify so it is unlikely the problem. It caches database pages.

My educated guess would be you might be leaking prepared statements.

Please post SHOW INNODB STATUS and SHOW STATUS when MySQL server has “leaked” large amount of memory