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.inde x_length)) /1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.ind ex_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_len gth))-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.
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.inde x_length)) /1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.ind ex_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_len gth))-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.
Comment