MYSQL high cpu usage

dg1m · March 12, 2023, 9:25am

So when uploading 100+ MB files or just thousands of smaller files into my Nextcloud v 25.0.4 I noticed that my CPU usage is at 100%. My TrueNAS-13.0-U3.1 hardware is 2x Xeon E5-2690v3, 256GB of RAM. When I run top in the jail I clearly see the mysql process at 100%.

last pid: 50629; load averages: 33.66, 33.80, 33.62 up 91+02:01:06 01:03:30
151 processes: 2 running, 149 sleeping
CPU: 98.8% user, 0.0% nice, 1.1% system, 0.1% interrupt, 0.1% idle
Mem: 5531M Active, 34G Inact, 3332M Laundry, 196G Wired, 11G Free
ARC: 179G Total, 99G MFU, 75G MRU, 6942K Anon, 542M Header, 5000M Other
167G Compressed, 171G Uncompressed, 1.03:1 Ratio
Swap: 10G Total, 1744M Used, 8496M Free, 17% Inuse

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
34334 mysql 124 20 0 4459M 950M select 25 108.7H 3041.94% mysqld
49535 www 1 50 0 1267M 182M accept 13 0:35 55.69% php-fpm
48845 www 1 52 0 1382M 270M accept 13 1:15 33.92% php-fpm
49533 www 1 38 0 1267M 182M CPU0 0 0:28 5.55% php-fpm
49495 www 1 20 0 1233M 184M select 17 0:25 0.85% php-fpm
33049 root 7 20 0 65M 35M select 15 1:19 0.29% python3.9

I’ve looked at the server tuning page and searched Google, but an issue I’m running into is that the file structure and even file names are somewhat different in a freebsd jail 13.1-RELEASE-p5. I’m also finding that the other high cpu forum posts are from multiple versions and multiple years ago.

What information do you need from me to further look into my settings?

Edson_Barbosa · March 12, 2023, 3:47pm

I have the same problem

jcvb · March 12, 2023, 5:24pm

I noticed the same behavior on my instance.
It started when I uploaded about 2Gigs through the windows app, and now my CPU usage is constantly at 100%, (from mysql) . Now I am unable to upload any more Data, a 600mb file uploaded via the website processes about 10mb and then stalls.

When i run SHOW PROCESSLIST from mysql cli, it shows 15 queries from my nextcloud database user.

mysql> SHOW PROCESSLIST;
+-----+-----------------+-----------+-----------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
| Id  | User            | Host      | db        | Command | Time | State                  | Info
                                                                             |
+-----+-----------------+-----------+-----------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
|   5 | event_scheduler | localhost | NULL      | Daemon  | 5057 | Waiting on empty queue | NULL
                                                                             |
|  65 | nextclouduser   | localhost | nextcloud | Query   |    1 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
|  66 | nextclouduser   | localhost | nextcloud | Query   |    1 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
|  68 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 143 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 144 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 146 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 222 | nextclouduser   | localhost | nextcloud | Query   |    1 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 223 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 225 | nextclouduser   | localhost | nextcloud | Query   |    1 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 286 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 287 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 290 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 351 | nextclouduser   | localhost | nextcloud | Query   |    1 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 353 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 355 | nextclouduser   | localhost | nextcloud | Query   |    0 | executing              | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name |
| 814 | root            | localhost | NULL      | Query   |    0 | init                   | SHOW PROCESSLIST                                                                                     |
+-----+-----------------+-----------+-----------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)

I am running Ubuntu 22.04 and NC 25.0.3.
Restarting the mysql, apache or the whole system did not resolve the issue.

dg1m · March 12, 2023, 5:30pm

The my.conf file is default, not sure if I need to do any tweaking for larger deployments of Nextcloud or not.

root@Nextcloud:/usr/local/etc/mysql # cat my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock

[mysql]
prompt = \u@\h [\d]>_
no_auto_rehash

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
bind-address = 127.0.0.1
basedir = /usr/local
datadir = /var/db/mysql
tmpdir = /var/db/mysql_tmpdir
replica-load-tmpdir = /var/db/mysql_tmpdir
secure-file-priv = /var/db/mysql_secure
log-bin = mysql-bin
log-output = TABLE
relay-log-recovery = 1
slow-query-log = 1
server-id = 1
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 16M
binlog_expire_logs_seconds = 2592000
default_password_lifetime = 0
enforce-gtid-consistency = 1
gtid-mode = ON
safe-user-create = 1
lower_case_table_names = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options = BACKUP,FORCE
open_files_limit = 32768
table_open_cache = 16384
table_definition_cache = 8192
net_retry_count = 16384
key_buffer_size = 256M
max_allowed_packet = 64M
long_query_time = 0.5
innodb_buffer_pool_size = 1G
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_redo_log_capacity = 512M
innodb_log_buffer_size = 16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_autoinc_lock_mode = 2

[mysqldump]
max_allowed_packet = 256M
quote_names
quick

jcvb · March 12, 2023, 7:10pm

Is there anything related to the database in your nextcloud logs? (@dg1m @Edson_Barbosa )

dg1m · March 12, 2023, 8:12pm

How do you log into the mysql shell (?) to run queries against the database or whatever.?

dg1m · March 12, 2023, 8:23pm

I pulled the logs from /var/log/nextcloud and nothing is jumping out. my daughter’s phone lost its token or whatever, so there were tons of logs for that.

jcvb · March 12, 2023, 10:17pm

Idk about TrueNAS, but on Ubuntu I can just log into the mysql root user with mysql -u root -p then enter the password of mysql root user. Then run SHOW PROCESSLIST;
It shows every running query from all users.

dg1m · March 12, 2023, 11:34pm

The Nextcloud plugin auto generates all the usernames and passwords and creates files for them in /root. I’ve tried every combination to no avail. When I run mysql -u ncadmin -p PASS then it asks for another password when I hit enter. So I’m not sure.

ERROR 1045 (28000): Access denied for user ‘oc_ncadmin’@‘localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘ncadmin’@‘localhost’ (using password: YES)

dg1m · March 13, 2023, 12:03am

Anyone using Recognize? I decided to disable extra apps and now I’ve been uploading strong for 20 minutes and CPU usage is looking good. I’ll continue to monitor.

jcvb · March 13, 2023, 12:52am

Yes, I’m using recognize as well. I will disable is too and report back. If that is the problem, it might be a good idea to create a bug report on their github page.

dg1m · March 13, 2023, 2:17am

I’m still good after disabling the Recognize app. I think I’m going to mark this as solved.

marcelklehr · March 13, 2023, 12:50pm

Could you please post the whole query that is stuck please so I can fix this?

jcvb · March 13, 2023, 1:46pm

I’m sorry, since i disabled recognize i was not able ro re-enable it. I now get the following error in my Logs:

Failed to install Tensorflow.js for GPU: GPU-linux-4.2.0.tar.gz * Downloading libtensorflow https://storage.googleapis.com/tensorflow/libtensorflow/libtensorflow-gpu-linux-x86_64-2.9.1.tar.gz * Building TensorFlow Node.js bindings node-pre-gyp install failed with error: Error: Command failed: node-pre-gyp install --fallback-to-build sh: 0: getcwd() failed: No such file or directory internal/fs/utils.js:332 throw err; ^ Error: ENOENT: no such file or directory, open './package.json' at Object.openSync (fs.js:497:3) at Object.readFileSync (fs.js:393:35) at Run.parseOpts [as parseArgv] (/var/www/nextcloud/apps/recognize/node_modules/@mapbox/node-pre-gyp/lib/node-pre-gyp.js:181:37) at new Run (/var/www/nextcloud/apps/recognize/node_modules/@mapbox/node-pre-gyp/lib/node-pre-gyp.js:90:8) at Object.<anonymous> (/var/www/nextcloud/apps/recognize/node_modules/@mapbox/node-pre-gyp/lib/main.js:16:14) at Module._compile (internal/modules/cjs/loader.js:1085:14) at Object.Module._extensions..js (internal/modules/cjs/loader.js:1114:10) at Module.load (internal/modules/cjs/loader.js:950:32) at Function.Module._load (internal/modules/cjs/loader.js:790:12) at Module.require (internal/modules/cjs/loader.js:974:19) { errno: -2, syscall: 'open', code: 'ENOENT', path: './package.json' }

I have GPU mode disabled, as I only have the iGPU of my i5 7500

marcelklehr · March 13, 2023, 1:48pm

occ app:remove recognize and reinstall might work

istler · March 13, 2023, 3:10pm

Hello,
I believe I have the same problem with recognize and a have DB load.
I have104 SQL queries running with this statement since yesterday:

SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `unencrypted_size`, `metadata_etag`, `creation_time`, `upload_time` FROM `oc_filecache` `filecache` LEFT JOIN `oc_filecache_extended` `fe` ON `filecache`.`fileid` = `fe`.`fileid` WHERE (`name` IN ('.noimage', '.nomedia')) AND (`storage` = 65)

I have also stopped recognize.

marcelklehr · March 13, 2023, 3:11pm

Thanks! I’ve added some caching for this now and will publish a new release shortly

jcvb · March 13, 2023, 3:40pm

unfortunately that did not help

istler · March 13, 2023, 3:42pm

Hello Macel,

many thanks for the fix.
I used recognize since some month and had no trouble before - great app! Since one of the last update (to 3.6.2??) I get the trouble with the database. I can’t remember exactly because I need to find out the relation of the db trouble with recognize.

Best regards
Maik

marcelklehr · March 14, 2023, 12:26pm

Release v3.6.3 · nextcloud/recognize · GitHub is out now – please let me know if it works now