Fli
09-20-2015, 10:22 PM
Is it good idea to store mysql temporary tables in RAM instead of classic HDD?
there are two interesting tutorials on how to make mysql prefer RAM (tmpfs) instead of HDD:
http://2bits.com/articles/reduce-your-servers-resource-usage-moving-mysql-temporary-directory-ram-disk.html
http://everythingmysql.ning.com/profiles/blogs/using-tmpfs-for-mysqls-tmpdir
(backup:
shell> mkdir /tmp/mysqltmp
shell> chown mysql:mysql /tmp/mysqltmp
shell> id mysql
##NOTE: make sure you get the uid and gid for mysql
shell> vi /etc/fstab
## make sure this in in your fstab
tmpfs /tmp/mysqltmp tmpfs rw,uid=25,gid=26,size=2G,nr_inodes=10k,mode=0700 0 0
shell> mount /tmp/mysqltmp
shell> vi /etc/my.cnf #or the mysql config file for your server
## NOTE: inside the file add the following under [mysqld]
tmpdir=/tmp/mysqltmp/
shell> service mysql restart
)
If MySQL is causing high I/O on HDD, %wait value is high and iotop -ao shows mysql processes as source of high disk usage, then it can be good idea to switch MySQL temporary tables storage from HDD to RAM..
there are two interesting tutorials on how to make mysql prefer RAM (tmpfs) instead of HDD:
http://2bits.com/articles/reduce-your-servers-resource-usage-moving-mysql-temporary-directory-ram-disk.html
http://everythingmysql.ning.com/profiles/blogs/using-tmpfs-for-mysqls-tmpdir
(backup:
shell> mkdir /tmp/mysqltmp
shell> chown mysql:mysql /tmp/mysqltmp
shell> id mysql
##NOTE: make sure you get the uid and gid for mysql
shell> vi /etc/fstab
## make sure this in in your fstab
tmpfs /tmp/mysqltmp tmpfs rw,uid=25,gid=26,size=2G,nr_inodes=10k,mode=0700 0 0
shell> mount /tmp/mysqltmp
shell> vi /etc/my.cnf #or the mysql config file for your server
## NOTE: inside the file add the following under [mysqld]
tmpdir=/tmp/mysqltmp/
shell> service mysql restart
)
If MySQL is causing high I/O on HDD, %wait value is high and iotop -ao shows mysql processes as source of high disk usage, then it can be good idea to switch MySQL temporary tables storage from HDD to RAM..