Design issue Move/Copy Posts times out on long threads

[Xon]

This is probably a design issue, but Moving/Coping posts between threads causes XenForo_DataWriter_Discussion_Thread::rebuildDiscussion() to be triggered.

For a long enough thread, this can take quite a long time. A 614 page thread, with 15301 visible posts took ~19 seconds to merge 2 posts into it (the posts where added to the last 5-10 pages).

Looking at recalculatePostPositionsInThread, the only thing which leaps out at me is that it is doing a fetch per post and an update per changed post position. This potentially can cause a very large number of roundtrips.

It should be possible to rewrite most of that into some SQL with a temporary table to dramatically drop the number of round-trips so they do not scale as the number of posts in the thread increase.

 

[Mike]

I'm going to consider this mostly a design issue. I would note that if you refer to "roundtrips" as "size of data/number of packets sent over the wire" then you're correct that it will scale with thread size but there's only one actual fetch query; the updates do scale based on the number of updates required (which would only be in the last 5-10 pages in your example).

When it comes to how XF is built, I can't say we directly consider MySQL network latency as a consideration -- if you're putting MySQL on a different server, low latency will be hugely important to response time.

 

[Xon]

It does a streaming-fetch of the rows which causes round-trips between MySQL & php. By default I think php will fetch 1 row over the wire a loop.

But as you said, it is a design issue with requiring the entire thread have every position recalculated when moving/copying post.

 

[Mike]

Just so you know, the while fetch loop isn't going to MySQL each time; it's really the same as a fetch all just not putting them into a PHP array simultaneously. It's a buffered query so the result set has already been fetched: http://php.net/manual/en/mysqlinfo.concepts.buffering.php The library doing the fetching may vary depending on how PHP is compiled/configured so it may vary how it pulls things down.

 

[Xon]

Just so you know, the while fetch loop isn't going to MySQL each time; it's really the same as a fetch all just not putting them into a PHP array simultaneously. It's a buffered query so the result set has already been fetched: http://php.net/manual/en/mysqlinfo.concepts.buffering.php The library doing the fetching may vary depending on how PHP is compiled/configured so it may vary how it pulls things down.

My apologies.

What I should have done at the start, is profile to see what the hotspot was.

I ran the function through xhprof while merging 1 post onto the very end of a thread with ~48000 posts, and of the ~13 second wall time. 11.91 seconds was 1 call to mysqli_stmt::store_result, so it does look like it is fetching the entire result set at once. Calling fetch in that function did take ~0.5 seconds, but most of that would be profiler overhead.

Running the same query but storing to a temp table took about 2-3 seconds.