Calculating average memory utilisation of a MySQL DB Instance

MySQL DB Instance Memory Usage Calculations : The following parameters to be considered to calculate average memory utilisation of a MySQL Database instance.

Global Parameters : These are the fixed sized pre allocated buffers created while bringing up the DB instance.

Performance_schema.memory
innodb_buffer_pool_size
innodb_additional_mem_pool_size ( depreciated since MySQL 5.6)
key_buffer_size
query_cache_size

Connection Based Parameters : The following are the connection specific buffers few of them gets created when the new connection to the DB created, and few of them will get created as per the requirement while processing the queries.

binlog_cache_size
binlog_stmt_cache_size
bulk_insert_buffer_size
innodb_log_buffer_size
join_buffer_size
max_allowed_packet
net_buffer_length
preload_buffer_size
query_prealloc_size
range_alloc_block_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
thread_stack
transaction_prealloc_size

A) By Using the following query you can get the average memory usage of a running MySQL DB instance.

Pre requisites :

1) get the value of “performance_schema.memory” from the performance_schema status and replace the value with “performance_schema.memory” in the following query.
2) get the max value of (Threads_cached,Threads_connected) from the status and replace it with max(Threads_cached,Threads_connected).

Query :

select (( performance_schema.memory + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@key_buffer_size + @@query_cache_size ) + max(Threads_cached,Threads_connected) * (@@binlog_cache_size + @@binlog_stmt_cache_size + @@bulk_insert_buffer_size + @@innodb_log_buffer_size + @@join_buffer_size + @@max_allowed_packet + @@net_buffer_length + @@preload_buffer_size + @@query_prealloc_size + @@range_alloc_block_size + @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@thread_stack + @@transaction_prealloc_size)) as Total_Memory;

NOTE : The out put of above command resulting the value between "VSZ" and "RSS" from "ps auuwwx" command. 

 
B) By Using the following query you can get the maximum memory can be used by the MySQL DB instance while it is dealing with the maximum no of connections with most of the memory allocations.

Pre requisites :

1) get the value of “performance_schema.memory” from the performance_schema status and replace the value with “performance_schema.memory” in the following query.

Query :

select (( performance_schema.memory + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@key_buffer_size + @@query_cache_size ) + @@max_connections * (@@binlog_cache_size + @@binlog_stmt_cache_size + @@bulk_insert_buffer_size + @@innodb_log_buffer_size + @@join_buffer_size + @@max_allowed_packet + @@net_buffer_length + @@preload_buffer_size + @@query_prealloc_size + @@range_alloc_block_size + @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@thread_stack + @@transaction_prealloc_size)) as Total_Memory;

NOTE : The out put of above command resulting the value between "VSZ" and "RSS" from "ps auuwwx" command. 

 
NOTE : You might not get the exact memory utilisation of a DB instance, these queries are only to give you the average utilisation. You might need to find out couple of other parameters and ways to find out the exact utilisation of a DB instance.

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers