Hello,
recent days i have issue with high load on the Linux CentOS 6 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
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
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
(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
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