[mythtv-users] sluggish system, potential HD failure, and how best to get back up and runnin

[Hika van den Hoven]

Hoi Simon,

Wednesday, March 5, 2014, 12:20:34 PM, you wrote:

> Greg Thompson <gthompson20 at gmail.com> wrote:

>> Mind sharing your DB Tuning Tips?


> With the disclaimer that I'm no expert on DB tuning ...

> A good starting point is mysqltuner.pl, look at the results and twiddle settings.

> However, even after reading the docs, some of the settings are a
> tad obscure. You also start getting into tradeoffs - some memory
> allocation are global, while some are per-connection. If you keep
> the allowable connections up then you find the total possible memory usage also goes up.

> Extract from my current results :

> [--] Data in MyISAM tables: 268M (Tables: 96)
> [--] Data in InnoDB tables: 48K (Tables: 3)
> Gives a total data size - ideally you want the memory allocation to
> be larger than this so all the tables can stay resident.

> [!!] Total fragmented tables: 27
> You can optimise the tables - but they'll fragment again sooner or
> later. Don't worry too much about this.

> [--] Up for: 142d 21h 7m 22s (66M q [5.404 qps], 22K conn, TX: 244B, RX: 46B)
> This is good, it means you get good statistics. Just after a
> restart you don't get meaningful numbers or recommendations.

> [--] Total buffers: 930.0M global + 4.6M per thread (20 max threads)
> [OK] Maximum possible memory usage: 1021.2M (51% of installed RAM)
> Start to worry if this gets to "most of your RAM", although it's
> unlikely that every connection will use all it's possible memory.

> [!!] Highest connection usage: 100%  (20/20)
> Hmm, looks like I need to tweak it up a bit, but then I've not seen
> any odd effects. Not sure when all the connections get used, because
> I've tried "making the system busy" and never got that high during testing.

> [OK] Key buffer size / total MyISAM indexes: 384.0M/323.6M
> IIRC this is one of the biggies - the buffer is big enough to keep
> all indexes in memory. That means any indexed operation (select,
> join, etc) shouldn't need to hit the disk.

> [OK] Key buffer hit rate: 99.9% (1B cached / 1M reads)
> And the result is a good figure

> [OK] Sorts requiring temporary tables: 0% (288 temp sorts / 445K sorts)
> That's good, and suggests some good use of indexes etc for sorts.

> [!!] Joins performed without indexes: 94335
> Ideally all joins would be via indexes, but largely that's out of
> the user's control as it's controlled by the programming (how it
> does joins) and the database schema (what indexes there are). I
> suspect "bad" custom criteria in recording rules could contribute to this figure.

> [OK] Temporary tables created on disk: 7% (11K on disk / 152K total)
> That's good, most of the temporary tables created were in memory.

> [!!] Table cache hit rate: 0% (236 open / 361K opened)
> Dunno about this one !

> Then at the end if gives a list of recommendations :

> -------- Recommendations
> -----------------------------------------------------
> General recommendations:
>     Run OPTIMIZE TABLE to defragment tables for better performance
>     Enable the slow query log to troubleshoot bad queries
>     Reduce or eliminate persistent connections to reduce connection usage
>     Adjust your join queries to always utilize indexes
>     Increase table_cache gradually to avoid file descriptor limits
> Variables to adjust:
>     max_connections (> 20)
>     wait_timeout (< 28800)
>     interactive_timeout (< 28800)
>     query_cache_size (> 24M)
>     join_buffer_size (> 2.0M, or always use indexes with joins)
>     table_cache (> 8192)

> I suggest that you don't just blindly follow the instructions, but
> go and read the MySQL docs and see what each setting does first. If
> nothing else, it'll make you realise just what a complex area it is :-O
> Also, after making a change, MySQL tuner won't give meaningful
> results for some aspects. So it can be a lengthy process of making
> adjustments, leaving the system to run for a few days, and then
> looking again. Where you can get immediate feedback is the total
> possible memory consumption and how your changes have affected it.


> From memory, the defaults are "fairly conservative - with the
> result that a lot of disk I/O is created which is not necessary. On
> a system doing nothing but running a DB it wouldn't be as bad as OS
> caching would hide the problem, but with a Myth system, there's so
> much other I/O that you're guaranteed to flush DB data from the OS
> cache and so it's a lot more important to tune the DB itself to keep
> it's own cache. More memory is often a good investment - and if
> there isn't enough memory to tune out virtually all disk access,
> then adding more memory will probably be a better investment than an SSD (or faster drive).


> Hope that's some help.


> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org

Another way to reduce disk access and increase speed is putting /tmp
on ramfs. Of cause you need to have enough ram or your system starts
swapping. I do it for /tmp but not for /var/tmp because the latter
gets often used for bigger files. Note the difference between ramfs
and tmpfs. ramfs assigns space at need and tmpfs assigns an in kernel
predefined size. A size you can change with kernel options.
I also use it for my constant rrd sensor measuring and commit it to
disk on shutdown and every once in a while.

Tot mails,
  Hika                            mailto:hikavdh at gmail.com

"Zonder hoop kun je niet leven
Zonder leven is er geen hoop
Het eeuwige dilemma
Zeker als je hoop moet vernietigen om te kunnen overleven!"

De lerende Mens
--