Support archive | go to Low

Low Search in MySQL 5.7

Stephen 11 Jul 2018 11:41 question, complete

Hi Low,

I've got an old website that's running EE 2.11.2 and Low Search 4.4.4.

I'm trying to move the site from a cPanel VPS that runs MySQL 5.6, to a new cPanel VPS (with more RAM so it should be faster) that happens to run with MySQL 5.7.

Everything else is setup the same and this is using PHP 5.4. I'm just testing the site and noticed that everything seems okay, except that performing a search with a Low Search form is taking about 7 seconds on this new VPS, but it was only taking about 1.5 seconds on the old VPS.

The only difference I can see is that this new VPS is using MySQL 5.7 instead of 5.6, but I'm told it can't be downgraded easily.

Do you have any idea why Low Search might seem to work a lot slower in MySQL 5.7?

Looking at the template debugging, it seems to slow down a lot after the 'Low Search: Calling the channel module' line. If I disable whatever extensions are listed after that, it doesn't really make any difference and whatever follows 'Low Search: Calling the channel module' is taking over 3 seconds (which is a large chunk of this extra time).

(0.221607 / 23.93MB) Low Search: Pre-applying search vars to tagdata
(0.231355 / 24.14MB) Low Search: Calling the channel module
(3.521634 / 26.05MB) Calling Extension Class/Method: Low_reorder_ext/channel_entries_query_result

Do you have any idea why Low Search might seem to work a lot slower in MySQL 5.7?

Thanks for any advice,

Stephen

Replies

Low 11 Jul 2018 14:16

It might look like a LS issue, but the time gap between those to log entries is practically all native workings.

Low Search uses the native Channel Entries tag to generate the search results. When LS says 'Calling the channel module', it has done most of the work, set the fixed_order or entry_id parameters, and calls channel:entries to let it parse the entries. That's where it takes a lot of time, but LS is pretty much done by then (apart from some minor post-processing).

So, not a lot LS can do here.
Stephen 11 Jul 2018 16:07

Hi Low,

No worries, thanks for letting me know.

Thanks,

Stephen
Paul Cripps 5 Feb 2019 15:57

@stephen, did you manage to make speed improvements?

Low 6 Feb 2019 08:26

Note that the native channel:entries tag takes longer to parse the more entries it needs to display. If you can, try and reduce the entries per page to 25 or 10. This will significantly reduce loading time, especially if the entries contain relationships.

Stephen 21 May 2019 10:08

Hi Paul, sorry, only just seen this. No, we never resolved the issue I'm afraid. Our workaround was to manually downgrade MySQL from 5.7 to 5.6, which isn't great. Have you had any more luck with this?

Low - I've got a test search here with 900 results and 10 entries per page, and it still takes 6.5 seconds to load locally when using MySQL 5.7. It only seems to take a quarter of a second less to load 1 result. It takes 1.5 seconds to load the full 10 entries per page on a basic VPS using MySQL 5.6. We can't look at upgrading EE until next year I'm afraid (if that would fix it, I don't know), so our only workaround at the moment is to downgrade MySQL.

Low 21 May 2019 10:13

If the delay still happens after "Calling the channel module", then my previous comments still apply. LS just calls native methods and has no influence over how EE handles that...

Stephen 21 May 2019 10:42

I think it does, if I'm reading the template debugging correctly. I've added it here for info: https://pastebin.com/RWiS3uez

Low 21 May 2019 10:55

Yeah. At 2 points, there's a big-ish jump. After "Getting channel field info from API" and "Calling the channel module", both of which are log entries made just before calling native EE methods, over which I don't have any control.

You might want to look at maybe indexing some columns in the DB of there are queries that take a long time (look at that debug info in the debugger, too). But it's safe to say LS can't really solve this.

Stephen 21 May 2019 13:20

Okay thanks Low.