What if table size more than buffer pool size . How data and index are cached in buffer pool size if the table size is more than buffer pool size.
For example:
One of my innodd table size is 231 gb. How data and index are cached in this table
Mysql – Table size bigger than buffer pool size
MySQL
Related Solutions
If you go strictly by that rule of accommodating an addition 10%, here is my suggestion:
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
This will produce exactly what you need to set innodb_buffer_pool_size in /etc/my.cnf. If you want to account for 25% increase in data and indexes over time, please change (SELECT 1 growth) BBB to (SELECT 1.25 growth) BBB
Recently, I answered another question like this in the DBA StackExchange.
If you expect your InnoDB data to grow, you should prepare for it by having a larger InnoDB Buffer Pool.
Where does the idea of having additional 10% extra space for the Buffer Pool come from ???
Please note the following diagram of the InnoDB Architecture
Look in the upper left hand corner where you see the buffer pool. There is a section of it called the "Insert Buffer Part". The purpose of the Insert Buffer is to populate changes to non-unique indexes into the Insert Buffer inside the system tablespace file (a.k.a. ibdata1).
From the diagram, up to 50% of the Buffer Pool can be used to manage the Insert Buffer. That being the case, it would be in your best interests to assign a lot more memory to the Buffer Pool. With a data set of 60M, 256M would be a great place to start,
Related Question
- Mysql – InnoDB buffer pool active pages
- Mysql – Optimal Number of MySQL InnoDB Buffer Pool Instances
- MySql not using Buffer Pool
- Mysql – What happens when the buffer pool size is insufficient in thesql
- Mysql – innodb_buffer_pool_size more data than pool size
- Mysql – select data which is bigger than buffer pool size
- Mysql – Control InnoDB buffer pool allocation in MySQL (5.7+)
- MySQL takes huge memory by ignoring buffer pool size
Best Answer
Short Answer. Not a problem, except for slowing down.
Long Answer: (InnoDB-specific)
innodb_buffer_pool_sizecontrols the InnoDB "cache". It is broken into 16KB "blocks", each of which comes and goes from RAM based, roughly, on a Least-Recently-Used algorithm. (See "1 page granularity" mentioned in a Comment.)So, when you fetch something from a table, the query execution probably needs to look in an index to find where to find the something, and proceeds to fetch blocks as needed to find it. For simple queries, this is a few blocks, for a full table scan of your 231GB, it will involve fetching all the approx 14 million blocks.
When a block needs to be fetched, but the cache is full, then some block must be bumped out of the cache. (Hence the reference to LRU.)
If the "working set" of your data is a lot smaller than 231GB, then there won't be much I/O, and the size of the cache (buffer_pool) is not critical. But if you do table scans, or use UUIDs, then queries will still run, but they will run slower due to the I/O due to [re]loading blocks into the cache.
All operations are done in the cache; almost nothing bypasses the cache. Indexes and Data are each stored in BTrees that are composed of 16KB blocks.