1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Understanding innodb_buffer_pool_size

Discussion in 'Server Configuration and Hosting' started by Marcus, Aug 14, 2014.

  1. Marcus

    Marcus Well-Known Member

    Which data is stored within the pool size, only the data once fetched from mysql (as "xf_post: rows 2,5,233,2353"), or does mysql store the whole "xf_post" table within database? The following statements are from http://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

    Code:
    SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    FROM information_schema.tables WHERE engine='InnoDB') A;
    However this shows me that less than 1 GB are used from the cache
    Code:
    SELECT(PagesData*PageSize)/POWER(1024,3) DataGB FROM(SELECT variable_value PagesData
    FROM information_schema.global_statusWHERE variable_name='Innodb_buffer_pool_pages_data') A,(SELECT variable_value PageSizeFROM information_schema.global_statusWHERE variable_name='Innodb_page_size') B;
     
  2. Xon

    Xon Well-Known Member

    InnoDB stores data in blocks in the InnoDB buffer pool, this data can be indexes or actual table data. This is evicted on a Last Recently Used basis (ref)

    Indexes actually contain column data + the primary key (ref), this means a query which only hit the indexed columns can get the primary key 'for free' without needing to take a trip to the row record.
     

Share This Page