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
Comments are closed.