mysql innodb tunning

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.

multiplier of 1.6 is with the average growth of data I've seen in many projects over ~1 year. You can make it lower if your data is predicted to grow slower. If you have for example a blog/news site with 5-10 articles per day and 20-50 comments per article added in time, I'd actually do Discover step each month with a multiplier of 1.5.

On an e-commerce shop, I usually do it every ~3 months with 1 year prediction, and works fine for me.

PS: the above makes WordPress with INNODB actually run like fed with steroids.