MariaDB High CPU Usage

Server
1

I recently set up a new RaspberryPi4b with DietPi and have it set up to back up a couple of Windows computers with rsync. For added utility, I installed ownCloud to be able to access the backed up files remotely, essentially as a read-only option. The other day, I saw the CPU temp was high and found that the mariadbd process was showing 400% cpu usage. Upon rebooting or restarting the process, it initially remains idle, but several hours later shows 100% usage. If I allow additional time to pass, it starts using all four cores.

The owncloud configuration is almost completely stock as installed by dietpi-software. I’ll attach the config report, but below are the few install choices and post-install configuration changes I made myself:

  • Lighttpd for the web server
  • Added {files_external_allow_create_new_local’ => ‘true’,} to /var/www/owncloud/config/config.php and added my usb drive as a storage location in the ownCloud web interface.
  • used the dietpi-letsencrypt utility to set up SSL stuff, along with a DDNS address, port forwarding in my router, and including the domain in the trusted list in config.php

I’m the only user, and the CPU usage rises to 100% and beyond without any access or activity on my part.

I originally asked for help on the DietPi forum (this system won’t let me include a link, but the topic # is 14916), and it was suggested that perhaps it is scanning/indexing the external hard drive. However, the drive is definitely idle during this time, and I have let it run for approximately five days (with cpulimit to keep it at 100%), but it doesn’t cease.

mysql -e “SHOW PROCESSLIST”; gives the following:
±—±---------±----------±---------±--------±-----±-------------±-----------------------------------------------------------------------------------------------------±---------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
±—±---------±----------±---------±--------±-----±-------------±-----------------------------------------------------------------------------------------------------±---------+
| 66 | oc_admin | localhost | owncloud | Query | 439 | Sending data | select fileid, name, user_id from oc_filecache fc join oc_mounts on storage = `storage | 0.000 |
| 68 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | 0.000 |
±—±---------±----------±---------±--------±-----±-------------±-----------------------------------------------------------------------------------------------------±---------+

I also notice two /usr/sbin/CRON -f processes running that reference php /var/www/owncloud/occ system:cron. These appear later as well and may coincide with the higher cpu usage.

I know next to nothing about mySQL databases, so please let me know what additional info I could provide or if there are any troubleshooting steps I can take.

Thanks in advance,
~Mike

htop
htop1141×754 95.6 KB

The system will not let me upload an attachment since I am a new user. So here is the top portion of the config report:
{
“basic”: {
“license key”: “REMOVED SENSITIVE VALUE”,
“date”: “Sat, 19 Nov 2022 16:23:05 +0000”,
“ownCloud version”: “10.11.0.6”,
“ownCloud version string”: “10.11.0”,
“ownCloud edition”: “Community”,
“server OS”: “Linux”,
“server OS version”: “Linux DietPi 5.15.76-v7l+ #1597 SMP Fri Nov 4 12:14:58 GMT 2022 armv7l”,
“server SAPI”: “fpm-fcgi”,
“webserver version”: “lighttpd/1.4.59”,
“hostname”: “REMOVED SENSITIVE VALUE***”,
“logged-in user”: “admin”
},
“stats”: {
“users”: {
“Database”: {
“total_count”: 1,
“guest_count”: 0,
“seen”: 1,
“logged in (30 days)”: 1
}
},
“groups”: {
“OC\Group\Database”: 1
}
},
“config”: {
“files_external_allow_create_new_local”: “true”,
“passwordsalt”: “REMOVED SENSITIVE VALUE”,
“secret”: “REMOVED SENSITIVE VALUE”,
“trusted_domains”: [
“localhost”,
“192.168.1.94”,
“DietPi”,
“REMOVED SENSITIVE VALUE***”
],
“datadirectory”: “/mnt/dietpi_userdata/owncloud_data”,
“overwrite.cli.url”:
“dbtype”: “mysql”,
“version”: “10.11.0.6”,
“memcache.local”: “\OC\Memcache\APCu”,
“filelocking.enabled”: true,
“memcache.locking”: “\OC\Memcache\Redis”,
“redis”: {
“host”: “/run/redis/redis-server.sock”,
“port”: 0
},
“dbname”: “owncloud”,
“dbhost”: “localhost”,
“dbtableprefix”: “oc_”,
“mysql.utf8mb4”: true,
“dbuser”: “REMOVED SENSITIVE VALUE”,
“dbpassword”: “REMOVED SENSITIVE VALUE”,
“allow_user_to_change_mail_address”: “”,
“logtimezone”: “UTC”,
“apps_paths”: [
{
“path”: “/var/www/owncloud/apps”,
“url”: “/apps”,
“writable”: false
},
{
“path”: “/var/www/owncloud/apps-external”,
“url”: “/apps-external”,
“writable”: true
}
],
“installed”: true,
“instanceid”: “ocwwyo6a60rt”
},
“integritychecker”: {
“passing”: true,
“enabled”: true,
“result”: []
},
“core”: {
“backgroundjobs_mode”: “cron”,
“enable_external_storage”: “yes”,
“first_install_version”: “10.11.0.6”,
“installedat”: “1667618631.6358”,
“lastcron”: “1668874502”,
“lastupdateResult”: “[]”,
“lastupdatedat”: “1668874896”,
“public_files”: “files_sharing/public.php”,
“public_webdav”: “dav/appinfo/v1/publicwebdav.php”
},
“apps”: [
{
“id”: “files_external”,
“name”: “External storage support”,
“description”: “This application enables administrators to configure connections to external storage providers. Administrators can choose which types of storage to enable and can mount these storage locations for a user, a group, or the entire system. Users will see a new folder appear in their root ownCloud directory, which they can access and use like any other ownCloud folder. External Storage also allows users to share files stored in these external locations. In these cases, the credentials for the owner of the file are used when the recipient requests the file from external storage, thereby ensuring that the recipient can access the shared file.\n\nExternal Storage can be configured using the GUI or at the command line. This second option provides the advanced user with more flexibility for configuring bulk external storage mounts and setting mount priorities. More information is available in the External Storage GUI documentation and the External Storage Configuration File documentation.”,
“licence”: “AGPL”,
“author”: “Robin Appelman, Michael Gapczynski, Vincent Petry”,
“documentation”: {
“admin”:
},
“rememberlogin”: “false”,
“version”: “0.9.0”,
“types”: [
“filesystem”
],
“ocsid”: “166048”,
“use-migrations”: “true”,
“namespace”: “Files_External”,
“default_enable”: “”,
“dependencies”: {
“owncloud”: {
“@attributes”: {
“min-version”: “10”,
“max-version”: “10”
}
}
},

2

Following up in case anyone else with a similar issues sees this thread. I got some help from the fantastic folks on the DietPi forum. The issue was resolved by adding an index for the path column of the oc_filecache table in the owncloud database


ALTER TABLE oc_filecache ADD INDEX path_index (path)

This can be implemented from the linux command line with:

mysql -e “ALTER TABLE owncloud.oc_filecache ADD INDEX path_index (path);”

systemctl restart mariadb.service

Or by entering the mysql command interface:

mysql

MariaDB [(none)]> USE owncloud;
Database changed

MariaDB [owncloud]> ALTER TABLE oc_filecache ADD INDEX path_index (path);
Query OK, 0 rows affected, 1 warning (11.897 sec)
Records: 0 Duplicates: 0 Warnings: 1

(use SHOW INDEXES FROM to see the result)
MariaDB [owncloud]> SHOW INDEXES FROM oc_filecache;

MariaDB [owncloud]> exit
Bye

systemctl restart mariadb.service

1 Like
3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.