mysqld.exe CPU spikes whenever TV Server is running (1 Viewer)

[Lehmden]

Hi.
I've got the same spikes. With MySQL those spikes are at 100% and leads to freezes of MP. That's why I'm using MSSQL lately. There are those spikes are "only" up to 50-70% so the HTPC is usable.. As I have lots of schedules (series recordings, I think those are about 60-70 active schedules at a time) I can see now where it comes from... And I only have EPG Data for about 40 Channels...

 

[jameson_uk]

Can you log what is happening.
Within MySQL execute

SET GLOBAL general_log = 'ON';

this will then log SQL commands (unless you have changed the location it will be C:\ProgramData\MySQL\MySQL Server 5.1\data\hostname.log)
leave this for a while and try and note the time of the spikes so this can tied up with the queries.
Turn off logging afterwards

SET GLOBAL general_log = 'OFF';

so we can check the logs.

I think this is a feature of the scheduling engine polling the database and not being event driven but just want to check the SQL

 

[DragonQ]

About to go out for the rugby but will try that this evening or tomorrow for you. If scheduling polling is the problem, a relatively simple solution would be to check the database less often - if I'm padding recordings with 3 minutes of pre-record, it surely isn't necessary to check every 5-10 seconds for a schedule.

But yeah, being event driven would be better, or even just if the polling could be optimized.

 

[Holzi]

Maybe MySQL 5.6 already solves the problem?

 

[Lehmden]

Maybe MySQL 5.6 already solves the problem?

Possible, but I don't believe it. I've tested MySQL 5.1 and 5.5 and MSSQL 2005, 2008 and 2010 as Database Server. (MySQL 5.6 was too new as I done this tests) The spikes are the lowest with MSSQL 2005 Express, but not too much difference at all. So I believe it's more TV Server related than SQL Engine related...

 

[Lotsofjazz]

I do not use any schedules (I wish I could but with windows 8 TVserver doesnt work properly when waking up from sleep), but also experience MySql spikes.. Solved it a bit by setting the refresh interval for updating the database to 4 hours.. also set to update EPG only when running idle with a time out of 3 minutes, so when closing Mediaportal, 3 minutes after that, it updates the database.. or when starting the HTPC, 3 minutes up and running it starts updating the database..

I have now changed the EPG to only update the EPG for the channels I watch (made a group for them) and that are only a handfull, lets see what that does.

 

[tourettes]

About to go out for the rugby but will try that this evening or tomorrow for you. If scheduling polling is the problem, a relatively simple solution would be to check the database less often - if I'm padding recordings with 3 minutes of pre-record, it surely isn't necessary to check every 5-10 seconds for a schedule.

Even polling once per hour wouldn't solve the issue - you sill will receive those CPU spikes and those can cause dropped frames in worst case.

 

[Owlsroost]

You could try using something like Process Lasso to lower the priority of the MySQL processes - at least that should make it less likely to interfere with playback (I've done this on my HTPC for ages - otherwise the idle EPG grabber causes dropped frames etc, even though I use MS SQL Express 2005).

 

[DragonQ]

Can you log what is happening.
Within MySQL execute

SET GLOBAL general_log = 'ON';

this will then log SQL commands (unless you have changed the location it will be C:\ProgramData\MySQL\MySQL Server 5.1\data\hostname.log)
leave this for a while and try and note the time of the spikes so this can tied up with the queries.
Turn off logging afterwards

SET GLOBAL general_log = 'OFF';

so we can check the logs.

I think this is a feature of the scheduling engine polling the database and not being event driven but just want to check the SQL

Log file and noted CPU usage spike times attached!

 

[megahorst]

I'm using MySQL 5.6 and do not see the spikes ...