[mythtv-users] Can mythfilldatabase interrupt recordings in progress?

[Jan Ceuleers]

On 29/06/15 23:39, Simon Hobson wrote:
> The ideal is to adjust MySQL's settings so that it keeps everything in RAM - the moment is needs to write stuff (eg temporary tables) out to disk then performance takes a nosedive. I added an SSD to my system, but I reckon by far the best improvement I got was from tuning MySQL which by default has fairly stingy settings - it has to, otherwise it would default to using more ram than some systems have available.
> 
> Fire up mysqltuner.pl and see what it says - then study the manuals toe see what the settings do. Some things you can't "fix", such as if some code in MythTV is doing a join on un-indexed fields, but generally it will give good pointers. Here is what it says about my system (running 0.24) :

Thanks Simon. Again; I had already done that but here is its current output:

 >>  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.43-0ubuntu0.12.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
-------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 758M (Tables: 127)
[--] Data in InnoDB tables: 112K (Tables: 5)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 12

-------- Security Recommendations
-------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics
-------------------------------------------------
[--] Up for: 5d 11h 1m 13s (1M q [2.552 qps], 5K conn, TX: 5B, RX: 971M)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 972.0M global + 2.7M per thread (250 max threads)
[OK] Maximum possible memory usage: 1.6G (43% of installed RAM)
[OK] Slow queries: 0% (9/1M)
[OK] Highest usage of available connections: 24% (62/250)
[OK] Key buffer size / total MyISAM indexes: 300.0M/781.6M
[OK] Key buffer hit rate: 99.5% (935M cached / 4M reads)
[OK] Query cache efficiency: 56.9% (533K cached / 937K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (426 temp sorts / 84K sorts)
[OK] Temporary tables created on disk: 5% (616 on disk / 10K total)
[OK] Thread cache hit rate: 96% (187 created / 5K connections)
[!!] Table cache hit rate: 1% (41 open / 3K opened)
[OK] Open file limit used: 5% (73/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 128.0M/112.0K
[OK] InnoDB log waits: 0
-------- Recommendations
-----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    table_cache (> 128)

Not clear why it's asking me to increase table_cache beyond 128 since
it's already set to 256. I'll look into this further (perhaps the
table_cache setting in my.cnf is being overridden somewhere else or
something). In any event it's not a very high setting given that
max_connections is set to 100.

Cheers, Jan