Very slow EPG scrolling (1 Viewer)

[mm1352000]

Hmmmm...

To be honest I'm drawing a blank.
Here's a few things you could try though to possibly give you more information:
- try deleting all the EPG from the database ("refresh DVB EPG" in the manual control section of TV Server configuration if you use the built-in grabber)
- make a channel group with only a few channels and see if the number of channels in the group affects the load time

mm

 

[ianc]

Problem sorted with a fresh install od TVServer. Complicated by the fact that I have M***I configured.

For future reference - Where is the configuration for TVService stored?

I copied the whole of the "Program Data\Team MediaPortal\MediaPortal TVService" and "Program files\Team MediaPortal\MediaPortal TVService" directories but then couldn't find a confir file in them

 

[mm1352000]

Hi again

Problem sorted with a fresh install od TVServer.

As much as I'm glad you have managed to sort it, I must say that I'm a little disappointed that you chose not to do those tests. Would have taken only a few minutes of your time and could have saved you a reinstall. It also means that we have no information about the problem and can't offer insight to anybody else who encounters this in the future. Anyhow, I respect your decision.

For future reference - Where is the configuration for TVService stored?

99% in the database. That folder contains the remaining 1%.

mm

 

[ianc]

mm,
Sorry - I should have explained further.
- I did delete the EPG (I'm using WebEPG). Still slow
- Cleaned out unused tv and radio stations (down from >1000 to <200). Still slow
- Made smallish group (less than 50) still slow
- Also looked around in the DB and all tables looked like they had about the right number or rows (at least in terms of order of magnitude)

I suspect an index in the db went stale and stopped getting used. I didn't know MySQL to be able to further diagnose.

Still doesn't confirm root cause but at least I've a reasonable idea "why".

Also - I feel better that I didn't just miss the config file when reinstalling.


Cheers and thanks for the help.

Ian

 

[ms1964]

Hi ,

I had the same problem running MP 1.2.3 with MySQL database.

First thing I found was that I had corrupt tables in the MySQL database (one of the tables was the program table), which I could fix with the following commands:
net stop tvservice
c:
cd \program files\mysql\mysql server 5.1\bin
mysqlcheck mptvdb --auto-repair -e -u root -p --password=MediaPortal
net start tvservice

Despite the repair EPG scrolling was still slow, so I decided to switch on the slow queries logging in MySQL.
Stop tv-service and mysql service, then edit file my.ini and add the following lines:
log-slow-queries
long_query_time=0.3
Start mysql and tv-service and use the EPG in MediaPortal then you will find the queries which execution times bigger than 0.3 s in the file c:\ProgramData\MySQL\MySQL Server 5.1\data\htpc-slow.log

The following kind of queries were slow:
SELECT * FROM Program WHERE ((EndTime > '2012-11-10 18:00:00' and EndTime < '2012-11-10 18:59:00') OR (StartTime >= '2012-11-10 18:00:00' and StartTime <= '2012-11-10 18:59:00') OR (StartTime <= '2012-11-10 18:00:00' and EndTime >= '2012-11-10 18:59:00')) AND (idChannel=60 or idChannel=3 or idChannel=9 or idChannel=11 or idChannel=57 or idChannel=61 or idChannel=12) ORDER BY startTime;

Query time was about 0.6 s, which is slow.

My next step was to check the access plan of MySQL for this statement. The explain command showed that an index named "idProgramBeginEnd" is used, so i decided to drop and rebuild this index at the mysql console:
mysql> alter table program drop index idProgramBeginEnd;
mysql> create unique index idProgramBeginEnd ON program(idChannel,startTime,endTime);

Now the query times dropped to 0.25 s, which is still not really fast, but EPG scrolling was much better.
My interpretation is that the repair fixed the corrupt tabe, but not the index.


Next thing I tried was to run the queries without the mentioned index and guess what...it was even faster!!!
Now the query times dropped to 0.1 s and EPG scrolling is pretty good now. Maybe even better than before.

I got the feeling that the index "idProgramBeginEnd" does not really help to speed up the queries which are produced by the EPG.

Cheers,
ms

 

[snowball]

@ms1964
that helped many thx. i had already autorepair on, but dropping the index solved my EPG scrolling problem!!

 

[ms1964]

Glad I could help