Koopman.ME

This is incredible, by the way. Any time MySQL needs to use a tmp table on disk, you can make it use RAM disk instead.

WARNING: if the tmpfs partition you make isn’t big enough, MySQL will not be able to complete queries. Make sure you have enough RAM to do this.

mkdir /tmp/mysqltmp
chown mysql:mysql /tmp/mysqltmp

id mysql
# example:   uid=502(mysql) gid=503(mysql) groups=503(mysql)

#to set up on server restart, put in fstab something like (replace gid, uid with number from above)
tmpfs    /tmp/mysqltmp    tmpfs   rw,gid=503,uid=502,size=2G,nr_inodes=10k,mode=0700 0 0

mount /tmp/mysqltmp

# you don't need this:
# mount -o size=2g,gid=520,uid=518,nr_inodes=10k,mode=0700 -t tmpfs tmpfs /tmp/mysqltmp

#edit my.cnf, adding
tmpdir=/tmp/mysqltmp/

restart mysql

Ok, that’s the premise. Now to automate it, check this out:

#!/bin/sh

ROOTPASS=xxxxxxx

IFS='
'
export IFS

uptime
df -h /tmp/mysqltmp

if [[ `egrep '^tmpdir.*mysqltmp' /etc/my.cnf*` ]]; then
  echo "/etc/my.cnf-* says already done, bailing"
  exit
fi

RAMKB=`cat /proc/meminfo  | grep MemTotal | awk '{print $2}'`

if [[ $RAMKB -lt 7864320 ]]; then
  echo "RAMKB at $RAMKB, too low, bailing"
  exit
fi

if [[ $RAMKB -lt 13631488 ]]; then
  R=1
else
  R=2
fi

echo "R=$R"

echo `date`" - dkoopman - enabling /tmp/mysqltmp" >> /etc/motd

rm -f /etc/my.cnf*.bak
sed -i.bak -e "s@^\#*tmpdir.*=.*@tmpdir         = /tmp/mysqltmp/@" /etc/my.cnf*

mkdir -p /tmp/mysqltmp
chown mysql:mysql /tmp/mysqltmp

G=`id -g mysql`
U=`id -u mysql`

#to set up on server restart, put in fstab something like (replace gid, uid with number from above)
if [[ ! `grep mysqltmp /etc/fstab` ]]; then
  echo "tmpfs    /tmp/mysqltmp    tmpfs   rw,gid=$G,uid=$U,size=$R"G",nr_inodes=10k,mode=0700 0 0" >> /etc/fstab
fi

if [[ ! `df | grep mysqltmp` ]]; then
  mount /tmp/mysqltmp
fi

# THIS ONLY WORKS IF YOU HAVE MYSQL STARTING UP OUT OF RC.LOCAL
# AND THIS IS A BIT UNUSUAL, BUT WORKS IN THE PARTICULAR ENVIRONMENT
# I'M DOING THIS IN.  ALSO NEEDED IS MULTIPLE INSTANCES OF MYSQL
# RUNNING, WHERE THE SOCKET FILE IN IN /tmp/ WITH THE FORMAT
# /tmp/mysql-NUMBER.sock and /etc/my.cnf-NUMBER
for i in `cat /etc/rc.local  | egrep '^[^#].*defaults-file'`; do
  echo $i > /tmp/dkstartup.tmp
  NUM=`echo $i | sed -e 's/.*my.cnf-//' -e 's/ .*//'`
  if [[ ! `echo $NUM | egrep "^[0-9]*$"` ]]; then
    echo "SEVERE ERROR - didn't get a num"
    exit
  fi
  echo "DOING: $NUM"
  /usr/local/mysql/bin/mysqladmin -uroot -p$ROOTPASS -S /tmp/mysql-$NUM.sock shutdown
  /bin/sh /tmp/dkstartup.tmp
  rm /tmp/dkstartup.tmp
done

echo "Restart complete, sleep 5, then show status and things"

sleep 5
# check status:
for x in /tmp/*.sock ; do /usr/local/mysql/bin/mysqladmin -uroot -p$ROOTPASS -S $x status ; done

# check slave status:
for x in /tmp/*.sock ; do echo $x; /usr/local/mysql/bin/mysql -uroot -p$ROOTPASS -S $x -e "show slave status \G" | egrep "Slave.*Running|Seconds" ; done

# check open_files_limit
for x in /tmp/*.sock ; do echo $x; /usr/local/mysql/bin/mysql -uroot -p$ROOTPASS -S $x -e "show variables like 'tmpdir'" ; done

df -h /tmp/mysqltmp
uptime