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
This is (or should be) a working example of sending some Ether between two addresses. Requirements are:
Tutorial
Ever wondered how to have a stable installation of Drupal 8 with a lot of modules and in need of applying a patch? If you apply it manually…

Recommended

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