Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Aug 2014

    Is SWAP usage caused by insufficient space in mysql tmpdir?


    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; }
    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

  2. #2

  3. #3
    New Coder
    Join Date
    Aug 2014
    Quote Originally Posted by Vege View Post
    Is mysql slow query log really empty as the tuner output suggests?
    Thank You for a reaction, yes it is:
    $ grep slow /etc/my.cnf

    $ wc -l /var/log/mysql/slow.log
    0 /var/log/mysql/slow.log

  4. #4
    Regular Coder Vege's Avatar
    Join Date
    Jan 2008
    I would look what the mysql is doing while the swapping happens. Just extreme high amount of visitors?
    There are also numerous small problems that could cause these kind of issues. Might try vmstat to check IO for extra clues.
    But in general this might be out of scope of this forum or at least me


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts