[Uatschitchun]

Optimizing DB
(Only 3 topics on the go in this thread!)

We'll keep it sorted

Optimizing DB access was not because of slowness but because of MySQLTuner always suggested a higher join_buffer_ize or indexed searches ...

This is what mysqltuner says:

Code:

[!!] Joins performed without indexes: 3239
Adjust your join queries to always utilize indexes
join_buffer_size (> 32.0M, or always use indexes with joins)

That is unexpected. From code I know we have indexes defined for the joins (although smart playlists can go beyond that, but that is fine), and I have checked query plans etc. on SQLite.

Is there any way to get this tuner to be more precise about what joins it thinks didn't have an index to hit? Assuming 3239 is for a session, perhaps do only set action in Kodi per session and see if we can find the action that upsets the tuner. Was that figure after a clean db attempt?

I've tried to get it sorted. Setting these in my.conf

Code:

log_queries_not_using_indexes   = 1
long_query_time                 = 60

logs slow queries only when exceeding 60s, but do log joins without using indexes

This is the output of mysqldumpslow (only joins):
http://sprunge.us/UCGO
And complete:
http://sprunge.us/EBZL

Code:

$ ./tuning-primer.sh

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 60.000000 sec.
You have 7864 out of 158004 that take longer than 60.000000 sec. to complete
...
JOINS
Current join_buffer_size = 4.00 M
You have had 212 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
...
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 7310 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes

This is from today's normal usage/navigate around, DB Update and Ckeaning tries