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.

I have several databases all in INNODB, 50GB are BLOB columns, only 700MB are not BLOB data. Tools as mysqltuner ask me to set innodb_buffer_pool_size = 51GB.

Does MySQL store the BLOB in RAM? If yes, how can I configure MYSQL so it doesn't use innodb_buffer_pool_size to store BLOB?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

The InnoDB buffer pool stores copies of anything that is stored in database pages on disk. That includes:

  • Data (which is really the clustered primary index for each table)
  • Secondary indexes
  • Blobs/Text/Varchar that won't fit on data pages
  • Undo pages
  • Change buffer pages
  • etc.

There's no way you can exclude one of these types of pages. They all occupy the buffer pool from time to time, based on demand.

So strictly speaking, the only answer to your question of how to exclude InnoDB Blob data from the buffer pool is to use a different storage engine.

You could also exclude Blob/Text/Varchar columns from your queries. Just don't reference these columns in your query (avoid using SELECT *). InnoDB is smart enough to skip loading the extra pages of Blob/Text/Varchar data it doesn't have to.

But you should take the recommendation of tools like mysqltuner with a grain of salt. You don't necessarily need as much buffer pool as the total amount of data. In fact, it's more typical that you have an 80/20 rule going on: 80% of your queries are served by 20% of your data.

Your mileage may vary -- the types of queries you run determine what is the best compromise on size of buffer pool. You could give it a best-guess allocation, and then monitor the ratio of logical page reads vs. physical page I/O:

mysql> show global status like 'innodb_buffer_pool_read%s';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 151062817 |
| Innodb_buffer_pool_reads         | 3585      |
+----------------------------------+-----------+

Or the page hit rate:

mysql> show engine innodb status\G
...
----------------------
BUFFER POOL AND MEMORY
----------------------
...
Buffer pool hit rate 999 / 1000, young-making rate 36 / 1000 not 0 / 1000
...

If you don't like these ratios, then allocate more RAM to the buffer pool.

share|improve this answer

Part 1 of the answer to your question is no, you can't selectively tell MySQL what not to store in the InnoDB buffer pool. It's an integral part of how InnoDB manipulates table data.

Part 2 is that you don't really want to or need to, because it's smart enough to work out what should stay there if the pool is not large enough to retain your entire data set.

Incidentally, I might have mentioned previously... don't trust tuning scripts.

At best, they may give you "interesting things" to investigate and read up on, but it's absolutely impossible to properly "tune" a MySQL server without understanding the nature of the data that's on that server... and, you're just as likely to tweak your performance downward as well as up.

InnoDB doesn't load anything into the buffer pool that isn't in some way useful. Leave a newly-restarted MySQL server alone without running any queries, and the buffer pool will sit there, allocated but mostly empty. SELECT from a table, and some number of the pages from that table's tablespace on disk will be loaded into the pool, but not usually all of them. Which pages depends on which rows you retrieve, what indexes are needed to find them, whether it's doing read-ahead, etc.

UPDATE a table and the pages with the rows you update will be loaded into the pool, modified in the pool, and then written back to disk in the background. INSERT and DELETE bring pages into the pool and ultimately flush them back to disk also.

But InnoDB keeps track of how "useful" what's currently in the buffer pool is, how recently it was needed, whether any changes have already been written back to disk or not, and will evict pages to make room for other pages when the pool is full. Many of my production servers have an InnoDB buffer pool that is smaller than the server's data set and essentially 100% full 100% of the time, but there's no performance impact from that when a large amount of the data hasn't been accessed for some time.

If a page in the pool hasn't been changed since it was loaded, or if it has been changed but since that time the changes have been written back to disk, and it hasn't been needed for a while, that chunk of the pool is readily reusable for something else the next time InnoDB wants to load something back into the pool from disk... so it's "in use" but still "available."

Unless you're frequently accessing all 50GB of those blobs, the size recommendation from the tuning script is overblown. Of course, in an ideal world, your pool would be larger than all of your tables and indexes and you'd have an all-in-memory database that only had to access the disk to flush changes out... but in the real world of real data, the chances are pretty good that a large portion of your data is not frequently accessed, and there's no way for a simple script to anticipate those conditions.

share|improve this answer

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.