[3.0.x] Tweaks for large forums

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Suggested Hosts
arod-1
Registered User
Posts: 1327
Joined: Mon Sep 20, 2004 1:33 pm

Re: [3.0.x] Tweaks for large forums

Post by arod-1 » Fri Jan 07, 2011 3:33 pm

1234homie wrote:So I should cache post counts in users table and run query only if its less than X user posts?
not exactly.
post count is *already* in users table, and query *already* runs only if post count is less than something.

User avatar
1234homie
Registered User
Posts: 439
Joined: Fri Sep 26, 2008 3:17 pm

Re: [3.0.x] Tweaks for large forums

Post by 1234homie » Sun Apr 17, 2011 8:11 pm

arod-1 wrote:
1234homie wrote:So I should cache post counts in users table and run query only if its less than X user posts?
not exactly.
post count is *already* in users table, and query *already* runs only if post count is less than something.
done and working very well :D

Anyone have ideas how tweak this mod http://www.phpbb.com/community/viewtopi ... 0&t=610134? Query to update users_table on every page seens to be killer for my server ;o

User avatar
JLA
Registered User
Posts: 478
Joined: Tue Nov 16, 2004 5:23 pm
Location: USA
Name: JLA FORUMS
Contact:

Re: [3.0.x] Tweaks for large forums

Post by JLA » Tue Aug 09, 2011 5:58 am

Ah, so there is a Tweaks thread for PHPBB3. Almost forgot about this. BTW - has anyone heard anything from Lanzer lately? For those who don't know, he was the main contributor for the PHPBB2 tweaks thread (that has seemed to vanish).

One of the main things we've learned with PHPBB2 and the PHPBB3 boards we run is the best way to tweak is to cut out the bloat. You have to weigh what options are really being used versus your traffic, hardware, budget, etc. You'd be surprised just how much bloat can be cut out of either platform.

Is there a summary post in this thread similar to the phpbb2 tweaks thread for those tweaks that have been tested and proven to be effective?

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: [3.0.x] Tweaks for large forums

Post by Oleg » Wed Aug 10, 2011 2:54 am

There are some performance-related tickets in the tracker (search for "open performance", without the quotes). Some of them have patches attached.
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

User avatar
JLA
Registered User
Posts: 478
Joined: Tue Nov 16, 2004 5:23 pm
Location: USA
Name: JLA FORUMS
Contact:

Re: [3.0.x] Tweaks for large forums

Post by JLA » Wed Aug 10, 2011 8:07 am

Oleg wrote:There are some performance-related tickets in the tracker (search for "open performance", without the quotes). Some of them have patches attached.
Thanks for this. :D

parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Re: [3.0.x] Tweaks for large forums

Post by parsim » Fri May 11, 2012 6:04 am

I notice the "View unanswered posts" function generates a database query that can't use any indexes, requiring a full table scan of phpbb_topics:

Code: Select all

mysql> EXPLAIN SELECT DISTINCT t.topic_last_post_time, p.topic_id FROM phpbb_posts p, phpbb_topics t WHERE t.topic_replies = 0 AND t.topic_moved_id = 0 AND p.topic_id = t.topic_id   AND p.post_approved = 1  AND p.forum_id NOT IN (17, 22, 28, 29) ORDER BY t.topic_last_post_time DESC  LIMIT 1001 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 154369
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: forum_id,topic_id,post_approved,tid_post_time
          key: topic_id
      key_len: 3
          ref: forum.t.topic_id
         rows: 49
        Extra: Using where
2 rows in set (0.00 sec)
On my forum, this isn't called very often, but can take 30 seconds or more when it is.

User avatar
JLA
Registered User
Posts: 478
Joined: Tue Nov 16, 2004 5:23 pm
Location: USA
Name: JLA FORUMS
Contact:

Re: [3.0.x] Tweaks for large forums

Post by JLA » Fri May 11, 2012 6:18 am

Do you really need it? There are so many misc things included in PHPBB3 and PHPBB2 - many which are unnecessary and nothing but bloat. If you don't need it, then strip it. Your hardware, software and most importantly - your member's will thank you.

Best way to decide. Take your usage trends for a long baseline period. Look at the percentages of what functions are actually used by your members. The ones that rank low should be immediately stripped out.
parsim wrote:I notice the "View unanswered posts" function generates a database query that can't use any indexes, requiring a full table scan of phpbb_topics:

Code: Select all

mysql> EXPLAIN SELECT DISTINCT t.topic_last_post_time, p.topic_id FROM phpbb_posts p, phpbb_topics t WHERE t.topic_replies = 0 AND t.topic_moved_id = 0 AND p.topic_id = t.topic_id   AND p.post_approved = 1  AND p.forum_id NOT IN (17, 22, 28, 29) ORDER BY t.topic_last_post_time DESC  LIMIT 1001 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 154369
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: forum_id,topic_id,post_approved,tid_post_time
          key: topic_id
      key_len: 3
          ref: forum.t.topic_id
         rows: 49
        Extra: Using where
2 rows in set (0.00 sec)
On my forum, this isn't called very often, but can take 30 seconds or more when it is.

User avatar
Derky
Development Team Member
Development Team Member
Posts: 4814
Joined: Sun Apr 10, 2005 9:58 am
Location: Netherlands
Contact:

Re: [3.0.x] Tweaks for large forums

Post by Derky » Fri May 11, 2012 8:14 am

parsim wrote:I notice the "View unanswered posts" function generates a database query that can't use any indexes, requiring a full table scan of phpbb_topics:
[..]
On my forum, this isn't called very often, but can take 30 seconds or more when it is.
Please create a bug report about this here: http://tracker.phpbb.com/secure/CreateI ... ate=Create
You can login with the same username/password as here.

There seems te be a similar problem with "View unread posts" PHPBB3-8779 but I don't know if they also fixed "View unanswered posts". So create a report to be sure they see it. :)

parsim
Registered User
Posts: 30
Joined: Fri Jan 15, 2010 10:29 pm

Re: [3.0.x] Tweaks for large forums

Post by parsim » Wed May 16, 2012 4:08 am

Derky wrote:Please create a bug report about this
Done.

evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: [3.0.x] Tweaks for large forums

Post by evepowr » Wed May 30, 2012 5:33 am

Hey all here is a new one.

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
Suggestions? Im thinking its from allowing people to subscribe to topics but im not 100% sure yet.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: [3.0.x] Tweaks for large forums

Post by drathbun » Wed May 30, 2012 12:49 pm

"Not In" queries can be quite bad, especially with a lengthy list of arguments. You are just about guaranteed that you're not going to hit an index in that case.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: [3.0.x] Tweaks for large forums

Post by evepowr » Wed May 30, 2012 1:41 pm

Yea had to disable subscribing to forums and topics. Seemed to be the only way around that one :(

User avatar
EverettB
Registered User
Posts: 326
Joined: Fri Aug 01, 2003 7:11 pm
Location: North America

Re: [3.0.x] Tweaks for large forums

Post by EverettB » Wed May 30, 2012 2:20 pm

I was interested so I looked at the code and you could move that NOT IN logic to the code.
I see it's doing a NOT IN for all the banned user ids.

Run the query, then skip all those banned ids in the loops right afterward (there are 2 loops)
OR
Let it load up notify_rows array in both of the loops it runs, then cut out everything that is in $sql_ignore_users at the end.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: [3.0.x] Tweaks for large forums

Post by drathbun » Wed May 30, 2012 4:36 pm

EverettB wrote:I see it's doing a NOT IN for all the banned user ids.
I was wondering why there was an extensive list of user ID values. It's probably possible that the same (or similar) logic exists elsewhere in the code as well.
Let it load up notify_rows array in both of the loops it runs, then cut out everything that is in $sql_ignore_users at the end.
I'm assuming an array lookup (using the user_id as an index) would be far more efficient.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Re: [3.0.x] Tweaks for large forums

Post by A_Jelly_Doughnut » Wed May 30, 2012 6:46 pm

An alternative solution would be to revert the query to the state it had in 3.0.8 (IIRC). It didn't always check ban status when sending out the subscription emails. For the majority of boards that allow guests access, it isn't really important to remove banned users from the subscription list.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish

Post Reply

Return to “phpBB Discussion”