MySQL-5.5 InnoDB memory issue

Question

Version in use is mysql-5.5.24.

In the Enterprise version of MySQL innodb_buffer_pool space is released after a while, however this does not appear to be happening in the community version.

I am not seeing free space in innodb_buffer_pool even after all the apps are shut down.

Is there such a difference between the Enterprise and Community versions of MySQL?

Answer 1

In an ideal world, all of your data would be resident in the InnoDB buffer pool, all the time... so I had a really hard time thinking of a situation where evicting data from the InnoDB buffer pool would be a good thing, but I did find two cases.

I assume you're referring to this value:

mysql> show status like 'Innodb_buffer_pool_pages_free';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_free | 28    |
+-------------------------------+-------+
1 row in set (0.00 sec)

I can't directly address the Enterprise aspect, since I don't use it, but I can tell you that I was able to get "MySQL Community Server (GPL) 5.5.15-log" to actually free up space in the InnoDB buffer pool.

If you have a light enough traffic load or small enough data set that your InnoDB buffer pool is technically larger than is needed (and therefore already has blocks free, the value of innodb_buffer_pool_pages_free will actually increase if you delete rows from a table that's already in the buffer pool.

In the example above, I started out with a value of 35 on a test server with very low traffic. I did SELECT * from a table that probably wasn't all in the pool, which dropped the value to 27. Then I deleted everything from the table and the value rose to 29... so at least in some cases, deletes from a table that is already in the pool will trigger an increase in the pages_free value, assuming your load isn't such that the pages will be claimed by a different table as soon as they are freed -- which is the case on my servers with much larger datasets and heavier traffic.

Similarly, from a second session, I created a small temporary table using InnoDB. This cost me 4 blocks from the pool. Then I closed that session, and immediately regained the 4 blocks back.

These same tests on a busy server don't yield the same results. The "free" pretty much stays at 0, which means I technically need a larger pool, though performance on that machine is quite adequate.

So the difference you are seeing may be a difference in traffic load and data size, rather than a difference in the server editions.

However, "free space" isn't something to be wished for, here, unless you gain that space by increasing the provisioned size of the pool.

I can think of no reason why actively evicting live data from the InnoDB buffer pool in the interest of "having more free space" there would be beneficial, at all, ever.

If a page is clean (i.e., its contents match the disk files where it came from because it's unmodified or modifications have been written back to the disk files), then it can be reused, without being "free" first. The InnoDB buffer pool is exactly where you want as much of your data as possible to always be resident.

Answer 2

What I find intriguing about this is something I have come to learn since MySQL 5.5 came into existence: If you have a multiple core DB Server, you must configure InnoDB variables to be more core aware.

ASPECT #1

MySQL 5.5 introduced innodb_buffer_pool_instances. This provides the opportunity to segment the InnoDB Buffer Pool into regions. I have come to learn that it should be set to the number of physical cores.

ASPECT #2

From a pure OS point-of-view, if you set innodb_buffer_pool_size too high, you could inadvertently introduce swapping. Here is what mean:

SCENARIO : DB Server with the following

• Dual Core Machine
• 128GB RAM
• 80GB Buffer Pool

What do you notice? The Buffer Pool is more than half the box's RAM. If you run numactl --hardware, it will let you know that one physical core can see only 64GB of RAM. The Buffer Pool is 80GB. What happens to any data parked above the 64GB mark? Swapping has to happen.

RECOMMEDATION : Please set innodb_buffer_pool_instances to the number of physical cores (in this case, 2). You should also run numactl --interleave all. Please look over more information about MySQL 5.5 InnoDB Buffer Pool aspects.

I learned a lot about this from Jeremy Cole's Blog

• http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/
• http://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/

EPILOGUE

Your original question was about a lingering Buffer Pool. I am simply giving you supplemental information about how the OS may view a Buffer Pool. Implementing these suggestion may help the OS cooperate in letting go of the RAM.

CAVEAT

Percona Server actually included numactl --interleave all in /etc/init.d/mysql. Maybe try out Percona Server and see if it helps release the Buffer Pool.