Skip to content

Instantly share code, notes, and snippets.

@knutov

knutov/mysql-performance-tuning.sql

Forked from mttjohnson/mysql-performance-tuning.sql
Created August 11, 2020 22:47
Show Gist options
  • Save knutov/9cc2bde268c9e0355a7d7b2b5da2c328 to your computer and use it in GitHub Desktop.
Save knutov/9cc2bde268c9e0355a7d7b2b5da2c328 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