[mythtv-users] After 7y5m running, time to start from scratch after update to .27?

[Stephen Worthington]

On Fri, 22 Nov 2013 22:16:25 +0100, you wrote:

>Hello everybody,
>
>sounds sad, after 7 years and 5 months running, with 1 year 8 months 
>recorded (I hope I havent watched that much TV, actually!), now it seems 
>that I have to start my backend from scratch, my backend is giving my 
>too much pain, and that started directly after updating to 0.27
>
>As reported earlier on this list (with more information than I want to 
>repeat here), there is the problem with the "mythcontext:backend is 
>online" messages, which indicate that the remote frontend is loosing the 
>contact to the backend appearently (but normally, no "is the backend 
>started?" messages). I thought this might be because of the xmltv data, 
>but I do not think that anymore, I do not know.
>
>Anyway, no solution to that. I tried to increase logging verbosity, but 
>I seem to be on "debug" and still the messages are not helpful to me. No 
>messages at syslog either (like, SATA problems or something, not at all)
>
>Now recently, it got worse, quite frequently, the backend goes to 100+% 
>of CPU load, and the frontends get unresponsive when I enter the 
>recording screen, they do not display any recordings (or they do, after 
>more than 20minutes). Still the backend log is unconspiciuous.
>
>Normally, when this happens, there is also a mythcomflag running, but 
>that does not seem to be responsible: killing it does not repair the 
>backend process.
>
>Restarting the backend fixes the situation. This for me indicates that 
>it is not hardware or mysql related.
>
>Is my mythconverg overloaded? It is 2.0GiB in size. It resides on an 
>SSD. The channel table is maybe a bit big, 3280 items. Definitely too 
>big for my use case, many channels are duplicatedand I am not sure, why, 
>I am only using less than 100 in my frontends. For example, I have 28 
>duplicates of the channel RTL from source 3, all of them get their 
>channel data from one xmltv run in the early morning (I still start 
>mythfilldatabase from a cron script; I never straightened out the method 
>of running it via housekeeping which was, I think, introduced with 0.25. 
>I also still start optimize_mythdb.pl every time after the 
>mythfilldatabase run).

Having that many channels could certainly be a problem.  And 2.0
Gibytes is pretty large, especially for so few recordings.  I
currently have 5747 recordings and my /var/lib/mysql/mythconverg
directory is only 1.1 Gibytes.  The largest part of the mythconverg
database is the recordedseek table, with all the entries allowing you
to jump around in a recording.  It normally is larger than the rest of
the database put together, and hence the database size is usually
fairly proportional to the number of hours of recordings you have. You
might like to go to /var/lib/mysql/mythconverg and see what the file
sizes are for each of the tables, to see if that can tell you
anything.  For reference, here is a listing of mine (ls -alsS command
output):

  http://www.jsw.gen.nz/mythtv/mythconverg_lsalS.txt

>I get messages regarding EIT scans when the backend is not behaving 
>well, maybe a hint? But I always got them, in all versions, the backend 
>was running fine anyway.
>
>I have 456 recorded programs; 208 with autoexpire=0, 51 with 
>autoexpire=9999 and the rest, autoexpire=1, =10000 and maybe others. I 
>think I have too many programs marked as "do not delete", mainly because 
>I only have about 1TB of expirable programs, i.e. useable space for new 
>recordings.
>
>I also get messages regarding autoexpiration when the backend behaves 
>badly; but also these messages should be normal.
>
>I wonder if I should start from scratch or maybe start cleaning up 
>manually first. I appreciate any helping hand/hint.
>
>My setup looks like that:
>
>Backend headless server, dual core ivy bridge celeron 2.6GHz, 4GB Ram, 
>60GB SSD (sandforce, running 15k hours, 8064GiB writes, 14.144GiB reads 
>during the lifetime), 2 x 1.5TB WD green disks as recording space, both 
>disks running for about 35k hours, healthy according to smart. Ubuntu 
>12.04 32bit, mythbuntu repos. 2 dvb-s2 receiver cards (max 2 concurrent 
>recordings per card as by configuration). MySQL data directory in /mysql 
>on the SSD (that, and the mythfilldatabase runs should be the writes on 
>the SSD, I would guess).
>
>Several frontends, connected via GBit wired network (no network problems 
>in other applications).
>
>Do I have to start from scratch? I would want to recover the recordings, 
>but I can initialize all other tables. I am sorry for the channels list 
>though, starting from scratch means like one day of work, because the 
>satellites give so many crap channels, editing the channels list is bad.
>
>Is it worth cleaning up the database first? Removing duplicate channels, 
>deleting recording rules etc?

If the extra channels are the source of your WAF problems, sure.
Otherwise, just do it when you have some spare time.  The first thing
is to diagnose just where your problems are coming from.

Are you OK with doing SQL commands?  If so, here are some to try that
might give some hints about your channel table problem.

Number of sources you have:

  select count(sourceid) from videosource;

Number of sources that do not have any channel table entries (should
be 0):

  select count(sourceid) from videosource v where sourceid not in
(select distinct sourceid from channel);

Number of channel table entries:

  select count(*) from channel;

Number of different callsigns in channel table:

  select count(distinct callsign) from channel;

Number of channel table entries that do not have a sourceid in
videosource (should be 0):

  select count(*) from channel where sourceid not in (select sourceid
from videosource);

Number of channel table entries where the callsign is the same as in
another channel table entry (Normally should be 0 if you have only one
source and no +1 channels):

  select count(distinct callsign) from channel c1 where (select
count(callsign) from channel c2 where c1.callsign=c2.callsign)>1;


>I cannot recompile with debugging, that is beyond my scope.
>
>I am tempted to go back to 0.26, but following the list, it seems I am 
>the only one with these problems. Sigh.
>
>As said above: I am grateful for any hint.
>
>I have to get back to good WAF values, that is very important for me!
>
>Thanks & Cheers,
>
>Matthias