Nextcloud slow, high server load caused by MySQL process

ℹ️ Support
1

Hello Nextcloud forums,

for a few days now, our Nextcloud server runs on a very high CPU load, most of the time more than 95% on each of the six cores; also the swap space is always fully taken. According to htop, that comes from the MySQL process, which takes up all of the resources. And it seems like it got worse gradually for a few days now. By today, even the web interface has become really slow.

What I found interesting is this output of show processlist; in MariaDB:

MariaDB [(none)]> show processlist;
+---------+------------------+-----------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| Id      | User             | Host      | db        | Command | Time | State        | Info                                                                                                 | Progress |
+---------+------------------+-----------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| 5533543 | oc_root          | localhost | nextcloud | Query   |  845 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5541855 | oc_root          | localhost | nextcloud | Query   |  477 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5545999 | oc_root          | localhost | nextcloud | Query   |  819 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5588786 | oc_root          | localhost | nextcloud | Query   |  325 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5590030 | oc_root          | localhost | nextcloud | Query   |  519 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5591869 | oc_root          | localhost | nextcloud | Query   |  726 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5595236 | oc_root          | localhost | nextcloud | Query   |  139 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5598040 | oc_root          | localhost | nextcloud | Query   |  322 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5599190 | oc_root          | localhost | nextcloud | Query   |  399 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5600335 | oc_root          | localhost | nextcloud | Query   |  629 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5600920 | oc_root          | localhost | nextcloud | Query   |  284 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5617378 | oc_root          | localhost | nextcloud | Query   |   19 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5618918 | oc_root          | localhost | nextcloud | Query   |   75 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5620627 | oc_root          | localhost | nextcloud | Query   |  763 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5622019 | oc_root          | localhost | nextcloud | Query   |  629 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5629989 | oc_root          | localhost | nextcloud | Query   |  546 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5632322 | oc_root          | localhost | nextcloud | Query   |  162 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5634767 | oc_root          | localhost | nextcloud | Query   |  816 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5637514 | oc_root          | localhost | nextcloud | Query   |  589 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5653279 | oc_root          | localhost | nextcloud | Query   |  850 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5656678 | oc_root          | localhost | nextcloud | Query   |  478 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5679457 | oc_root          | localhost | nextcloud | Query   |  629 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5695029 | oc_root          | localhost | nextcloud | Query   |  172 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5708272 | oc_root          | localhost | nextcloud | Query   |  585 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5710313 | oc_root          | localhost | nextcloud | Query   |  848 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5712299 | oc_root          | localhost | nextcloud | Query   |  475 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5714361 | oc_root          | localhost | nextcloud | Query   |  170 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5716562 | oc_root          | localhost | nextcloud | Query   |  314 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5718013 | oc_root          | localhost | nextcloud | Query   |  595 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5719218 | oc_root          | localhost | nextcloud | Query   |    5 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5720771 | oc_root          | localhost | nextcloud | Query   |  763 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5722252 | oc_root          | localhost | nextcloud | Query   |  726 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5723505 | oc_root          | localhost | nextcloud | Query   |  410 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5727958 | oc_root          | localhost | nextcloud | Query   |  121 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5745232 | oc_root          | localhost | nextcloud | Query   |  629 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5747516 | oc_root          | localhost | nextcloud | Query   |  465 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5755215 | oc_root          | localhost | nextcloud | Query   |  627 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5805639 | oc_root          | localhost | nextcloud | Query   |   72 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5816884 | oc_root          | localhost | nextcloud | Query   |  341 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5860822 | oc_root          | localhost | nextcloud | Query   |  445 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5902350 | oc_root          | localhost | nextcloud | Query   |  112 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5914670 | oc_root          | localhost | nextcloud | Query   |  627 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5933105 | oc_root          | localhost | nextcloud | Query   |  885 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 5948863 | oc_root          | localhost | nextcloud | Query   |  322 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 6122940 | oc_root          | localhost | nextcloud | Query   |  333 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |
| 6128702 | oc_root          | localhost | nextcloud | Query   |  172 | Sending data | SELECT `fileid`, `storage`, `path`, `parent`, `name`,
				`mimetype`, `storage_mtime`, `mimepart`, ` |    0.000 |

(output truncated, below this there were a few unsuspicious looking processes)

Here is the apache error log:

root@nc:~# tail -n 50 /var/log/apache2/error.log
[Thu Mar 07 06:25:22.971740 2019] [mpm_prefork:notice] [pid 17027] AH00163: Apache/2.4.25 (Debian) OpenSSL/1.0.2q configured -- resuming normal operations
[Thu Mar 07 06:25:22.971796 2019] [core:notice] [pid 17027] AH00094: Command line: '/usr/sbin/apache2'
[Thu Mar 07 08:06:34.267909 2019] [authz_core:error] [pid 3048] [client 80.147.196.215:54907] AH01630: client denied by server configuration: /var/www/nextcloud/config
[Thu Mar 07 09:26:38.663169 2019] [authz_core:error] [pid 6764] [client 192.168.96.236:54736] AH01630: client denied by server configuration: /var/www/nextcloud/config
[Thu Mar 07 09:27:22.481754 2019] [authz_core:error] [pid 6502] [client 192.168.96.155:51525] AH01630: client denied by server configuration: /var/www/nextcloud/config
[Thu Mar 07 09:42:28.806836 2019] [authz_core:error] [pid 7411] [client 80.147.196.215:49739] AH01630: client denied by server configuration: /var/www/nextcloud/config
[Thu Mar 07 09:59:25.802701 2019] [ssl:error] [pid 7411] AH02032: Hostname <redacted> provided via SNI and hostname <redacted> provided via HTTP have no compatible SSL setup
[Thu Mar 07 09:59:30.523761 2019] [authz_core:error] [pid 7906] [client 185.64.158.204:55442] AH01630: client denied by server configuration: /var/www/nextcloud/config
[Thu Mar 07 10:03:47.842428 2019] [authz_core:error] [pid 8066] [client 192.168.96.112:37806] AH01630: client denied by server configuration: /var/www/nextcloud/config
[Thu Mar 07 10:08:48.600313 2019] [authz_core:error] [pid 8253] [client 192.168.96.167:44312] AH01630: client denied by server configuration: /var/www/nextcloud/config
[Thu Mar 07 10:31:07.648225 2019] [authz_core:error] [pid 9228] [client 192.168.96.179:55857] AH01630: client denied by server configuration: /var/www/nextcloud/config
[Thu Mar 07 11:14:20.502554 2019] [authz_core:error] [pid 11546] [client 192.168.96.167:46868] AH01630: client denied by server configuration: /var/www/nextcloud/data/.ocdata
[Thu Mar 07 11:14:35.336506 2019] [authz_core:error] [pid 11681] [client 192.168.96.167:46888] AH01630: client denied by server configuration: /var/www/nextcloud/data/.ocdata

Any idea in which direction to check here?

Thank you very much - have a good thursday!

Nextcloud version (eg, 12.0.2): 15.0.4
Operating system and version (eg, Ubuntu 17.04): Debian Stretch
Apache or nginx version (eg, Apache 2.4.25): 2.4
PHP version (eg, 7.1): 7.1

2

looks like bans have fail2ban installed? how does htop looks like? look at /var/log/auth.log to and fail2ban.log syslog apache access log

3

No, fail2ban is not installed.

Here is a screenshot of htop, although it doesn’t show anything relevant that I haven’t said already (I think):

Screenshot_2019-03-07_11-59-37
Screenshot_2019-03-07_11-59-371095×351 61.3 KB

4

59

Change your authorization configuration:

<Directory /home/remix/>
    #...
    Order allow,deny
    Allow from all
</Directory>

…to the Apache 2.4 version of the same.

<Directory /home/remix/>
    #...
    Require all granted
</Directory>

check chown -R www-data:www:data /data and /webbfiles

u have a acceess deny error in mysql

systemctl status mariadb all ok? There is a mariadb command to show error log dont remember it. somthing like mariadb --error used it a few days but dont rember the command.

5

mysqldumpslow -a /var/lib/mysql/slowquery.log

6

look for banned ip whit iptables -L grep some ip whit apache error log

Are u under attack? Mem is almost full swap is full somthing is not right whit your msyql… Make a msyqldump of alll db’s to be safe.

root@linuxburken:/var/lib/mysql/lcloud# mysql -u root -p -se “SHOW VARIABLES” | grep -e log_error -e general_log -e slow_query_log
Enter password:
general_log OFF
general_log_file linuxburken.log
log_error /var/log/mysql/error.log
slow_query_log OFF
slow_query_log_file linuxburken-slow.log
root@linuxburken:/var/lib/mysql/lcloud# nano /var/log/mysql/error.log
root@linuxburken:/var/lib/mysql/lcloud#

add logging to your mysql looks like mine is off going to check this today.

7

Thanks for your help, but I don’t see what any of these have to do with my problem. There’s no point in changing the syntax of the apache config, because access works and always has. Access rights to the files are also ok, otherwise I couldn’t access the files in Nextcloud. There will also be no “banned” IPs because as I said I don’t use fail2ban.

8

Yes I understand you. Have been google your problem for 2 h and tested I dont get it there is authz_core:error client denied there is somthing that dont get access to the config file by apache its an apache log file I’ll usaly find fixes for this when searhing but not this time i get banns when searhing try get more logs from msyql maby.

https://mariadb.com/kb/en/library/server-monitoring-logs/

9

My colleague figured it out! If anyone comes across this problem: there were maaany instances of cron.php running, they presumably tried to clean the trash bin, but all of them were stuck on a specific file in the bin. Don’t know what was wrong with that file, it looked normal but the trash cleanup processes didn’t seem to like it. We stopped them all and deleted the file by hand, now everything is back to normal. Unfortunately we couldn’t do more debugging to prevent this bug in the future, but it was more important to get Nextcloud running again.

Solved - thanks!

2 Likes