'Out of sort memory' error when downloading data

[smathot]

@nclaidiere experienced the following error when trying to download data:

I haven't seen this myself with v0.6.0, but I also don't have tremendously large datasets yet (a few thousand rows). @nclaidiere Can you give us a rough estimate of the number of trials that you're trying to download here? And I'm guessing this doesn't happen with small datasets, right?

[dschreij]

I haven't seen this before myself either. It may be solved by just setting a parameter in the server settings, or the system that omm-server is currently run on simply has too little RAM and increasing this will solve the issue.

[nclaidiere]

I think it may be linked to the multiple connections problem... but we will need to run a bunch of studies to see if that comes up again. Let's wait and see!

[dschreij]

Alright. Keep me posted. And if it does occur again, please provide the steps which may help us reproduce the problem.

[dschreij]

Has this error popped up more frequently? If not, could it have been spurious or hardware dependent?

[nclaidiere]

We haven't had another long go at it so we don't know, we are waiting for the next release, I'll let you know if it appears again.

[nclaidiere]

Hi Guys!

So this error is back! Pascal was just prevented from pulling data for an experiment with a few hundred rows (so there is no problem with the RAM), from what I read it seems to appear when mysql is sorting json tables:

mozilla/addons#8068

Our investigations so far:
The query to reproduce it is simply SELECT * FROM scanners_results ORDER BY scanners_results.created DESC LIMIT 10;
The problem is that created is not indexed - we thought it was
That forces MySQL to do a filesort for this query. Because some rows are quite large (they contain raw json) MySQL needs a lot of space to do the sorting
We're hoping an index would fix it. We're going to try to reproduce locally.
It's still possible it was caused by the upgrade, but it could just be bad luck, just caused by one big row added to the table recently.

See also the full thread on:

https://stackoverflow.com/questions/29575835/error-1038-out-of-sort-memory-consider-increasing-sort-buffer-size

The problem with increasing the sorting is clearly that the issue will reappear with large experiments.

What are your thoughts?

Cheers,
Nico (& Pascal)

[smathot]

Thanks for this thorough investigation 👍

[dschreij]

Thanks for looking into this. I need to dive into this a bit further. I think we indeed sort by a timestamp such as created_at, so it might be possible to make this an index which could resolve this sorting problem.
It may also be problematic because an index has to have a unique value, and there is no such guarantee for a timestamp. For instance if two participants add a datapoint at exactly the same time, the database will give an error. Even though such a chance is quite slim, it should be taken into account.

[dschreij]

Also, if I work on this it is hard for me to reproduce this problem to determine my changes fixed it. @smathot have you been able to reproduce this issue?

[smathot]

@dschreij I haven't reproduced it myself, but I assume it's just a matter of seeding the database with enough data until the sort error occurs.

[nclaidiere]

I just asked Pascal if the error is still occurring (I know he restarts the experiment regularly), if it is I could get download the DB and send it to you (I might need some directions to do that...). What do you think?

[dschreij]

I seeded the database with 1000 participants, which all had completed a study with 15 jobs. This results in 15.000 rows of data in Excel, which my laptop had no trouble exporting.
I don't know how much data I have to seed to reproduce the problem, but if you already say the error occurred for you with around 250 records, I think we have to look at other causes or factors than mysql.

Can you tell me a bit more about the hardware on which you are running the system? For instance, how much GB of RAM (memory) does it have, or what is the age of the machine?

[dschreij]

Another difference is that Sebastiaan and I are running on Linux and MacOS, while you @nclaidiere is probably using Windows. Additionally, @smathot are you using a locally installed MySQL or the Docker container? I am using the former atm, so it may be worth trying the same with a Docker container again.