[mythtv-users] MythTV mysql database is MUCH slower after upgrading from Ubuntu 14.04 to 16.04

[Will Dormann]

On 12/7/18 9:20 PM, Allen Edwards wrote
> I "updated" to 16.04 not long ago.  I just tried to search for a
> show in listings on Mythweb, which is what I assume you are doing,
> and it takes about 2 seconds.  I did a clean install of 16.04 and had
> to increase my memory to 6GB on my old AMD 5400 processor.  The
> added memory really sped things up for me.


The system in question has 8GB of RAM, and a pretty modern (albeit
loe-end celeron) 3.2GHz dual-core CPU.

I just checked searching for an arbitrary term ("burger") in mythweb,
and it took over 1 minute 40 seconds with the CPU pegged during that
time.   Before I upgraded from Ubuntu 14.04 to 16.04, that same sort of
query would take a few seconds.  No more than 5 based on memory.

Looking at the slow-queries.log file, I see the following:

# Time: 2018-12-08T02:53:49.770090Z
# User at Host: mythtv[mythtv] @ localhost [127.0.0.1]  Id:    16
# Query_time: 101.036997  Lock_time: 0.000510 Rows_sent: 452
Rows_examined: 91152759
SET timestamp=1544237629;
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
(1002,1003,1004,1005,1007,1009,1010,1011,1013,1016,1017,1018,1019,1021,1022,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1044,1045,1046,1047,1049,1050,1051,1052,1053,1054,3903,1061,1068,1070,1072,1073,1074,1075,1076,1083,1084,3932,1085,1086,1087,1088,1089,1090,1091,1092,1093,1096,1097,1100,1101,1102,1103,1104,1105,1106,3897,1107,1108,1109,1110,1111,1112,1114,1115,1116,1117,1118,3872,1119,1120,1121,1122,1123,1124,3880,1125,1126,1127,1128,1129,1130,1131,1132,1134,1139,1140,1141,1142,1144,1145,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,3928,1161,1162,1163,1164,1165,1167,1168,1169,1170,1172,3892,3875,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1188,1189,1194,1196,1198,1199,1210,1211,3934,1215,1216,1217,1218,1219,1220,1221,1224,1228,1229,1230,1231,1232,1233,1234,1235,1239,1240,1241,1245,3894,1247,1250,1251,1252,1253,1254,1255,1256,2257,1259,1260,1263,1270,1272,1273,1275,1281,1285,1286,1288,1289,1292,1293,1295,1297,1303,1317,3899,1320,1321,1322,1323,1324,1325,1326,1327,1328,1330,1331,1332,1339,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1385,1386,1387,1388,1390,1391,1400,1401,1402,1403,1404,1406,1407,1408,1409,1410,1411,1440,1441,3916,1455,1461,1462,1463,1465,1466,1467,1468,1469,1470,1471,1473,1474,1475,1476,1483,1491,1492,1500,1502,1503,1504,1505,1507,1510,1511,1513,1516,1521,1550,1551,1552,1553,1554,1558,1568,1570,1571,1572,1573,1574,1576,1583,1584,1588,1589,1590,1593,1596,1597,1599,1600,1601,1602,1603,1615,1617,1618,1620,1621,1622,1623,1628,1629,1630,1631,1632,1633,1635,1639,1640,1641,1644,1649,1650,1651,1656,1657,1662,1664,1665,1667,1668,1669,1670,1674,1676,1680,1681,1683,1684,1685,1690,1691,1692,1694,1695,1696,1697,1699,1710,1711,1716,1717,1720,1721,1731,1732,1734,1735,1738,1739,1740,1741,1752,1754,1756,1757,1765,1770,1771,1772,1775,1780,1781,1786,1789,1793,1795,1797,3883,3936,1798,1803,3890,1805,1817,1818,1820,1821,1822,1823,1824,1825,1826,1827,1828,1834,1865,1867,1869,1873,1885,1887,2895,2896,1899,1900,1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911,2010,2020,3021,3924,2456,2457,2458,2459,3937,3941,3926,2464,3939,3935,3925,3929,2469,3930,2471,2472,3940,3931,2475,2476,2477,2478,2479,2480,2481,2482,2483,2500,2501,2502,2504,2505,3510,2520,2521,2522,2525,2534,2540,3927,2541,2542,2543,2544,2545,2546,2547,2562,2564,2565,2566,2580,2581,2582,2600,2601,2602,2603,2604,2621,2622,2623,2625,2640,2641,2643,2645,2646,2660,2661,2665,3680,2700,2701,2707,2719,3726,2728,2729,2775,2800,2801,2802,2803,2804,2805,2806,2807,2808,2809,2810,2811,2812,2813,2814,2815,2816,2817,2818,2819,2820,2821,2822,2823,2824,2825,2826,2827,2828,2829,2830,2831,2832,2833,2834,2835,2836,2837,2838,2839,2840,2841,2842,2843,2844,2845,2846,2847,2848,2849)
AND (program.endtime > FROM_UNIXTIME('1544237528') AND program.starttime
< FROM_UNIXTIME('1546915928') AND program.starttime != program.endtime)
AND (program.title       LIKE '%burger%' OR program.subtitle    LIKE
'%burger%' OR program.description LIKE '%burger%' OR program.category
 LIKE '%burger%' OR people.name         LIKE '%burger%') AND
(program.endtime >= NOW()) ORDER BY program.starttime;


As you can see, this query took 101.036997 seconds.


Does anybody know why I'm seeing such a staggering slowdown in database
query time?   Is is really possible that one of the tunables that I
commented out to allow the upgrade to happen would cause such a change
in performance?


Thanks
-WD