My Host Called and Im killing thier server

[sigepjedi]

Yep, my host (MediaTemple) called me today saying that my site is killing their server. Specificly mySQL and apparently queries for comments on pictures. Im running CPG 1.4.2, with loads of data and pics.

Here were the issues he had:

1.) Queries selecting all albums
2.) Picture pages not paging comments (i have some that have hundreds of comments)
3.) Reads and writes on the hitcount of pages/pictures.

Here are two Queries he sent me off the server:

SELECT p.*, msg_id, author_id, msg_author, UNIX_TIMESTAMP(msg_date) as msg_date, msg_body, aid FROM bw_comments as c, bw_pictures as p WHERE approved = 'YES' AND c.pid = p.pid AND ( aid IN (9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,265,266,267,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,301,302,303,304,306,307,308,309,310,311,312,313,314,315,316,318,319,320,321,322,323,324,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,365,366,367,368,369,370,372,373,374,375,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,551,552,553,554,555,556,557,558,559,560,561,563,564,565,566,567,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,681,682,683,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,737,739,740,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,857,858,859,860,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,890,891,892,893,894,895,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,921,922,923,924,926,927,928,930,931,933,934,935,936,937,938,939,940,941,943,944,945,946,947,948,949,950,951,952,953,954,955,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,992,994,995,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1022,1023,1025,1026,1027,1028,1029,1030,1031,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1047,1049,1051,1053,1054,1055,1056,1057,1058,1060,1061,1062,1063,1064,1066,1068,1071,1072,1073,1074,1075,1076,1087,1089,1091,1092,1093,1095,1096,1097,1098,1099,1100,1101,1102,1103,1105,1108,1109,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1125,1126,1128,1129,1130,1132,1134,1136,1137,1138,1139,1140,1144,1145,1146,1147,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1169,1170,1171,1172,1173,1174,1175,1176,1177,1179,1180,1181,1183,1184,1185,1186,1187,1188,1189,1190,1191,1194,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1228,1230,1231,1232,1233,1234,1236,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1270,1271,1272,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1289,1290,1291,1292,1293,1294,1295,1296,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1321,1322,1323,1324,1325,1326,1328,1329,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1345,1346,1347,1348,1350,1355,1356,1357,1359,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1390,1391,1392,1393,1394,1395,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1408,1409,1410,1411,1412,1413,1414,1417,1418,1419,1420,1421,1422,1423,1425,1426,1427,1428,1429,1430,1431,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1464,1465,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1504,1505,1506,1507,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1527,1528,1529,1531,1532,1533,1535,1539,1540,1541,1543,1544,1546,1547,1548,1549,1550,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,1569,1570,1571,1572,1573,1574,1575,1576,1578,1579,1580,1581,1582,1583,1584,1585,1586,1587,1588,1589,1591,1592,1593,1594,1595,1596,1597,1599,1600,1601,1603,1605,1606,1607,1608,1609,1610,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1639,1640,1642,1643,1644,1645,1646,1648,1649,1651,1652,1653,1655,1656,1657,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,1675,1676,1677,1678,1679,1680,1681,1682,1683,1684,1685,1686,1687,1688,1689,1690,1691,1692,1693,1694,1695,1696,1697,1698,1699)  ) ORDER by msg_id DESC  LIMIT 147616

SELECT COUNT(*) from bw_pictures WHERE (aid='1274'  ) OR (keywords like '%Missy%'  ) AND approved='YES' AND aid IN (9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,265,266,267,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,301,302,303,304,306,307,308,309,310,311,312,313,314,315,316,318,319,320,321,322,323,324,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,365,366,367,368,369,370,372,373,374,375,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,551,552,553,554,555,556,557,558,559,560,561,563,564,565,566,567,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,681,682,683,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,737,739,740,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,857,858,859,860,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,890,891,892,893,894,895,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,921,922,923,924,926,927,928,930,931,933,934,935,936,937,938,939,940,941,943,944,945,946,947,948,949,950,951,952,953,954,955,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,992,994,995,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1022,1023,1025,1026,1027,1028,1029,1030,1031,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1047,1049,1051,1053,1054,1055,1056,1057,1058,1060,1061,1062,1063,1064,1066,1068,1071,1072,1073,1074,1075,1076,1087,1089,1091,1092,1093,1095,1096,1097,1098,1099,1100,1101,1102,1103,1105,1108,1109,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1125,1126,1128,1129,1130,1132,1134,1136,1137,1138,1139,1140,1144,1145,1146,1147,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1169,1170,1171,1172,1173,1174,1175,1176,1177,1179,1180,1181,1183,1184,1185,1186,1187,1188,1189,1190,1191,1194,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1228,1230,1231,1232,1233,1234,1236,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1270,1271,1272,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1289,1290,1291,1292,1293,1294,1295,1296,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1321,1322,1323,1324,1325,1326,1328,1329,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1345,1346,1347,1348,1350,1355,1356,1357,1359,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1390,1391,1392,1393,1394,1395,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1408,1409,1410,1411,1412,1413,1414,1417,1418,1419,1420,1421,1422,1423,1425,1426,1427,1428,1429,1430,1431,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1464,1465,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1504,1505,1506,1507,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1527,1528,1529,1531,1532,1533,1535,1539,1540,1541,1543,1544,1546,1547,1548,1549,1550,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,1569,1570,1571,1572,1573,1574,1575,1576,1578,1579,1580,1581,1582,1583,1584,1585,1586,1587,1588,1589,1591,1592,1593,1594,1595,1596,1597,1599,1600,1601,1603,1605,1606,1607,1608,1609,1610,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1639,1640,1642,1643,1644,1645,1646,1648,1649,1651,1652,1653,1655,1656,1657,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,1675,1676,1677,1678,1679,1680,1681,1682,1683,1684,1685,1686,1687,1688,1689,1690,1691,1692,1693,1694,1695,1696,1697,1698,1699);

I would REALLY appreciate replies on this.... before my host kicks me!  :-)
Thanks in advance.

[Joachim Müller]

Don't use cpg1.4.2 - it is outdated and has security issues. It is mandatory to upgrade to the most recent version (currently cpg1.4.10).

[sigepjedi]

i would upgrade, but I have so many mod's on this puppy...
Im afraid I will lose my mind. going to 1.4 was a killer!

Should I invest the time to go to the current, or should I hold off for a larger realease?

[Joachim Müller]

Upgrading is mandatory no matter what.

[khoking]

i would upgrade, but I have so many mod's on this puppy...
Im afraid I will lose my mind. going to 1.4 was a killer!

Should I invest the time to go to the current, or should I hold off for a larger realease?

If it has security issue...then I think it is a MUST for you to update/upgrade. You won't want your site being hacked then only consider upgrade?

[sigepjedi]

Upgrading is mandatory no matter what.

Ok, so I upgraded that night, on March 01... So Ive been running the latest for about 6days now.
Im still running into the same mySQL issues, and I just got off the phone with the mySQL DBA's at MediaTemple.

They are going to email me the problematic queries and such,
and other technical logs and info for you guys to take a look at.

It is likely that my usage of Coppermine is probably beyond the majority of CG users, and Mediatemple has confirmed that my usage with them is second to only 1 other client/customer of theirs.

I will get that info posted as soon as it comes in!

[b4uphotos]

you can be out of such issues as i too faced same on www.b4uphotos.com thus i turned off all views counters and no. of views displays to reduce the sql turnover...

[sigepjedi]

Im not sure your site is a very good comparison -

YOUR SITE:

We have 100 registered users
In total there are 24 users online :: 0 Registered, and 24 Guests
Most users ever online: 39 on Mar 06, 2007 at 10:16 PM

MY SITE:

We have 129093 registered users
In total there are 54 users online :: 32 Registered, and 22 Guests
Most users ever online: 1092 on Feb 21, 2007 at 01:54 AM

However, if this is something you ran into with that kind of traffic just imagine what I am facing!

Im still awaiting the logs and such from MT, this was their last email from yesterday:

Was great talking to you today...
per our conversation...

You will supply us links to the forum threads that cover your issues. this will help us by allowing us to follow along on what info they are asking for.

We are in the process of upgrading your container to the extreme level so your site will be able to handle more load.

We will also email you the stats on the container, basic suggestions on things that could be optimized, and a list of queries that are being generated from your site that are doing the most stress on the system resources.

If you have any further questions regarding your (mt) Media Temple services, please feel free to contact us at any time.


Best Regards,
(mt) Media Temple

[sigepjedi]

Latest reply:

Thank you for your patience with this issue. I've attached the MySQL
slow query log from your MySQL server. The "xxx" you see in text file
were automatically inserted to protect the privacy of your data.

By analyzing this log we detected a few major patterns that are
significantly impacting the utilization of MySQL resources.

1) Performing concurrent updates and selects from the bw_users table. As
the table is a MyISAM table when you perform an update on it the entire
table is locked while the update is being performed. At the same time
the table is locked the software is also trying to query this table so
it must wait until the table is unlocked before proceeding.

2) Number of rows being scanned per page hit. In many instances your
code is attempting to select enormous data sets from the bw_pictures and
bw_comments tables for every single page load. Scaling this number back
will improve performance.

You can read more about the slow query log here:
http://dev.mysql.com/doc/refman/4.1/en/slow-query-log.html


Best Regards,

Product Manager
(mt) Media Temple, Inc

[sigepjedi]

He included a 801K slow query log text file.

Here are a few of the queries:

### 1791 Queries
### Total time: 40818, Average time: 22.7906197654941
### Taking 6  to 100  seconds to complete
### Rows analyzed 126156 - 126348
SET timestamp=XXX;
SELECT COUNT(*) FROM `bw_users` WHERE DATE_FORMAT(user_regdate,  
'XXX') = CURDATE();

SET timestamp=1172702592;
SELECT COUNT(*) FROM `bw_users` WHERE DATE_FORMAT(user_regdate, '%Y-%
m-%d') = CURDATE();


### 1166 Queries
### Total time: 29952, Average time: 25.6878216123499
### Taking 6  to 169  seconds to complete
### Rows analyzed 282160 - 451548
SELECT count(*) FROM bw_comments as c LEFT JOIN bw_pictures as p ON  
c.pid=p.pid LEFT JOIN bw_albums as a ON a.aid=p.aid WHERE XXX;

SELECT count(*) FROM bw_comments as c LEFT JOIN bw_pictures as p ON  
c.pid=p.pid LEFT JOIN bw_albums as a ON a.aid=p.aid WHERE 1;

SELECT COUNT(*) from bw_comments, bw_pictures  WHERE approved = 'YES'  
AND bw_comments.pid = bw_pictures.pid AND ( aid IN  
(9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,26,27,28,29,30,31,32,33,34,
35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,
59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,
83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,1
05,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,1
23,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,1
41,142,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,1
60,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,178,1
79,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,1
97,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,2
15,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,2
33,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,2
51,252,253,254,255,256,257,258,259,260,261,262,263,265,266,267,269,270,2
71,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,2
89,290,291,292,293,294,295,296,297,298,299,301,302,303,304,306,307,308,3
09,310,311,312,313,314,315,316,318,319,320,321,322,323,324,326,327,328,3
29,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,3
47,348,349,350,351,352,353,354,355,356,357,358,359,360,361,365,366,367,3
68,369,370,372,373,374,375,377,378,379,380,381,382,383,384,385,386,387,3
88,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,4
06,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,4
25,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,4
43,444,445,446,447,448,449,451,452,453,454,455,456,457,458,459,460,461,4
62,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,4
80,481,482,483,485,486,487,488,489,490,491,492,493,494,495,496,497,498,4
99,500,501,502,503,504,505,506,507,508,509,510,511,512,513,515,516,517,5
18,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,5
36,537,538,539,540,541,542,543,544,545,546,547,548,549,551,552,553,554,5
55,556,557,558,559,560,561,563,564,565,566,567,569,570,571,572,573,574,5
75,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,5
93,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,610,611,6
12,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,6
30,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,6
49,650,651,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,6
68,669,670,671,672,673,674,675,676,677,678,679,681,682,683,685,686,687,6
88,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,7
06,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,7
24,725,726,727,728,729,730,731,732,733,734,735,737,739,740,742,743,744,7
45,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,7
63,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,7
81,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,7
99,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,8
17,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,8
35,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,8
53,854,855,857,858,859,860,862,863,864,865,866,867,868,869,870,871,872,8
73,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,890,891,8
92,893,894,895,897,898,899,900,901,902,903,904,905,906,907,908,909,910,9
11,912,913,914,915,916,917,918,919,921,922,923,924,926,927,928,930,931,9
33,934,935,936,937,938,939,940,941,943,944,945,946,947,948,949,950,951,9
52,953,954,955,957,958,959,960,961,962,963,964,965,966,967,968,969,970,9
71,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,9
92,994,995,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009
,
1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1022,1023,1025,10
26,1027,1028,1029,1030,1031,1033,1034,1035,1036,1037,1038,1039,1040,1041
,
1042,1043,1044,1045,1047,1049,1051,1053,1054,1055,1056,1057,1058,1060,10
61,1062,1063,1064,1066,1068,1071,1072,1073,1074,1075,1076,1087,1089,1091
,
1092,1093,1095,1096,1097,1098,1099,1100,1101,1102,1103,1105,1108,1109,11
11,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1125,1126
,
1128,1129,1130,1132,1134,1136,1137,1138,1139,1140,1144,1145,1146,1147,11
49,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163
,
1164,1165,1166,1167,1169,1170,1171,1172,1173,1174,1175,1176,1177,1179,11
80,1181,1183,1184,1185,1186,1187,1188,1189,1190,1191,1194,1197,1198,1199
,
1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1211,1212,1213,1214,12
15,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1228,1230,1231
,
1232,1233,1234,1236,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,12
48,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262
,
1263,1264,1265,1266,1267,1270,1271,1272,1274,1275,1276,1277,1278,1279,12
80,1281,1282,1283,1284,1285,1286,1289,1290,1291,1292,1293,1294,1295,1296
,
1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1310,1311,1312,13
13,1314,1315,1316,1317,1318,1319,1321,1322,1323,1324,1325,1326,1328,1329
,
1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1345,13
46,1347,1348,1350,1355,1356,1357,1359,1362,1363,1364,1365,1366,1367,1368
,
1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,13
83,1384,1385,1386,1387,1388,1390,1391,1392,1393,1394,1395,1397,1398,1399
,
1400,1401,1402,1403,1404,1405,1406,1408,1409,1410,1411,1412,1413,1414,14
17,1418,1419,1420,1421,1422,1423,1425,1426,1427,1428,1429,1430,1431,1434
,
1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,14
49,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1464
,
1465,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,14
83,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497
,
1498,1499,1500,1501,1502,1504,1505,1506,1507,1509,1510,1511,1512,1513,15
14,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1527,1528
,
1529,1531,1532,1533,1535,1539,1540,1541,1543,1544,1546,1547,1548,1549,15
50,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564
,
1565,1566,1567,1569,1570,1571,1572,1573,1574,1575,1576,1578,1579,1580,15
81,1582,1583,1584,1585,1586,1587,1588,1589,1591,1592,1593,1594,1595,1596
,
1597,1599,1600,1601,1603,1605,1606,1607,1608,1609,1610,1611,1612,1613,16
14,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628
,
1629,1630,1631,1632,1633,1634,1635,1636,1637,1639,1640,1642,1643,1644,16
45,1646,1648,1649,1651,1652,1653,1655,1656,1657,1659,1660,1661,1662,1663
,
1664,1665,1666,1668,1669,1670,1671,1672,1673,1675,1676,1677,1678,1679,16
80,1681,1682,1683,1684,1685,1686,1687,1688,1689,1690,1691,1692,1693,1694
,
1695,1696,1697,1698,1699,1700,1705,1706,1707,1708,1709,1712,1713,1714,17
15,1716,1717,1719,1720,1721,1722,1723,1724,1725,1726,1727,1728,1730,1731
,1732,1733,1734,1737,1738,1739,1740)  );

Please PM me for the complete log, admins.
Thanks in advance!

[sigepjedi]

Can someone please take a look at this, and address these issues?

[sigepjedi]

Can someone please take a look at this, and address these issues?

BuMp

[sigepjedi]

 

[Nibbler]

Code Select Expand


SELECT COUNT(*) FROM `bw_users` WHERE DATE_FORMAT(user_regdate, 
'XXX') = CURDATE();


That's something you have added yourself, remove or rewrite that.

The others are for collecting stats so disable or cache any stats you don't need.

[sigepjedi]

Code Select Expand


SELECT COUNT(*) FROM `bw_users` WHERE DATE_FORMAT(user_regdate, 
'XXX') = CURDATE();


That's something you have added yourself, remove or rewrite that.

The others are for collecting stats so disable or cache any stats you don't need.

Ok, I tracked this down, and it was in an older version of onlinestats. My index.php had a case for this, as well as the Plugin......
Thanks for help with that.

What are your thoughts on those giant selects on the aid IN()

Code Select Expand

ELECT COUNT(*) from bw_comments, bw_pictures  WHERE approved = 'YES' 
AND bw_comments.pid = bw_pictures.pid AND ( aid IN  .......

What are your thoughts on the

[sigepjedi]

It would seem one thing that my host is still suggesting is for picture pages to have a 'paging' mechanism for the comments.
This is directly due to the large number of comments some of my pictures have.

Is this something that is perhaps slated as a plugin or for the next release?

[Nibbler]

Perhaps. I have code to do that on one of my galleries just a matter of finding time to commit it.

[memento]

Hello,

on our site (www.nikonpassion.com), we are experiencing the same issue with the gallery which sometimes uses 100% of CPU and make the server crash.
sigepjedi did you solve your problem? if yes how?

Thanks for your help.

PS : I have tried the different optimisations recommended on this forum (running the latest version 1.4.12, no statistic activated, no exif or iptc information displayed)