DB performance issue when using contact app

[muppeth]

Steps to reproduce

1. Go to Contact App
2. Enter new contact

Expected behavior

Contact app should display your contacts and relatively fast prepare to add new contacts

Actual behaviour

Contact app takes about 5 sec to fully load. Each time you press add new contact or remove contact it takes about 4-5sec to process. The CPU on database server goes to 100%.

Server configuration

Operating system: Debian jessie

Web server: nignx

Database: mariadb 10.0.31

PHP version: 7.0.22

Nextcloud version: 12.0.0.29

Updated from an older Nextcloud/ownCloud or fresh install: Updated way back from owncloud(nc10 i think)

Where did you install Nextcloud from:

Signing status:

Signing status

List of activated apps:
Affected with all apps disabled (apart form files and contacts)

Nextcloud configuration:

<?php
$CONFIG = array (
  'instanceid' => 'eR3Aezaf',
  'passwordsalt' => 'eew9Taev',
  'secret' => 'Peke9aeV',
  'trusted_domains' => 
  array (
    0 => 'cloud.example.org',
  ),
  'datadirectory' => '/var/www/nextcloud/data',
  'overwrite.cli.url' => 'https://cloud.example.org',
  'dbtype' => 'mysql',
  'version' => '12.0.0.29',
  'dbname' => 'nextcloud',
  'dbhost' => 'db.example.org',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'admin',
  'dbpassword' => 'eiMai5ai',
  'default_language' => 'en',
  'knowledgebaseenabled' => true,
  'enable_avatars' => true,
  'auth.bruteforce.protection.enabled' => true,
  'trusted_proxies' => 
  array (
    0 => 'proxy.example.org',
  ),
  'forwarded_for_headers' => 
  array (
    0 => 'HTTP_X_FORWARDED_FOR',
  ),
   'trashbin_retention_obligation' => 'auto',
  'log_type' => 'owncloud',
  'logfile' => '/var/log/nextcloud.log',
  'loglevel' => 2,
  'installed' => true,
  'forcessl' => true,
  'forceSSLforSubdomains' => true,
  'mail_smtpmode' => 'smtp',
  'mail_from_address' => 'no-reply',
  'ldapIgnoreNamingRules' => false,
  'mail_domain' => 'example.org',
  'mail_smtpauth' => 1,
  'mail_smtphost' => 'mail.example.org',
  'mail_smtpport' => '587',
  'mail_smtpname' => 'no-reply',
  'mail_smtppassword' => 'eic9Oodu',
  'mail_smtpauthtype' => 'LOGIN',
  'theme' => '',
  'defaultapp' => 'apporder',
  'overwritehost' => 'cloud.example.org',
  'overwriteprotocol' => 'https',
  'maintenance' => false,
  'memcache.distributed' => '\\OC\\Memcache\\Redis',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'redis' => 
  array (
    'host' => 'localhost',
    'port' => 6379,
  ),
  'asset-pipeline.enabled' => true,
  'appstoreenabled' => true,
  'appstoreurl' => 'https://apps.nextcloud.com/api/v0',
  'customclient_android' => 'https://f-droid.org/packages/com.nextcloud.client/',
  'appstore.experimental.enabled' => true,
   'mail_smtpsecure' => 'tls',
  'ldapProviderFactory' => '\\OCA\\User_LDAP\\LDAPProviderFactory',
  'updater.release.channel' => 'stable',
  'mysql.utf8mb4' => true,
);

Are you using encryption: yes

Are you using an external user-backend, if yes which one: LDAP

While investigating, I enabled slow-query. Result of which is 99% of queries logged as slow are Contact related. The top slow query is ;

SELECT `id`, `uri`, `lastmodified`, `etag`, `size`, `carddata` FROM `oc_cards` WHERE (`addressbookid` = 'xxx') AND (`uri` = 'xxx.vcf')

As stated in the forum post: https://help.nextcloud.com/t/database-performance-issue/19583/3 mysqltunner yells about many joins performed without indexes and suggests to top up join-buffer-size. Currently at 16M but I went as far as 64M and mysqltunner just kept suggesting more, so I dont think it's the way to go. I did not notice any disk IO issue and I have plenty or RAM to cache.

I hope someone here has some ideas on what could be going wrong. If you need more info please ask.
I've tested my setup on separate server (same db) with only one user connected and no load whatsoever and the results were the same. As soon as I went to Contact app the CPU went through the roof.

[rullzer]

So that table isn't properly indexed it seems. But even then I don't see how the query could be so super duper slow.

Anyway we should add indexes here.

[muppeth]

@rullzer thanks for the reply. I was loosing hope anyone has noticed my issue.

My idea now is to deploy fresh instance and move only the contact related tables over to see if the performance impact moves also. Additionally (something I didnt throughly tested yet), check if the issue appears when using only api though contact sync ap such as davdroid (maybe its only webapp related).

just in case if for some reason there is a missing index:

MariaDB [nextcloud]> show index from oc_addressbooks;
+-----------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name          | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_addressbooks |          0 | PRIMARY           |            1 | id           | A         |        2525 |     NULL | NULL   |      | BTREE      |         |               |
| oc_addressbooks |          0 | addressbook_index |            1 | principaluri | A         |        2525 |     NULL | NULL   | YES  | BTREE      |         |               |
| oc_addressbooks |          0 | addressbook_index |            2 | uri          | A         |        2525 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MariaDB [nextcloud]> show index from oc_cards;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_cards |          0 | PRIMARY  |            1 | id          | A         |       46424 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MariaDB [nextcloud]> show index from oc_cards_properties;
+---------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_cards_properties |          0 | PRIMARY              |            1 | id          | A         |      212018 |     NULL | NULL   |      | BTREE      |         |               |
| oc_cards_properties |          1 | card_contactid_index |            1 | cardid      | A         |      106009 |     NULL | NULL   |      | BTREE      |         |               |
| oc_cards_properties |          1 | card_name_index      |            1 | name        | A         |          31 |     NULL | NULL   | YES  | BTREE      |         |               |
| oc_cards_properties |          1 | card_value_index     |            1 | value       | A         |      212018 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

[rullzer]

@muppeth yep that looks like it should... or let me reprhase. It looks the same as here. And we should really add some indexes ;)

[muppeth]

@rullzer thanks a lot for taking care of this (I was loosing my mind over it the last week).

I still wonder if there is any way I could further troubleshoot to eliminate possible mistakes in my setup.

[rullzer]

@muppeth how many entries are there in your oc_cards db?

[muppeth]

@rullzer

MariaDB [nextcloud]> select COUNT(*) from oc_cards;
+----------+
| COUNT(*) |
+----------+
|    48378 |
+----------+

my db size is 3.9GB and my innodb buffer pool: 5G

[muppeth]

from mysqltunner:

-------- Performance Metrics ---------------------------------------------------------------------
[!!] Joins performed without indexes: 10592629
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 5.0G/2.6G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/5.0G should be equal 25%
[OK] InnoDB buffer pool instances: 5
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (28648302231 hits/ 28648565642 total)
[!!] InnoDB Write Log efficiency: 36.54% (10805474 hits/ 29570888 total)
[OK] InnoDB log waits: 0.00% (0 waits / 18765414 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.7% (1M cached / 39K reads)

[tcitworld]

Had also disastrous performance with this app, particularly while searching for a contact. Adding an index on addressbookid for table oc_cards_properties kind of fixed things for now.

[tcitworld]

Also added one on addressbookid for oc_cards table.