Tutorial

MYSQL swapping? Use this handy script to see why!

If you are a newbie like I am and struggling with setting the proper MYSQL my.cnf config, use this simple program to prevent swapping.

Your server will swap to swap space once it runs out of RAM, which is slow and will hurt performance of your VPS / Dedicated server.

Let's do it!

Pridal/a lubo dňa St, 08/24/2022 - 01:18
Create file adjust.pl

And place the following code inside:

#!/bin/sh
# you might want to add some user authentication here
mysql -u root -p  -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"
}'
Run the file from Terminal

Run the file by the command:

perl adjust.pl

It will print-out the following output:

Adjust the variables in my.cnf

From the code itself you can see how it calculates the MIN and MAX usage of RAM for your setup.

The variables to adjust, that matters the most are:

max_connections
key_buffer_size
query_cache_size
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size

 

Do not stop there!

Of course this is only a small handy script / tool to check the potential RAM usage. To be noted, your MYSQL server might never hit such values, those are only theoretical values.

Anyway, I highly reccomend pairing this script with mysqltuner.pl

Might interest you

Tutorial
I stumbled across the problem with Contact Form 7 module, when AJAX was not working at all. Official documentation was of no help so I…
Tutorial
This is (or should be) a working example of sending some Ether between two addresses. Requirements are:

Recommended

Tutorial
2 views
When using Swiftmailer under Drupal 8 / 9 it automatically sets the headers for sender to…
Tutorial
2 views
Yes, IOS / Safari is the new internet explorer. Amount of time I spend on debugging…
Tutorial
8 views
There is a very handy function in Drupal 8 / 9, allowing developers refresh view when…
Tutorial
2 views
Often, when doing SEO checkups, SEO specialist come up with adding Schema.org…
Tutorial
19 views
I needed to test my contracts against USDC contract, specifically I needed ERC-721 mint…