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

[Simon Hobson]

Jan Ceuleers <jan.ceuleers at gmail.com> wrote:

> I know that mysql's memory usage can vary a lot based on the queries it
> is executing at any one time, but I would expect mythfilldatabase not to
> be the most memory-hungry task mythtv has mysql do (but rather the
> scheduler).
> 
> I also know that mysql can need a lot of temporary storage; there is
> more than 25GB of disk space available in the directory where it creates
> its temporary files.

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) :

> # ./mysqltuner.pl 
> 
>  >>  MySQLTuner 1.2.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.
> 
> -------- General Statistics --------------------------------------------------
> [--] Skipped version check for MySQLTuner script
> [OK] Currently running supported MySQL version 5.1.49-3
> [OK] Operating on 64-bit architecture
> 
> -------- Storage Engine Statistics -------------------------------------------
> [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
> [--] Data in MyISAM tables: 428M (Tables: 96)
> [--] Data in InnoDB tables: 48K (Tables: 3)
> [!!] Total fragmented tables: 23
> 
> -------- Security Recommendations  -------------------------------------------
> [OK] All database users have passwords assigned
> 
> -------- Performance Metrics -------------------------------------------------
> [--] Up for: 481d 9h 44m 37s (224M q [5.389 qps], 26K conn, TX: 1035B, RX: 201B)
> [--] Reads / Writes: 58% / 42%
> [--] Total buffers: 930.0M global + 4.6M per thread (25 max threads)
> [OK] Maximum possible memory usage: 1.0G (52% of installed RAM)
> [OK] Slow queries: 0% (121/224M)
> [OK] Highest usage of available connections: 68% (17/25)
> [OK] Key buffer size / total MyISAM indexes: 384.0M/524.0M
> [OK] Key buffer hit rate: 99.4% (4B cached / 27M reads)
> [OK] Query cache efficiency: 73.7% (138M cached / 187M selects)
> [!!] Query cache prunes per day: 291
> [OK] Sorts requiring temporary tables: 0% (828 temp sorts / 808K sorts)
> [!!] Joins performed without indexes: 260328
> [OK] Temporary tables created on disk: 15% (25K on disk / 168K total)
> [OK] Thread cache hit rate: 99% (112 created / 26K connections)
> [!!] Table cache hit rate: 0% (237 open / 620K opened)
> [OK] Open file limit used: 2% (350/16K)
> [OK] Table locks acquired immediately: 99% (192M immediate / 192M locks)
> [OK] InnoDB data size / buffer pool: 48.0K/8.0M
> 
> -------- Recommendations -----------------------------------------------------
> General recommendations:
>     Run OPTIMIZE TABLE to defragment tables for better performance
>     Enable the slow query log to troubleshoot bad queries
>     Adjust your join queries to always utilize indexes
>     Increase table_cache gradually to avoid file descriptor limits
> Variables to adjust:
>     query_cache_size (> 24M)
>     join_buffer_size (> 2.0M, or always use indexes with joins)
>     table_cache (> 8192)

The only issue is that it can take some time - as you have to adjust your settings, then leave it for a while (or manually trigger a range of representative tasks) to see what the result is. I recall taking some time to get the number of connections right - too few and clients will fail to get a connection sometimes, too many and you increase max memory size since some memory allocations are per connection.