Solved Loading Event Log page throws ER_OUT_OF_SORTMEMORY

[d19dotca]

I noticed when viewing the Event Log page in Cloudron that I get an odd error I haven’t seen reported yet… ‘’’ER_OUT_OF_SORTMEMORY’’’

I don’t see any OOM errors yet in any logs, and I have the same memory values set for quite a long time now without seeing this issue before.

As best I can tell from a quick search online, the error seems to be MariaDB related, but there’s nothing in the MySQL service logs around that time other than healthcheck requests. I found this page that references increasing the sort buffer size… https://mariadb.com/docs/reference/mdb/error-codes/ER_OUT_OF_SORTMEMORY/

Memory itself seems fine from what I can tell on the server, with no OOM log entries that I can see. Plenty of memory allocated too to each service.

Any thoughts on what to set for the sort buffer size? And does it seem necessary to do for new installs too? Is it something wrong in my setup instead?

[nebulon]

@d19dotca this is related to the mysql instance on the ubuntu system itself I assume, since this is where the eventlog resides. The mysql service addon is only for apps and not for the system itself.

Looks like your eventlog is quite huge then!

If you want you can try to increase that innodb sort buffer size by adding for example:

innodb_sort_buffer_size=2097152

to the mysqld section in /etc/mysql/mysql.cnf on the ubuntu system and then systemctl restart mysql

The default seems to be 1048576, so this would double it.

[girish]

I think innodb_sort_buffer_size is to control the buffer size when index is created i.e using ALTER command - https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size . What we probably need here is sort_buffer_size. Like:

sort_buffer_size = 4M

Looks like we are missing an index for the creationTime field (since we use ORDER BY to list the eventlog).

[girish]

I have added the indices in the next release.

[d19dotca]

Funny enough, I went to look at the Event Log this morning and it doesn't throw the same error anymore and seems to load correctly. Not sure what the cause was there, but seems like it's okay at least for now. For context, I moved from OVH to a new Vultr instance, not sure if the Vultr instance has different MySQL settings in their Ubuntu image though I thought Cloudron overwrote the /etc/my.cnf file anyways, so not sure if that's relevant.

[d19dotca]

@girish I was just looking at the Git PR for this... https://git.cloudron.io/cloudron/box/-/commit/2421536c235c8025b1bbdfe8671a9ea016834042#1ee7de92349500e2dcdc985a7cd620f916ac8170

I noticed it seems the file being modified is mysql.cnf rather than my.cnf... if I were to manually add these changes for the time being, which file should I be editing exactly? Why is mysql.cnf seemingly a duplicate of my.cnf? This is throwing me off a bit, lol.

---

EDIT: Just realized it seems the my.cnf file is just a symlink, so I gues this makes more sense now. haha. Basically the real settings are coming from /etc/mysql/myswql.cnf, right?

[girish]

@d19dotca said in Loading Event Log page throws ER_OUT_OF_SORTMEMORY:

Basically the real settings are coming from /etc/mysql/mysql.cnf, right?

Yes, they are the same file as you found out. In any case, the change I made is good because ORDER BY <xx> should always have xx indexed in the database to lower memory use.