[Solved] Very high cpu load on mysql after upgrade to nextcloud 10

ℹ️ Support
1

Hello everybody, i have upgraded my nextcloud to 10 and now it works veeeeeeeeeery slow! It seems that problem is overloaded mysql service. Here is first string from top:

PID  USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND  
2273 mysql     20   0 1568996 315672   8240 S 126,7 15,4 397:45.81 mysqld

I have fully updated ubuntu server 16.04.1, hardware of server is double core Intel® Pentium® D CPU 2.80GHz, 2 gb of ram, and softraid made with mdadm. I know that hardware isn’t so good, but before upgrade nextcloud worked very fast

I also have cache enabled:
‘memcache.local’ => ‘\OC\Memcache\APCu’,
‘memcache.distributed’ => ‘\OC\Memcache\Redis’,

2

Is there still a command from the update running?
If it’s also during normal Nextcloud usage, use the Slow Query Log to identify problematic queries.

With not too much RAM, you might want to optimize the mysql cache settings (look for the tuning-primer-script). If the server starts swapping, it will slow down everything.

3

I have updated everything yesterday through php occ upgrade, so upgrade script it not running.
Slow query log is almost empty :frowning:
tuning-primer script only told that

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 16 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = .10 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
4

No tips regarding InnoDB? There are a few settings innodb_* and the default is perhaps not the best value. If you have phpmyadmin, it can show to a root user all active queries (command line probably also available, use search engine).

5

Thanks for clue! I have looked in phpmyadmin advisor and there i saw advise that i must add

query_cache_type = on

in my config, I have added and everything become dramatically faster!

2 Likes
6

What file and where is the file to put that???

7

This is config of mysql, and file /etc/mysql/my.cnf

8

thanks for your solution, i get the same probleme with mysql
and i have enbled it, now i can acces on my website but still get 100% usage but your solution had an effect
thanks bro