Flarum search functionality error 500

marcozambi

Hello everybody,
I have completed a test import of my 275000 posts from SMF2 to Flarum, which went really well.
Now I started to play around with its features, and one of the key ones is the Search functionality.

It could be because of the huge amount of data we have, but vey often I run in a Server 500 error.
The system looks a bit overwhelmed, maybe an ajax call is timing out in the background?

Is there anything one could tune/test for specifically understand why most (but not all!) my searches are ending up being a Server 500 error?

This is the error I receive:

500 Internal Server Error
GET http://astronautica.community/flarum/api/discussions?filter%5Bq%5D=space%20&page%5Blimit%5D=3&include=relevantPosts%2CrelevantPosts.discussion%2CrelevantPosts.user

null

This happens a while after sending my search request, this is why I guess it can be an ajax call just timing out somewhere...

jordanjay29

marcozambi What's your php exec time?

marcozambi

jordanjay29 It's actually 30 seconds. I'd give it a more relaxed timeout, I guess...

marcozambi

jordanjay29 I have relaxed the execution time to 120 seconds but the error 500 comes well before that timeout.

It looks to me, and I might be wrong, that what "fails" is the quick search which is started directly in the search bar, to offer some results in the drop-down list.
If I click on "Search all discussions for..." then after a while and another pair of error 500, the results are appearing in the "main content" part of the page.

The "turning wheel" in the search bar text field continues rotating though, hence my impression that that task is timing out, and it looks it does it independently by what happens with the "main" search.

I hope I am clear enough explaining, this is a video of what happens.

FBI

Flarum search feature is amazing fast...

But, @marcozambi had same problem with me ?

It happen also in my millions post's forum, also.
It just some "keywords".
My server is powerful enough to search some, within secons.

Maybe this could be the problem?
- some characters during migration from other forum engine?
- db caching related? suggesting Flarum has feature rebuilt/re-index database?
- timeout related problem on core apps (not PHP setting), because I have checked from my old engine forum, that the search keyword is displayed as result, but not in flarum ?

luceos

FBI you're probably running MySQL, which isn't exactly good at these things. Laravel 5.3 now offers scout, which might be a great solution to built a search on top off with angolia or elasticsearch.

marcozambi

luceos Hi Luceos, thanks for your answer.

I understand that MySQL or MariaDB could not be the stronger soution for full text searches, but:
- I don't think MqSQL gets crushed by 300k posts (450 MB of text, dumped in a SQL file). MySQL is currently our platform interfaced to SMF2 and it runs heavy searches like a charm.
- MySQL is by far the most adopted db engine on the web, for generic uses. I'd reccomend in this development phase, to treat it as the main choiche for most of future flarum installations, and perform your testing against a very large sample db (even if it was 2 million lines of "lorem ipsum" random posts).

If you as developers have special requirements or tuning to reccomend, please do it by all means. I think, again, that it might be the case to change the timeout time of the ajax process which kicks off whenever you start typing stuff in the search bar.

luceos

marcozambi I wasn't mentioning the few thousands, I was mentioning the millions ?

marcozambi

luceos Ahahah sorry, no, I'd like to have so much discussions but not at all ?

Just try, if you have a workload simulation campaign of some sort internally, as part of QA, to test flarum search preview (as said the "normal search works, as you can see in the video I posted) with some relaxed parameters on millions of rows. Maybe you can replicate the problem..

It could be enough to handle graciously the process' death, with a nicer error message in the instant search feature.

Thanks for your efforts!

marcozambi

luceos Hi there!
Today I did a bit more playing with the Search function and I think I understand what is going on.

When a search is initiated by typing, say, "xyz" (with no double quotes btw) in the Search Bar, this starts an aJax call to some search engine in flarum. This in my case times out after about 30 seconds, and I have an error 500.
If I try a search then clicking on "Search all discussion for xyz" where xyz is/are again the same keywords used before, then a second ajax process is started. After a while I have an error 500 for this search as well, not remotely close to the max execution time of php script I have set to 120 seconds. This second error 500 comes luckily with plenty of debug info.
There are so many lines that I couldn't copy-paste them here otherwise I'd exceeded the max amount of chars for a post. Here is the Link.
https://dl.dropboxusercontent.com/u/25636589/flarum_error_500.txt

Long story short, I think the search engine gets stuck and does not properly release resources. The first ajax process start maybe a buffered sql query that in turn influences the second, hence the complains you can read in the linked file.

I have a strong feeling the search is too resource hungry...
Could somebody take a look into this, maybe with a flarum install having at least 300000 posts?

Flarum is super great but Search is a function that has to work, especially wit big forums...

P.S. I run a beta 5 with imported data from my current SMF2. Everything else works like a charm!

marcozambi

Another element added to this investigation, I have moved my test environment on the production server, which has a lot of muscles ?

I might have failed to explain exactly what is my current configuration/situation:

I am testing flarum beta 5 with all core extensions installed, plus a pair of extensions from the list I found here (Mediaembed, Image attachments upload)
I have ported all my forum content from SMF2, using the script I've developed (see https://discuss.flarum.org/d/3585-smf2-to-flarum-migration-script-alpha-3/22). The export/import works perfectly, thanks to vast usage of s9e\TextFormatter library for my content.
I have a total of ~256000 posts and 18.800 discussions, with ~ 1300 users.
I can with no problems whatsoever navigate through my content. Flarum is quick and responsive, even with discussions containing hundreds of posts.
The only real issue I have with flarum, which would be definitively a show stopper, is with the search funcionality.

Now that I run flarum on my production server (dedicated Virtual Server with Ubuntu 14.04, 4 GB of RAM, 60 GB of disk space on SSD) I repeated the same experiment:

I type "space shuttle" in the search box, and there starts the first ajax call which is supposed to offer some quick results;
I waited for at least 2 minutes, nothing happened.
Then i clicked on "Search all discussion for space shuttle", and this kicks out another ajax call.
Almost 12 minutes later, still mysqld is at 100% of cpu consumption, and the search still not finished, and finally the query times out, and I have error 500. Still, having "top" open in the background to monitor, the mysqld process was active and I had to kill and restart mysqld to ease the load on the processor.

Here is the screenshot of what I describe.

Don't take me wrong: I understand flarum is still a beta product and exactly for this I'd like to see this behaviour investigated, because I can tell that this is going to be a major showstopper for me and for many bigger forums waiting to migrate.

Has anyone in the dev team tried to run search on a meaningful DB? I mean, I don't think that even this very forum has the same amount of discussions/posts I am playing with.

I am absolutely sure it's something related to the search algorythm because when I perform a partial import (e.g aroung 10.000 posts) from SMF, I have no problems whatsoever even with the most complicated search.

IMHO the Laravel search engine is really trying to crunch too much data and the process fails with no proper error handling somehow, and it does it in two de facto concurrent processes, one started as soon as one types something in the search bar, a second started clicking on "search all discussions for...".

I'm more than happy to know whether there are DB optimization techniques I could try, and I will gladly to support the investigation.

I apologise to be so persistent, but I think that if you can reproduce and confirm this behavior you have a big problem to solve before releasing a stable.

Kulga

I have a total of ~256000 posts and 18.800 discussions, with ~ 1300 users.

This is hard to test!
I have a sandbox server (Devflarum) setup, but generating 256k posts, 18k discussions and 1.3k users is no easy task - unless there is and I'm not aware
That said, I figured out (finally) how to use a api_key to get around the spam limit ? and am loading it up with pseudo posts / discussions

Once that's complete I'll make it available again and see if similar issues exist.

marcozambi

Kulga Thanks for your support!

Later in the day I monitored real-time what mysql was doing internally (keeping an eye on the internal active processes of mysql) and i saw that for all the time the processor was at 100%, mysql was "returning data" to the flarum object which initiated the query. I suppose it's the Laravel DB component.

Kulga

marcozambi Kulga Hey, I just logged in as testuser5 and tried to use the search.
It crashes with error 500 on devflarum as well, but with a different error message than mine...

What are the steps required to replicate this? Been using the search but I don't see any errors?

marcozambi

Kulga I logged in on devflarum as testuser5, then i simply searched "test". Got 3 errors 500 as red bars on bottom left part of screen with error description. Nothing more complex than that.

wignu

marcozambi Kulga I logged in on devflarum as testuser5, then i simply searched "test". Got 3 errors 500 as red bars on bottom left part of screen with error description. Nothing more complex than that.

Just tried exactly the same, got a result within 1 second and no errors.

Kulga

Firefox 48.0
Either I'm missing steps or this might be on your side?
Curious to see what the debug error was for you

luceos

Kulga a (former) colleague of mine found out that relationships in Laravel are very, very expensive (aside from them simply being an additional query). So all the queries that use discussion - posts without joins are by default a cause for timing out, especially on a board that k's posts per discussion.

marcozambi

Hmmm grrrr I tried again now to play on devflarum server, and it works like a charm with me as well. I wasn't clever enough yesterday to take a screenshot to show you ?
It was complaining about queries in need to be recompiled... but I might remember wrongly. I' ll try again and give screenshots if it happens again.

Back to my test install I'll also try to collect as much information as possible on my test forum, where the vast majority of searches do not work.

These are the screenshot collected a short while ago. Again, I logged in and simply tried to search "space shuttle".
After about 10 minutes of "waiting", a series of error 500 are fired, one after the other.
Here are the screenshots:
In the last one you can see the beginning of the query stucking up the server (I have also a detailed error description of the query failing here: https://dl.dropboxusercontent.com/u/25636589/flarum_error_500.txt)

So in the end, I have multiple error 500, with different query somehow failing. The most indicative is the failure of the "big one", wich you can see in the text file linked above.
If somebody of the staff is interested, I can create a user for you and you can "play" and understand by yourselves

Kulga

jordanjay29 Here's a thread detailing some experiments on search with large quantities of posts: https://discuss.flarum.org/d/3758-flarum-search-functionality-error-500

I'd consider that a case study on that users specific setup. I wasn't able to replicate similar results.

marcozambi Hmmm grrrr I tried again now to play on devflarum server, and it works like a charm with me as well. I wasn't clever enough yesterday to take a screenshot to show you ?

This was tested on Devflarum when there were >30k posts