Topic: Punbb search performance tweak
Hi, I am managing a quite big punbb forum (+- 500 000 posts). We have had recently some real performance problems, forum hits sometimes taking anormally long.
We searched a lonnnng time for the cause of the problem, tuning our fastcgi configuration, php environment vars, before stating the problem was the SQL server. I got help from some guys working at skyblog.com (understand: far more qualified than I am), and they stated the error was coming from the search queries.
After investigating the problem some time, they gave me that patch: http://www.skyblog.com/search.php.patch
Before yelling that it may break searches in some cases: we know it! As fas as I understood it, it may display inappropriate search results for posts that have been removed, moved, changed, whatever. But, still, have a look at the performance change:
http://waste.mandragor.org/mawu_cpu.png
http://waste.mandragor.org/mawu_slow.png
http://waste.mandragor.org/mawu_load.png
The patch was applied at 16h.
So, let's think about it: would there be one way to push that join out of the search query? Deleting that join would dramatically improve punbb's performance.
Anyway, even if some queries aren't as accurate as before and if the patch introduces some wrong-true results, I'm so happy these performances problems have ended, I wanted to share that with you!