modxcms
MySQL 8 - modx_discuss_posts_closure table slow
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!
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.
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!
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)
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.