Searching tickets seems to be getting slower and slower lately, and when anyone is doing a large search it slows down the entire staff ticket system. Server logs show CPU never above 120 and Memory is at a constant 25% with some rare spikes to 50% utilization.
I don't think it's the web server or database server as their config has not changed in several years. The only thing that has changed is that our database has gotten larger as time goes on.
When people see slow search times and lagging staff panel (when someone is searching), is insufficient memory typically the culprit?
First action for slow search
Typically yea. It can also be a case of needing to optimize MySQL itself (like buffer pool size, thread concurrency, etc.), needing to repair/optimize the database tables, and/or setup multiple database servers with something like ProxySQL to distribute the load. We have some really large databases in our hosted platform with no performance issues.
Sometimes it’s also due to the search query/criteria, what they are searching for, and where they are searching. Some searches can be really expensive as it has to scan all rows of a table, etc. We do have some updates for search optimizations coming soon but that mainly deals with user email searching.
Cheers.