Question about delayed inserts

[Mindzipper]

I'm trying to track down a load issue i'm getting and I ran into something strange

'waiting for insert' is coming up while monitoring mariaDB, and it even changes the username to 'delayed'.

I'm trying to figure out if this could be causing the issue. this seems to be random

when i see these they seem to run to about 300 seconds then time out.

 

[Mike]

I assume based on the title of the thread that you know the cause (insert delayed) and that this is really expected as that's how MySQL has implemented them. You can disable the "Enable Delayed Insert SQL Queries" option if you don't want them.

 

[Mindzipper]

yep i know the cause, i'm not clear on the reason for using delayed inserts. without causing problems could someone help me understand? if it's not that big a deal I can turn them off (I assume without hunting this is done in a config somewhere but I will look it up)

 

[Mike]

It's just designed to be an optimization, but a minor one and likely system dependent. You're free to disable it.

 

[Mindzipper]

THank you. I searched the manual, and google for disabling delayed entries and didn't find anything. do you mind if I ask where to disable it if I want to test?

 

[Mike]

You can use the ACP search for the option I mentioned above.

 

[Mindzipper]

derp. right you are. found it. thank you very much. sorry about that

 

[digitalpoint]

Delayed insert really shouldn't be used in XenForo unless you have a really bizarre situation, as it will make things slower.

Also, INSERT DELAYED as a whole is deprecated in MySQL 5.6 and will be removed completely from MySQL at some point.

http://dev.mysql.com/doc/refman/5.6/en/insert-delayed.html

INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it.

As of MySQL 5.6.6, INSERT DELAYED is deprecated, and will be removed in a future release. Use INSERT (without DELAYED) instead.

 

[digitalpoint]

Just ran across this in MySQL 5.7... maybe time to remove the option from XF?

DELAYED inserts and replaces were deprecated in MySQL 5.6.6. In MySQL 5.7, DELAYED is not supported.