Skip to content

Instantly share code, notes, and snippets.

@mttjohnson

mttjohnson/mysql-performance-tuning.sql

Last active August 23, 2024 17:54
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mttjohnson/5ba5200582b7b1a19a3dda82b7fc32d5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mttjohnson/5ba5200582b7b1a19a3dda82b7fc32d5 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL Performance Tuning
Raw
mysql-performance-tuning.sql
-- Query the database to calculate a recommended innodb_buffer_pool_size
-- and get the currently configured value
SELECT
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 RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
# 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;
"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment