Loading Event Log page throws ER_OUT_OF_SORTMEMORY
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?
@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:
/etc/mysql/mysql.cnfon the ubuntu system and then
systemctl restart mysql
The default seems to be
1048576, so this would double it.
innodb_sort_buffer_sizeis 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).
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.
@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?
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.