fitting database into buffer pool memory wrt index_length and data_length

MySQL & MariaDB Other MySQL® Questions
1

Hi Guys,

quick question for you.
If i want to fit a particular database into memory, do i need to size the buffer pool to be as large as index + data, or just data ?

For example, if i run this query :

SELECT s.schema_name, CONCAT(IFNULL(ROUND(SUM(t.data_length) /1024/1024,2),0.00),“Mb”) total_data, CONCAT(IFNULL(ROUND(SUM(t.index_length) /1024/1024,2),0.00),“Mb”) total_index, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00),“Mb”) total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),“Mb”) data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),“Mb”) data_free, IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name = “dbname” GROUP BY s.schema_name ORDER BY pct_used DESC;

on a sample 5.1.52 database i have (with file per table), i get :

±----------------±-----------±------------±-----------±----------±----------±---------±-------------+| schema_name | total_data | total_index | total_size | data_used | data_free | pct_used | total_tables |±----------------±-----------±------------±-----------±----------±----------±---------±-------------+| foo | 1596.97Mb | 2139.06Mb | 3736.03Mb | 3696.03Mb | 40.00Mb | 98.93 | 84 |±----------------±-----------±------------±-----------±----------±----------±---------±-------------+

I know that there is probably in this instance redundant indexes, but the question is the same. Should the buffer pool be roughly 4G or will 1.5G be sufficient ?

In this example, i am not taking into account potential poor fill factor for secondary indexes. I also know that percona server adds information about how well tables fit into buffer pool memory, but haven’t tested with that yet.

Thanks for the help,
Cheers,
Romain.

2

If you want to guarantee that the database fits in memory, then you need to make sure the buffer pool is large enough to fit the data, the indexes and dirty pages, as well as UNDO data and the insert buffer. There is also overhead for the data structures such as the data dictionary.

You can adjust the maximum size of the insert buffer and data dictionary in Percona Server, but you still need more memory than the size of index + data.

3

Thanks a lot.

I’ll look into that.
Do you have a pointer on the best way to monitor at runtime how much memory is needed for dirty pages, undo data, and the insert buffer ? show innodb status ? mysqladmin variables ?

Cheers,
Romain.

4

“the data, the indexes and dirty pages,”

Data is in dirty pages. It does not hold data in the buffer pool twice, right? So I would omit ‘dirty pages’ from your enumeration.