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.