Login creates high db load

🍱 Features & apps
1

My CPU load is also very high but only if a user is logging in. According to HTOP on my terminal it is MYSQL creating that problem. I tried it with three different users nearly at the same time and the load of my CPU comes up to 100%. It takes about 2-3 minutes then all comes back to normal. REDIS, MEMCACHED is configured and working. There are 7 users on my NC and I am the only one having some files and music stored.

I am using NC 13.0.2
Ubuntu 16.04 (latest updates)
redis-server 2.2.8
mariadb 10.1.32
nginx 1.10.13
php7.0.28
memcached 1.4.25

Linux Kernel 4.13.0-39
Memory 8GB
4 Cores

My config file:

<?php
$CONFIG = array (
  'instanceid' => 'oc2bcxz77sgy',
  'passwordsalt' => '123567890',
  'secret' => '1234567890',
  'trusted_domains' => 
  array (
    0 => 'aaaaa',
    1 => 'aaaaa',
    2 => 'aaaaa',
  ),
  'datadirectory' => '/media/cloud/nextcloud/data',
  'overwrite.cli.url' => 'https:aaaaa',
  'dbtype' => 'mysql',
  'version' => '13.0.2.1',
  'dbname' => 'nextcloud',
  'dbhost' => '127.0.0.1',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'nextcloud',
  'dbpassword' => 'aaaaa',
  'logtimezone' => 'Europe/Berlin',
  'installed' => true,
  'forcessl' => true,
  'default_language' => 'de',
  'defaultapp' => 'files',
  'knowledgebaseenabled' => true,
  'enable_avatars' => true,
  'allow_user_to_change_display_name' => true,
  'remember_login_cookie_lifetime' => 1296000,
  'session_lifetime' => 86400,
  'session_keepalive' => true,
  'mail_smtpmode' => 'smtp',
  'mail_smtphost' => 'aaaaa',
  'mail_smtpport' => 'aaaaa',
  'mail_smtpauthtype' => 'aaaaa',
  'mail_from_address' => 'aaaaa',
  'has_internet_connection' => true,
  'appstoreenabled' => true,
  'appstoreurl' => 'https://apps.nextcloud.com/api/v0',
  'apps_paths' => 
  array (
    0 => 
    array (
      'path' => '/var/www/nextcloud/apps',
      'url' => '/apps',
      'writable' => true,
    ),
  ),
  'appcodechecker' => true,
  'appstore.experimental.enabled' => true,
  'updatechecker' => true,
  'check_for_working_webdav' => true,
  'check_for_working_wellknown_setup' => true,
  'loglevel' => 2,
  'theme' => '',
  'maintenance' => false,
  'overwritewebroot' => '/nextcloud',
  'debug' => false,
  'trashbin_retention_obligation' => 'auto',
  'filelocking.enabled' => 'true',
  'memcache.local' => '\OC\Memcache\Redis',
  'redis' => array(
    'host' => '/var/run/redis/redis.sock',
    'port' => 0,
    'dbindex' => 0,
    'password' => 'secret',
    'timeout' => 1.5,
  ),
  'memcache.distributed' => '\OC\Memcache\Redis',
  'memcache.locking' => '\OC\Memcache\Redis',
  'redis' => 
  array (
    'host' => 'localhost',
    'port' => 6379,
    'timeout' => 0.0,
    'dbindex' => 0,
  ),
  'updater.release.channel' => 'stable',
  'forwarded_for_headers' => 
  array (
    0 => 'HTTP_X_FORWARDED',
    1 => 'HTTP_FORWARDED_FOR',
  ),
  'auth.bruteforce.protection.enabled' => false,
  'mail_domain' => 'outlook.de',
  'mail_smtpsecure' => 'tls',
  'mail_smtpauth' => 1,
  'mail_smtpname' => 'aaaaa',
  'mail_smtppassword' => 'aaaaa',
);
2

Are you using an external authentication backend. If not, have you perhaps enabled one? For mysql you can as well enable slow-query log, perhaps that gives an idea what’s taking so long.

3

slow-query log is enabled and the time is set to 5.
Port 3306 which is the ejabberd is stored there but nothing else.
I am not using an external authentication backend and nothing like that is enabled.
Now I found out that after connection of a user it takes about 2-3 minutes till it comes back to normal. Sometimes it takes a bit longer. So if am connecting 5 users nearly at the same time then the CPU load is about 100% for about 2-3 minutes. After that all is working normal.
I do not know what is going on cause it seems that it is only if a user is logging in.

4

Now I found out what is creating that CPU load.
After checking the processlist in mysql I saw that it is the APP w2g2.
After deactivation of that app all works fine. No high loaded CPU even if more then 5 users
login at the same time.
Can you tell me who should get a ticket so that it is on the right way?

root@sielstetten:~# mysqladmin -uroot -p processlist
+-----+-----------+-----------------+-----------+---------+------+----------------+--------------------------------------------------+----------+
| Id  | User      | Host            | db        | Command | Time | State          | Info                                             | Progress |
+-----+-----------+-----------------+-----------+---------+------+----------------+--------------------------------------------------+----------+
| 112 | nextcloud | localhost:56642 | nextcloud | Query   | 0    | Writing to net | SELECT * FROM oc_locks_w2g2 WHERE file_id = NULL | 0.000    |
| 180 | root      | localhost       |           | Query   | 0    | init           | show processlist                                 | 0.000    |
+-----+-----------+-----------------+-----------+---------+------+----------------+--------------------------------------------------+----------+
5

Hi,

There is an issue open for that already. I just searched the repository after you mentioned w2g2 being the root cause.
So maybe you want to add your findings there:

6

Thanks for your info.
The only thing I can tell you that this app producing that load of the cpu.
If the app is activated and you are logging in about 5 users in my case my 4 cores are loaded 100%. It takes about 3 minutes till it comes back to normal state till you are trying to change in the browser some apps. Then the cpu load is growing up again for about 3 minutes.
If am deactivating that app all is running quick and normal without high load.
Thats all I know for the moment.

7

Alright. I responded to that issue anyway and linked your posts from this thread right here. Maybe it helps.
Let’s treat this thread as closed then.
All updates regarding that issue and the solution can be seen in the issue on Github and I suggest to subscribe to that issue.

8

To me your config looks like you don’t need memcached installed and running, because you are not using it.
Please correct if I’m wrong.

9

OK lets do it in that way.

10

You are right,
I deleted memcached but without success.
Memcached was working and I used that script to test it.

<?php
$mem = new Memcached();
$mem->addServer("127.0.0.1", 11211);

$result = $mem->get("blah");

if ($result) {
    echo $result;
} else {
    echo "No matching key found yet. Let's start adding that now!";
    $mem->set("blah", "I am data!  I am held in memcached!") or die("Couldn't save anything to memcached...");
}
?>
11

Sorry to tell you that.
I found that the problem creating high load was the app w2g2.
There was no problem regarding memcached or something else but I am searching there first. I tried a lot so maybe my config was not ok that time.

12

I didn’t think that Memcached cause high cpu load. Just little tweaking. :wink:
You are using Redis according to your config. Redis is alternative to Memcached. So there’s no need to keep Memcached running unless you’re running something else on the same server, which uses Memcached.
At least I don’t run Redis and Memcached at the same time. :slight_smile:

13

I installed memcached for testings and it is deleted again. As you saw I am using redis that is right.
The high load of my CPU comes from the APP w2g2. As soon I deactivated it all was runnin normal.