Blog

INSERT DELAYED is gone.

The release notes of MySQL 5.6.6  (http://dev.mysql.com/doc/refman/5.6/en/news-5-6-6.html) – probably to be released very soon – say  “INSERT DELAYED is now deprecated, and will be removed in a future release”.

INSERT DELAYED had several problems (search bugs.mysql.com yourself).  IMO it always was a dirty solution substituting what should have been done 5+ years ago: change the protocol to allow ‘parallelism of multiple queries in a single connection’.  INSERT DELAYED worked around it in some scenarios as the execution of such query was ‘passed on’ to another thread.  But it was no real solution. We are still waiting for the real solution – true parallelism/multitasking in the client.

(MariaDB BTW has moved as far as possible in that direction without changing the protocol: A single client instance can handle parallel queries – but due to restriction with the protocol they will still require each their connection to the server).

 

8 thoughts on “INSERT DELAYED is gone.

  1. I think what you are describing is better described as “asynchronous queries”. It’s helpful for single threaded applications, since you do not get nickeled-and-dimed on round trip latency. This is how MongoDB behaves by default.

    The mysqlnd (php) driver supports this. I’m not sure how many of the other drivers do.

  2. Thanks for the comment. But we still have *one open connection per running query* I think? (anyway PHP is somewhat different, I think, due to non-persistent nature of connections. However I don’t claim to know much about mysqlnd and how it differes from php_mysql() and php_mysqli() distributed with PHP. I mostly have the C-API and applications compiled with it in mind).

    Removing INSERT DELAYED will affect some users having applications using it. People should be aware of this before upgrading to 5.6 (if the syntax is planned to go away in 5.6 GA, what I am not sure about). That is the primarily reason why I write this blog.

    I think one reason for this change is INSERT DELAYED is less important with INNODB (now default) than was the case with MyISAM. With MyISAM a table LOCK could easily prevent an INSERT to execute and the client (or thread) would be in wait-state for as long (or till timeout). INSERT DELAYED was a workaround kicking the query off to another thread on the server so that we can continue!

  3. I’ve not checked on the technical implimentation of asynchronous queries in mysqlnd – but yes, I agree with you it certainly would work best with server support.

    If a driver were to emulate this feature, then it is either going to need to create multiple connections, or queue in a backlog. Both are bad: the backlog queue doesn’t address the problem, since you still hit network latency – also there’s no opportunity to execute in parallel on the server, which is another nice win. The multiple connections sounds a little ugly – although it may be better on a server with the thread-pool feature (MySQL commercial feature, also in MariaDB).

  4. One thought: Why not accept INSERT DELAYED syntax (for a while at least) but let the parser simply ignore DELAYED keyword? Or at least give an option/variable for this behavior (not that I generally are in favor of more options and variables!)?

  5. That may be what they do – but they need to scare people to stop using it for now.

    The change log entry says it is deprecated, so it’s “going” not “gone”. IIRC, the official deprecation process inside MySQL: it needs to be deprecated in one major release (5.6) before it can be removed (5.7?).

  6. Deprecation normally means that a warning will be issued when it’s used and the feature will be removed no sooner than the release after the one in which the deprecation notice appears.

    INSERT DELAYED tends not to have good performance compared to alternative approaches. Having clients insert into dedicated tables or a few clients share some tables works better. Periodically merge these tables into a main table as frequently as desired. This will normally beat INSERT DELAYED on performance when there’s significant concurrency.

    The biggest drawback of INSERT DELAYED is really that it’s for MyISAM tables and usually it’s best to use InnoDB unless there’s a proven need to use MyISAM instead.

    Views are my own, for an official Oracle view consult a PR person.

    James Day, MySQL Senior Principal Support Engineer, Oracle

  7. Sometimes deprecated features stay in for multiple versions. An example was the TYPE keyword which was deprecated in favor of the ENGINE keyword.

    ENGINE was added in 4.0.18 which officially deprecated TYPE, yet it remained available as a keyword until 5.5 which removed it.

    As for mysqlnd, you need a new connection for each asynchronous query. It is really meant for sharding, where you get a set of results from multiple servers in parallel, not for executing a series of statements on a single server in parallel.

    Regardless, INSERT DELAYED was really intended to work around MySQL table level locking. Getting rid of things like INSERT .. DELAYED are the first marching steps toward deprecating MyISAM.

  8. Pingback: Webyog » Goodbye MySQL – Welcome MySQL.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>