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.