Ticket #591 (closed maintenance: fixed)

Opened 3 years ago

Last modified 3 years ago

Move MySQL temporary directory to tmpfs

Reported by: jim Owned by: chris
Priority: major Milestone: Maintenance
Component: Live server Keywords:
Cc: ed, jim Estimated Number of Hours: 0.0
Add Hours to Ticket: 0 Billable?: yes
Total Hours: 0.5


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?

Change History

comment:1 Changed 3 years ago by ed

  • Type changed from task to maintenance

comment:2 Changed 3 years ago by chris

  • Add Hours to Ticket changed from 0.0 to 0.4
  • Total Hours changed from 0.0 to 0.4

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

comment:3 Changed 3 years ago by chris

  • Add Hours to Ticket changed from 0.0 to 0.1
  • Status changed from new to closed
  • Resolution set to fixed
  • Total Hours changed from 0.4 to 0.5

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)

comment:4 Changed 3 years ago by chris

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

comment:5 Changed 3 years ago by chris

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

Note: See TracTickets for help on using tickets.