We used to have RT 4.4.3, MariaDB 5.5.68 and Sphinx 2.2.11 all running under CentOS 7, and we now have RT 5.0.3 with MariaDB 10.3.35 running under Alma Linux 8. So we are now using the native ‘mysql’ full-text search whereas we used to run with Sphinx. We have roughly 120K tickets and the RT5 database is about 22G on disk.
It works OK, but if someone does something stupid like searching for Content LIKE 'and' then no one can use RT for over 5 minutes (in fact anything up to 20 minutes depending on the query). The same query on RT4/Sphinx was almost instant, but didn’t return all tickets because of the MaxMatches=>10000 setting. Sadly the MaxMatches setting is Sphinx-specific and so doesn’t do anything on our RT5 instance.
Can anything be done to stop people from accidentally DOSing our RT server (short of going back to Sphinx, I guess)? Why is it so slow? (Searching for less common words has acceptable performance but is still usually slower than on RT4/Sphinx.)
So are you sure that nobody can search? I’ve noticed in RT5 that if you perform a search and try to open a new tab (under the same user and/or session) there’s protection to prevent RT from letting a single user perform multiple searches… At first I also thought I was DOS’ing my system but it turned out that if I opened a separate browser on a different client with a different user the system was perfectly responsive… Perhaps you’re having the same issue?
It works OK, but if someone does something stupid like searching for Content LIKE 'and' then no one can use RT for over 5 minutes (in fact anything up to 20 minutes depending on the query). The same query on RT4/Sphinx was almost instant, but didn’t return all tickets because of the MaxMatches=>10000 setting. Sadly the MaxMatches setting is Sphinx-specific and so doesn’t do anything on our RT5 instance.
To be fair, it’s still quite bad if I can accidentally lock myself out of RT for 20 minutes.
What you say is true to some extent. What’s probably happening is that all requests from the same browser are being funneled through the same HTTPS connection (which is busy) rather than that any protection is going on.
Originally we had the Apache Multi-Processing Module (MPM) set wrongly and that did cause massive performance problems for everyone (I had to reboot the server at least once after someone did a bad search because it went into pathological swapping mode). Fortunately that’s fixed now.
So yes, it doesn’t lock everyone else out but I think it’s still a performance bottleneck.
yes this will lock out for all users if you are using MySQL or MariaDB. It’s a database level lock issue not Apache or nginx issue. If you open up an admin db console you can see the other queries that are locked waiting for the first query to complete.
We upgraded our RT instance from version 4.4.3 to 5.0.3, migrating from CentOS 7 to Alma Linux 8 in the process. With this upgrade, we transitioned from Sphinx to the native ‘mysql’ full-text search in MariaDB 10.3.35. While the new version seems to work well overall, we’ve encountered an issue with search performance. Specifically, when users inadvertently search for common words like ‘and’, it triggers a significant slowdown, causing RT to be unusable for up to 20 minutes.
Unfortunately, we do see this issue with native MariaDB and MySQL full text search. When searching for very common terms, it takes a long time, even with row limits. In RT 5.0.4, we added the DatabaseQueryTimeout configuration option that allows you to set a max time for a query to try to run. This can help reduce the impact of individual queries. We also don’t see the same behavior on postgres, so that’s another option.
