search

modxcms / Discuss

MODX Revolution native forums.
19 stars 34 forks source link

MySQL 8 - modx_discuss_posts_closure table slow #43

Open gleighfield opened 1 month ago

gleighfield commented 1 month ago

I've migrated a site over to a new server that is pre-installed with MySql 8.

I've got cloud linux 5.6 on, and everything is fine - apart from when creating a new post.

The current table has some 1 million entries in it - and when posting, is resulting in delays of 2+ minutes.

I've done extensive troubleshooting and can confirm that this is the issue - an empty table results in the post going through straight away as expected.

Any help/advice would be appreciated!

netProphET commented 1 month ago

My initial hunch from looking at the xPDO schema is that the indexing may not be so great. It's basically a bunch of single-column indexes which all need to be updated on each new post. I don't have any recent working experience with Discuss but I would suggest taking a look at the queries generated where performance matters and to see if some better indexing could be figured out.

gleighfield commented 1 month ago

Hi netProphET

Sorted today, and that was what it was. Switching the tabled over to innoDB and setting the indexes.

A massive thank you to Mark Willis who helped me with this!

gleighfield commented 1 month ago

Question - do you happen to know the purpose of the emodx_discuss_posts_closure table? I'm struggling to understand the logic of it. (Some missing records I need to re-create)

netProphET commented 1 month ago

Hi Graeme,

That table is for representing the tree-like structure of posts. The idea of a closure table is to represent a heirarchy in a way that allows for efficient and/or simpler querying of certain types of relationships. Or said another way, "faster tree traversal". I can't say for sure in the case of this Extra, but there could be code in there somewhere to help maintain the integrity of the structure.