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.