Calculate Maximum MySQL Memory Usage

Published on Author gryzliLeave a comment

Recently I found the following script pretty handy for calculating the maximum amount of memory mysql could use, based on the current buffers configuration.

 

This is the source:

#!/bin/sh# you might want to add some user authentication here
mysql -e "show variables; show status" | awk '  
{
VAR[$1]=$2  
}
END {  
MAX_CONN = VAR["max_connections"]  
MAX_USED_CONN = VAR["Max_used_connections"]  
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]  
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]  
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN  
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576  
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN  
printf "| %40s | %18d |\n", "max_connections", MAX_CONN  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576  
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576  
printf "+------------------------------------------+--------------------+\n"  
}'

 

And here is an example output of the script:

+------------------------------------------+--------------------+
|                          key_buffer_size |          12.000 MB |
|                         query_cache_size |          12.000 MB |
|                  innodb_buffer_pool_size |        2048.000 MB |
|          innodb_additional_mem_pool_size |           0.000 MB |
|                   innodb_log_buffer_size |          16.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |        2088.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           1.000 MB |
|                         read_buffer_size |           1.000 MB |
|                     read_rnd_buffer_size |           1.000 MB |
|                         join_buffer_size |           1.000 MB |
|                             thread_stack |           0.285 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |          24.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          28.316 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                 62 |
|                          max_connections |                 80 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |        3843.617 MB |
|                              TOTAL (MAX) |        4353.312 MB |
+------------------------------------------+--------------------+

 

Total(MAX) is calculated based on the maximum user connections you might have.

Total(MIN) is calculated based on the maximum user connections you had (Max_used_connections).

Leave a Reply

Your email address will not be published. Required fields are marked *