madalinignisca/mysql-innodb-tunning.md

## mysql-innodb-tunning.md

      
    Raw
  

              mysql-innodb-tunning.md
            
          
    Discover

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;
Take the value and set it as innodb_buffer_pool_size sufixed with G, example 20G
Don't mess with it, this is what you need so your queries will work practically from RAM and get max performance.
Check real usage periodic

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;
You will see how many gigabytes of ram the buffer really uses.
Do not get with the impression that if you'll rescale it to a lower value to save ram you will be fine.
Current value is actually on all data that it is used, meaning the rest simply was never queried.
Why checking it periodic?
Simply because you will later be able to understand the value to decrease, as probably X% of data is likely never accessed, situation on huge databases with old data, like a store with Orders from 10 years ago.