Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: My Host Called and Im killing thier server  (Read 7050 times)

0 Members and 1 Guest are viewing this topic.

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
My Host Called and Im killing thier server
« on: January 30, 2007, 10:41:37 pm »

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:

Quote
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

Quote
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.
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47844
  • aka "GauGau"
    • gaugau.de
Re: My Host Called and Im killing thier server
« Reply #1 on: January 31, 2007, 08:13:39 am »

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).
Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #2 on: March 01, 2007, 03:25:54 am »

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?
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47844
  • aka "GauGau"
    • gaugau.de
Re: My Host Called and Im killing thier server
« Reply #3 on: March 01, 2007, 08:00:53 am »

Upgrading is mandatory no matter what.
Logged

khoking

  • Coppermine newbie
  • Offline Offline
  • Posts: 11
Re: My Host Called and Im killing thier server
« Reply #4 on: March 01, 2007, 08:14:22 am »

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?
Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #5 on: March 07, 2007, 10:43:45 pm »

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!



Logged

b4uphotos

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 59
    • B4U India
Re: My Host Called and Im killing thier server
« Reply #6 on: March 08, 2007, 08:16:39 am »

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...
Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #7 on: March 08, 2007, 05:17:30 pm »

Im not sure your site is a very good comparison -

YOUR SITE:
Quote
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:
Quote
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:
Quote
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
Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #8 on: March 12, 2007, 08:56:04 pm »

Latest reply:

Quote
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

Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #9 on: March 12, 2007, 08:57:14 pm »

He included a 801K slow query log text file.

Here are a few of the queries:


Quote
### 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;


Quote
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!
Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #10 on: March 14, 2007, 01:00:03 am »

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

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #11 on: March 15, 2007, 07:01:46 pm »

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

BuMp
Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #12 on: March 17, 2007, 12:24:17 am »

 ???
Logged

Nibbler

  • Guest
Re: My Host Called and Im killing thier server
« Reply #13 on: March 17, 2007, 12:28:12 am »

Code: [Select]
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.
Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #14 on: March 28, 2007, 09:23:34 pm »

Code: [Select]
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]
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
Logged

sigepjedi

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 111
Re: My Host Called and Im killing thier server
« Reply #15 on: April 16, 2007, 08:30:04 pm »

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?
Logged

Nibbler

  • Guest
Re: My Host Called and Im killing thier server
« Reply #16 on: April 16, 2007, 09:41:01 pm »

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

memento

  • Coppermine newbie
  • Offline Offline
  • Posts: 1
Re: My Host Called and Im killing thier server
« Reply #17 on: July 08, 2007, 07:47:00 pm »

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)
Logged
Pages: [1]   Go Up
 

Page created in 0.029 seconds with 19 queries.