Skip to content

Instantly share code, notes, and snippets.

@madalinignisca
Created May 12, 2023 05:57
Show Gist options
  • Save madalinignisca/630ef3bdfff422baa738c1e5202b890e to your computer and use it in GitHub Desktop.
Save madalinignisca/630ef3bdfff422baa738c1e5202b890e to your computer and use it in GitHub Desktop.
mysql innodb tunning

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.

@madalinignisca
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment