Fixed DELAYED INSERT - Issue with InMotionHosting

Akrion

Well-known member
Ok guys, I really need somebody from XenForo Ltd. to take a look @ this since I am going on and off with inmotion hosting for about 15 emails and more then 2 weeks. Here is their last reply:

"Hello,

Thanks for getting back to us.

At this time the biggest concern we have would be with the delayed inserts. Since all of the example queries forwarded to you by Brandon seem to target a specific table, it may be quite easy to narrow this down further. In fact, after a very rudimentary search for any reference to "xf_thread" on your account, the only results populated were in the audiera.com/forums/ location which seems to contain a xenforo installation.

While the queries aren't actually processing anything until they complete, they do occupy an open connection which may be needed by another user (or yourself) later on and occupy memory and resources on the server. This has a detrimental effect when you're sharing the server with more than a few other users and because of this we do notify you when this seems to be a consistent or problematic issue. Most software developers do not take this into account, and typically shift the blame to avoid performing optimizations to their code or providing alternatives to multi-user environments, sacrificing efficiency and resources for speed. This allows the script to complete running even though it is still processing your request (or waiting to process it) in the background.

See the official MySQL documentation for more information:

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

"Note that 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.

The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost."

This aside, by all appearances this should be an easy adjustment you can make if this is an option you can disable (or if they will not allow you to alter this in your settings you may be able to replace the affected portions with some that do not have the same effect). I took the liberty of performing a secondary scan (just in your forum directory) and given the context of the query sent to you found the file that's likely responsible for generating these queries:

.../forums/library/XenForo/Model/Thread.php

You should be able to edit this file (or more specifically the following function within it)

public function logThreadView($threadId)
{
$this->_getDb()->query('

INSERT DELAYED INTO xf_thread_view
(thread_id)
VALUES
(?)
', $threadId);
}

to something like this

public function logThreadView($threadId)
{
$this->_getDb()->query('
INSERT INTO xf_thread_view
(thread_id)
VALUES
(?)
', $threadId);
}

essentially, just removing the "DELAYED" word from it. This will change the way the software is running by sending data, forcing it to send the data as soon as possible, rather than having a background process.

Best Regards,

"

I really appreciate your assistance.
 
What error are you experiencing that caused you to open a ticket with your host? DELAY_INSERT is used for updating view counts; it is queued until the server reports that it is okay to insert the data (when more important inserts/updates are completed).
 
What error are you experiencing that caused you to open a ticket with your host? DELAYED_INSERT is used for updating view counts; it is queued until the server reports that it is okay to insert the data (when more important inserts/updates are completed).
Well On numerous ocasions I had erros from multiple browsers that "connection to the server had been refused" or "cannot connect to the server", then I also got Database connection issue as well. When I called them they told me my account was suspended due to the overloading of the MySQL server. After I asked for logs and proof of what caused all this they started trowing all this "DELAYED INSERTS" theories.

That is why I wanted to ask here ... I understand what those are (Thank you very much Walter) but as you can understand no matter what I said I still get from them the same story ... its your code (as you can see above Walter).
 
In the end, we are probably going to have to add a switch to disable this.

Not because delayed inserts are bad (on the contrary) but because there are various hosts that have no idea what they are and why they're good. They go on to blame the issues with their server (load) on something that they don't know much about. The bit that annoys me most is that, as Walter has pointed out, they can actually explicitly disable that feature of MySQL. (Or if they don't want to disable it, lower the default wait_timeout limit and they shouldn't hang around for as long.)

I'm sure many programmers can attest to the frustration of having to do something not because of any empirical evidence, but because someone somewhere thinks it's "bad". :)
 
On a side note, since INSERT DELAYED isn't supported by all storage engines so having a switch to disable it will be quite useful. For example if we use MySQL Cluster (NDB storage engine), it wouldn't support INSERT DELAYED (NDB is faster than MEMORY storage engines).
 
Presumably it would just ignore the "delayed" component (like transactions and MyISAM)?
 
I would assume so, but I have not tried it yet to see. The MySQL docs say doing it on an unsupported storage engine will throw an error... But maybe it's just a warning of sorts.
 
Presumably it would just ignore the "delayed" component (like transactions and MyISAM)?
Random side note... it does NOT ignore the "DELAYED" component when using ndbcluster. :) Thankfully the XF option to disable it works fine.

Another tip for people using ndbcluster... for the tables that normally use the MEMORY storage engine, you can make an ndbcluster table act like one. If the whole cluster restarts, the data in that table is lost (does not write out anything to disk... no redo log or dumped to LCP). It makes those particular tables really, really, really fast since there's never any disk I/O for anything.

If you ALTER the table like so, the ndb_table_no_logging variable tells that table to never write anything related to it to disk.

Code:
SET @@ndb_table_no_logging = 1;alter table xf_session_activity ENGINE = ndbcluster;SET @@ndb_table_no_logging = 0;

You end up with the speed of MEMORY tables (since ndbcluster normally stores everything in memory anyway), but also redundancy (it keeps multiple copies of the table/data in your cluster) as well as record-level locking (MEMORY storage engine only supports table locks).

I'm actually tossing around the idea of just replacing memcached with a no-logging ndbcluster table... better failover and redundancy with it.
 
Thank goodness for Google & this active community with great information!

I've just recently converted my forum to xF and saw some lines in MyTop that had me scratching my head:

6 DELAYED localhost my_XF 252 Delayed Waiting for INSERT

This thread helped me to understand what is going with these delayed inserts:
  1. They aren't a bad thing (actually they are a good thing)
  2. xF 1.2 has an admin setting that allows admins to turn it off if their host complains about it
Thanks for all the great info in this thread!
 
Top Bottom