TECHIES WORLD

For Techs.... Techniques.... Technologies....

CpanelMysql

How to reduce the memory usage of MySQL

admin

Sometimes it appears that the server’s free memory runs very low because MySQL is using more memory than expected.

The maximum memory usage comes from a lot of different settings, mostly buffer sizes but it appears that documentations don’t clearly state how to find or set this value.

So here we are going to calculate the memory that MySQL consumes with the current settings.

The below script can be useful for finding out MySQL’s potential memory usage based on the buffers and caches memory allocation.

Thanks "Eduardo Franceschi" for creating and sharing such a script.

Please note that this script will not make any changes to the MySQL conf and it will print the report only. Currently its not using any authentication inside the script and we can include it if needed.

#!/bin/sh
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_CONNprintf "+------------------------------------------+--------------------+\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"  
}'

Let us check one sample output of script.

+------------------------------------------+--------------------+
|                          key_buffer_size |          16.000 MB |
|                         query_cache_size |          16.000 MB |
|                  innodb_buffer_pool_size |         128.000 MB |
|          innodb_additional_mem_pool_size |           8.000 MB |
|                   innodb_log_buffer_size |           8.000 MB |   
+------------------------------------------+--------------------+
|                              BASE MEMORY |         176.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           2.000 MB |
|                         read_buffer_size |           0.125 MB |
|                     read_rnd_buffer_size |           0.250 MB |
|                         join_buffer_size |           0.125 MB |
|                             thread_stack |           0.188 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |          16.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          18.719 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                  1 |
|                          max_connections |                151 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |         194.719 MB |
|                              TOTAL (MAX) |        3002.531 MB |
+------------------------------------------+--------------------+

The TOTAL (MAX) is the maximum memory MySQL can use with the current configuration is3GB. This is too much if the server is a low configuration one.

The current configuration of MySQL is as follows.

key_buffer              = 16M  
max_allowed_packet      = 16M  
thread_stack            = 192K  
thread_cache_size       = 8  
max_connections        = 100

Here the two things that directly or indirectly influence are the "memory per connection" and the "maximum number of concurrent connection". So we need to tweak the values of innodbbufferpoolsize, sortbuffersize, readbuffersize, tmptablesize and maxconnections.

key_buffer              = 16M  
read_buffer             = 60K  
sort_buffer             = 1M  
innodb_buffer_pool_size = 64M  
tmp_table               = 8M  
max_allowed_packet      = 16M  
thread_stack            = 192K  
thread_cache_size       = 8  
max_connections        = 25

Now we need to execute the script again.

+------------------------------------------+--------------------+
|                          key_buffer_size |          16.000 MB |
|                         query_cache_size |          16.000 MB |
|                  innodb_buffer_pool_size |          64.000 MB |
|          innodb_additional_mem_pool_size |           8.000 MB |
|                   innodb_log_buffer_size |           8.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |         112.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           1.000 MB |
|                         read_buffer_size |           0.059 MB |
|                     read_rnd_buffer_size |           0.250 MB |
|                         join_buffer_size |           0.125 MB |
|                             thread_stack |           0.188 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |           8.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |           9.652 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                  1 |
|                          max_connections |                 25 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |         121.652 MB |
|                              TOTAL (MAX) |         353.309 MB |
+------------------------------------------+--------------------+

Here we can see that the the potential memory usage has been reduced from 3GB to 350MB.

Please note that the tweaks that need to be applied will be different for different servers depending on the resources and usage. But we can fine tune the MySQL servr by following this procedure.

Leave a Reply