User event list causes the MySQL database to run out of memory

[mattes3]

With the new version 1.5.7, when I click on the events of a particular user, Parcelvoy does not display them. In the log, I found this error message:

api_1           |   Error: select `user_events`.* from `user_events` where `project_id` = 2 and `user_id` = 1060 order by `id` desc, `id` desc limit 26 - Out of sort memory, consider increasing server sort buffer size
api_1           |       at createQueryBuilder (/usr/src/app/node_modules/knex/lib/knex-builder/make-knex.js:320:26)
api_1           |       at knex (/usr/src/app/node_modules/knex/lib/knex-builder/make-knex.js:101:12)
api_1           |       at UserEvent.table (/usr/src/app/build/core/Model.js:245:16)
api_1           |       at UserEvent.build (/usr/src/app/build/core/Model.js:249:36)
api_1           |       at UserEvent.search (/usr/src/app/build/core/Model.js:114:33)
api_1           |       at getUserEvents (/usr/src/app/build/users/UserEventRepository.js:32:40)
api_1           |       at /usr/src/app/build/users/UserController.js:167:62
api_1           |       at dispatch (/usr/src/app/node_modules/koa-compose/index.js:42:32)
api_1           |       at /usr/src/app/build/users/UserController.js:144:18
api_1           |       at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
api_1           |       at async serve (/usr/src/app/node_modules/koa-static/index.js:59:5)
api_1           |       at async cors (/usr/src/app/node_modules/@koa/cors/index.js:109:16)

[mattes3]

The table contains only 22,140 events. I tried to run the select statement inside the mysql CLI, and it works fine when I omit the data column. When I select the data column, it crashes with out of memory again.

The particular user has only 18 events so far.

[pushchris]

That's a super odd one since it's only trying to sort based on the ID column which is indexed and shouldn't cause any problems. Any chance you could provide the following:

• What version of MySQL are you running?
• Is your docker instance out of memory or out of storage?
• Can you try dropping the second sort order and seeing if it makes any difference in executing the query?
• Does removing the order completely help with executing the query?

Thanks!

[mattes3]

Docker runs on an 8GB Ubuntu machine that still has 3.6GB of free memory. A quick docker stats shows this:

NAME                                   CPU %     MEM USAGE / LIMIT     MEM %
parcelvoy_parcelvoy-ui_1               0.00%     3.457MiB / 7.574GiB   0.04%
parcelvoy_worker_1                     0.02%     89.43MiB / 7.574GiB   1.15%
parcelvoy_api_1                        0.03%     88.67MiB / 7.574GiB   1.14%
parcelvoy_redis_1                      0.42%     8.074MiB / 7.574GiB   0.10%
parcelvoy_mysql_1                      0.60%     426.3MiB / 7.574GiB   5.50%

Pretty relaxed situation.

I am running MySQL 8.0.27

I dropped the second sort order, and it did not help. I removed the order completely, and it worked.

The result of the select looks weird, especially for the email_sent events. The data column of the table contains a huge piece of JSON with long lists of integers.

[pushchris]

@mattes3 try bumping the version of MySQL up to 8.0.28 or higher. The release notes state the exact bug you are running into as addressed https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html so I'm hoping that will do the trick!

[mattes3]

The upgrade to 8.0.36 made the problem go away, thanks a lot for digging into this, Chris!

[pushchris]

No problem! Apparently all of the ones I had locally for testing had already been upgraded by happenstance and didn't notice it off jump. The latest release also stores less data for SES sends (removes the serialized raw buffer that it returns in the result) so that should help with storage sizes as well.