Page 1 of 1

Looking For Someone To Help Tweak Large Board

Posted: Mon May 21, 2012 6:44 pm
by evepowr
So the problem is pretty simple, we have a large board and when a post is made for the first time by a user after a few hours it hangs for a good 20 seconds. That's just not acceptable so I'm looking for some quick help. Here is the process taking so long.

SELECT u.user_id, u.username, u.user_email, u.user_lang, u.user_notify_type, u.user_jabberFROM ph thats all i could get from the process.

So its seems to be when someone posts its updating the site but for such a long delay it seems odd.

I know there are tons of things i can do to fix large boards so im hoping to get some tricks from other members to help fix this bug.

Thanks in Advance :)

Also is it against the rules to try and solicit a professional to check out our phpbb3 database and optimize it? I don't want to break any rules just that would save me a ton of time if possible.

Re: Looking For Someone To Help Tweak Large Board

Posted: Mon May 21, 2012 9:27 pm
by HGN
Are you aware of this topic: [3.0.x] Tweaks for large forums.
evepowr wrote:Also is it against the rules to try and solicit a professional to check out our phpbb3 database and optimize it?
Yes: Rule 1k.

Re: Looking For Someone To Help Tweak Large Board

Posted: Mon May 21, 2012 9:54 pm
by evepowr
Yea i saw that and wasnt sure. It said recruitment but does that fall under offering to pay people to help fix problems? If so thats understandable and i wont bring it up again here just curious. Also checking out that link but i have done most of those already :(

Re: Looking For Someone To Help Tweak Large Board

Posted: Tue May 22, 2012 4:31 am
by Oleg
To start you need to obtain a complete list of all running queries when the slow query is executing. See http://www.phpbb.com/community/viewtopi ... ilit=query.

Re: Looking For Someone To Help Tweak Large Board

Posted: Wed May 23, 2012 8:34 am
by evepowr
Here is the part thats lagging it. Based on phpmyadmin

Query 12 statistics SELECT u.user_id, u.username, u.user_email, u.user_lang, u.user_notify_type, u.user_jabber
FROM ph

It cuts off there.

Re: Looking For Someone To Help Tweak Large Board

Posted: Sat May 26, 2012 2:50 pm
by evepowr
Any clues?

Re: Looking For Someone To Help Tweak Large Board

Posted: Sun May 27, 2012 10:02 am
by RMcGirr83
That query looks to be the one from within functions_posting.php for the notification of topics or forum subscription.

Re: Looking For Someone To Help Tweak Large Board

Posted: Sun May 27, 2012 1:31 pm
by Jeffrey C McMahan
It would have been helpful if you had used the support request template. It seems that your issue is with php or your database. Since you haven't given any details about your installation your pretty much fishing in the dark. You might try purging your forums cache, or running resynchronization function on all boards. Note; purging the cache is not clearing board cookies.

Re: Looking For Someone To Help Tweak Large Board

Posted: Mon May 28, 2012 5:34 am
by Oleg
Oleg wrote:To start you need to obtain a complete list of all running queries when the slow query is executing. See http://www.phpbb.com/community/viewtopi ... ilit=query.
Google "a complete list of all running queries mysql" if you need help obtaining it.

Re: Looking For Someone To Help Tweak Large Board

Posted: Wed May 30, 2012 1:18 am
by evepowr
Got the fully process list seems to be a bad mod install tracking it down now. Seems to be in constants.php based on other posts. Ill update when i find the error.

Code: Select all

SELECT u.user_id, u.username, u.user_email, u.user_lang, u.user_notify_type, u.user_jabber
       FROM phpbb_topics_watch w, phpbb_users u
       WHERE w.topic_id = 82113
           AND w.user_id NOT IN (1, 47771, 878, 1018, 1576, 6043, 11177, 27558, 37711, 41054, 44474, 46295, 47619, 47666, 86918, 88700, 89231, 89979, 91771, 92696, 92933, 95135, 95889, 97401, 103899, 110798, 120174, 123099, 171692, 173969, 181315, 185452, 193062, 198835, 198898, 198903, 199233, 199571, 199652, 199653, 199672, 200423, 203167, 204201, 205332, 207060, 208386, 210167, 210291, 210415, 210688, 211811, 212214, 213688, 213899, 214221, 214381, 214564, 215387, 215488, 216827, 217949, 219760, 221333, 223617, 224241, 224715, 225770, 227473, 227977, 229191, 229672, 229712, 232527, 233485, 235331, 236347, 238976, 240884, 241188, 242965, 243959, 244388, 245207, 245606, 245696, 246436, 247531, 248334, 249732, 250375, 251137, 252889, 252982, 252990, 253564, 253918, 253996, 254200, 256305, 256502, 257664, 258387, 260791, 261264, 261918, 262596, 262827, 263768, 264129, 264773, 265010, 265548, 266738, 267323, 268961, 269356, 270327, 270518, 270753, 271312, 271430, 273093, 273381, 273509, 274875, 274993, 275304, 275810, 276909, 277515, 278359, 278417, 278754, 281838, 283580, 283939, 284105, 284149, 284292, 284654, 284711, 285038, 285326, 285553, 286309, 286565, 286697, 287275, 287349, 288231, 288386, 288636, 288800, 288820, 288849, 288866, 289229, 289237, 290175, 290184, 290589, 290676, 290711, 291701, 291910, 292654, 294047, 295361, 297367, 298900, 300487, 300522, 300895, 301695, 301834, 301867, 302620, 303270, 303610, 304446, 304745, 304894, 305008, 305371, 307188, 307304, 307450, 307693, 308263, 308288, 309613, 309827, 310410, 311768, 312708, 313089, 313711, 313826, 314222, 314891, 317204, 320073, 320635, 322873, 324123, 324228, 324969, 325566, 326906, 327172, 327852, 329723, 330481, 331790, 332057, 337084, 337180, 338222, 338672, 340003, 340281, 340290, 341361, 341522, 341831, 342212, 342275, 342672, 342736, 342745, 343672, 344030, 344081, 344164, 345121, 345194, 345842, 346792, 347162, 347233, 347410, 347625, 347764, 347861, 348216, 349098, 349787, 349813, 349917, 350214, 350546, 351109, 351386, 351831, 352314, 352343, 352726, 353119, 353214, 353487, 353974, 353997, 354447, 354479, 354868, 355611, 356464, 357247, 357439, 357485, 357661, 357687, 357748, 358115, 358491, 358808, 359102, 360414, 361162, 361377, 362099, 362256, 362739, 362743, 363529, 363619, 363893, 364174, 364825, 365785, 366203, 368193, 368555, 369074, 370105, 371239, 372490, 374502, 374620, 376274, 376558, 376812, 377208, 377594, 377638, 378241, 381563, 381793, 382135, 382350, 382644, 383219, 383581, 385463, 385902, 389196, 389204, 390110, 391357, 392274, 392812, 394176, 394267, 394759, 394835, 395372, 395448, 398179, 398262, 398401, 400161, 401417, 401490, 401636, 402235, 402243, 402488, 402545, 403296, 403496, 403557, 404084, 404234, 405358, 405388, 405710, 406112, 406533, 406875, 407139, 409036, 409383, 409512, 409681, 411102, 411592, 412653, 412795, 413164, 413315, 413324, 413770, 413851, 413905, 414288, 414482, 414944, 415012, 417369, 418997, 419188, 421460, 421666, 423342, 423426, 423637, 423895, 424277, 424315, 425021, 425891, 427180, 427237, 427767, 429178, 429192, 430461, 430633, 430833, 431116, 432855, 433242, 434323, 435038, 436701, 436752, 437118, 437325, 439191, 439522, 440645, 442029, 442121, 442721, 442925, 442983, 443371, 443802, 446412, 446730, 448159, 452152, 452536, 454419, 455417, 455476, 456670, 457155, 457323, 457701, 458079, 458556, 458854, 459338, 459975, 461105, 461438, 461558, 461588, 462452, 462971, 463751, 464200, 464417, 464869, 464953, 465709, 465766, 466027, 466138, 466153, 466175, 466617, 466910, 468100, 471756, 472519, 472900, 475187, 477762, 480014, 480060, 480063, 482475, 482476, 484211, 484902, 484977, 487538, 487753, 490248, 495055, 495552, 495805, 496927, 496972, 498082, 498270, 502652, 504486, 505114, 505247, 505903, 506221, 506284, 506358, 506610, 506737, 507431, 508408, 508457, 510552, 511569, 512508, 513288, 515838, 516552, 517178, 517189, 518461, 519299, 520101, 521487, 522187, 523668, 525759, 526888, 527712, 528450, 528844, 529262, 529315, 531541, 533257, 534591, 535046, 537776, 539154, 540002, 542611, 542678, 544284, 544507, 545559, 546445, 546927, 547284, 548135, 549576, 549997, 551703, 556928, 557246, 557532, 557885, 561390, 561457, 563974, 566661, 566819, 567414, 567658, 567673, 568589, 569650, 570416, 570954, 572748, 574989, 578114, 583977, 586064, 589265, 591698, 594243, 594579, 596063, 596343, 598371, 599073, 600216, 601438, 602823, 605728, 605845, 606296, 608513, 614809, 619320, 620297, 620387, 622059, 622539, 623666, 629574, 630249, 632677, 633665, 637081, 639356, 639423, 641327, 642102, 643493, 644616, 646919, 647214, 650336, 650994, 652603, 654962, 657242, 658280, 659839, 661336, 663299, 663854, 664975, 665830, 666724, 668365, 669512, 669839, 671773, 672013, 672042, 672259, 672341, 674107, 674381, 674950, 675984, 677093, 678599, 678614, 679050, 679182, 679772, 682100, 682592, 683577, 685024, 685372, 688653, 688688, 690152, 690653, 690912, 691535, 695230, 695562, 695613, 696201, 701814, 707846, 708342, 708922, 709496, 711521, 712275, 713866, 713879, 715218, 717163, 719560, 721160, 721542, 723505, 723604, 724230, 724430, 725397, 725972, 725994, 726185, 726247, 728329, 734314, 735311, 737144, 738696, 738811, 739996, 740538, 740818, 741025, 741608, 742223, 743279, 744225, 748842, 751326, 755043, 757714, 758654, 759386, 767450, 772405, 772406, 772780, 774502, 777874, 779672, 780303, 781973, 782579, 783852, 784012, 785842, 788540, 790588, 793604, 795891, 798531, 799198, 802026, 803033, 803959, 804217, 805202, 805501, 806449, 807011, 808286, 810312, 812211, 813272, 814339, 815470, 816319, 818889, 820256, 820596, 824227, 827210, 828030, 830963, 832290, 833587, 833913, 835529, 836586, 839574, 841085, 841282, 841339, 841559, 841688, 845749, 846394, 850944, 851073, 851864, 852640, 863437, 864181, 866878, 871817, 877720, 878053, 878191, 878342, 881839, 881943, 884775, 885027, 885860, 886170, 887619, 889052, 889484, 891209, 894386, 898455, 899685, 899723, 903978, 905276, 909468, 911523, 913524, 913993, 914852, 919453, 921258, 921261, 928556, 930942, 931970, 932168, 932644, 933411, 934379, 934958, 935390, 940242, 943915, 944768, 946671, 947032, 947054, 948561, 949714, 953691, 954195, 954233, 955902, 956928, 956961, 959147, 959248, 963897, 968138, 971083, 974392, 975101, 975883, 978469, 978729, 981545, 982194, 982592, 982648, 983993, 984569, 984782, 984928, 987797, 988295, 990397, 991004, 991327, 996805, 996979, 997019, 998379, 1002344, 1004111, 1004907, 1004925, 1004926, 1005499, 1007632, 1008536, 1009478, 1010430, 1011477, 1011815, 1011837, 1011935, 1012831, 1015512, 1015635, 1016191, 1016193, 1016194, 1016433, 1017011, 1018255, 1019188, 1019509, 1019577, 1024860, 1027278, 1030168, 1034460, 1034585, 1036561, 1040338, 1045271, 1046309, 1046477, 1050117, 1051623, 1053121, 1054203, 1055462, 1058958, 1069915, 1070202, 1072239, 1072519, 1073870, 1085938, 1089870, 1093868, 1094394, 1098296, 1101063, 1102167, 1103553, 1105325, 1106408, 1106653, 1107274, 1110969, 1117718, 1117887, 1118420, 1118644, 1120732, 1120785, 1121103, 1121606, 1122636, 1122714, 1128437, 1132494, 1132497, 1137566, 1138294, 1139179, 1139716, 1140052, 1140750, 1140870, 1140979, 1141146, 1141193, 1141342, 1141389, 1141761, 1142595, 1142688, 1143431, 1145326, 1145655, 1145664, 1145678, 1145735, 1145769, 1145848, 1145975, 1146053, 1146082, 1146196, 1146218, 1146263, 1146347, 1146538, 1146552, 1146790, 1147020, 1147021, 1147106, 1147190, 1147360, 1147573, 1147635, 1148185, 1148511, 1150610, 1150865, 1151917, 1152013, 1152101, 1152783, 1153346, 1153865, 1154301, 1154795, 1155594, 1155671, 1156012, 1156161, 1156475, 1156771, 1156865, 1157725, 1157842, 1158091, 1158299, 1159350, 1159431)
           AND w.notify_status = 0
           AND u.user_type IN (0, 3)
           AND u.user_id = w.user_id
Also thanks a ton for all the help. I love this community.

Re: Looking For Someone To Help Tweak Large Board

Posted: Wed May 30, 2012 3:24 am
by evepowr
Hmm so based on this i think it may just be due to having a few hundred thousand members and i need to tweak this for a large board.

Re: Looking For Someone To Help Tweak Large Board

Posted: Sun Jun 03, 2012 6:21 pm
by Oleg
What is the query plan for that query on your system?