[mythtv-users] Bad mysql performance -- huge oldrecorded -- joins without index

[Håkon Alstadheim]

Hi all, I just noticed I had more than 100 000 entries in oldrecorded.
Bludgeoned it down to ~30 000, and mysqltuner seems to indicate that
fewer temporary tables are now being written to disk. Down from 12% to
2% , but I need to run longer to be sure. Setting join buffer size even
higher got temp. tables on disk down to 8%, but that meant going
dangerously high on ram-setting. Anyway, this is not just my
ricer-personality showing, there is actually a noticeable difference on
front-end responsiveness.

This seeming success caused me to think that it would be nice if stuff
like daily news-shows which I only ever keep a couple of editions of,
and which never are repeated, should maybe be deleted after a few days
from oldrecorded, and it should be possible to set a limit for how far
back myth should remember old recordings.

Mysqltuner complains about "Joins performed without indexes", but I do
not know enough mysql to start looking into that. Any hints ?

There are also quite a few slow queries given "long_query_time = 0.7" .
Most are things like " UPDATE recordmatch  INNER JOIN sched_temp_record ..."

My drives are not the fastest, but I have plenty of ram, so I'm looking
for the best way to use that ram.

Below is a sample mysqltuner-run from before I hit "oldrecorded" over
the head: ( a ridiculous amount of ram used, I know)
---

======== MySQLtuner ====

 >>  MySQLTuner 1.3.0 - Major Hayden <major at mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.49-0+deb8u1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 16M (Tables: 13)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in InnoDB tables: 1G (Tables: 362)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 17

-------- Security Recommendations  -------------------------------------------
[!!] User 'dspam-readonly at localhost' has no password set.
[!!] User 'mediatomb at localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 4h 25m 27s (45M q [166.683 qps], 26K conn, TX: 22B, RX: 10B)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 727.0M global + 66.6M per thread (70 max threads)
[OK] Maximum possible memory usage: 5.3G (53% of installed RAM)
[OK] Slow queries: 0% (2K/45M)
[OK] Highest usage of available connections: 71% (50/70)
[OK] Key buffer size / total MyISAM indexes: 12.0M/15.1M
[OK] Key buffer hit rate: 100.0% (1B cached / 126K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 124K sorts)
[!!] Joins performed without indexes: 32066
[OK] Temporary tables created on disk: 12% (14K on disk / 110K total)
[OK] Thread cache hit rate: 98% (311 created / 26K connections)
[!!] Table cache hit rate: 7% (844 open / 11K opened)
[OK] Open file limit used: 1% (122/9K)
[OK] Table locks acquired immediately: 99% (69M immediate / 69M locks)
[!!] InnoDB  buffer pool / data size: 312.0M/1.1G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 64.0M, or always use indexes with joins)
    table_open_cache (> 4096)
    innodb_buffer_pool_size (>= 1G)