MySQL: is SWAP usage caused by insufficient space in mysql tmpdir?

Hello,

recent days i have issue with high load on the server, as a result of overload spikes is a high SWAP usage and the MySQL (5.5.58-cll) is the process that takes most of the SWAP:

$ ps -e -o vsz,rss,comm | { read x; echo "$x"; sort -k2n | tail; }
VSZ  RSS COMMAND
348704 265096 httpd
348828 265120 httpd
349132 265140 httpd
349088 265288 httpd
349280 265468 httpd
349156 265520 httpd
349208 265548 httpd
917556 534052 clamd
3485196 2773796 grep
13168468 2827400 mysqld

Code (markup):

Issue is that once SWAP gets filled, it do not empty until i restart MySQL.

$ free -mht
             total       used       free     shared    buffers     cached
Mem:           13G        12G       504M       592K         0B       8.5G
-/+ buffers/cache:       4.0G       9.0G
Swap:         2.0G       2.0G        17M
Total:         15G        14G       521M

Code (markup):

My MySQL tmpdir is /dev/shm (it has 9GB size), but i recently decreased RAM on my VPS to around 13GB as i needed more RAM for another VPS. Server has slow HDD which usually becomes a bottleneck.

# df -h|grep -v virtfs
Filesystem      Size  Used Avail Use% Mounted on
/dev/simfs      342G   92G  251G  27% /
none            9.0G  4.0K  9.0G   1% /dev
none            9.0G  8.0K  9.0G   1% /dev/shm
tmpfs           9.0G  376K  9.0G   1% /tmp
tmpfs           9.0G     0  9.0G   0% /var/tmp

Code (markup):

(i always though /dev/shm was in memory (tmpfs), now i see "none" filesystem above, should i move mysql tmpdir to tmpfs like /tmp or to other non memory location with more disk quota?)

By the way regarding MySQL settings, mysqltuner.pl script says:

[--] Physical Memory     : 13.0G
[--] Max MySQL memory    : 2.6G
[--] Other process memory: 4.3G
[--] Total buffers: 2.3G global + 1.1M per thread (230 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.5G (19.07% of installed RAM)
[OK] Maximum possible memory usage: 2.6G (19.67% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

Code (markup):

I do not want to waste your time, but if anyone is interested, full script output: https://pastebin.com/60PBLfzs
I have 1-2 low visit sites which mysql table is near 1GB, i am not a programmer to change its structure to use indexes, it is known CMS (PHPBB, vBulletin).

My mysql settings: /etc/my.cnf (https://pastebin.com/V2t2u48L)
(i do not believe in increasing buffers as per the tuner advices, but if you see one or two values too low or high, please let me know)

My question is, as mentioned above what to do with mysql tmpdir and second, if You see anything wrong in my configuration and third, what are possible causes of SWAP being full of MySQL data and that these data are not removed and SWAP stays full and fourth, how to prevent that (except increasing RAM significantly)? Thank You

 

BACKUP YOUR MY.CNF SOMEWHERE

Add skip-name-resolve to my.cnf
Lower your InnoDB buffer pool size to 1G (from 2G)
Install the sys schema, and re-run mysqltuner after 24 hours
Drop max_connections to 150, max_user_connections to 30
Increase join_buffer_size to 1M
Set query_cache_size=32M

Restart MySQL, also consider using MariaDB 10.1

 

@zacharooni I think that the MySQL SWAPped badly because some other process exhausted all available memory, so mysql ended using SWAP (OpenVZ vSWAP - "The difference between normal swap and vswap is that with vswap no actual disk I/O usually occurs. Instead, a container is artificially slowed down, to emulate the effect of the real swapping. Actual swap out occurs only if there is a global memory shortage on the system.") i killed one process "nixstatsagent" that was eating excessive RAM and HDD I/O and from that time, i no longer see issue with overloaded server, and SWAP seems no to be full..

1) I tried to use skip-name-resolve before, but it caused "an issue" which disappeared when i removed it
2) done
3) this system seems too complicated, but thank you for an advice
4) done
max_connections means "The maximum permitted number of simultaneous client connections."
max_user_connections means "The maximum number of simultaneous connections permitted to any given MySQL user account."
5) done
6) done

 

It should be optimized for best performance,just try to configure multi threaded compatibility.

There is enabled using virtual memory for fast performance,allocated swap is too low than needed.

Stop using virtual memory from functionality or systems.

 

Pretty much once you go into swap for your mysql server, it is a lost cause. Swap is much slower than RAM, and it will just cause a viscous cycle that will end with you having to restart mysql. The suggestions provided by
@zacharooni are good ones. I also advise setting the following settings

table_open_cache=10000 (may have to increase this. Most active servers, i have set to 50000)
interactive_timeout=90
wait_timeout=300
connect_timeout=300

If you have 150 mysql connections, and it fills up, then your mysql server is having an issue.

If it fills, then you will want to log into the mysql server and run the following command

"show processlist;"

And this may assist you in figuring out what process is locking up the database.

Also, do you have a dedicated mysql server, or are you using the same server for your website? You may need to break it up based on the load. If you don't have much traffic, then tuning your mysql server should be all your require.