Insert Delayed with MySQL
I just discovered that MySQL has some handy optimization hints, and commands that you can pass in your SQL statements. Since there is a lot to cover with each tip, I will be posting them in separate blog entries. I have already written about the MySQL Query Cache optimization hints.
You can delay INSERT's from happening until the table is free by using the
DELAYED hint in your SQL statement. For example:
INSERT DELAYED INTO table (col) VALUES ('val');
The above SQL statement will return quickly, and it stores the insert statement in a memory queue until the table your inserting into is free from reads. This means that if there are multiple inserts in the queue they can be written in one block, which is a more optimal use of IO.
The downside to this is that it is not transactionally safe at all. You don't really know how long its going to take for your INSERT to happen. If the server crashes, or is forcefully shutdown you will loose your INSERTs. So don't use this on any critical information that would suck to loose.
One great use for the
DELAYED keyword would be for storing web stats in a database. You don't want the client waiting for the stats to insert, and its not that big of a deal if you loose a few stats (for most people).
The following status variables can be used to monitor your delayed inserts:
Delayed_errors Delayed_insert_threads Delayed_writes Not_flushed_delayed_rows
You can get the values by running the SQL statement:
You can find more information about
INSERT DELAYED in the MySQL Documentation.
You might also like:
- Multiple Inserts with MySQL - June 10, 2005
- MySQL Optimization Hints - January 2, 2007
- Sphinx - Open Source SQL Full Text Search Engine - November 1, 2006
- Google Video: MySQL Performance Tuning Best Practices - May 11, 2006
- Updated SQL Reserved Words Checker - March 28, 2006
- MySQL FULLTEXT Indexing and Searching - September 29, 2005
- SQL to Select a random row from a database table - September 14, 2005
- Faster Inserts with PostgreSQL - August 31, 2005