Tibor's Musings

MySQL In-Memory Temporary File System

If a MySQL application uses many tables containing TEXT columns, then for various JOIN and GROUP BY operations on these tables, MySQL cannot use in-memory temporary tables and need to resort to using on-disk ones. The in-memory vs on-disk speed difference is considerable in these cases. Introducing a special in-memory temporary file system can speed performance by a factor of 10x or more.

Here is how I introduced in-memory temporary file system for the INSPIRE application.

(1) Looking at slow query log (/opt/mysql-data/mysqld-slow.log), I could see that there many slow queries like:

SELECT bx.value FROM bib70x AS bx, bibrec_bib70x AS bibx
 WHERE bibx.id_bibrec IN (689533,865819,844129,778444,768538,)
   AND bx.id=bibx.id_bibxxx AND bx.tag LIKE '700__a'
 ORDER BY bibx.field_number, bx.tag ASC;

coming from bibindex processes.

(2) mysqltuner showed that our DB is running with too many temporary tables created on the disk:

[!!] Temporary tables created on disk: 27% (107M on disk / 387M total)

It is highly probable that these two issues are related, because bibxxx tables use TEXT column definitions, and MySQL cannot use in-memory temporary tables for various join and group operations in these cases; it false back to using on-disk temporary tables.

(3) Observing I/O activity on the DB server shows some /tmp activity, albeit not very much:

PCUDSSW1502 ~> sudo iostat -n
Linux 2.6.18-194.17.1.el5 (pcudssw1502.cern.ch)         11/04/12

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           2.47    0.00    1.39    4.53    0.00   91.61

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda              98.64       671.79       531.22 31215469644 24683531996
sda1              0.01         0.04         0.03    1643438    1299532
sda2              0.00         0.00         0.00       7768       4272
sda3              0.49         1.48         7.39   68689098  343189936
sda4              0.00         0.00         0.00         62          0
sda5              0.07         0.01         0.74     454417   34359264
sda6             96.34       669.28       480.12 31098923522 22309295328
sda7              0.33         0.00        24.45     218871 1136130448
sda8              1.39         0.98        18.49   45515876  859253216

PCUDSSW1502 ~> mount | grep sda[678]
/dev/sda8 on /var type ext3 (rw)
/dev/sda7 on /tmp type ext3 (rw)
/dev/sda6 on /opt type ext3 (rw)

Mostly /opt is busy, but /tmp/ may get busy as well at times... especially at indexing times.

(4) The preceding items indicate that using dedicated in-memory temporary file system to host temporary DB tables that MySQL would otherwise have to create on disk (due to TEXT columns) should help considerably in alleviating disk I/O in these indexing-heavy cases.

So I've edited /etc/fstab to introduce a new dedicated tmpfs partition named /mysqltmp having size 2 GB and then mounted it:

PCUDSSW1502 ~> id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql) context=user_u:system_r:unconfined_t
PCUDSSW1502 ~> sudo vim /etc/fstab # edit as follows
PCUDSSW1502 ~> tail -1 /etc/fstab
tmpfs                   /mysqltmp               tmpfs   size=2G,nr_inodes=10k,mode=700,uid=27,gid=27    0   0
PCUDSSW1502 ~> sudo mkdir /mysqltmp
PCUDSSW1502 ~> sudo chown 27.27 /mysqltmp
PCUDSSW1502 ~> sudo ls -ld /mysqltmp
drwxr-xr-x 2 mysql mysql 4096 Apr 11 21:48 /mysqltmp
PCUDSSW1502 ~> sudo mount /mysqltmp
PCUDSSW1502 ~> df -h /mysqltmp
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 2.0G     0  2.0G   0% /mysqltmp

Then configured MySQL to use it:

PCUDSSW1502 ~> sudo vim /etc/my.cnf # edit as follows
PCUDSSW1502 ~> sudo grep tmpdir /etc/my.cnf
tmpdir          = /mysqltmp/

Then restarted MySQL when task queue permitted it:

PCUDSSW1502 ~> sudo /sbin/service mysqld restart

(5) According to iostat, the usage of /tmp went to zero even during indexing, and there are no slow indexing queries in /opt/mysql-data/mysqld-slow.log anymore.

Now MySQL is much faster thanks to using in-memory temporary tables as opposed to using on-disk temporary tables. Especially since doing this will also liberate disk systems to serve other purposes rather than creating many small temporary files.