Someone care to explain the logic in this to me...

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
frankoamiricano
Registered User
Posts: 73
Joined: Thu Apr 11, 2002 3:24 am

Someone care to explain the logic in this to me...

Post by frankoamiricano »

I have been trying to get a answer to this for a week now, after moving to mysql 4 and phpBB 2.0.6 I have about 12 hours of good running, then certain pages take 30 seconds or more to load. I have mysqldumped the database and built it new, the only thing that fixes it is to restart mysql, something I do not like to do.

This url is the culprit
http://forums.osxfaq.com/search.php?sea ... unanswered

Now, I printed out the sql that is issued from that url, and I would love to know what it is up to and why it has to be this way.

Any ideas why it would be OK for 12 hours or so, then all of a sudden slow down?

Here is the SQL statement, if someone could tell me why this needs to be this way, I would love to know.

Code: Select all

SELECT t.*, f.forum_id, f.forum_name, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time FROM osxfaq_topics t, osxfaq_forums f, osxfaq_users u, osxfaq_posts p, osxfaq_posts p2, osxfaq_users u2  WHERE t.topic_id IN (5, 19, 246, 245, 24, 25, 27, 28, 30, 34, 35, 37, 39, 40, 617, 66, 67, 616, 72, 89, 98, 101, 4635, 108, 113, 121, 122, 123, 125, 129, 132, 141, 142, 144, 146, 148, 159, 601, 169, 599, 202, 615, 215, 231, 233, 255, 597, 260, 268, 274, 275, 288, 292, 1519, 606, 607, 330, 331, 336, 339, 596, 349, 350, 351, 361, 595, 368, 594, 389, 403, 412, 416, 417, 420, 422, 425, 432, 444, 445, 447, 462, 465, 480, 482, 498, 502, 513, 588, 521, 524, 525, 526, 535, 536, 537, 542, 546, 549, 554, 556, 564, 567, 572, 579, 583, 657, 662, 672, 680, 683, 686, 706, 711, 717, 735, 740, 742, 743, 752, 764, 766, 767, 772, 773, 774, 781, 783, 792, 795, 801, 805, 815, 817, 819, 820, 826, 827, 829, 831, 848, 862, 889, 895, 907, 910, 911, 916, 919, 923, 930, 938, 948, 950, 954, 961, 966, 968, 976, 978, 988, 1004, 998, 1013, 1014, 1016, 1026, 1027, 1039, 1044, 1055, 1058, 1059, 1075, 1098, 1108, 1115, 1118, 1121, 1130, 1151, 1152, 1155, 1163, 1164, 1167, 1170, 1174, 1175, 1184, 1191, 1195, 1196, 1197, 1204, 1205, 1209, 1211, 1213, 1222, 1235, 1236, 1251, 1258, 1260, 1262, 1264, 1282, 1283, 1296, 1309, 1310, 1313, 1315, 1316, 1322, 1326, 1329, 1343, 1345, 1346, 1347, 1349, 1350, 1352, 1354, 1356, 1371, 1372, 1384, 1388, 1391, 1406, 1407, 1412, 1414, 1418, 1433, 1434, 1436, 1438, 1439, 1440, 1441, 1442, 1448, 1451, 1452, 1459, 1462, 1485, 1486, 1492, 1500, 1504, 1507, 1526, 1527, 1535, 1536, 1540, 1545, 1557, 1566, 1572, 1585, 1593, 1596, 1611, 1620, 1642, 1647, 1676, 1687, 1710, 1713, 1716, 1730, 1741, 1748, 1749, 1751, 1757, 1764, 1765, 1773, 1788, 1790, 2065, 2044, 1811, 1818, 1826, 1835, 1850, 1865, 1868, 2061, 1882, 1917, 1926, 1934, 1939, 2054, 1948, 1949, 1954, 1971, 1978, 1979, 2008, 2017, 2027, 2031, 2072, 2076, 2124, 2087, 2088, 2090, 2100, 2101, 2115, 2121, 2125, 2132, 2135, 2142, 2152, 2155, 2157, 2158, 2309, 2167, 2171, 2176, 2177, 2178, 2179, 2188, 2201, 2202, 2206, 2214, 2216, 2222, 2228, 2256, 2257, 2265, 2269, 2270, 2294, 2278, 2283, 2299, 2307, 2310, 2325, 2326, 2335, 2336, 2340, 2352, 2357, 2369, 2375, 2380, 2387, 2395, 2399, 2401, 2402, 2404, 2405, 2412, 2425, 2436, 2438, 2439, 2446, 2447, 2449, 2452, 2454, 2459, 2466, 2470, 2473, 2484, 2487, 2488, 2489, 2491, 2492, 2493, 2504, 2508, 2511, 2513, 2514, 2517, 2518, 2525, 2529, 2533, 2536, 2538, 2546, 2553, 2557, 2559, 2560, 2561, 2571, 2573, 2579, 2586, 2594, 2595, 2598, 2602, 2606, 2609, 2610, 2615, 2617, 2619, 2621, 2624, 2630, 2633, 2640, 2641, 2646, 2648, 2649, 2656, 4634, 2658, 2678, 2679, 2680, 2682, 2683, 2685, 2686, 2701, 2704, 2709, 2710, 2712, 2730, 2733, 2735, 2737, 2744, 2745, 2748, 2751, 2753, 2754, 2772, 2773, 2775, 2777, 2778, 2782, 2784, 2786, 2791, 2792, 2794, 2795, 2796, 2799, 2802, 2804, 2805, 2808, 2810, 2811, 2815, 2821, 2824, 2827, 2831, 2840, 2842, 2843, 2848, 2849, 2850, 2856, 2860, 2869, 2878, 2880, 2890, 2892, 2893, 2899, 2902, 2909, 2912, 2914, 2916, 2917, 2946, 2948, 2949, 2953, 2960, 2961, 2966, 2975, 2978, 2982, 2986, 2987, 2988, 2992, 2996, 3009, 3010, 3027, 3031, 3039, 3061, 3062, 3074, 3078, 3087, 3099, 3101, 3108, 3110, 3114, 3126, 3129, 3141, 3142, 3150, 3159, 3165, 3171, 3172, 3173, 3191, 3192, 3193, 3198, 3204, 3206, 3215, 3218, 3227, 3230, 3235, 3239, 3255, 3268, 3265, 3279, 3283, 3290, 3297, 3299, 3300, 3303, 3314, 3322, 3324, 3335, 3340, 3341, 3348, 3349, 3355, 3361, 3376, 3377, 3379, 3387, 3392, 3395, 3407, 3408, 3411, 3415, 3417, 3423, 3424, 3439, 5120, 3448, 3449, 3466, 3479, 3481, 3485, 3488, 3491, 3494, 3514, 3517, 3522, 3524, 3529, 3535, 3538, 3541, 3542, 3551, 3565, 3570, 3571, 3577, 3580, 3584, 3589, 3592, 3599, 3602, 3603, 3611, 3630, 3644, 3651, 3664, 3676, 3725, 3727, 3729, 3731, 3741, 3745, 3780, 3758, 3765, 3789, 3806, 3809, 3812, 3815, 3819, 3821, 3824, 3825, 3836, 3845, 3857, 3859, 3871, 3875, 3885, 3888, 3889, 3890, 3892, 3896, 3900, 3910, 3916, 3923, 3926, 3927, 3934, 3938, 3939, 3947, 3958, 3972, 3979, 3980, 3984, 3986, 3988, 3992, 3998, 3999, 4011, 4013, 4014, 4020, 4022, 4024, 4027, 4031, 4032, 4034, 4037, 4040, 4044, 4055, 4078, 4088, 4091, 4094, 4098, 4099, 4102, 4103, 4108, 4111, 4116, 4117, 4118, 4120, 4123, 4126, 4129, 4130, 4133, 4134, 4135, 4140, 4142, 4147, 4152, 4168, 4171, 4173, 4182, 4196, 4203, 4218, 4220, 4222, 4224, 4229, 4237, 4239, 4242, 4256, 4264, 4279, 4288, 4299, 4306, 4311, 4312, 4313, 4317, 4323, 4324, 4325, 4334, 4335, 4336, 4342, 4354, 4364, 4368, 4375, 4381, 4388, 4394, 4397, 4418, 4422, 4425, 4428, 4429, 4430, 4433, 4444, 4450, 4460, 4462, 4463, 4471, 4477, 4481, 4490, 4497, 4506, 4511, 4519, 4527, 4533, 4536, 4548, 4549, 4550, 4575, 4577, 4578, 4582, 4590, 4599, 4612, 4621, 4623, 4624, 4626, 4628, 4641, 4654, 4655, 4658, 4665, 4671, 4678, 4679, 4681, 4683, 4694, 4695, 4722, 4724, 4725, 4729, 4734, 4740, 4746, 4753, 4762, 4765, 4768, 4773, 4782, 4783, 4788, 4789, 4806, 4810, 4814, 4816, 4817, 4818, 4821, 4823, 4825, 4840, 4841, 4858, 4865, 4868, 4870, 4876, 4883, 4889, 4891, 4894, 4896, 4899, 4904, 4914, 4915, 4916, 4927, 4934, 4936, 4944, 4946, 4948, 4949, 4952, 4959, 4961, 4965, 4979, 4982, 4983, 4990, 4997, 4998, 5002, 5019, 5025, 5031, 5032, 5033, 5040, 5045, 5054, 5055, 5056, 5059, 5065, 5070, 5072, 5073, 5083, 5086, 5087, 5089, 5090, 5095, 5097, 5102, 5106, 5119, 5121, 5133, 5134, 5140, 5141, 5142, 5150, 5153, 5159, 5164, 5169, 5173, 5180, 5188, 5202, 5208, 5216, 5223, 5226, 5231, 5245, 5252, 5264, 5268, 5275, 5284, 5289, 5291, 5297, 5299, 5309, 5310, 5330, 5332, 5339, 5340, 5341, 5343, 5348, 5349, 5352, 5355, 5357, 5361, 5368, 5377, 5378, 5381, 5382, 5383, 5397, 5404, 5406, 5410, 5422, 5429, 5433, 5440, 5442, 5450, 5451, 5453, 5462, 5467, 5495, 5501, 5504, 5505, 5512, 5513, 5515, 5516, 5519, 5524, 5539, 5542, 5545, 5546, 5548, 5555, 5564, 5566, 5580, 5583, 5585, 5593, 5602, 5607, 5608, 5613, 5617, 5618, 5620, 5621, 5622, 5630, 5631, 5633, 5637, 5638, 5642, 5645, 5648, 5649, 5660, 5661, 5669, 5671, 5674, 5680, 5681, 5689, 5691, 5699, 5701, 5708, 5712, 5715, 5716, 5718, 5719, 5726, 5733, 5746, 5757, 5759, 5766, 5768, 5770, 5772, 5774, 5779, 5786, 5788, 5789, 5793, 5794, 5800, 5802, 5805, 5807, 5813, 5822, 5824, 5826, 5829, 5834, 5842, 5848, 5849, 5856, 5859, 5863, 5864, 5867, 5872, 5876, 5894, 5897, 5900, 5905, 5909, 5910, 5916, 5920, 5929, 5934, 5944, 5946, 5953, 5955, 5958, 5962, 5963, 5964, 5966, 5969, 5973, 5979, 5986, 5989, 5991, 5992, 5995, 5997, 5999, 6003, 6005, 6011, 6013, 6017, 6020, 6021, 6027, 6028, 6036, 6039, 6052, 6066, 6072, 6075, 6076, 6080, 6081, 6094, 6108, 6112, 6116, 6117, 6128, 6136, 6141, 6142, 6143, 6151, 6155, 6158, 6167, 6169, 6170, 6177, 6180, 6182, 6184, 6189, 6191, 6192, 6195, 6196, 6200, 6202, 6203, 6207, 6210, 6211, 6212, 6216, 6217, 6220, 6225, 6226, 6228, 6232, 6233, 6239, 6255, 6258, 6259, 6261, 6262, 6270, 6274, 6275, 6277, 6278, 6279, 6281, 6286, 6293, 6298, 6302, 6308, 6320, 6321, 6324, 6325, 6326, 6339, 6343, 6347, 6370, 6384, 6387, 6403, 6409, 6410, 6411, 6415, 6417, 6422, 6424, 6429, 6435, 6437, 6438, 6451, 6453, 6460, 6478, 6495, 6522, 6530, 6543, 6551, 6557, 6560, 6562, 6566, 6568, 6569, 6574, 6577, 6578, 6579, 6588, 6593, 6598, 6600, 6605, 6606, 6607, 6619, 6623, 6628, 6629, 6637, 6643, 6644, 6668, 6672, 6673, 6680, 6683, 6684, 6688, 6691, 6714, 6717, 6731, 6736, 6741, 6751, 6757, 6769, 6784, 6788, 6808, 6809, 6819, 6820, 6833, 6852, 6856, 6862, 6887, 6893, 6901, 6922, 6925, 6928, 6933, 6935, 6936, 6937, 6944, 6941, 6956, 6962, 6967, 6968, 6970, 6971, 6972, 6975, 6976, 6979, 6983, 6984, 6988, 6993, 7000, 7025, 7026, 7028, 7034, 7039, 7045, 7046, 7049, 7053, 7054, 7056, 7059, 7060, 7088, 7096, 7112, 7114, 7126, 7144, 7149, 7150, 7152, 7153, 7157, 7158, 7162, 7163, 7164, 7167, 7170, 7182, 7183, 7195, 7196, 7201, 7206, 7208, 7209, 7214, 7215, 7225, 7232, 7255, 7260, 7261, 7279, 7282, 7285, 7293, 7298, 7303, 7302, 7304, 7318, 7330, 7333, 7335, 7339, 7341, 7342, 7344, 7352, 7353, 7355, 7356, 7358, 7362, 7363, 7370, 7373, 7380, 7388, 7395, 7397, 7404, 7408, 7419, 7420, 7422, 7424, 7425, 7433, 7436, 7449, 7451, 7452, 7453, 7460, 7462, 7470, 7474, 7476, 7481, 7508, 7509, 7514, 7531, 7543, 7545, 7552, 7553, 7562, 7565, 7567, 7569, 7575, 7577, 7590, 7600, 7606, 7608, 7617, 7621, 7624, 7633, 7634, 7648, 7649, 7653, 7660, 7662, 7668, 7677, 7678, 7690, 7701, 7702, 7707, 7713, 7732, 7733, 7750, 7755, 7769, 7780, 7797, 7798, 7799, 7802, 7805, 7806, 7819, 7829, 7831, 7833, 7837, 7851, 7854, 7855, 7856, 7857, 7858, 7863, 7864, 7877, 7879, 7889, 7893, 7902, 7906, 7909, 7910, 7918, 7922, 7923, 7937, 7948, 7963, 7966, 7974, 7982, 7988, 7989, 7993, 7994, 8003, 8013, 8023, 8041, 8043, 8067, 8071, 8076, 8078, 8080, 8082, 8086, 8087, 8089, 8099, 8103, 8104, 8109, 8114, 8115, 8130, 8136, 8139, 8141, 8149, 8160, 8162, 8178, 8179, 8194, 8191, 8196, 8197, 8200, 8207, 8210, 8215, 8223, 8226, 8238, 8240, 8249, 8253, 8255, 8257, 8260, 8263, 8265, 8273, 8283, 8286, 8291, 8294, 8308, 8312, 8320, 8335, 8341, 8350, 8358, 8366, 8370, 8372, 8376, 8377, 8380, 8387, 8408, 8410, 8441, 8442, 8447, 8451, 8464, 8469, 8485, 8492, 8496, 8507, 8510, 8514, 8518, 8525, 8530, 8531, 8534, 8557, 8563, 8570, 8571, 8573, 8585, 8593, 8603, 8608, 8634, 8637, 8639, 8649, 8651, 8653, 8655, 8658, 8660, 8661, 8668, 8671, 8681, 8683, 8686, 8693, 8694, 8698, 8707, 8706, 8712, 8720, 8724, 8741, 8749, 8760, 8763, 8778, 8787, 8796, 8790, 8814, 8836, 8847, 8856, 8862, 8869, 8881, 8883, 8884, 8894, 8901, 8909, 8914, 8932, 8937, 8998, 8956, 8959, 8983, 8984, 8991, 8992, 8995, 8996, 9003, 9005, 9010) AND t.topic_poster = u.user_id  AND f.forum_id = t.forum_id AND p.post_id = t.topic_first_post_id  AND p2.post_id = t.topic_last_post_id  AND u2.user_id = p2.poster_id
frankoamiricano
Registered User
Posts: 73
Joined: Thu Apr 11, 2002 3:24 am

Post by frankoamiricano »

bump...
Any developers out there that can explain this to me, I have been digging at the code in the page search.php, I have had a few others look at it as well, we all come to the conclusions that it is phpBB code that is causing this slowdown I have, pretty much rule out php and mysql as the trouble. I would really love to know why you need to search IN
    User avatar
    CTCNetwork
    Former Team Member
    Posts: 15424
    Joined: Fri Dec 19, 2003 3:50 am
    Location: In that Volvo behind you!
    Contact:

    No Help. . But

    Post by CTCNetwork »

    Hi,

    This is the "Unanswered Posts". You have a link to "View Unanswered Posts" on your index page.

    Don't want to sound like stating the obvious, but everyone who visits the forum will go to the index page, and thus generating this search for "unanswered posts" to supply the link with info (prefetch).

    Perhaps the answer for you lies in removing the link on the index page, that way the info is not going to be looked for! And that will then raamp your speed back up. . .

    Another answer would be to frantically post replies to all those unanswered posts and lock them. . .

    Just a suggestion for you. . . :)
    Density:- Not just a measurement~Its a whole way of Life.! ! !
    | Welcome! | RTFM!!! | Search! It's Easy! | Problem? | Spam? | Advice! |
    frankoamiricano
    Registered User
    Posts: 73
    Joined: Thu Apr 11, 2002 3:24 am

    Post by frankoamiricano »

    Thanks, first, removing the ability to look for unanswered posts is not a option, it is a valuabel resault set.

    I want to know what the SQL is up to, why IN [really large list that I do not know what needs to be there.]

    I think a select this, that from table where answered = 0 would suffice. yes, you need to join on some other tables to get the forum data and subjects, but you get the idead

    I am sorry, I have just never seen a 10k sql statement, and I would love to know wht it needs to be that way.

    There are enough comments in the search.php file that allude to it being a less than optimum way fo doing something, expecially the comments about a messy hack to deal with timeouts.

    Alas, I can not break the rules and PM a support developer, but I think they may be the only ones who know why the code is the way it is.

    The main point here is the page loads fine for hours, then over time it degrades, I suspect this has to do with the stored SQL list that is in one table. This table seems to auto clean itself with a delete, we all know marking records offline rather than deleting them is prefered, unless somewhere in the code a optimize if going to be ran on schedule.

    I would really love to open a dialogue with a developer and work on this, I am pretty sure this is a bug.
    User avatar
    CTCNetwork
    Former Team Member
    Posts: 15424
    Joined: Fri Dec 19, 2003 3:50 am
    Location: In that Volvo behind you!
    Contact:

    Post by CTCNetwork »

    Your best answer, after consideration would be to post in the developer section. If as you say, this is an inefficient way of getting the result, they should be aware of it - and prior to the release of 2.2. . .

    I am not a programmer, so it gets a wee bit beyond me . . . :?
    Density:- Not just a measurement~Its a whole way of Life.! ! !
    | Welcome! | RTFM!!! | Search! It's Easy! | Problem? | Spam? | Advice! |
    niekas
    Registered User
    Posts: 562
    Joined: Sun Sep 23, 2001 7:34 am

    Post by niekas »

    thats is just wrong :twisted:
    Graham
    Former Team Member
    Posts: 8462
    Joined: Tue Mar 19, 2002 7:11 pm
    Location: UK
    Contact:

    Post by Graham »

    CTCNetwork - Just having a link to that page is not going to cause the problems since the SQL is only executed when you access that page.

    back to the problem, I have seen a few optimizations which can be carried out to the search.php code to help it on large forums (see the relevant topic in phpBB Discussion for some details) but I'm not sure I see why the code would be fine for 12 hours and then do this.

    Which version of mySQL are you running?
    "So Long, and Thanks for All the Fish"

    phpBB Useful Links: Knowledge Base | Userguide | Forum Search | MOD Database | Styles Database
    My Links: Blog!
    frankoamiricano
    Registered User
    Posts: 73
    Joined: Thu Apr 11, 2002 3:24 am

    Post by frankoamiricano »

    Graham wrote: CTCNetwork - Just having a link to that page is not going to cause the problems since the SQL is only executed when you access that page.

    back to the problem, I have seen a few optimizations which can be carried out to the search.php code to help it on large forums (see the relevant topic in phpBB Discussion for some details) but I'm not sure I see why the code would be fine for 12 hours and then do this.

    Which version of mySQL are you running?


    I wish I knew why after a certain time it slowed down as well. At this point I am not ready to say it is phpBB at all. I am exploring all my options from mysql to the OS to phpBB.

    I am running MySQL 4
    Locked

    Return to “2.0.x Support Forum”