XF 2.1 Wrong statisics and performance issue after mass move and tagging of threads

[SynBay]

We moved roughtly 10k out of 100k threads from ther original sub forums to other sub forums. Within this step we tagged several threads with prefixes. There were no prefixes used before.

This mass move of threads had two effects to the forum:

First the statistics has become wrong. Thread and post counter now show roughtly three times as much as they really are.
Second it takes up to ten seconds to "Mark all forums read".

All cron jobs are running and i already rebuilded the user cache and the site index with no effect. What else can i do to get the statistics right again and to speed up the "mark all forums read" function back to normal speed? Thanks for any help. (XF v2.1.10P2)

 

[Mike]

In terms of the stats, you can rebuild thread and forum counters in the rebuild caches section and that should sort that.

In terms of mark all forums read, this isn't really affected by moving threads. It's affected by the total number of forums, but adding a few shouldn't make much significant difference. It could just be that your server was under higher load at the time, causing a general slow down (particularly with writes).

 

[SynBay]

Mike, thanks for the help. I found the problem now, but i have no idea what caused it or how to fix it.

As i wrote mass moving of threads broke our counters. I think i can reproduce this behavior. We moved maybe 2-3% of the threads in serveral steps, counters were tripled. Rebuilding the counters as you suggested solved that problem.

But another strange thing happened. Our node structure was tripled and that is the rease why "mark all thread read" suddenly is so slow. Suddenly we have for all categories, forums and sub-forums three identical nodes. Please have a look at the picture.



Next strange thing. Even though these nodes have correct permissions, the copied nodes do not show up in the forum list at the front page, only the original nodes show up. That is the reason, why i did not recognize it.

I already tried "rebuild forums", this did not change anything.

Any idea what happened here? Do you think it is save simply to delete the cloned nodes? Why don't they show up and where do they come from??? Hopefully somebody got an idea.

 

[Mike]

Just to check, how did you do this mass move?

I have zero idea how the nodes could have been duplicated like that. There's nothing in XF that can do that and it's never something I've heard of before. At best, I could only guess an add-on was involved.

It's hard to make any true recommendation when something very bizarre like this happens as we don't really know the state of the data. You could potentially remove the excess nodes, though it'd probably have to be done manually. The problem is I don't know if these are properly created, fully "formed" nodes with the paired node-type data. If that's missing, they may have to be removed through the database. Ideally, I'd recommend restoring a backup from before when this happened, though the amount of data loss there might not be acceptable. I'd certainly recommend taking a backup before you go about removing these nodes on the basis that we don't know what might be in/associated with them, so it's possible there might be other knock on effects.

 

[SynBay]

Mass move was done using inline moderation. I selected up to a couple of hundred threads. They were moved to another forum, in the same step they were assigned a prefix.



We have the plugin [8WR] XenPorta 2 (Portal) PRO 2.1.0.7 installed. This plugin is dealing with the forum structure, so possible it is caused by this plugin. I will do some further testing.

Anyhow, how to fix this problem now? Restoring a backup is not an option. The forum runs several weeks now with this damanged structure without any noticable problem, except that "mark all forums read" takes so long. Going back to the last backup without this problem means we loose several thousand of posts.

How can i find out if these nodes are fully "formed" nodes with paired node-type data?

The tables xf_node and xf_forum hold datasets for the cloned nodes. I suppose they are not fully formed because they do not show up at the frontend.

Which other tables have foreign key relations to the xf_node and xf_forum tables? In other words, which other tables need to be checked for orphaned data sets if i delete data from xf_node and xf_forum? Is there a database relation model available? That would help me a lot to understand the data structure of XenForo.

 

[Mike]

How can i find out if these nodes are fully "formed" nodes with paired node-type data?

The tables xf_node and xf_forum hold datasets for the cloned nodes. I suppose they are not fully formed because they do not show up at the frontend.

Actually I was expecting xf_node records only for these. If xf_forum records exist, then that's even more odd as that's quite a complex structure to get duplicated. You may just be able to delete the unexpected nodes from the control panel then.

Note that they will probably appear at some point. It seems likely that they're hidden because some data rebuilds haven't been triggered, but if that happens (and deleting a node will probably do that), they may suddently appear. It's definitely worth taking a backup before doing this and I'd probably close the site while doing the deletions to ensure people don't post in the duplicated forums accidentally.

 

[SynBay]

The problem is obviously caused by the plugin [8WR] XenPorta 2 (Portal) PRO. I disabled the plugin which caused all forums to be visible at the front page. I removed then the empty cloned forums and nodes in the admin UI. Now i have clean xf_node and xf_forum tables.

However, the problem with the slow "mark all forums read" function is still there, it takes around 8 seconds.

We have 142 nodes and 124 forums. The log shows that "mark all forums read" sends 1742 commads to the mysql db, 498 of this are select and insert statements. (Three select and one insert statement for each forum)

Wouldn't it be possible to do this special function, which is frequently used, in one loop by selecting all forums a user has read permissions and do all the "INSERT INTO xf_forum_read ..." statements in one loop? This would reduce the amount of queries to roughtly one quarter and speed up things a lot. Just an idea..