1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

Discussion in 'Site & Server Administration' started by postcd, Dec 2, 2017.

  1. #1
    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.
    SEMrush
    $ 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
     
    postcd, Dec 2, 2017 IP
    SEMrush
  2. zacharooni

    zacharooni Well-Known Member

    Messages:
    345
    Likes Received:
    20
    Best Answers:
    4
    Trophy Points:
    120
    #2
    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, Dec 10, 2017 IP
  3. postcd

    postcd Well-Known Member

    Messages:
    991
    Likes Received:
    8
    Best Answers:
    1
    Trophy Points:
    170
    #3
    @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
     
    postcd, Dec 11, 2017 IP
  4. RomanEpo

    RomanEpo Member

    Messages:
    94
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    43
    #4
    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.
     
    RomanEpo, Feb 20, 2018 IP
  5. Cameron Fillers

    Cameron Fillers Member

    Messages:
    33
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    33
    #5
    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.
     
    Cameron Fillers, Jun 18, 2018 IP
    postcd likes this.