David.. Posted June 19, 2022 Posted June 19, 2022 I tried moving from MySQL search to Elastic Search and midway I noticed that Elastic Search results were pretty bad, so during the rebuilding of posts, I moved back to MySQL search as the results are much better. Everything was fine, but now I noticed the forum loading very slowly and checking processlist shows me this: If I reboot my database, the slow loading times are instantly fixed. So this leads me to believe that the issue is in the screenshot above. Could any staff please review and hopefully fix? I have my access information up to date
Randy Calvert Posted June 19, 2022 Posted June 19, 2022 When you move from mySQL to ElasticSearch, it has to actually build an index in ElasticSearch with information from all of the content from your site. That will involve making lots of queries that are done as background tasks over the course of several hours. You have to wait for that process to complete before all of the results will properly show. You should not judge the results until the indexing is fully complete. Now... if your site is slowing down dramatically, it most likely means your server is running close to capacity anyway. You might try doing it during a slow time in the middle of the night OR you might consider turning your board offline for several hours while the indexing process is done if there are not sufficient resources to do it and serve your normal traffic.
David.. Posted June 19, 2022 Author Posted June 19, 2022 1 minute ago, Randy Calvert said: When you move from mySQL to ElasticSearch, it has to actually build an index in ElasticSearch with information from all of the content from your site. That will involve making lots of queries that are done as background tasks over the course of several hours. You have to wait for that process to complete before all of the results will properly show. You should not judge the results until the indexing is fully complete. Now... if your site is slowing down dramatically, it most likely means your server is running close to capacity anyway. You might try doing it during a slow time in the middle of the night OR you might consider turning your board offline for several hours while the indexing process is done if there are not sufficient resources to do it and serve your normal traffic. I understand. The rebuild took quite a while and everything completed except for posts. Topic titles are most important in our case, and when searching using ES, status updates appeared way too many times - sometimes even above actual topics. This was not the case with MySQL search. Right now, nothing is processing in ACP. MySQL search is working, but it seems like there may be an underlying issue from switching to ES and back to MySQL which may need reviewing. Especially since a simple SQL restart solves the issue and clears out the process list.
Randy Calvert Posted June 19, 2022 Posted June 19, 2022 (edited) The posts table itself is where the majority of data is going to be though. Also IPB when I did my conversion did not run each type separately. It would run say 50-100 of one type, then do another 50-100 of another type, etc. So you're judging a cake on taste before it's done cooking in the oven. 🙂 With that being said... the next release of IPB software release notes (https://invisioncommunity.com/release-notes/) shows that there are improvements being made to the ElasticSearch index building process which might help in your case. If you just interrupt the migration and switch back, it does not automatically remove the MySQL queries that are pending. They should eventually time out, but that depends on your mySQL config. (You would talk to your server DBA for help with that.). Restarting the database just kills anything already running. IPB does not have any sort of concept of "aborting" a rebuild of the search index. Edited June 19, 2022 by Randy Calvert
David.. Posted June 19, 2022 Author Posted June 19, 2022 Does the posts table contain topic titles? So to resolve this would be to rebuild for ES and wait for it to complete. Then the issue should be theoretically fixed then I can decide on whether to stay with ES or switch back to MySQL?
Randy Calvert Posted June 20, 2022 Posted June 20, 2022 6 minutes ago, David.. said: Does the posts table contain topic titles? The post table does not contain the the topic title, but remember... the index is looking for information expected in there to make judgements on other areas to look at. 7 minutes ago, David.. said: So to resolve this would be to rebuild for ES and wait for it to complete. Then the issue should be theoretically fixed then I can decide on whether to stay with ES or switch back to MySQL? Yes. I would highly recommend letting the process actually fully complete before judging efficacy. If it does not provide the results you want afterwards, switch back. 🙂.
David.. Posted June 20, 2022 Author Posted June 20, 2022 10 minutes ago, Randy Calvert said: The post table does not contain the the topic title, but remember... the index is looking for information expected in there to make judgements on other areas to look at. Yes. I would highly recommend letting the process actually fully complete before judging efficacy. If it does not provide the results you want afterwards, switch back. 🙂. Will do. Waiting on a reply from IPS to decide as I don't want to make matters worse.
Randy Calvert Posted June 20, 2022 Posted June 20, 2022 1 minute ago, David.. said: Will do. Waiting on a reply from IPS to decide as I don't want to make matters worse. No problem. There is limited support on the weekends, so that might need to wait till tomorrow sometime.
Marc Posted June 20, 2022 Posted June 20, 2022 Randy is correct there. Judging search results before the rebuild is completed will simply result in you judging the results on incorrect/incomplete data. This is actually stated on the search page when you perform a search if its not yet completed. Its not very clear here what state you are at. Do you still have those errors, or did it resolve completely with the restart? Either way, I would advise rebuilding the search index here. Go to System->Search and select rebuild index, then wait for that to complete fully before we go any further with any potential issues you may have
David.. Posted June 20, 2022 Author Posted June 20, 2022 4 hours ago, Marc Stridgen said: Its not very clear here what state you are at. Do you still have those errors, or did it resolve completely with the restart? It is the second time it comes back. But it does resolve after a restart so far. I'll do what you recommend and get back to you.
David.. Posted June 21, 2022 Author Posted June 21, 2022 (edited) 12 hours ago, Marc Stridgen said: No problem. Hope this resolves the issue for you After rebuilding forever, at the 80% mark I am now receiving a PHP memory exhaustion message. Is 512M not enough? Something feels wrong here. My access details are up to date. Edited June 21, 2022 by David..
David.. Posted June 21, 2022 Author Posted June 21, 2022 I left it running for a few more hours and it seems to have been completed now as there’s no tasks in ACP. However, there are 0 improvements or changes to search results as compared to searching while it was still rebuilding. It’s still really bad for me. Switching back to MySQL now and hopefully no issues.
David.. Posted June 21, 2022 Author Posted June 21, 2022 9 hours ago, Marc Stridgen said: No problem. Please let us know It has not been solved. I rebuilt both ES & MySQL search and for some reason the queries in the above screenshot keep coming back. Can someone take a look into this?
Jim M Posted June 21, 2022 Posted June 21, 2022 Things would be removed from the index as required by processes, tasks, or actions. Could you please wait a little to see if this is happening after these complete? Additionally, you mentioned performance issues, are you still seeing this? Things seem quite snappy on your community for viewing, getting search results, etc...
David.. Posted June 21, 2022 Author Posted June 21, 2022 Just now, Jim M said: Things would be removed from the index as required by processes, tasks, or actions. Could you please wait a little to see if this is happening after these complete? Additionally, you mentioned performance issues, are you still seeing this? Things seem quite snappy on your community for viewing, getting search results, etc... Shouldn't the rebuild process take care of these? Are there after-tasks that need to be done once the rebuild is done? As mentioned above, the issue seems to go away after restarting MySQL. But it eventually comes back and that is the problem.
Jim M Posted June 21, 2022 Posted June 21, 2022 Content being removed from the search index are not exclusive to the rebuild process of moving from or to MySQL. They would happen in a natural environment as content is hidden, deleted, moved, etc... What we're trying to ascertain here is if it is from the move back to MySQL or not.
David.. Posted June 21, 2022 Author Posted June 21, 2022 2 minutes ago, Jim M said: Content being removed from the search index are not exclusive to the rebuild process of moving from or to MySQL. They would happen in a natural environment as content is hidden, deleted, moved, etc... What we're trying to ascertain here is if it is from the move back to MySQL or not. Should that be causing such a massive slowdown to the entire community then? For now, I will keep monitoring for when the issue returns and reply to this topic informing you. Hopefully you would be able to check as soon as possible as the slowdowns are quite infuriating for everyone.
Jim M Posted June 21, 2022 Posted June 21, 2022 1 minute ago, David.. said: Should that be causing such a massive slowdown to the entire community then? That would be something that you would need to analyze on your server, unfortunately. If there are any slow query logs we can take a look at, please let us know.
David.. Posted June 21, 2022 Author Posted June 21, 2022 41 minutes ago, Jim M said: That would be something that you would need to analyze on your server, unfortunately. If there are any slow query logs we can take a look at, please let us know. It’s happening again now and I notice in processlist the query is present. I won’t restart until you’ve reviewed, if you have time.
Jim M Posted June 21, 2022 Posted June 21, 2022 6 minutes ago, David.. said: It’s happening again now and I notice in processlist the query is present. I won’t restart until you’ve reviewed, if you have time. A bunch of topics were just deleted and unhidden from your community by a moderator so that would be expected. You will want to take a look at your server performance if this is taking some time. You can check your moderator logs by going to ACP -> Members -> Staff -> Moderators -> Moderator Logs.
David.. Posted June 21, 2022 Author Posted June 21, 2022 5 minutes ago, Jim M said: You will want to take a look at your server performance if this is taking some time. I'll investigate this again and get back to you.
David.. Posted June 21, 2022 Author Posted June 21, 2022 @Jim M So as you saw, the community is quite snappy. The issue here is that as soon as this query comes up in the MySQL processlist, the whole community starts to slow down. IPS\Content\Search\Mysql\_Index::removeClassFromSearchIndex:279 The issue here is that the query never seems to go away once it comes up (or it does and just keeps looping? I noticed the number changes from 279 to 278 to 265 randomly). But it always is there, running. To solve this issue, a simple MySQL restart does it, until the query comes back eventually. I've never had this issue before. It only started once I attempted to use Elastic Search which did not work well for me so I switched back. Another thing that may be contributing is the recent switch to a new server, but would it be related if a simple restart and clearing of processes fix it?
David.. Posted June 21, 2022 Author Posted June 21, 2022 (edited) Another issue that may be related is that the clearincompletemembers task is constantly locking for me and failing to process or complete. Could that be related to the above query/issue? I think it may be. As soon as I manually run the task, the query seems to pop up and cause a slowdown. Any way to resolve this? Edited June 21, 2022 by David..
Recommended Posts