[mythtv-users] DB Tuning (Was: Replace backend 3.5 system disk with 2 2.5 raid1?)

[Mike Carron]

On 09/28/2014 02:44 AM, Simon Hobson wrote:
> Mike Carron <jmcarron at starstream.net> wrote:
>
>>> mysqltuner.pl is a useful tool.
>> Thanks. I noticed that mysqltuner.pl doesn't claim compatibility with MySQL 5.5. What problems, if any, might that cause?
> No idea !
> I wouldn't have thought all that much would have changed, so I'd expect the results to still be valid. But then I'm not a MySQL expert.
[mike] I'm sure no MySQL expert either, far from it. I ran mysqltuner.pl 
with the following recommendations:
General recommendations:
     Enable the slow query log to troubleshoot bad queries
     Increase table_open_cache gradually to avoid file descriptor limits
     Read this before increasing table_open_cache over 64: 
http://bit.ly/1mi7c4C
Variables to adjust:
     table_open_cache (> 256)

There is no table_open_cache in etc/mysqal/conf.d/mythtv.cnf but there 
is a table_cache. The article in the provided link seemed to indicate 
that the two are interchangeable, namely increasing the value of 
table_cache in mythtv.cnf would satisfy the recommendation to increase 
table_open_cache. Is that a good guess? I haven't done it yet.
>> Also, you mentioned "oodles" of RAM but did not identify how much is an "oodle."
> I think there are several oodles to a bucketload, and several bucketloads to a wow :-)
[mike] Using that definition I guess I have several wow.:-)
>
> It's one of those "how long is a piece of string ?' type questions. My own backend has 2G of RAM, and at the moment (it's currently recording one radio stream from DVB-T, aka Freeview) I get these stats from top :
>
> Mem:   2027944k total,  1854508k used,   173436k free,    27108k buffers
> Swap:  2097144k total,    53636k used,  2043508k free,  1145296k cached
[mike] Here is a snapshot from top:
KiB Mem:  15883168 total, 15715256 used,   167912 free, 27664 buffers
KiB Swap: 16218108 total,   166432 used, 16051676 free. 14579708 cached Mem

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ 
COMMAND
25412 mythtv    37  17 1301340 114444  22304 R 106.4  0.7 312:20.53 
mythcommflag
  2055 mythtv    20   0 6170972  88332   7556 S  19.9  0.6 1179:07 
mythbackend
  2556 root      20   0  371416   3324   1936 S   2.3  0.0 1:20.02 udisksd
    99 root      20   0       0      0      0 S   0.3  0.0 9:40.79 kswapd0
  1303 mysql     20   0 3525080 126964   4516 S   0.3  0.8 24:26.27 mysqld
27981 mythtv    37  17 1232676  43196  23704 S   0.3  0.3 0:01.10 
mythcommflag
28084 mythtv    37  17 1233172  45652  23724 S   0.3  0.3 0:00.95 
mythcommflag
28123 root      20   0   23672   1732   1160 R   0.3  0.0 0:00.10 top

> I don't think Myth itself takes a lot of memory - it's more the stuff that goes with it : getting your TV listings, the database when it's being pushed during a reschedule, commflagging, transcoding, ... If you don't commflag or transcode then I suspect 1G would be adequate, if you do either of those then more memory has benefits.
>
> One benefit of more RAM is if you run tasks that access recently recorded material. If that material is still in cache memory then there is no disk overhead in accessing it, but if it's been flushed then it'll have to be read back in which means more disk I/O and more disk seeking (cf earlier discussions in this thread). The more memory you have, the more recorded material you can keep in cache.
>
> The other benefit of more memory is that you can safely tune MySQL to use more - note that my tuning allows MySQL to use up to about 1G max. It won't hold onto that if it's not using it, but it can use it if it needs to. If you only had 1G of RAM then you'd need to be a bit more conservative - with a tradeoff in terms of disk usage. What you really need to avoid is any significant swapping, that's a real performance killer.
>
> There is a saying that you can't have too much RAM. In general that's true, though I have found a situation* where too much RAM breaks a system. On most systems, the cost difference between 1G, 2G, and 4G makes 4G (or more) a bit of a "no brainer".
>
> * Tried running the backend as a Xen guest with PCI passthrough for the tuners. Found that if the host had more than 4G RAM, the tuners didn't work.
> _______________________________________________
> 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
>
>