Looking For Someone To Help Tweak Large Board

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Ideas Centre
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Looking For Someone To Help Tweak Large Board

Post 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.
User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post 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.
evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post 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 :(
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post 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.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post 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.
evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post by evepowr »

Any clues?
User avatar
RMcGirr83
Former Team Member
Posts: 21600
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Looking For Someone To Help Tweak Large Board

Post by RMcGirr83 »

That query looks to be the one from within functions_posting.php for the notification of topics or forum subscription.
Appreciate the extensions/mods/support then buy me a beerImage
Former Modifications/Extensions Team Member | My extensions | github | All requests for support via PM will be ignored
Jeffrey C McMahan
Registered User
Posts: 25
Joined: Sat May 26, 2012 9:01 pm
Location: usa
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post 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.
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post 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.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post 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.
evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post 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.
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Looking For Someone To Help Tweak Large Board

Post by Oleg »

What is the query plan for that query on your system?
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
Locked

Return to “[3.0.x] Support Forum”