[mythtv-users] Database tuning

David Watkins watkinshome at gmail.com
Sun Sep 1 07:36:02 UTC 2013


On 31 August 2013 19:08, Simon Hobson <linux at thehobsons.co.uk> wrote:

> I've been trying to tune my database to reduce times on activities like
> rescheduling. However I seem to have hit something of a wall.
>
> The backend is an HP Microserver with 2G of RAM - running Debian Wheezy
> and MythBackend 0.24.1*. The database in it's entirety is 200 to 250M. I've
> been usingmysqltuner.pl, but some of the recommendations it comes up with
> either don't have an effect or are out of my control. Key bits from
> mysqltuner :
>
> [OK] Currently running supported MySQL version 5.1.49-3
> [OK] Operating on 64-bit architecture
>
> [--] Data in MyISAM tables: 231M (Tables: 96)
> [--] Data in InnoDB tables: 48K (Tables: 3)
> [--] Total buffers: 434.0M global + 3.6M per thread (30 max threads)
> [OK] Maximum possible memory usage: 540.9M (27% of installed RAM)
> [OK] Highest usage of available connections: 46% (14/30)
> [OK] Key buffer size / total MyISAM indexes: 384.0M/278.1M
> [OK] Key buffer hit rate: 99.9% (49M cached / 28K reads)
> [OK] Sorts requiring temporary tables: 0% (8 temp sorts / 8K sorts)
> [!!] Joins performed without indexes: 2512
> [OK] Temporary tables created on disk: 16% (449 on disk / 2K total)
> [!!] Table cache hit rate: 1% (145 open / 7K opened)
> [OK] Open file limit used: 1% (261/16K)
> [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
>
> -------- 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:
>   join_buffer_size (> 1.0M, or always use indexes with joins)
>   table_cache (> 8192)
>
> So as far as I can tell, I've not got buffers large enough to hold indexes
> in memory, the data in memory, and should have "fast" response times.
> But if (for example) I bring up the upcoming recordings screen in MythWeb,
> and click on "Don't Record" for a program - it takes perhaps 10s to refresh
> the page. During that time, top shows that :
> Wait I/O goes to 40% or more - I've seen 90+%. And cached memory jumps
> from around 50M (I recently did a "echo 3 > /proc/sys/vm/drop_caches" to
> empty the caches) to 250M or more and then drops back to 50M or so. This is
> much the same after multiple tests - so any data really ought to have been
> read into cache after the first one or two goes.
>
> I'm thinking that MySQL is creating some large temporary tables and for
> some reason this is hitting disk i/o - either for the temp tables, or by
> flushing out data that should be held in MySQL's caches.
>
> I stopped increasingtable_cache as a found a note that having this value
> constantly increasing is normal if the system is creating temporary tables.
> See comments to
> http://dev.mysql.com/doc/refman/5.1/en/table-cache.html
>
> At the moment I;ve got these settings in /etc/mysql/my.cnf :
> key_buffer = 16M
> key_buffer_size     = 384M
> max_allowed_packet = 16M
> thread_stack = 192K
> thread_cache_size    = 8
> max_connections     = 30
> table_cache       = 8192
> #thread_concurrency   = 10
> join_buffer_size    = 1M
> query_cache_limit = 1M
> query_cache_size    = 24M
>
> Could anyone suggest what else I might be needing to adjust ? I've tried
> turningjoin_buffer_size up to 16M, but other than considerably increasing
> MySQL's max memory usage it's not made any difference.
>
> * Yes, I know it's a few versions behind, but if it aint broke ...
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users


You've probably thought about  this already, but have you tried
rationalising your recording rules.?

When I looked at mine I found I had multiple rules for many programmes
(including 20 different recording rules for the recording the Simpsons),
accumulated over the years, plus dozens and dozens of rules for things I
would never want to record again.  Removing the unwanted stuff made a
significant improvement to the rescheduling time.

I also removed a lot of stuff from 'Previously Recorded'  that I knew I
wouldn't want to record again.  It meant losing my recording history but
that wasn't a problem for me.

HTH
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20130901/58cd584c/attachment.html>


More information about the mythtv-users mailing list