Optimizing iowait with tmpdir and tmpfs or ramfs for Drupal 5 site
A note from the developer who oversees one of my Drupal sites. We could use your help.
Hello,
I have a rather burly Drupal based site that seems to be causing some
problems, today we had a major outage. There are many slow queries
and also mysql related iowait that causes server processes to hang, at
least that is the theory.
Here you can examine some of the stats on the server:
http://andric.us/load/localdomain/localhost.localdomain.html
You can see my iowait stats here, which are pretty high:
http://andric.us/load/localdomain/localhost.localdomain-cpu.html
And I have written to quite some lengh about the symptoms here, if you
are curious.
http://forum.slicehost.com/comments.php?DiscussionID=3373&page=1
http://news.ycombinator.com/item?id=543614
But the final conclusion is to use memory rather than disk for mysql's
tmpdir setting. So my question is, how does one do this in
debian/unix? Are there any recipes you can share? Should I use tmpfs
vs ramfs? Thanks for your help.
--
Milan
ps. I hope it's not too much information but I also thought I would
post my conf file here. I tweaked these according to the tuning script
available here: https://launchpad.net/mysql-tuning-primer
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
max_allowed_packet = 16M
max_connections = 500
table_cache = 500
key_buffer = 256M
thread_stack = 64K
thread_cache_size = 4
sort_buffer=64K
net_buffer_length=2K
query_cache_limit = 1M
query_cache_size = 16M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
skip-bdb
max_heap_table_size = 128M
tmp_table_size = 256M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
tmpfs vs ramfs
I know this topic is a little old but I felt it needed a response.
There are many ways to speed up mysql and running it's temp directory in ram is one. In linux there are two ways to mount folders in RAM. Tmpfs and Ramfs
The difference in both is that with tmpfs you need to specify a ram size and with ramfs the size will grow as more data gets written to it, the advantage of tmpfs is that you can set a limit on how much of your ram to use and once if gets to that limit it will use your swap, with ramfs if for example you have 2096k of ram and your ramfs mounted folder uses it all up, you're system might crash and you'll loose data, in either case both methods will loose all data on a reboot or system crash.. one example of using tmpfs is
mount -t tmpfs -o size=400M tmpfs /tmpwith ramfs is the same but you just don't set a sizemount -t ramfs ramfs /tmpHope this helps you understand what tmpfs and ramfs is and what benefits are between the two.