fitting database into buffer pool memory wrt index_length and data_length

  • Filter
  • Time
  • Show
Clear All
new posts

  • fitting database into buffer pool memory wrt index_length and data_length

    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,

  • #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 ?



      • #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.


        • #5
          gmouse is right.

          Another question is whether you need to fit the whole thing into memory, or only the "hot"portion of the data. Normally there is quite a bit of data in a table that isn't really touched -- rarely if at all -- and so the working set of data is much smaller than the full size.