MySQL insert delayed, mysqldelayed

MySQL insert delayed, mysqldelayed

Insert delayed syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT },...), (...),... [on duplicate key update col_name = expr,...] or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name = {expr | DEFAULT },... [on duplicate key update col_name = expr,...]

 

The DELAYED option for INSERT statements is an extension of MySQL compared with standard SQL statements. This option is useful if your client cannot wait for the INSERT to complete. This is a very common problem when you use MySQL for log writing. You can also periodically run SELECT and UPDATE statements, which take a long time.

When a client uses insert delayed, a confirmation is immediately obtained from the server. And the row is queued. When the table is not used by other threads, the row is inserted.

Another important benefit of using insert delayed is that inserts from many clients are centralized and written into a block. This is much faster than executing many independent inserts.

There are some restrictions when using DELAYED:

Note: Currently, each row in the queue is only stored in the memory until they are inserted into the table. This means that if you forcibly stopMysqld(For example, use kill-9) orMysqldIf it is accidentally stopped, all rows that are not written to the disk will be lost.

The following describes in detail what happens when you use the DELAYED option for INSERT or REPLACE. In these descriptions, "Thread" refers to the thread that has accepted an insert delayed statement, and "administrative program" refers to the thread that processes all the insert delayed statements for a specific table.

NOTE: If an insert delayed hypervisor is running, this means that the insert delayed statement has a higher priority than the conventional INSERT statement. Other update statements must wait until the insert delayed statement queue is completed, or the management program thread is suspended (using KILL thread_id), or a flush tables statement is executed.

The following status variables provide information about the insert delayed statement:

Status variable	Meaning
Delayed_insert_threads	Number of Management Program threads
Delayed_writes	Number of rows written using INSERT DELAYED
Not_flushed_delayed_rows	Number of rows waiting to be written

You can send a show status statement or executeMysqladmin extended-statusCommand to view these variables.

Note: When a table is not used, insert delayed is slower than the conventional INSERT statement. For the server, manipulating an independent thread for each table containing delayed rows is also an additional system overhead. This means that insert delayed should be used only when you confirm that you need it.

 

Reposted from:Http://dev.mysql.com/doc/refman/5.6/en/insert-delayed.html