MySQL analyse tools and performance recommendations

As discussed here I used MySQLTuner and the tuning primer script to analyse my MySQL instance.

MySQLTuner shows the following:

[!!] Joins performed without indexes: 20460
General recommendations:
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes

The tuning-primer script gives the same recommendation.
I just use the untouched my.cnf so what is the join queries log about and how do I enable it if recommended?

Now I was reading here about it (oh sorry, german text there :anguished:) and it seems that all this is just about the MyISAM engine? And as far as I understood Nextcloud uses InnoDB and therefore all this join query index topic is irrelevant here, same as “OPTIMIZE TABLE” for defragmentation, which is not supported by InnoDB?

Thank you very much and happy new year in the front :smiley: .

You can activate the slow query log and log queries that take longer than e.g. 2s. Then after some time you can check how many slow queries were logged and report them to the bug tracker.

The two script are not so good to find good parameters for the innodb_variables in combination with maria_db. I gave up on the detailed tuning and just gave a generous amount of memory (for a couple of users):

innodb_buffer_pool_size = 3824M
innodb_buffer_pool_instance = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 525M
innodb_max_dirty_pages_pct = 99

At some point I might look a bit more into the details, especially on smaller system where you have to manage the little RAM you have, that can be quite challenging.

After running a longer time MariaDB, I rechecked with MySQLTuner:

  • Slow queries are at 0% (just a few), so actually nothing to do about it.
  • But still high number (maybe all) joins performed without indexes.

I wanted to get clarification about the second point and found, that even there are more index types available for MyISAM (source), also for InnoDB the topic is relevant (source). Also here some query tuning docs from MariaDB. But it is a highly customized procedure, as far as I can see and needs some deeper knowledge about which table is used in what way how often to set the indexes up an efficient way etc. etc., so too much work for a maybe too low effect in my case.

So I decided to totally drop this topic for now, until my time, interest and most of all knowledge about this stuff might increase ;).

I open up the topic, because after long runtime and optimizations there is just one hint left from MySQLTuner:

[!!] Temporary tables created on disk: 46% (2K on disk / 4K total)

 Variables to adjust:
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

I already liftet both variables from default (16M) to 32M, but there was no significant change on temporary tables on disk. Does Nextcloud use much BLOB and Text columns?, as they cannot be written to ram: https://stackoverflow.com/questions/3753504/mysqltuner-suggestions-and-changes-to-my-cnf

I’m struggling with databse performance. I noticed that addressbook queries are ending up in slow-query log, also seeing: Joins performed without indexes: 1141540 when running mysqltunner.

When checking slow query (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 |
+------+-------------+----------+------+---------------+------+---------+------+-------+-------------+

I just went thought my old topics. Since some versions ago the Nextcloud admin panel shows a warning + occ command solution if there are tables without indexes. When running the command after install and updates of Nextcloud, the mysqltuner warnings about joins without indexes are gone. The temporary tables on disk can be ignored, since not relevant for Nextcloud which uses InnoDB and InnoDB tunings can be done according to mysqltuner recommendations. Then there is no question left :slightly_smiling_face:.