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