mod_log_sql 2.0 Documentation

Home » Projects » Apache » Mod_log_sql » Docs-2.0 » mod_log_sql 2.0 Documentation

Prev: Using the same database for production and test --- Up: Advanced logging scenarios

Optimizing for a busy database

A busy MySQL database will have SELECT statements running concurrently with INSERT and UPDATE statements. A long-running SELECT can in certain circumstances block INSERTs and therefore block mod_log_sql. A workaround is to enable mod_log_sql for "delayed inserts," which are described as follows in the MySQL documentation.

The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can't wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete. DELAYED was introduced in MySQL Version 3.22.15. It is a MySQL extension to ANSI SQL92.

INSERT DELAYED only works with ISAM and MyISAM tables. Note that as MyISAM tables supports concurrent SELECT and INSERT, if there is no free blocks in the middle of the data file, you very seldom need to use INSERT DELAYED with MyISAM.

When you use INSERT DELAYED, the client will get an OK at once and the row will be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than doing many separate inserts.

The general disadvantages of delayed inserts are

  1. The queued rows are only stored in memory until they are inserted into the table. If mysqld dies unexpectedly, any queued rows that were not written to disk are lost.

  2. There is additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED.


The MySQL documentation concludes, "This means that you should only use INSERT DELAYED when you are really sure you need it!" Furthermore, the current state of error return from a failed INSERT DELAYED seems to be in flux, and may behave in unpredictable ways between different MySQL versions. See FAQ entry -- you have been warned.

If you are experiencing issues which could be solved by delayed inserts, then set LogSqlDelayedInserts On in the httpd.conf . All regular INSERT statements are now INSERT DELAYED, and you should see no more blocking of the module.