WL#6073: Remove INSERT DELAYED

Status: Complete   —   Priority: Medium

INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables
(not InnoDB, NDB, CSV, MERGE). With InnoDB as default engine it becomes less
relevant. It works only for INSERT VALUES without ON DUPLICATE KEY UPDATE.

It contains a lot of code hacks (*), and "making a new feature work with INSERT
DELAYED" can end up taking a significant portion of the development time of the
new feature (example: CURRENT_TIMESTAMP as DEFAULT for DATETIME columns).

http://bugs.mysql.com/search.php?
search_for=delayed&status=Active&severity=&limit=10&order_by=&cmd=display&direct
ion=ASC&bug_type=&os=0&phpver=&bug_age=0
shows 8 open bugs for INSERT DELAYED.

(*) examples:
- how the TABLE object is constructed in get_local_table() (esp.
def_read_set/def_write_set)
- for statement-based replication to work, some pieces of context (timezone,
sql_mode, ...) need to be saved together with the "delayed row" when this row is
put into the "delayed queue"
- sql_insert.cc is 4300 lines, of which 1400 are exclusively for INSERT DELAYED
=> INSERT DELAYED is cause of a significant share of complexity

Additional notes (Marc Alff)
----------------------------

The implementation of INSERT DELAYED is also causing numerous bugs and dead
locks, because mutexes are used in an order incompatible with the rest of the
server implementation.

+1 from Jonas, doesnt work with Ndb either
+1 from Martin H., this feature is to blame for at least four months of the 
delay to implement Worklog#5874.
+1 from Paul D. Removing this simplifies the manual and gets rid of a bunch of
special case notes.

Additional note from James Day: seems OK. The workaround is to have clients
inserting into different tables to improve parallelism and then merge into the
main table at regular intervals. That'll probably scale better than insert delayed.
Preliminaries
-------------
Per agreement with PM, it has been decided to keep the INSERT/REPLACE DELAYED
syntax, but convert it to INSERT/REPLACE with a warning before executing.

The following system variables are devoted to INSERT DELAYED:

- delayed_insert_limit
- delayed_insert_timeout
- delayed_queue_size
- max_insert_delayed_threads
- max_delayed_threads

These will also remain in place, but the server will ignore them. The
current deprecation message will continue to be displayed.

Parser Changes
--------------
The parser will be changed to parse INSERT/REPLACE DELAYED just like
INSERT/REPLACE.

Mutexes etc
-----------
The following mutexes will be deleted:

- key_LOCK_delayed_create
- key_LOCK_delayed_insert
- key_LOCK_delayed_status.

The lock type TL_WRITE_DELAYED will be removed.

Code Changes
------------
The implementation of INSERT DELAYED will be completely deleted.

performance_schema Changes
--------------------------
The following columns will be deleted in the table
performance_schema.table_tlws_by_table: 

- COUNT_WRITE_DELAYED,
- SUM_TIMER_WRITE_DELAYED
- MIN_TIMER_WRITE_DELAYED
- AVG_TIMER_WRITE_DELAYED
- MAX_TIMER_WRITE_DELAYED

Replication Tool Changes
------------------------
The tool mysqlbinlog outputs smart comments mentioning INSERT
DELAYED. these will be dropped.

mysqldump Chagnes.
------------------
mysqldump has an option --delayed-insert which causes delayed inserts
to be used when available. This functionality and option will be
removed.

Changes to regression test suite
--------------------------------
- All tests targeting INSERT DELAYED functionality will be deleted.
- All tests exposing INSERT DELAYED mutexes will be deleted.
- Tests revealing the performance_schema table columns mentioned above
  will be altered.