7

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?

  • Are you doing a bulk insert into a MyISAM table ??? – RolandoMySQLDBA Jan 6 '17 at 11:17
  • No it's an InodeDB, and it's an incremental sync between two databases, running on script. – TolMera Jan 6 '17 at 11:42
  • Is the goal to insert lots of rows eventually, but with minimal impact on other queries? Generally, you are better off letting InnoDB figure it out for itself. You might get some useful tips here . – Rick James Jan 6 '17 at 18:09
  • Can you use Replication instead of a script? – Rick James Jan 6 '17 at 18:11
4

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.

1

I take your question to mean, that you need to defer work until after a given period of time.

In these circumstances, I would use a queue and defer my queue job for the required time.

As an example https://laravel.com/docs/5.3/queues#delayed-dispatching

Though from the docs "queued to be inserted when the table is not in use by any other thread" is not achievable as is via this method, you would need to implement this functionality in your job if it is required.

  • Is this possible without using Laravel? Ostensibly this does not really answer my question. – TolMera Jan 6 '17 at 11:13
  • I would have put my answer as a comment, though I do not have enough rep. Yes, it is possible without laravel, you could look at rabbitmq.com/tutorials/tutorial-two-php.html – 64k Jan 6 '17 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.