Slow searches on new Linode machine

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Albert Wiersch
Registered User
Posts: 145
Joined: Sat Dec 11, 2004 6:00 pm
Location: Dallas, TX
Name: Albert Wiersch
Contact:

Re: Very slow searches for simple words

Post by Albert Wiersch » Thu Mar 29, 2018 1:04 pm

janus_zonstraal wrote:
Thu Mar 29, 2018 10:44 am
Did you try the MySQL Fulltext for the "Search backend".
Yes, tried that and just tried again (and I did rebuild the MySQL Fulltext index before searching). A search for "apple" took about 26 seconds.

The index rebuild took less time than the search for "apple". The rebuild took about 6 seconds with 9395 indexed posts, while the search for "apple" took about 26 seconds.

User avatar
Mick
Support Team Member
Support Team Member
Posts: 21681
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: Very slow searches for simple words

Post by Mick » Thu Mar 29, 2018 1:10 pm

Can you supply a user/test account as you have search turned off for guests please?
"The more connected we get the more alone we become" - Kyle Broflovski

Albert Wiersch
Registered User
Posts: 145
Joined: Sat Dec 11, 2004 6:00 pm
Location: Dallas, TX
Name: Albert Wiersch
Contact:

Re: Very slow searches for simple words

Post by Albert Wiersch » Thu Mar 29, 2018 1:43 pm

Mick wrote:
Thu Mar 29, 2018 1:10 pm
Can you supply a user/test account as you have search turned off for guests please?
I will PM a test account to you. Thank you!

User avatar
Mick
Support Team Member
Support Team Member
Posts: 21681
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: Very slow searches for simple words

Post by Mick » Thu Mar 29, 2018 1:52 pm

Try enabling cookie secure, it’s disabled ATM.

I can confirm BTW that searching for Apple is slow.
"The more connected we get the more alone we become" - Kyle Broflovski

Albert Wiersch
Registered User
Posts: 145
Joined: Sat Dec 11, 2004 6:00 pm
Location: Dallas, TX
Name: Albert Wiersch
Contact:

Re: Very slow searches for simple words

Post by Albert Wiersch » Thu Mar 29, 2018 2:06 pm

Thank you. I've enabled 'Cookie secure'.

User avatar
Mick
Support Team Member
Support Team Member
Posts: 21681
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: Very slow searches for simple words

Post by Mick » Thu Mar 29, 2018 2:15 pm

Cheers, it didn’t make much difference :(

I think the issue is that there are four pages of hits for Apple because of, presumably, iOS stuff and things like apple-touch-icon.png etc. If you search using quotes like “Apple” it finds it almost immediately.
"The more connected we get the more alone we become" - Kyle Broflovski

Albert Wiersch
Registered User
Posts: 145
Joined: Sat Dec 11, 2004 6:00 pm
Location: Dallas, TX
Name: Albert Wiersch
Contact:

Re: Very slow searches for simple words

Post by Albert Wiersch » Thu Mar 29, 2018 2:24 pm

But 25 seconds for a search? Is it normal for some phpBB search to be that slow? It's not just "apple" as that was just an example. I could come up with some other slow searches, and sometimes they are so slow that they just time out.

A search for "optimization" comes back rather quickly and has 29 matches.

A search for "development" takes about 29 seconds and has 208 matches.

User avatar
Mick
Support Team Member
Support Team Member
Posts: 21681
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: Very slow searches for simple words

Post by Mick » Thu Mar 29, 2018 2:30 pm

No certainly not, I’ve tested it elsewhere and the search is only a couple of seconds. I see you’ve tried other search engines so it’s probably nothing to do with that. The only thing I can think is it’s a server/dB issue. Have you got a test copy you can run on your local machine? If so that may throw some light on it, apart from that I’m stumped.
"The more connected we get the more alone we become" - Kyle Broflovski

Albert Wiersch
Registered User
Posts: 145
Joined: Sat Dec 11, 2004 6:00 pm
Location: Dallas, TX
Name: Albert Wiersch
Contact:

Re: Very slow searches for simple words

Post by Albert Wiersch » Thu Mar 29, 2018 2:36 pm

It's Linode virtual server but my buddy who manages it for me replicated the system on a dedicated machine and it still had the same problem.

We've checked the databases for errors and they came back "OK".

So we are (still) stumped too and have just been living with it for almost a year now because we can't figure it out.

User avatar
JoshyPHP
Code Contributor
Posts: 1046
Joined: Mon Jul 11, 2011 12:28 am

Re: Very slow searches for simple words

Post by JoshyPHP » Thu Mar 29, 2018 3:26 pm

Albert Wiersch wrote:
Thu Mar 29, 2018 4:47 am
https://www.htmlvalidator.com/temp/SQL%20Report.html

So... if I'm reading this right, all the SQL queries are fast but something happens after QUERY #7 and before QUERY #8 that takes a very long time and spikes the CPU... so what is it doing between these two queries that is taking such a long time?
You'd need to profile the script to see where most of the time is spent. That's something that's easier to do on a development server rather than live.

If you post a copy of your phpinfo() it may reveal some important information. Maybe something related to PCRE.

You may also try to edit search.php and replace $hilit = str_replace(' ', '|', $hilit); with $hilit = ''; to see if disabling this feature makes any difference with the slow search terms.

If you have a list of censored words, you can try clearing it and see if it makes a difference. Obviously, those changes would need to be reverted after use.
I wrote the thing that does BBCodes in 3.2.

Albert Wiersch
Registered User
Posts: 145
Joined: Sat Dec 11, 2004 6:00 pm
Location: Dallas, TX
Name: Albert Wiersch
Contact:

Re: Very slow searches for simple words

Post by Albert Wiersch » Thu Mar 29, 2018 7:31 pm

JoshyPHP wrote:
Thu Mar 29, 2018 3:26 pm
You'd need to profile the script to see where most of the time is spent. That's something that's easier to do on a development server rather than live.
Thanks. I have never profiled a PHP script before so I'd have to figure this out.
JoshyPHP wrote:
Thu Mar 29, 2018 3:26 pm
If you post a copy of your phpinfo() it may reveal some important information. Maybe something related to PCRE.
Here it is:
https://www.htmlvalidator.com/temp/PHP% ... ation.html
JoshyPHP wrote:
Thu Mar 29, 2018 3:26 pm
You may also try to edit search.php and replace $hilit = str_replace(' ', '|', $hilit); with $hilit = ''; to see if disabling this feature makes any difference with the slow search terms.

If you have a list of censored words, you can try clearing it and see if it makes a difference. Obviously, those changes would need to be reverted after use.
Thanks... I will try this and see what happens.
Last edited by Albert Wiersch on Thu Mar 29, 2018 9:23 pm, edited 1 time in total.

Albert Wiersch
Registered User
Posts: 145
Joined: Sat Dec 11, 2004 6:00 pm
Location: Dallas, TX
Name: Albert Wiersch
Contact:

Re: Very slow searches for simple words

Post by Albert Wiersch » Thu Mar 29, 2018 9:23 pm

JoshyPHP wrote:
Thu Mar 29, 2018 3:26 pm
You may also try to edit search.php and replace $hilit = str_replace(' ', '|', $hilit); with $hilit = ''; to see if disabling this feature makes any difference with the slow search terms.
That made a HUGE difference and must be it! But what does this mean exactly and how do I bring this feature back and fix the speed problem?

User avatar
JoshyPHP
Code Contributor
Posts: 1046
Joined: Mon Jul 11, 2011 12:28 am

Re: Very slow searches for simple words

Post by JoshyPHP » Thu Mar 29, 2018 9:52 pm

I suspect the routine that highlights search terms suffers from excessive matching under some conditions. I looked at your PHP configuration and it's fine. I had hoped that PCRE's JIT was off and and that turning it on would have improved the performance enough for the search to be usable. Unfortunately, your configuration is fine (JIT is enabled) which means there's nothing to optimize there.

The same regexp is used in 5+ places throughout phpBB, which can be found by searching for <span class="posthilit"> inside the codebase.
I wrote the thing that does BBCodes in 3.2.

Albert Wiersch
Registered User
Posts: 145
Joined: Sat Dec 11, 2004 6:00 pm
Location: Dallas, TX
Name: Albert Wiersch
Contact:

Re: Very slow searches for simple words

Post by Albert Wiersch » Thu Mar 29, 2018 10:48 pm

JoshyPHP,

Again, thank you so much for helping me find this issue. It was really irritating me!

But what do I do here? I really like the search highlights but the search time is a huge problem and is unacceptable.

If the only practical solution to fixing search times is to disable highlighting by making the change you suggested then I will do it... unless there is another option that you or someone else knows about?

Also, I assume (and hope) there are no other negative side effects of disabling the search highlighting other than the obvious lack of highlights?

UPDATE: Unfortunately I found a side-effect.... going to another page of the search results does not work (like page 2, page 3, etc) with $hilit = ''; There's gotta be a better solution. Are the PHP developers aware of this problem? Perhaps I should file a bug report. I can't be the only one suffering from this issue.
Last edited by Albert Wiersch on Thu Mar 29, 2018 11:18 pm, edited 1 time in total.

User avatar
JoshyPHP
Code Contributor
Posts: 1046
Joined: Mon Jul 11, 2011 12:28 am

Re: Very slow searches for simple words

Post by JoshyPHP » Thu Mar 29, 2018 11:05 pm

Now comes the hardest part: finding someone who's willing and able to volunteer their time to work on a fix and follow through with a Pull Request knowing it will take 3 to 6 months before it gets released publicly.

Alternatively, it may be possible to implement a workaround as an extension. It's about the same amount of work but it becomes usable immediately without having to wait for a release.

Based on a cursory examination of the file, I don't believe there are any side-effects to disabling highlighting apart from the obvious.
I wrote the thing that does BBCodes in 3.2.

Post Reply

Return to “[3.2.x] Support Forum”