Hi folks,
I recently upgraded my MythTV box to Ubuntu 22.04 / MythTV 32 (from
Ubuntu 20.04 / MythTV 31) mostly to get support for a new WiFi card, and
for the most part things went fine. With the new setup, my prior issues
with Nvidia picture quality and lirc are now moot, as it's living a
backend-only life now.
However, one thing that I noticed is that using mythweb to search for
something (using the search box) is abysmally slow. mysqld has the CPU
pegged, and my web browser has been spinning for about 15 MINUTES and
counting, and it's still going.
I'm sure I've used mythweb search before and had it come back with
results within 10 seconds or so. But given the multiple moving parts
with this recent upgrade, it's hard to track down what the culprit might be.
I did a web search for mythtv slow mysql, and all I found was
<https://www.mythtv.org/wiki/Tune_MySQL>, which as with seemingly many
things in the wiki is woefully out of date. Specifically the sample
mythtv.cnf file that is provided has about a half-dozen options that
mysqld no longer recognizes and will refuse to start if they are set.
A glance at mytop shows that the query it's slowly executing is:
SELECT DISTINCT program.*, UNIX_TIMESTAMP(program.starttime) AS
starttime_unix, UNIX_TIMESTAMP(program.endtime) AS endtime_unix,
IFNULL(pr1.`system`, "") AS rater, IFNULL(pr1.rating, "") AS rating,
channel.callsign, channel.channum FROM program USE INDEX (id_start_end)
LEFT JOIN programrating pr1 on program.chanid = pr1.chanid and
program.starttime = pr1.starttime LEFT OUTER JOIN programrating pr2 on
program.chanid = pr2.chanid and program.starttime = pr2.starttime and
pr2.`system` > pr1.`system` LEFT JOIN channel on channel.chanid =
program.chanid LEFT JOIN credits on program.chanid = credits.chanid and
program.starttime = credits.starttime LEFT JOIN people USING (person)
WHERE pr2.`system` is null and program.chanid IN
(2002,20003,2003,2004,2005,2007,2009,3918,2013,2016,2017,2018,2019,2021,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2044,2045,2046,2047,20068,2070,20071,2072,2074,2075,20083,20084,20085,2086,2087,2088,2089,2092,20093,2096,2097,2105,2106,20107,2110,2111,20113,2115,3874,20121,2122,2123,2125,2126,20127,2128,2129,2131,2132,2134,2142,20144,2149,2158,20159,2161,2163,20167,2168,20182,2183,20184,2185,20188,2194,2210,20212,20213,20214,3964,2217,20218,20219,20220,20222,20225,2230,2231,2234,2235,20238,2239,2240,3957,20246,2251,20253,20254,2255,2256,2259,20260,20263,20269,2270,20271,2272,20277,2281,2285,2286,20287,21831,20291,2292,2295,20315,20316,3900,2332,20335,2339,2340,2341,2342,2343,2344,2345,2346,2347,2348,2349,2350,2351,2352,2353,2354,2355,2356,2357,2358,2359,2360,2361,2362,2365,2366,2367,2369,2370,2373,2374,2375,2376,2377,2378,2379,2380,2385,2386,2387,2388,2390,2391,20395,20445,21832,2455,2461,2465,2466,2467,3965,2470,3962,20472,2473,2474,3863,3967,20482,38
82,3858,3867,2503,3862,3857,2507,2510,2511,2513,2516,3868,20529,2550,20551,2552,20554,3960,20568,2570,20571,2572,2574,20575,20576,20581,20583,20584,20585,20586,20587,2588,2589,2593,20594,20595,2596,20597,20599,3865,3856,3870,20605,20609,20610,2611,20612,20613,20614,2615,2617,2618,20620,20621,3854,3866,2624,3958,2628,2629,2630,20631,2632,2633,20634,20635,20639,3852,20641,20644,20645,2649,2650,2651,2652,2653,2654,2656,20657,20658,2662,20662,20663,2664,20665,20667,2668,2670,2674,2676,20677,20678,2679,2680,20681,2683,20684,2685,20688,20689,2690,2692,2694,2695,2696,20697,20699,2710,2711,20715,2717,20720,20721,20726,20730,2731,2734,20735,20737,20738,2739,2740,2741,20743,20744,20746,2752,20754,2756,2757,2763,2765,2770,2771,2772,20773,20774,3963,20776,20777,2780,2781,2786,20787,2789,2793,20794,20795,2797,3884,3959,3864,20806,20810,20812,20813,20814,20815,3907,20818,20830,20831,20833,20834,20835,1840,1842,1844,1845,1846,1847,1848,1849,1850,1852,1854,1856,1858,20860,1861,1862,2865,2867,20868,
2869,2873,2885,2887,20895,20896,21000,21001,21002,21009,3010,21450,21451,21452,21453,21454,3455,3456,3457,3458,3459,3461,3462,3463,3464,3465,3466,3467,3468,3469,3470,3471,3472,3473,3474,21496,21498,21499,3500,21501,21502,21503,21505,21506,21507,21508,21509,3510,21511,21512,21513,21515,21516,21517,3520,3521,21524,21525,21528,21533,3534,21535,21536,21537,21538,21539,3541,3542,3543,3544,3545,3546,3547,21548,21549,21550,21551,21552,21560,21561,21562,3564,3565,21566,21567,21579,3580,21581,21583,21584,21585,21586,21587,21588,21590,3600,21601,3602,21603,3621,3622,3623,21624,3625,3640,21641,21643,3645,3646,21648,3660,3661,21662,21663,21664,21665,21666,21670,21680,21681,21683,21685,21686,21687,21688,21689,21691,21692,21693,21700,3966,21702,21703,21704,21705,3707,21710,21711,21721,21724,21725,3726,3728,3729,21740,21741,21750,3751,21752,21753,21754,21755,21756,21757,21758,21759,21760,21761,21762,21763,21764,21765,21768,21769,21770,21771,21833,21774,21776,21777,21778,21779,21780,21781,21782,217
83,21784,21787,21788,21789,21790,21791,21792,21793,21794,21795,21796,21797,3800,21801,3802,3803,3804,3805,3806,3807,3808,3809,3810,3811,3812,3813,3814,3815,3816,3817,3818,3819,3820,3821,21822,21823,3824,3825,3826,3827,21828,3829,21830,3831,3832,3833,3834,3835,3836,3837,3838,3839,3840,3841,3842,3843,3844,3845,3846,3847,3848,3849)
AND (program.endtime > FROM_UNIXTIME('1695935481') AND program.starttime
< FROM_UNIXTIME('1698527481') AND program.starttime != program.endtime)
AND (program.title LIKE '%SearchTerm%' OR program.subtitle LIKE
'%SearchTerm%' OR program.description LIKE '%SearchTerm%' OR
program.category LIKE '%SearchTerm%' OR people.name LIKE '%SearchTerm%')
AND (program.endtime >= NOW()) ORDER BY program.starttime
Any tips as to what my problem might be? I'd ideally like to avoid
restoring from backup to 20.04 to confirm if my memory was correct about
being able to use mythweb search.
Thanks!
-WD
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
I recently upgraded my MythTV box to Ubuntu 22.04 / MythTV 32 (from
Ubuntu 20.04 / MythTV 31) mostly to get support for a new WiFi card, and
for the most part things went fine. With the new setup, my prior issues
with Nvidia picture quality and lirc are now moot, as it's living a
backend-only life now.
However, one thing that I noticed is that using mythweb to search for
something (using the search box) is abysmally slow. mysqld has the CPU
pegged, and my web browser has been spinning for about 15 MINUTES and
counting, and it's still going.
I'm sure I've used mythweb search before and had it come back with
results within 10 seconds or so. But given the multiple moving parts
with this recent upgrade, it's hard to track down what the culprit might be.
I did a web search for mythtv slow mysql, and all I found was
<https://www.mythtv.org/wiki/Tune_MySQL>, which as with seemingly many
things in the wiki is woefully out of date. Specifically the sample
mythtv.cnf file that is provided has about a half-dozen options that
mysqld no longer recognizes and will refuse to start if they are set.
A glance at mytop shows that the query it's slowly executing is:
SELECT DISTINCT program.*, UNIX_TIMESTAMP(program.starttime) AS
starttime_unix, UNIX_TIMESTAMP(program.endtime) AS endtime_unix,
IFNULL(pr1.`system`, "") AS rater, IFNULL(pr1.rating, "") AS rating,
channel.callsign, channel.channum FROM program USE INDEX (id_start_end)
LEFT JOIN programrating pr1 on program.chanid = pr1.chanid and
program.starttime = pr1.starttime LEFT OUTER JOIN programrating pr2 on
program.chanid = pr2.chanid and program.starttime = pr2.starttime and
pr2.`system` > pr1.`system` LEFT JOIN channel on channel.chanid =
program.chanid LEFT JOIN credits on program.chanid = credits.chanid and
program.starttime = credits.starttime LEFT JOIN people USING (person)
WHERE pr2.`system` is null and program.chanid IN
(2002,20003,2003,2004,2005,2007,2009,3918,2013,2016,2017,2018,2019,2021,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2044,2045,2046,2047,20068,2070,20071,2072,2074,2075,20083,20084,20085,2086,2087,2088,2089,2092,20093,2096,2097,2105,2106,20107,2110,2111,20113,2115,3874,20121,2122,2123,2125,2126,20127,2128,2129,2131,2132,2134,2142,20144,2149,2158,20159,2161,2163,20167,2168,20182,2183,20184,2185,20188,2194,2210,20212,20213,20214,3964,2217,20218,20219,20220,20222,20225,2230,2231,2234,2235,20238,2239,2240,3957,20246,2251,20253,20254,2255,2256,2259,20260,20263,20269,2270,20271,2272,20277,2281,2285,2286,20287,21831,20291,2292,2295,20315,20316,3900,2332,20335,2339,2340,2341,2342,2343,2344,2345,2346,2347,2348,2349,2350,2351,2352,2353,2354,2355,2356,2357,2358,2359,2360,2361,2362,2365,2366,2367,2369,2370,2373,2374,2375,2376,2377,2378,2379,2380,2385,2386,2387,2388,2390,2391,20395,20445,21832,2455,2461,2465,2466,2467,3965,2470,3962,20472,2473,2474,3863,3967,20482,38
82,3858,3867,2503,3862,3857,2507,2510,2511,2513,2516,3868,20529,2550,20551,2552,20554,3960,20568,2570,20571,2572,2574,20575,20576,20581,20583,20584,20585,20586,20587,2588,2589,2593,20594,20595,2596,20597,20599,3865,3856,3870,20605,20609,20610,2611,20612,20613,20614,2615,2617,2618,20620,20621,3854,3866,2624,3958,2628,2629,2630,20631,2632,2633,20634,20635,20639,3852,20641,20644,20645,2649,2650,2651,2652,2653,2654,2656,20657,20658,2662,20662,20663,2664,20665,20667,2668,2670,2674,2676,20677,20678,2679,2680,20681,2683,20684,2685,20688,20689,2690,2692,2694,2695,2696,20697,20699,2710,2711,20715,2717,20720,20721,20726,20730,2731,2734,20735,20737,20738,2739,2740,2741,20743,20744,20746,2752,20754,2756,2757,2763,2765,2770,2771,2772,20773,20774,3963,20776,20777,2780,2781,2786,20787,2789,2793,20794,20795,2797,3884,3959,3864,20806,20810,20812,20813,20814,20815,3907,20818,20830,20831,20833,20834,20835,1840,1842,1844,1845,1846,1847,1848,1849,1850,1852,1854,1856,1858,20860,1861,1862,2865,2867,20868,
2869,2873,2885,2887,20895,20896,21000,21001,21002,21009,3010,21450,21451,21452,21453,21454,3455,3456,3457,3458,3459,3461,3462,3463,3464,3465,3466,3467,3468,3469,3470,3471,3472,3473,3474,21496,21498,21499,3500,21501,21502,21503,21505,21506,21507,21508,21509,3510,21511,21512,21513,21515,21516,21517,3520,3521,21524,21525,21528,21533,3534,21535,21536,21537,21538,21539,3541,3542,3543,3544,3545,3546,3547,21548,21549,21550,21551,21552,21560,21561,21562,3564,3565,21566,21567,21579,3580,21581,21583,21584,21585,21586,21587,21588,21590,3600,21601,3602,21603,3621,3622,3623,21624,3625,3640,21641,21643,3645,3646,21648,3660,3661,21662,21663,21664,21665,21666,21670,21680,21681,21683,21685,21686,21687,21688,21689,21691,21692,21693,21700,3966,21702,21703,21704,21705,3707,21710,21711,21721,21724,21725,3726,3728,3729,21740,21741,21750,3751,21752,21753,21754,21755,21756,21757,21758,21759,21760,21761,21762,21763,21764,21765,21768,21769,21770,21771,21833,21774,21776,21777,21778,21779,21780,21781,21782,217
83,21784,21787,21788,21789,21790,21791,21792,21793,21794,21795,21796,21797,3800,21801,3802,3803,3804,3805,3806,3807,3808,3809,3810,3811,3812,3813,3814,3815,3816,3817,3818,3819,3820,3821,21822,21823,3824,3825,3826,3827,21828,3829,21830,3831,3832,3833,3834,3835,3836,3837,3838,3839,3840,3841,3842,3843,3844,3845,3846,3847,3848,3849)
AND (program.endtime > FROM_UNIXTIME('1695935481') AND program.starttime
< FROM_UNIXTIME('1698527481') AND program.starttime != program.endtime)
AND (program.title LIKE '%SearchTerm%' OR program.subtitle LIKE
'%SearchTerm%' OR program.description LIKE '%SearchTerm%' OR
program.category LIKE '%SearchTerm%' OR people.name LIKE '%SearchTerm%')
AND (program.endtime >= NOW()) ORDER BY program.starttime
Any tips as to what my problem might be? I'd ideally like to avoid
restoring from backup to 20.04 to confirm if my memory was correct about
being able to use mythweb search.
Thanks!
-WD
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org