Welcome to Centmin Mod Community
Register Now

Sql tmp on ram

Discussion in 'System Administration' started by pamamolf, Feb 3, 2016.

  1. pamamolf

    pamamolf Well-Known Member

    2,818
    251
    83
    May 31, 2014
    Ratings:
    +445
    Local Time:
    2:00 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Hi

    I want to use a ram partition for tmp for my sql:

    Code:
    Filesystem      Size  Used Avail Use% Mounted on
    /dev/sda2       922G  3.4G  872G   1% /
    devtmpfs         95G     0   95G   0% /dev
    tmpfs            95G     0   95G   0% /dev/shm
    tmpfs            95G   66M   95G   1% /run
    tmpfs            95G     0   95G   0% /sys/fs/cgroup
    /dev/sda1       477M  115M  333M  26% /boot
    tmpfs            19G     0   19G   0% /run/user/0
    tmpfs           2.2G  589M  1.6G  27% /svr-setup
    What path do you advice me to use and replace the:
    Code:
    tmpdir=/home/mysqltmp
    Thank you
     
  2. eva2000

    eva2000 Administrator Staff Member

    30,897
    6,908
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,403
    Local Time:
    10:00 PM
    Nginx 1.13.x
    MariaDB 5.5
    You'd want to create your own dedicated tmpfs ramdisk for mysql to use solely for mysql and change tmpdir= to that new dedicated tmpfs. Only do this if you fully understand how mysql uses the tmpdir and what it's used for. Not properly setting this up can end up with corrupt data and/or inability to repair or restore mysql backups and much more. For that read official mysql documentation ;)
     
  3. Matt

    Matt Moderator Staff Member

    697
    322
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +449
    Local Time:
    12:00 PM
    1.7.1
    MariaDB 10
    Or it can lock up MySQL if the partition size isn't large enough to accommodate the tmp tables created there ;-)
     
    • Agree Agree x 1
  4. eva2000

    eva2000 Administrator Staff Member

    30,897
    6,908
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,403
    Local Time:
    10:00 PM
    Nginx 1.13.x
    MariaDB 5.5
    yeah the = much more part :)

    it's reason why Centmin Mod by default uses tmpdir=/home/mysqltmp for mysql as most cases /home partition has the largest amount of free disk space compared to default /tmp which in some cases are a separate and much smaller partition :)
     
    • Agree Agree x 1