Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

share|improve this answer
    
Thank you Michael for your detailed explanation. I got your point that we don't wish for free space in buffer pool. But I wonder how would I know if there is enough space or every time it has to make up space to accomodate new data into buffer pool. –  MNM Oct 18 '12 at 18:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.