Move MySQL temporary directory to tmpfs

[jim]

Chris, please read: ​http://2bits.com/articles/reduce-your-servers-resource-usage-moving-mysql-temporary-directory-ram-disk.html

I think we could easily drop a little MySQL memory to give it some in-memory disk space to do the temporary table munching Drupal is causing it. I see there are already some mounted tmpfs partitions.

Related to #590 (proposal L: Review slow query log, explain queries, tweak as necessary/flag poorly behaving modules)

What do you think? Worth doing?

[chris]

Debian Squeeze has a RAM disk already:

df -h
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 4.0G   12K  4.0G   1% /dev/shm

So I have added this to /etc/rc.local to create a directory for !MySQL on boot:

mkdir /dev/shm/mysql ; chown mysql:mysql /dev/shm/mysql ; chmod 700 /dev/shm/mysql

And I have edited /etc/mysql/my.cnf:

tmpdir                  = /dev/shm/mysql

One thing we will have to remember is that /dev/shm will become /run/shm when we upgrade to Wheezy on ticket:535

[chris]

And check it's set:

MariaDB [(none)]> SHOW VARIABLES LIKE 'tmpdir';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tmpdir        | /dev/shm/mysql |
+---------------+----------------+
1 row in set (0.01 sec)

[chris]

I have added a link to this ticket from wiki:PuffinServer#MariaDB.

[chris]

There is a crontab entry to create and chown the directory on the ram disk, see wiki:PuffinServer#Cron