home
products
contribute
download
documentation
forum
Home
Forums
New posts
Search forums
What's new
New posts
All posts
Latest activity
Members
Registered members
Current visitors
Donate
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Search titles only
By:
Menu
Log in
Register
Navigation
Install the app
Install
More options
Contact us
Close Menu
Forums
MediaPortal 1
Support
General Support
Very slow EPG scrolling
Contact us
RSS
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="ms1964" data-source="post: 930820" data-attributes="member: 112455"><p>Hi ,</p><p> </p><p>I had the same problem running MP 1.2.3 with MySQL database.</p><p> </p><p>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:</p><p>net stop tvservice</p><p>c:</p><p>cd \program files\mysql\mysql server 5.1\bin</p><p>mysqlcheck mptvdb --auto-repair -e -u root -p --password=MediaPortal</p><p>net start tvservice</p><p> </p><p>Despite the repair EPG scrolling was still slow, so I decided to switch on the slow queries logging in MySQL.</p><p>Stop tv-service and mysql service, then edit file my.ini and add the following lines:</p><p>log-slow-queries</p><p>long_query_time=0.3</p><p>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</p><p> </p><p>The following kind of queries were slow:</p><p>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;</p><p> </p><p>Query time was about 0.6 s, which is slow.</p><p> </p><p>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:</p><p>mysql> alter table program drop index idProgramBeginEnd;</p><p>mysql> create unique index idProgramBeginEnd ON program(idChannel,startTime,endTime);</p><p> </p><p>Now the query times dropped to 0.25 s, which is still not really fast, but EPG scrolling was much better.</p><p>My interpretation is that the repair fixed the corrupt tabe, but not the index.</p><p> </p><p> </p><p>Next thing I tried was to run the queries without the mentioned index and guess what...it was even faster!!!</p><p>Now the query times dropped to 0.1 s and EPG scrolling is pretty good now. Maybe even better than before.</p><p> </p><p>I got the feeling that the index "idProgramBeginEnd" does not really help to speed up the queries which are produced by the EPG.</p><p> </p><p>Cheers,</p><p>ms</p></blockquote><p></p>
[QUOTE="ms1964, post: 930820, member: 112455"] 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 [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 1
Support
General Support
Very slow EPG scrolling
Contact us
RSS
Top
Bottom