6 replies to this topic

[farmerdave]

I have found these two scripts on the MythTV wiki, optimize_mythdb.pl and mysqltuner.pl.

If I run the mysqltuner.pl script, I get the following output.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.22-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in InnoDB tables: 7M (Tables: 74)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 74

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 26s (1K q [4.718 qps], 16 conn, TX: 1M, RX: 309K)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 216.0M global + 7.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.3G (66% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Key buffer size / total MyISAM indexes: 48.0M/98.0K
[OK] Key buffer hit rate: 100.0% (28 cached / 0 reads)
[!!] Query cache efficiency: 14.9% (167 cached / 1K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 32 sorts)
[!!] Joins performed without indexes: 10
[OK] Temporary tables created on disk: 21% (5 on disk / 23 total)
[OK] Thread cache hit rate: 56% (7 created / 16 connections)
[OK] Table cache hit rate: 28% (64 open / 227 opened)
[OK] Open file limit used: 1% (20/1K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[!!] Connections aborted: 12%
[OK] InnoDB data size / buffer pool: 7.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 8M, or use smaller result sets)
    join_buffer_size (> 128.0K, or always use indexes with joins)

Particularly, the recommendation to run OPTIMIZE TABLE because I have 74 fragmented tables (the mythconverg database I assume) troubles me. I am running the optimize_mythdb.pl script in my daily cron. Should this correct these fragmented tables?
I have also tried logging in to MYSQL manually and running "mysql> OPTIMIZE TABLE callsignnetworkmap;" and then running the mysqltuner.pl script again, with no difference.

[arkay]

Dunno to be honest. I've been running myth at least 3 years now I think. All the same DB with a couple of upgrades now and then as the OS gets updated. In all that time I haven't once run an optimise job on the tables in mysql.

You'd have to wonder just how fragmented and/or slow a home use little mediacenter database could get, given mysql can handle corporate database requirements.

Unless you're having a specific database issue I'd say there's little to worry about with the database.

Cheers,

Arkay.

[dave1234]

If I run the mysqltuner.pl script

I found that recently as well, I ran it and tried their recommended settings along with the ones I'd found in the mythtv optimisation info. Result was that it was allocating about 75% of my available memory to mysql I think. Didn't seem any faster either and tbh I don't see how the defaults would be that much of a problem with the relatively small databases we run even if they've got a few years worth of recorded tv history.

Edited by dave1234, 09 April 2012 - 09:37 PM.

[farmerdave]

The reason I started thinking about this is because consistently around the time shepherd runs (and hence mythfilldatabase) any video I am watching, whether through MythTV or XBMC, seems to stutter at regular intervals, between 5-15 minutes apart. The stutter behaves as though something that is supposed to be 50 fps drops to about 10 fps, although there doesn't appear to be anything in the logs of either program to help me.

I've since changed my cron job so that shepherd runs no earlier than midnight, and I think that has helped. I haven't really seen the problem too much, although it still happens in MythTV. I can make it happen in XBMC if I uncheck "Sync playback to display", even though in nvidia-settings I have "Sync to VBlank" set.

[spudstrawb]

There are buttons to "Optimize Tables" and "Repair Tables" in Settings ==> Database in Mythweb. It will show you what the issues are before and after running these things.

[farmerdave]

Thanks spudstrawb, I've seen those and clicked them too, although I'm going to have a closer read of the issues now.

[arkay]

Sounds odd.

You could try dropping the priority of shepherd with the nice command when called via cron, that should prevent it from stealing resources from myth.
Though it seems odd that shepherd could steal or even use enough resource to cause an issue in the first place.

man nice

Will show you how to use it to lower the priority of shepherd.

Cheers,

Arkay.