[mythtv] backend from SVN 9761 bangs on mysqld

[Janne Grunau]

On Sunday 23 April 2006 18:07, Daniel Kristjansson wrote:
> On Sun, 2006-04-23 at 17:32 +0200, Janne Grunau wrote:
> > It is not the scheduler alone. One of the recent EIT-refactoring
> > (iirc 9671) adds noticeable DB-load.
>
> I don't think hat wouldn't create the 100% cpu use spikes he's
> seeing.

I think we were both wrong. Even with my patches mysql's load is over 
30% while inserting events (without rescheduling). Depending on the 
system an unpatched version might create nearly 100% cpu utilization.
But at least great parts if not the majority of load is caused by 
GetOverlappingPrograms (70% with my patched version).

I just spoke to him on irc. He uses an AthlonXP 2500+, that's not that 
much slower than my Athlon64 3000. But he is not seeing spikes of high 
cpu utilization but plateaus of it. It starts just after adding the eit 
pid and ends with the eit-scanner report of the number of events added.
See http://tikei.de/log2 for a combined log of the backend and top.

> basically you just need to make sure that if you reuse the version
> data, it hasn't been too long since you last used it. If you don't do
> EIT data collection for a week the EIT table version numbers will
> have wrapped around and your cache will be out of date.

I think it's best to start with a clean cache but the problem persists 
then too. If we miss the update to the maximum version number we won't 
update until the next time.

> BTW The data 
> does need to be stored in the DB, even the EIT version cache becomes
> too big after a while. (But not nearly as big as the cache of the
> entire EIT event that 0.19 uses.)

I don't think using the db for the cache is a good idea. The greatest 
parts of the EIT-scanning are atm queries. So adding more is not the 
solution even if these queries are cheaper.
If counted just my program table and the average number of events per 
channel is clearly below 500. Using 8 kbyte (plus overhead of QMap) 
memory per program is not that bad.
The cache needs some method to prune old entries though. In the 
signature are 32 bits left so we can save the seconds since UNIX epoch.

I'll start make the cache persistant per backend run. So if anyone else 
has already started or thought of it please contact me. 

> > A 'if (match = 12000) return 0;' in UpdateDB(MSqlQuery, int)
> > reduces the number of inserted events and the DB load significantly
> > (together with other changes).
>
> Which pretty much disables updates; it only allows inserts to go
> ahead. This means you will never get the longer program description
> present in EIT tables transmitted closer to the broadcast time.

No, not really. I changed score_match in such a way that only a perfect 
match can archieve a score of 12000. But Stuart Auchterlonie and I are 
both not sure what you want to archieve with the second part of that 
function. See attached diff.

> I think a persistent cache will make things much more efficient,
> but right after adding a new EIT source you wouldn't have a cache
> and so would still have a lot of DB load if this is what is causing
> Tino's problem.

But the work has to be done. The only thing we can change is the rate of 
insertions.

Janne
-------------- next part --------------
A non-text attachment was scrubbed...
Name: eit_debugging.diff
Type: text/x-diff
Size: 2470 bytes
Desc: not available
Url : http://mythtv.org/pipermail/mythtv-dev/attachments/20060423/12b2fcee/attachment.bin