Flarum search functionality error 500

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

marcozambi

Another update.
I installed flarum to another test server, a phisical machine I own, having:

OS Ubuntu 16.04 64 bit
8 GB RAM
Apache: 2.4.18
PHP: 7.0.8-0ubuntu0.16.04.2
MySQL: 5.7.15-0ubuntu0.16.04.1
flarum: Beta-5 with bundled extensions

I imported here the whole content of my current SMF2 forum, flawlessly.
I run the usual test seraches, and in short, search is broken as it is, sorry.

Some details:

exactly as it happens on my virtual server, where I have done my testing so far, also in this new server I have most (not all) of the searches failing with error 500.
some searches are successful (e.g. "spacex" or "Nespoli", all single words)
some searches report no results found but i know I have many (hundreds) posts containing those words (e.g. "msl", just 3 letters, or "sts-135", this one has a dash)
most of the searches fail with error 500 (e.g. "space shuttle" - this ones have two words)
I perform all my tests using the search function from the "home page", to be sure to include all available posts.

As said before, if I work with smaller imports, substets of only 10k posts, everything is fine.
If I test a full import, ~18,000 discussion and ~260,000 posts, searching is useless.

I realise I repeate myself every second day, but this is because I am trying my best to nail down and understand what goes onnhere. At the moment I have not much success, because I know too little (nothing actually) about the inner working of flarum.

Unfortunately, as of today, this behavior is a total showstopper: I would not migrate to flarum like this.
I know the stable release is not happening tomorrow and there are other beta and testing coming, but i think this behaviour, now repeating itself in two environments both compliant with flarum minimum requirements, with just ubuntu vanilla provided packets (apache2, php mysql), is making me believe I found a potential major bug.
It's clear that with small dataset there is no issue at alll (or so it looks), but what happens if somebody ports big forums with millions of posts?

I think this is a potentally very bad situation that requires you guys @luceos, @Toby and others from the core dev team a serius investigation, setting up a big test database, a real case of the outside world environment where flarum will have to play and compete.

I love flarum and I would like to help. Can anybody give me a push in the right direction, pointing me to:

what I can read to understand the part of Laravel (Illuminate?) in charge of the database management?
which files in flarum core can I read to understand how you guys are setting up a search?
can you give some hints about which queries are performed by flarum every time a user starts a search?
is there a minimum number of letters below which no search is performed?
are symbols such as points, dashes, hyphens problematic for the search engine?

I hope somebody from the core team finds the time to answer these questions, then I can try to find out more myself without knocking on everybody's door for a few days ?

jordanjay29

I wonder if it'd be possible to pinpoint (approximately) the threshold where Flarum fails to search full text. If your 10k import, and Kulga's 50k devflarum test both work, then the threshold must be higher. Try 100k, 150k, 200k, see where the breaking point truly lies.

This will help further pinpoint where Flarum's (really, it's library dependency, but it's still Flarum's overall responsibility) failure lies.

If this isn't something you're interested in, would you mind opening an issue at Github on the flarum/core repository? Then we can get more discussion and further investigation so that the devs can look into a proper solution.

I agree, it's not a tenable state for large boards to ask them to go without a working internal search. I echo your hope that it can be resolved so that Flarum can be usable for large communities just as much as small ones.

marcozambi

jordanjay29 I wonder if it'd be possible to pinpoint (approximately) the threshold where Flarum fails to search full text. If your 10k import, and Kulga's 50k devflarum test both work, then the threshold must be higher. Try 100k, 150k, 200k, see where the breaking point truly lies.

Will certanly try.

jordanjay29 If this isn't something you're interested in, would you mind opening an issue at Github on the flarum/core repository? Then we can get more discussion and further investigation so that the devs can look into a proper solution.

Sure. I'll try to pinpoint exactly what happens, I'll try to include the queries which fail (taking them from mysql log) and create a single post summarising all my findings on github.

jordanjay29 I agree, it's not a tenable state for large boards to ask them to go without a working internal search. I echo your hope that it can be resolved so that Flarum can be usable for large communities just as much as small ones.

I just home I am missing something obvious and this problem is self created by me somehow. In case I will come here and publicly apologise (shame shame shame...) ?

marcozambi

jordanjay29 I wonder if it'd be possible to pinpoint (approximately) the threshold where Flarum fails to search full text. If your 10k import, and Kulga's 50k devflarum test both work, then the threshold must be higher. Try 100k, 150k, 200k, see where the breaking point truly lies.

Ok, the threshold is, for my server, between 60,000 and 100,000 posts.
At 60,000 query were still working, all of them (single words, two or more words separated with spaces), but at 100,000 all kind of searches fail to one or more error 500.
Of course I expect these results to vary according to the server capabilities, but I consider a dual core, 4GB RAM, 60 GB SSD server quite powerful, since it's running SMF2 like a charm...

In the next few hours I'll open an issue on github.

marcozambi

While collecting info to prepare the github entry, here is....

Case 1 - Searches of terms containing dashes do not work below certaing string lenghth

FBI

marcozambi

Unfortunately, as of today, this behavior is a total showstopper: I would not migrate to flarum like this.
I know the stable release is not happening tomorrow and there are other beta and testing coming, but i think this behaviour, now repeating itself in two environments both compliant with flarum minimum requirements, with just ubuntu vanilla provided packets (apache2, php mysql), is making me believe I found a potential major bug.
It's clear that with small dataset there is no issue at alll (or so it looks), but what happens if somebody ports big forums with millions of posts?

I have it, 2 million posts.
With "super" Centos Dedicated Derver (32GB, Xeon with SSD)
Have same issue like you.
And this weekend, I will finding like you did.
And simulating all possible search keywords.
Just wait a moment.. Yo do have problem like me.
But, I came from MyBB not SMF ?

Since this is Beta stage. I turned-off debugging OFF, at a moment.
I dont want to bother my visitor's error displayed.

HavocK

FBI I dont want to bother my visitor's error displayed.

You don't want to use it in production in ANY way ... Risky....

FBI

HavocK

No problem, I know that..
Since SMF Beta, PunBB Beta or MyBB Beta. I always follow the future. ?

dhkkim

I fixed by updating script called MySqlFulltextDriver.php file.
it's located under : vendor/flarum/core/src/Core/Search/Discussion/Fulltext

<?php

/*
 * This file is part of Flarum.
 *
 * (c) Toby Zerner <toby.zerner@gmail.com>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace Flarum\Core\Search\Discussion\Fulltext;

use Flarum\Core\Post;
use Flarum\Core\Discussion;

class MySqlFulltextDriver implements DriverInterface
{
    /**
     * {@inheritdoc}
    
    public function match($string)
    {
        $discussionIds = Post::where('type', 'comment')
            ->whereRaw('MATCH (`content`) AGAINST (? IN BOOLEAN MODE)', [$string])
            ->orderByRaw('MATCH (`content`) AGAINST (?) DESC', [$string])
            ->lists('discussion_id', 'id');

        $relevantPostIds = [];

        foreach ($discussionIds as $postId => $discussionId) {
            $relevantPostIds[$discussionId][] = $postId;
        }

        return $relevantPostIds;
    }
	*/
    public function match($string)
    {
        $discussionTitleIds = Discussion::where('title', 'like', "%$string%")
            ->lists('id');
        $discussionIds = Post::where('type', 'comment')
            ->where('content', 'like', "%$string%")
            ->lists('discussion_id', 'id');

        $relevantPostIds = [];
        foreach ($discussionTitleIds as $discussionId) {
            $relevantPostIds[$discussionId][] = 0;
        }

        foreach ($discussionIds as $postId => $discussionId) {
            $relevantPostIds[$discussionId][] = $postId;
        }

        return $relevantPostIds;
    }	
}

« Previous Page