Mailing List Archive: MythTV: Users

[mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%)

Index | Next | Previous | View Threaded

mtdean at thirdcontact

Nov 19, 2009, 11:33 AM

Post #1 of 10 (964 views)
Permalink

[mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%)

On 11/19/2009 02:09 PM, GXL.nl wrote:
> I have the same thing, also made a bug report (#7604) for it since it
> only happens since I upgraded to 0.22 (and from Ubuntu 8.04 to 9.1).
>
> I have enabled the slow-query-log from MySQL and it shows the
> following query as being slow (13 secs):
>
>
<scheduler query>
> I dug in a little deeper and when I run this query direclt from mysql
> (phpmyadmin) it takes 13 secs but if I remove the two fields from
> oldrecstatus it only takes 3 secs. For this test I have disabled the
> cache with SQL_NO_CACHE and I have only removed the two fields
> (oldrecstatus.recstatus, oldrecstatus.reactivate) but have kept the
> JOIN on that table.
>
> Anyone have an idea?

GXL, I'm assuming you're the person who submitted ticket #7604, right?
Are you seeing any issues or negative effects or UI slowness or anything?

That query does take time. 13s could be normal or may indicate a
problem on your system--depending on a lot of different factors. But,
the UI should be completely disconnected from the scheduler query's
execution, so it shouldn't affect anything even if it is running slow.

Also, can you please provide the (complete) output of:

mythbackend --version

Please describe symptions--what do you see besides just a query being
reported in the slow-query log?

Thanks,
Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

info at gxl

Nov 19, 2009, 11:49 AM

Post #2 of 10 (928 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

Michael T. Dean said the following on 19/11/09 20:33:
> On 11/19/2009 02:09 PM, GXL.nl wrote:
>> I have the same thing, also made a bug report (#7604) for it since it
>> only happens since I upgraded to 0.22 (and from Ubuntu 8.04 to 9.1).
>>
>> I have enabled the slow-query-log from MySQL and it shows the
>> following query as being slow (13 secs):
>>
>>
> <scheduler query>
>> I dug in a little deeper and when I run this query direclt from mysql
>> (phpmyadmin) it takes 13 secs but if I remove the two fields from
>> oldrecstatus it only takes 3 secs. For this test I have disabled the
>> cache with SQL_NO_CACHE and I have only removed the two fields
>> (oldrecstatus.recstatus, oldrecstatus.reactivate) but have kept the
>> JOIN on that table.
>>
>> Anyone have an idea?
>
> GXL, I'm assuming you're the person who submitted ticket #7604, right?
> Are you seeing any issues or negative effects or UI slowness or anything?
>
> That query does take time. 13s could be normal or may indicate a
> problem on your system--depending on a lot of different factors. But,
> the UI should be completely disconnected from the scheduler query's
> execution, so it shouldn't affect anything even if it is running slow.
>
> Also, can you please provide the (complete) output of:
>
> mythbackend --version
>
> Please describe symptions--what do you see besides just a query being
> reported in the slow-query log?
>
> Thanks,
> Mike
> _______________________________________________
> mythtv-users mailing list
> mythtv-users [at] mythtv
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
Hi Mike,

Yes it was me who entered that ticket and I was about to write a mail to the list as (you?) suggested in that ticket when I
saw Ronald having the same issue.

The frontend hangs on the confirmation message. So when I select a recording to delete and I press `Yes Delete` that window
will be shown for about 15-20 secs. It might not sound as a long time but 20 sec waiting is a long time :) That is the only
thing noticable (but I havent really used this version yet).

Since I wanted to find out what was keeping the system busy I saw that mysql was at 100% at that time so I enabled the
slow-query-log.

$ mythbackend --version
Please include all output in bug reports.
MythTV Version : 22594
MythTV Branch : branches/release-0-22-fixes
Network Protocol : 50
Library API : 0.22.20091023-1
QT Version : 4.5.2
Options compiled in:
linux profile using_oss using_alsa using_pulse using_jack using_backend using_dvb using_firewire using_frontend
using_glx_proc_addr_arb using_hdhomerun using_hdpvr using_iptv using_ivtv using_joystick_menu using_libfftw3 using_lirc
using_mheg using_opengl_video using_opengl_vsync using_qtwebkit using_v4l using_x11 using_xrandr using_xv using_xvmc
using_xvmc_vld using_xvmcw using_bindings_perl using_bindings_python using_opengl using_vdpau using_ffmpeg_threads
using_libavc_5_3 using_live using_mheg

If you need more info, please let me know

Nijn
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

dibblah.allan.stirling at googlemail

Nov 19, 2009, 12:18 PM

Post #3 of 10 (928 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

GXL.nl wrote:
> Michael T. Dean said the following on 19/11/09 20:33:
>> On 11/19/2009 02:09 PM, GXL.nl wrote:
>>> I have the same thing, also made a bug report (#7604) for it since it
>>> only happens since I upgraded to 0.22 (and from Ubuntu 8.04 to 9.1).
>>>
>>> I have enabled the slow-query-log from MySQL and it shows the
>>> following query as being slow (13 secs):
>>>
>>>

Can you tell us if you run mythfilldatabase at all, or if
this is an entirely EIT based setup?

Cheers,

Allan.
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

info at gxl

Nov 19, 2009, 12:27 PM

Post #4 of 10 (915 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

Allan Stirling said the following on 19/11/09 21:18:
> GXL.nl wrote:
>> Michael T. Dean said the following on 19/11/09 20:33:
>>> On 11/19/2009 02:09 PM, GXL.nl wrote:
>>>> I have the same thing, also made a bug report (#7604) for it since
>>>> it only happens since I upgraded to 0.22 (and from Ubuntu 8.04 to
>>>> 9.1).
>>>>
>>>> I have enabled the slow-query-log from MySQL and it shows the
>>>> following query as being slow (13 secs):
>>>>
>>>>
>
>
> Can you tell us if you run mythfilldatabase at all, or if this is an
> entirely EIT based setup?
>
> Cheers,
>
> Allan.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users [at] mythtv
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
Yes, I run mythfilldatabase although not daily and since 0.22 it may
have run only 2-3 times.

The log also shows:
Rows_sent: 608 Rows_examined: 2574883
Which seems like a lot of rows examined and might indicate a bad join/index?

Nijn
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mtdean at thirdcontact

Nov 19, 2009, 12:30 PM

Post #5 of 10 (911 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

On 11/19/2009 02:49 PM, GXL.nl wrote:
> Michael T. Dean said the following on 19/11/09 20:33:
>> On 11/19/2009 02:09 PM, GXL.nl wrote:
>>> I have the same thing, also made a bug report (#7604) for it since
>>> it only happens since I upgraded to 0.22 (and from Ubuntu 8.04 to 9.1).
>>>
>>> I have enabled the slow-query-log from MySQL and it shows the
>>> following query as being slow (13 secs):
>>>
>>>
>> <scheduler query>
>>> I dug in a little deeper and when I run this query direclt from
>>> mysql (phpmyadmin) it takes 13 secs but if I remove the two fields
>>> from oldrecstatus it only takes 3 secs. For this test I have
>>> disabled the cache with SQL_NO_CACHE and I have only removed the two
>>> fields (oldrecstatus.recstatus, oldrecstatus.reactivate) but have
>>> kept the JOIN on that table.
>>>
>>> Anyone have an idea?
>>
>> GXL, I'm assuming you're the person who submitted ticket #7604,
>> right? Are you seeing any issues or negative effects or UI slowness
>> or anything?
>>
>> That query does take time. 13s could be normal or may indicate a
>> problem on your system--depending on a lot of different factors.
>> But, the UI should be completely disconnected from the scheduler
>> query's execution, so it shouldn't affect anything even if it is
>> running slow.
>>
>> Also, can you please provide the (complete) output of:
>>
>> mythbackend --version
>>
>> Please describe symptions--what do you see besides just a query being
>> reported in the slow-query log?
> Yes it was me who entered that ticket and I was about to write a mail
> to the list as (you?) suggested in that ticket when I saw Ronald
> having the same issue.
>
> The frontend hangs on the confirmation message. So when I select a
> recording to delete and I press `Yes Delete` that window will be shown
> for about 15-20 secs. It might not sound as a long time but 20 sec
> waiting is a long time :) That is the only thing noticable (but I
> havent really used this version yet).

Can you run and paste the output of:

mysql -umythtv -p mythconverg -e 'SHOW INDEXES FROM oldrecorded;'

Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mtdean at thirdcontact

Nov 19, 2009, 12:38 PM

Post #6 of 10 (920 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

On 11/19/2009 03:27 PM, GXL.nl wrote:
> Yes, I run mythfilldatabase although not daily and since 0.22 it may
> have run only 2-3 times.
>
> The log also shows:
> Rows_sent: 608 Rows_examined: 2574883
> Which seems like a lot of rows examined and might indicate a bad
> join/index?

mysql -umythtv -p mythconverg -e

cat << "EOF" | mysql -umythtv -p mythconverg
SELECT COUNT(*) FROM oldrecorded;
SELECT NOW();
SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
EOF

Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

info at gxl

Nov 19, 2009, 12:46 PM

Post #7 of 10 (905 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

Michael T. Dean said the following on 19/11/09 21:30:
> On 11/19/2009 02:49 PM, GXL.nl wrote:
>> Michael T. Dean said the following on 19/11/09 20:33:
>>> On 11/19/2009 02:09 PM, GXL.nl wrote:
>>>> I have the same thing, also made a bug report (#7604) for it since
>>>> it only happens since I upgraded to 0.22 (and from Ubuntu 8.04 to
>>>> 9.1).
>>>>
>>>> I have enabled the slow-query-log from MySQL and it shows the
>>>> following query as being slow (13 secs):
>>>>
>>>>
>>> <scheduler query>
>>>> I dug in a little deeper and when I run this query direclt from
>>>> mysql (phpmyadmin) it takes 13 secs but if I remove the two fields
>>>> from oldrecstatus it only takes 3 secs. For this test I have
>>>> disabled the cache with SQL_NO_CACHE and I have only removed the
>>>> two fields (oldrecstatus.recstatus, oldrecstatus.reactivate) but
>>>> have kept the JOIN on that table.
>>>>
>>>> Anyone have an idea?
>>>
>>> GXL, I'm assuming you're the person who submitted ticket #7604,
>>> right? Are you seeing any issues or negative effects or UI slowness
>>> or anything?
>>>
>>> That query does take time. 13s could be normal or may indicate a
>>> problem on your system--depending on a lot of different factors.
>>> But, the UI should be completely disconnected from the scheduler
>>> query's execution, so it shouldn't affect anything even if it is
>>> running slow.
>>>
>>> Also, can you please provide the (complete) output of:
>>>
>>> mythbackend --version
>>>
>>> Please describe symptions--what do you see besides just a query
>>> being reported in the slow-query log?
>> Yes it was me who entered that ticket and I was about to write a mail
>> to the list as (you?) suggested in that ticket when I saw Ronald
>> having the same issue.
>>
>> The frontend hangs on the confirmation message. So when I select a
>> recording to delete and I press `Yes Delete` that window will be
>> shown for about 15-20 secs. It might not sound as a long time but 20
>> sec waiting is a long time :) That is the only thing noticable (but I
>> havent really used this version yet).
>
> Can you run and paste the output of:
>
> mysql -umythtv -p mythconverg -e 'SHOW INDEXES FROM oldrecorded;'
>
> Mike
> _______________________________________________
> mythtv-users mailing list
> mythtv-users [at] mythtv
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
mysql -umythtv -p mythconverg -e 'SHOW INDEXES FROM oldrecorded;'
Enter password:
> +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name
> | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
> Comment |
> +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | oldrecorded | 0 | PRIMARY | 1 | station
> | A | 25 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 0 | PRIMARY | 2 | starttime
> | A | 4226 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 0 | PRIMARY | 3 | title
> | A | 4226 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | endtime | 1 | endtime
> | A | 4226 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | title | 1 | title
> | A | 183 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | seriesid | 1 | seriesid
> | A | 201 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | programid | 1 | programid
> | A | 37 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | recordid | 1 | recordid
> | A | 211 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | recstatus | 1 | recstatus
> | A | 8 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | recstatus | 2 | programid
> | A | 44 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | recstatus | 3 | seriesid
> | A | 211 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | recstatus_2 | 1 | recstatus
> | A | 8 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | recstatus_2 | 2 | title
> | A | 201 | NULL | NULL | | BTREE
> | |
> | oldrecorded | 1 | recstatus_2 | 3 | subtitle
> | A | 1408 | NULL | NULL | | BTREE
> | |
> +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
Nijn
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

info at gxl

Nov 19, 2009, 12:58 PM

Post #8 of 10 (918 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

Michael T. Dean said the following on 19/11/09 21:38:
> On 11/19/2009 03:27 PM, GXL.nl wrote:
>> Yes, I run mythfilldatabase although not daily and since 0.22 it may
>> have run only 2-3 times.
>>
>> The log also shows:
>> Rows_sent: 608 Rows_examined: 2574883
>> Which seems like a lot of rows examined and might indicate a bad
>> join/index?
>
> mysql -umythtv -p mythconverg -e
>
> cat << "EOF" | mysql -umythtv -p mythconverg
> SELECT COUNT(*) FROM oldrecorded;
> SELECT NOW();
> SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
> EOF
>
> Mike
> _______________________________________________
> mythtv-users mailing list
> mythtv-users [at] mythtv
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
$ cat << "EOF" | mysql -umythtv -p mythconverg
> SELECT COUNT(*) FROM oldrecorded;
> SELECT NOW();
> SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
> EOF
Enter password:
COUNT(*)
4226
NOW()
2009-11-19 21:57:21
MIN(starttime)
2009-11-08 19:40:00

_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mtdean at thirdcontact

Nov 19, 2009, 1:45 PM

Post #9 of 10 (915 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

On 11/19/2009 03:58 PM, GXL.nl wrote:
> Michael T. Dean said the following on 19/11/09 21:38:
>> On 11/19/2009 03:27 PM, GXL.nl wrote:
>>> Yes, I run mythfilldatabase although not daily and since 0.22 it may
>>> have run only 2-3 times.
>>>
>>> The log also shows:
>>> Rows_sent: 608 Rows_examined: 2574883
>>> Which seems like a lot of rows examined and might indicate a bad
>>> join/index?
>> mysql -umythtv -p mythconverg -e
>>
>> cat << "EOF" | mysql -umythtv -p mythconverg
>> SELECT COUNT(*) FROM oldrecorded;
>> SELECT NOW();
>> SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
>> EOF
> $ cat << "EOF" | mysql -umythtv -p mythconverg
> > SELECT COUNT(*) FROM oldrecorded;
> > SELECT NOW();
> > SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
> > EOF
> Enter password:
> COUNT(*)
> 4226
> NOW()
> 2009-11-19 21:57:21
> MIN(starttime)
> 2009-11-08 19:40:00

That and the reply with the indices shows that your oldrecorded table
(the one aliased as oldrecstatus in the query that you saw was slow) is
in good shape. You have all the indices you should have and it's being
cleaned up properly by the backend (so I'll assume the other tables get
cleaned up just as well).

Therefore, I'm guessing you either just have a) a huge number of
programs in your listings, b) a huge number of recording
rules/schedules, c) a huge number of matches on your rules, d) an
underpowered and/or frequency-scaled CPU, or e) a poor-performing MySQL
configuration.

cat << "EOF" | mysql -umythtv -p mythconverg
SELECT COUNT(*) FROM program;
SELECT COUNT(*) FROM record;
SELECT COUNT(*) FROM recordmatch;
EOF

Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

info at gxl

Nov 19, 2009, 2:04 PM

Post #10 of 10 (917 views)
Permalink

Re: [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%) [In reply to]

Michael T. Dean said the following on 19/11/09 22:45:
> On 11/19/2009 03:58 PM, GXL.nl wrote:
>> Michael T. Dean said the following on 19/11/09 21:38:
>>> On 11/19/2009 03:27 PM, GXL.nl wrote:
>>>> Yes, I run mythfilldatabase although not daily and since 0.22 it may
>>>> have run only 2-3 times.
>>>>
>>>> The log also shows:
>>>> Rows_sent: 608 Rows_examined: 2574883
>>>> Which seems like a lot of rows examined and might indicate a bad
>>>> join/index?
>>> mysql -umythtv -p mythconverg -e
>>>
>>> cat << "EOF" | mysql -umythtv -p mythconverg
>>> SELECT COUNT(*) FROM oldrecorded;
>>> SELECT NOW();
>>> SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
>>> EOF
>> $ cat << "EOF" | mysql -umythtv -p mythconverg
>> > SELECT COUNT(*) FROM oldrecorded;
>> > SELECT NOW();
>> > SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
>> > EOF
>> Enter password:
>> COUNT(*)
>> 4226
>> NOW()
>> 2009-11-19 21:57:21
>> MIN(starttime)
>> 2009-11-08 19:40:00
>
> That and the reply with the indices shows that your oldrecorded table
> (the one aliased as oldrecstatus in the query that you saw was slow) is
> in good shape. You have all the indices you should have and it's being
> cleaned up properly by the backend (so I'll assume the other tables get
> cleaned up just as well).
>
> Therefore, I'm guessing you either just have a) a huge number of
> programs in your listings, b) a huge number of recording
> rules/schedules, c) a huge number of matches on your rules, d) an
> underpowered and/or frequency-scaled CPU, or e) a poor-performing MySQL
> configuration.
>
> cat << "EOF" | mysql -umythtv -p mythconverg
> SELECT COUNT(*) FROM program;
> SELECT COUNT(*) FROM record;
> SELECT COUNT(*) FROM recordmatch;
> EOF
>
> Mike
> _______________________________________________
> mythtv-users mailing list
> mythtv-users [at] mythtv
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>

I ran the query on two machines, one of them Quad Q9550 with 6GB and even then it takes 10 sec.

cat << "EOF" | mysql -umythtv -p mythconverg
> SELECT COUNT(*) FROM program;
> SELECT COUNT(*) FROM record;
> SELECT COUNT(*) FROM recordmatch;
> EOF
Enter password:
COUNT(*)
42261
COUNT(*)
149
COUNT(*)
606
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads