First 50 results taking too long for one million and a half records

keniobats · May 14, 2021, 4:42am

I have…

[ X ] Read the following guideline: https://docs.squidex.io/01-getting-started/installation/troubleshooting-and-support. I understand that my support request might get deleted if I do not follow the guideline.

I’m submitting a…

[ ] Regression (a behavior that stopped working in a new release)
[ ] Bug report
[ X ] Performance issue
[ ] Documentation issue or request

Current behavior

Squidex fresh install. I’ve a one million and a half records on a schema. When I click on it on the content UI, it takes about 4 to 6 minutes to appear but if I search ONE word(even if its still loading the first 50) it gives the results immediately.
If I search for more than ONE word, it takes like 20 seconds or more to load and it gives innacurate results with the default full text search.

Expected behavior

To return accurate results in less time.

Minimal reproduction of the problem

Environment

[ X ] Self hosted with docker (macbook air 2017, intel i7, 8GB RAM)
[ ] Self hosted with IIS
[ ] Self hosted with other version
[ ] Cloud version

Version: [VERSION]

Browser:

[ X] Chrome (desktop)
[ ] Chrome (Android)
[ ] Chrome (iOS)
[ ] Firefox
[ ] Safari (desktop)
[ ] Safari (iOS)
[ ] IE
[ ] Edge

Others:

Sebastian · May 14, 2021, 5:42am

We are investigating 2 issues here:

1. Why is the normal query so slow?

2. The quality of the full text search.

2.1. Why does it take so long for 2 words?
2.2. Why are the results for 2 words bad?

We should skip 2.2. for now and focus on the performance issues first.

Here are a few things you can do:

Check the MongoDB indexes and ensure that your MongoDB instance has enough RAM to keep all instances in Memory.
Turn on profiler logs: https://github.com/Squidex/squidex/blob/master/backend/src/Squidex/appsettings.json#L335
Turn on mongodb profiler:
https://docs.mongodb.com/manual/tutorial/manage-the-database-profiler/
Test elastic for full text:
https://github.com/Squidex/squidex/blob/master/backend/src/Squidex/appsettings.json#L46

keniobats · May 14, 2021, 1:09pm

Hi Sebastian! Thanks for your time and support. I really love your work!!!

I did what you say, turned on the profiler logs and mongodb profiler, then stop/start the container but now I have a new issue: localhost throws a 502 error and the squidex/mongodb logs shows something like a background running process trying to populate the db.
As a side note, I imported a CSV with python and POST’ed successfully through the REST API.

Now that the container services are starting, it seems like it’s trying to “POST” some remaining data(or all data) from somewhere(the python script finished his work and it’s not running) but I don’t know why.

I’ll PM the logs due that it has some sensitive information on it.