Database performance issue

ℹ️ Support
1

Hi guys,
I wasnt sure if it’s a general bug or something on my instance, so I didn’t create an issue on github yet. I was hoping someone could help me debug, the issue I’m having. I’m struggling with databse performance. I noticed that addressbook queries are ending up in slow-query log. I’m also seeing a lot of: Joins performed without indexes: 1141540 when running mysqltunner.
Basically the moment anyone goes to Contact app or syncs new contacts my CPU goes through the roof (seeing even picking at 500% on htop). Disk IO is low so it’s not related to writes. I’ve tested it on test instance with default my.cnf mariadb settings with all other apps disabled (same db).

Checking slow query like (usually addressbook related queries):
explain SELECTid,uri,lastmodified,etag,size,carddataFROMoc_cardsWHEREaddressbookid= '1543';
the result is:

+------+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | oc_cards | ALL  | NULL          | NULL | NULL    | NULL | 36593 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+-------+-------------+

Any hints, ideas?

1 Like
2

You have 36593 entries in your addressbook?

Do you use redis and/or apcu for caching? This reduces the load on your database.

3

Yes. I use both redis and apcu for caching. At the moment my contacts size is:
select COUNT(*) from oc_cards; - 43615
oc_addressbooks; - 2099
oc_adressbookchanges; - 426194
oc_cards_properties ; - 203849

Mysqltunner yells there is 6570722 joins performed without indexes atm.
I’m worndering if that’s normal since the developer guide mentions to always use indexes. Most of the queries in slow log are like:

SELECT `id`, `uri`, `lastmodified`, `etag`, `size`, `carddata` FROM `oc_cards` WHERE (`addressbookid` = '1493') AND (`uri` = '.4caa34af-99af-4a3b-9e24-5c2bc1d02342.vcf')

I’ve moved that DB to test a server where only one user was logged in, and the results were pretty much the same. When using calendars, activities, db is responsive and quite fast. The moment I go to contacts the cpu grind starts. Creating/deleting contact takes about 4 sec. to complete.

If there is anything I should check or troubleshoot let me know. I need suggestions or hints to change my perspective on the issue, Maybe I’m missing something obvious. Atm I cache the entire database (4GB) in RAM with innodb_buffer_pool_size, but that seem to not improve much. My innodb_log_file_size is set quite high (512MB) so there is not that much disk IO. Mysqltunner suggest to crank up join_buffer_size which I did to 2MB, but seems like it could be easily 32MB and mysqltuner would suggest increasing (should I?).