MySQL Performance

[djx]

NethServer Version: 7.7.1908
Module: mysql

I’m having issues with moving to Nextcloud to Nethserver.

First - when doing a sync over WebDAV throws many errors about files being locked:

Couldn’t delete: “filename” is locked: OCA\DAV\Connector\Sabre\Exception\FileLocked: 423 Locked

Next - when I try to do a manual SQL update of data, MySQL seems to choke on very small queries. Trying to join two tables with 100,000 entries and 8 columns does not finish after 10 minutes.

My server has 2 cores and 4 GB of ram, the CPU goes to 100% usage during the query and RAM is almost unused.

I can’t see where in the config files the memory is set, or any documentation on the wiki about how to adjust the memory for MySQL in Nethserver. Any help would be appreciated :).

[giacomo (Giacomo Sanchietti)]

@djx

First - when doing a sync over WebDAV throws many errors about files being locked:

I can’t help on this, I’ve never seen such problem before. Did you take a look to Nextcloud forum?

@djx

My server has 2 cores and 4 GB of ram, the CPU goes to 100% usage during the query and RAM is almost unused.

This quite strange, we have mysql on production machines with much less CPU and memory, and they work quite good on heavy load.
Try also to check the disk speed.

@djx

I can’t see where in the config files the memory is set, or any documentation on the wiki about how to adjust the memory for MySQL in Nethserver. Any help would be appreciated :).

You can just drop in a config file like: /etc/my.cnf.d/optimization.cnf.
For example, this is the content of my optimization conf:

[mysqld]

innodb_buffer_pool_size = 2GB
query_cache_limit = 0
query_cache_size = 0
query_cache_type = 0

slow_query_log = 1
long_query_time = 3

Then, restart mysqld: systemct restart mysqld

[djx]

@giacomo

I can’t help on this, I’ve never seen such problem before. Did you take a look to Nextcloud forum?

Yes - they mark this as a known issue and suggest to use Redis for this type of caching. I noticed that Redis is already installed in NethServer so maybe it’s possible to use it as an out-of-the-box. I started to poke around at this but, I still haven’t successfully migrated my Nextcloud to NethServer so I can’t focus on it yet.

@giacomo

and they work quite good on heavy load.

I found that it was an issue with my join. I’m used to being pampered by other RDBMses that can handle a join on a long string column without me needing to manually build indices . In the end, this didn’t prove to be a workable solution for getting my Nextcloud metadata corrected.

@giacomo

You can just drop in a config file like

Thank you very much for this example. I’m still not very comfortable with the templating system so I don’t always know where to edit and how. I didn’t see an samples or documentation elsewhere on how to customize MySQL on NethServer. I’m sure this example will help me, and others, in the future.