Skip to content

Instantly share code, notes, and snippets.

@mttjohnson
Last active August 23, 2024 17:54
Show Gist options
  • Save mttjohnson/5ba5200582b7b1a19a3dda82b7fc32d5 to your computer and use it in GitHub Desktop.
Save mttjohnson/5ba5200582b7b1a19a3dda82b7fc32d5 to your computer and use it in GitHub Desktop.
MySQL Performance Tuning
-- Query the database to calculate a recommended innodb_buffer_pool_size
-- and get the currently configured value
-- The rollup as the bottom row gives the total for all DBs on the server, where each other row is recommendations per DB.
SELECT
TABLE_SCHEMA,
CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size,
(
SELECT CONCAT(CEILING(variable_value/POWER(1024,FLOOR(LOG(variable_value)/LOG(1024)))),SUBSTR(' KMGT',FLOOR(LOG(variable_value)/LOG(1024))+1,1))
FROM information_schema.global_variables
WHERE variable_name = 'innodb_buffer_pool_size'
) AS 'innodb_buffer_pool_size'
FROM
(
SELECT TABLE_SCHEMA, RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT TABLE_SCHEMA, SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1 growth) BBB
WHERE ENGINE='InnoDB' GROUP BY TABLE_SCHEMA WITH ROLLUP
) AA
) A;
-- Analysis by table
SELECT
TABLE_SCHEMA,
TABLE_NAME,
CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size,
(
SELECT CONCAT(CEILING(variable_value/POWER(1024,FLOOR(LOG(variable_value)/LOG(1024)))),SUBSTR(' KMGT',FLOOR(LOG(variable_value)/LOG(1024))+1,1))
FROM information_schema.global_variables
WHERE variable_name = 'innodb_buffer_pool_size'
) AS 'innodb_buffer_pool_size'
FROM
(
SELECT TABLE_SCHEMA, TABLE_NAME, RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT TABLE_SCHEMA, TABLE_NAME, SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1 growth) BBB
WHERE ENGINE='InnoDB' GROUP BY TABLE_SCHEMA, TABLE_NAME WITH ROLLUP
) AA
) A ORDER BY RIBPS DESC
# System memory usage + connection memory usage
# http://www.mysqlcalculator.com/
# https://dba.stackexchange.com/questions/1229/how-do-you-calculate-mysql-max-connections-variable
# System Memory Limits
mysql -e "
select
(@@key_buffer_size/1024/1024) key_buffer_size,
(@@query_cache_size/1024/1024) query_cache_size,
(@@tmp_table_size/1024/1024) tmp_table_size,
(@@innodb_buffer_pool_size/1024/1024) innodb_buffer_pool_size,
(@@innodb_additional_mem_pool_size/1024/1024) innodb_additional_mem_pool_size,
(@@innodb_log_buffer_size/1024/1024) innodb_log_buffer_size;
"
# Connection Memory usage
# max_connections * (per connection memory limits)
mysql -e "
select
(@@max_connections) max_connections,
(@@sort_buffer_size/1024/1024) sort_buffer_size,
(@@read_buffer_size/1024/1024) read_buffer_size,
(@@read_rnd_buffer_size/1024/1024) read_rnd_buffer_size,
(@@join_buffer_size/1024/1024) join_buffer_size,
(@@thread_stack/1024/1024) thread_stack,
(@@binlog_cache_size/1024/1024) binlog_cache_size;
"
# Estimated Max Memory Usage
mysql -e "
select
(
(@@key_buffer_size/1024/1024) + -- key_buffer_size,
(@@query_cache_size/1024/1024) + -- query_cache_size,
(@@tmp_table_size/1024/1024) + -- tmp_table_size,
(@@innodb_buffer_pool_size/1024/1024) + -- innodb_buffer_pool_size,
(@@innodb_additional_mem_pool_size/1024/1024) + -- innodb_additional_mem_pool_size,
(@@innodb_log_buffer_size/1024/1024) -- innodb_log_buffer_size
) +
(
@@max_connections * -- max_connections,
(
(@@sort_buffer_size/1024/1024) + -- sort_buffer_size,
(@@read_buffer_size/1024/1024) + -- read_buffer_size,
(@@read_rnd_buffer_size/1024/1024) + -- read_rnd_buffer_size,
(@@join_buffer_size/1024/1024) + -- join_buffer_size,
(@@thread_stack/1024/1024) + -- thread_stack,
(@@binlog_cache_size/1024/1024) -- binlog_cache_size
)
) MAX_MEMORY_USAGE_MB
;
"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment