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
There is no error in logs, and your Drupal 8 website is writing a message: The website encountered an unexpected error. Please try again…
Tutorial
This one is quick and works the same with any other code provided by third party.

Recommended

Tutorial
3 views
This sketch is quite easy, I used Arduino Nano with OLED 0.96″ display 128×64 resolution…
Tutorial
8 views
While working on a fairly complex website with very complex views setup, including tens…
Tutorial
6 views
In this case we have two options, either we use hook_user_presave() or we can create new…
Tutorial
6 views
When using Swiftmailer under Drupal 8 / 9 it automatically sets the headers for sender to…
Tutorial
3 views
Yes, IOS / Safari is the new internet explorer. Amount of time I spend on debugging…
Tutorial
10 views
There is a very handy function in Drupal 8 / 9, allowing developers refresh view when…
Tutorial
4 views
Often, when doing SEO checkups, SEO specialist come up with adding Schema.org…
Tutorial
79 views
I needed to test my contracts against USDC contract, specifically I needed ERC-721 mint…
Tutorial
4 views
If you are a newbie like I am and struggling with setting the proper MYSQL my.cnf config…
Tutorial
10 views
I had trouble to set this up properly, because documentation is quite misleading or often…
Article
56 views
As the title says, DO NOT in any circumstances install ANY bitcoin price extension to ANY…
Tutorial
173 views
This is (or should be) a working example of sending some Ether between two addresses.…
Module
43 views
This list was fetched from Zapper, with their /v1/token-list endpoint. Which you can…
Tutorial
98 views
In the last months I am being pretty much bombarded by my clients with asking what…
Tutorial
25 views
So sometimes you just need to transliterate some kind of foreign (or local) language, and…