Insert Delayed with MySQL

databases

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.

Delayed Inserts

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).

Status Variables

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: SHOW STATUS

You can find more information about INSERT DELAYED in the MySQL Documentation.



Related Entries

8 people found this page useful, what do you think?

Trackbacks

Trackback Address: 430/73E605AAA84E2C03AE26A5B119B115F0

Comments

On 08/03/2005 at 3:25:37 AM EDT Roger Benningfield wrote:
1
Pete: I'm gonna bear this one in mind for the future. Right now, JournURL's referrer logging probably doesn't need it (particularly after reading the docs), but it could become a big deal down the road.

Thanks for the pointer!

On 08/03/2005 at 12:15:37 PM EDT Julian Halliwell wrote:
2
Good tip, but note that it doesn't work with InnoDB tables.

On 05/15/2006 at 12:21:40 PM EDT Anonymous wrote:
3
loose != lose

On 02/11/2010 at 9:57:52 AM EST adrianTNT wrote:
4
The delayed thing is a great tip. I have a site where I approve some user uploads and I have to insert email notifications, for 100 000 users it takes almost 2 minutes to insert the records. So I can't wait to try this :D It is also not "critical" information so this would be the best example for using delays.

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?