MySQL Performance Tuning – reducing I/O wait

If the performance of an existing MySQL based system is critical and someone aims to avoid the I/O bottleneck: the MySQL-Engine uses on disk temporary tables for some operations. Use the explain-syntax to detect the details of your queries which might be generating temporary tables and look for “Using temporary”.

Once you found them, check if there are BLOB or TEXT fields inside. If this is the case MySQL will create these tables on your hard disk instead of your RAM, thus generating lots of additional IO. So you know what to do…

… either create a virtual disk inside your RAM and have MySQL using this as temporary file system or move your BLOB and TEXT cols to extension tables (tables which extend other tables).

[update] as of MySQL 5.03 you can use “varchar (55635)” for your session data inside memory tables.

This entry was posted on Monday, September 24th, 2007 at 12:04 am and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.