patternsqlMinor
MySQL - INSERT DELAYED <- deprecated What now?
Viewed 0 times
insertwhatdeprecateddelayedmysqlnow
Problem
I've just been looking at the MySQL doc's https://dev.mysql.com/doc/refman/5.7/en/insert-delayed.html
I was intending to use an INSERT DELAYED, but I see that this has been deprecated.
I still need this functionality for compatibility with some PHP code that is handling some very large data transfers. I'm quite happy to update my PHP but I have no idea how to achieve the same functionality now.
How would we achieve the same outcome as traditional INSERT DELAYED now that it's deprecated?
I was intending to use an INSERT DELAYED, but I see that this has been deprecated.
I still need this functionality for compatibility with some PHP code that is handling some very large data transfers. I'm quite happy to update my PHP but I have no idea how to achieve the same functionality now.
How would we achieve the same outcome as traditional INSERT DELAYED now that it's deprecated?
Solution
According to the MySQL 5.5. Documentation
INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. For engines that do not support DELAYED, an error occurs.
You could downgrade to MySQL 5.5 (Of course, I am joking). I have three suggestions. Please look into them.
SUGGESTION #1
You could setup MySQL Replication and perform the following
SUGGESTION #2
Tuning the InnoDB Environment to handle faster writes. Please see my 4.5-year-old posts How to use insert delay with the InnoDB engine and use less connection for insert statements? and Mysql load from infile stuck waiting on hard drive
SUGGESTION #3
Customize the bulk insert to use extended inserts that load a fixed number of rows at a time like a mysqldump does.
INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. For engines that do not support DELAYED, an error occurs.
You could downgrade to MySQL 5.5 (Of course, I am joking). I have three suggestions. Please look into them.
SUGGESTION #1
You could setup MySQL Replication and perform the following
- Bulk insert into a table on the Master whose storage engine is BLACKHOLE.
- The same table on the Slave would be InnoDB. Perform your SELECTs on the Slave
- See my 3.5-year-old post How to do something like UPDATE DELAYED in MySQL
SUGGESTION #2
Tuning the InnoDB Environment to handle faster writes. Please see my 4.5-year-old posts How to use insert delay with the InnoDB engine and use less connection for insert statements? and Mysql load from infile stuck waiting on hard drive
SUGGESTION #3
Customize the bulk insert to use extended inserts that load a fixed number of rows at a time like a mysqldump does.
Context
StackExchange Database Administrators Q#160168, answer score: 6
Revisions (0)
No revisions yet.