MySQL – Does InnoDB Buffer Pool Store BLOB?

blobbuffer-poolinnodbMySQL

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?

Best Answer

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.

QUERY #1 : Disk Usage by Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

QUERY #2 : Disk Usage by Storage Engine

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

I got responses 10-20 seconds. How? The client uses innodb_file_per_table=0.

I REPEAT : innodb_file_per_table=0

WHAT DO YOU DO NOW ???

Here is why I said "heartbreaking": You would have put all your InnoDB data back inside ibdata1. Would you like to know all the steps to do this ? Here we go...

STEP #1

Add these to /etc/my.cnf

[mysqld]
innodb_file_per_table = 0
innodb_fast_shutdown = 0

STEP #2

service mysql restart

STEP #3

Create a script that converts InnoDB to InnoDB

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > RemakeInnoDB.sql
cat RemakeInnoDB.sql

STEP #4

Execute the script

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} < RemakeInnoDB.sql

EPILOGUE

When everything is done, all the .ibd files will be gone. All the data and index pages will be inside ibdata1. You will find accessing the INFORMATION_SCHEMA against all InnoDB tables shockingly fast.

Here is the InnoDB Architecture

InnoDB Architecture

Note that ibdata1 (a.k.a. System Tablespace will house)

Data Pages
Index Pages
Data Dictionary
Double Write Buffer (Needed for crash recovery)
Insert Buffer (changes to secondary indexes)
Rollback Segments
Undo Space

Don't worry about putting 106G inside ibdata1. MySQL was running with innodb_file_per_table=0 before MySQL 5.5. It can still handle it.

Give it a Try !!!

Thesql innodb_buffer_pool_size let me understand

If your mysql is on dedicated server then you can set the buffer pool to the value 4GB. Initially the queries will run little slow because they require disk seeks. As your data set is very small so you can keep most of the data in memory. Once the data is present in memory performance will increase.

As your server is having huge number of reads and less number of writes good buffer pool will give better results. You can allocate around 70% memory to mysql in dedicated setup.

In crease you Key_buffer_size if you have any myisam tables. Increase the value for the below variable to avoid disk based temporary tables.

max_heap_table_size = 64M 
tmp_table_size = 64M

Dont change any on session variables they will consume more memory and may decrease the performance. Below are the session level variables for which defaults works better to most work loads.

sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size

If you data not change frequently then enable query cache.

query_cache_size = 256M
query_cache_type = ON
query_cache_wlock_invalidate = ON ( to get accurate data )
query_cache_limit = 10M

Increasing innodb_log_buffer_size and innodb_log_file_size may help to gain the performance. But be careful if you increase the file size, it will increase the recovery time.

Last but not lease if you are observing performance hit with 4GB buffer pool try setting innodb_buffer_pool_instances=2

give it a try!

Mysql has to fetch the data from the disk and keep it in memory for the first time you have accessed the data. As buffer pool contains both index and data inside it, your query performance will increase.

You can set the log file size to the best value as described HERE.

The best further description is given in rollando.