MySQL Performance Tuning

mysql-performance-tuning.sql

-- 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
  ;
  "