Larger board with ~600.000 posts -> posting _very_ slow

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
DocSnyder
Registered User
Posts: 42
Joined: Wed Aug 28, 2002 10:05 am

Larger board with ~600.000 posts -> posting _very_ slow

Post by DocSnyder » Tue Jun 23, 2009 7:56 am

I have a board with around 600.000 post and after upgrading to phpBB3 posting a new thread or replying became very slow: around 10 seconds after hitting the submit button.

The board runs on MySQL 4 with MySQL fulltext indexing enabled and MyISAM tables.
The server itself is an Intel Core Duo server with 2 GB of RAM, which should be suffcient (at least it was with phpBB2).
What's the reason it became so slow and how can I make it run faster again?

DocSnyder
Registered User
Posts: 42
Joined: Wed Aug 28, 2002 10:05 am

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by DocSnyder » Tue Jun 23, 2009 8:18 am

Anyone?
Would switching to phpBB3's native indexing method help me out?
The increased DB size and the load during the initial indexing wouldn't be a problem as long as it runs faster afterwards.

Any feedback would be much appreciated.

DocSnyder
Registered User
Posts: 42
Joined: Wed Aug 28, 2002 10:05 am

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by DocSnyder » Tue Jun 23, 2009 9:23 am

Maybe it helps for diagnosis when I post the SHOW CREATE for the posts table.
Is the table what it's supposed to be or is there possibly something missing/wrong that could cause the slow speed when posting?

Code: Select all

CREATE TABLE `phpbb_posts` (
  `post_id` mediumint(8) unsigned NOT NULL auto_increment,
  `topic_id` mediumint(8) unsigned NOT NULL default '0',
  `forum_id` mediumint(8) unsigned NOT NULL default '0',
  `poster_id` mediumint(8) unsigned NOT NULL default '0',
  `icon_id` mediumint(8) unsigned NOT NULL default '0',
  `poster_ip` varchar(40) collate utf8_bin NOT NULL default '',
  `post_time` int(11) unsigned NOT NULL default '0',
  `post_approved` tinyint(1) unsigned NOT NULL default '1',
  `post_reported` tinyint(1) unsigned NOT NULL default '0',
  `enable_bbcode` tinyint(1) unsigned NOT NULL default '1',
  `enable_smilies` tinyint(1) unsigned NOT NULL default '1',
  `enable_magic_url` tinyint(1) unsigned NOT NULL default '1',
  `enable_sig` tinyint(1) unsigned NOT NULL default '1',
  `post_username` varchar(255) collate utf8_bin NOT NULL default '',
  `post_subject` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `post_text` mediumtext character set utf8 collate utf8_unicode_ci NOT NULL,
  `post_checksum` varchar(32) collate utf8_bin NOT NULL default '',
  `post_attachment` tinyint(1) unsigned NOT NULL default '0',
  `bbcode_bitfield` varchar(255) collate utf8_bin NOT NULL default '',
  `bbcode_uid` varchar(8) collate utf8_bin NOT NULL default '',
  `post_postcount` tinyint(1) unsigned NOT NULL default '1',
  `post_edit_time` int(11) unsigned NOT NULL default '0',
  `post_edit_reason` varchar(255) collate utf8_bin NOT NULL default '',
  `post_edit_user` mediumint(8) unsigned NOT NULL default '0',
  `post_edit_count` smallint(4) unsigned NOT NULL default '0',
  `post_edit_locked` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`post_id`),
  KEY `forum_id` (`forum_id`),
  KEY `topic_id` (`topic_id`),
  KEY `poster_ip` (`poster_ip`),
  KEY `poster_id` (`poster_id`),
  KEY `post_approved` (`post_approved`),
  KEY `tid_post_time` (`topic_id`,`post_time`),
  FULLTEXT KEY `post_subject` (`post_subject`),
  FULLTEXT KEY `post_text` (`post_text`),
  FULLTEXT KEY `post_content` (`post_subject`,`post_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

DocSnyder
Registered User
Posts: 42
Joined: Wed Aug 28, 2002 10:05 am

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by DocSnyder » Tue Jun 23, 2009 3:33 pm

Bump.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50916
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by stevemaury » Tue Jun 23, 2009 3:36 pm

Please stop bumping this topic in violation of our 6-hour bump rule - thanks.

I don't know where you are, but people in Europe and the US were asleep when you posted.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

DocSnyder
Registered User
Posts: 42
Joined: Wed Aug 28, 2002 10:05 am

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by DocSnyder » Wed Jun 24, 2009 6:31 am

You can close this thread, I solved it on my own.

User avatar
Dogs and things
Registered User
Posts: 2114
Joined: Fri Sep 01, 2006 9:04 am
Location: Spain
Contact:

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by Dogs and things » Wed Jun 24, 2009 8:51 am

Hello Doc,

What did you do to solve this issue, please?
For phpBB2 support visit phpBB2refugees.

WastedJoker
Registered User
Posts: 74
Joined: Mon Nov 17, 2008 10:20 pm

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by WastedJoker » Wed Jun 24, 2009 8:43 pm

Hopefully OP will reply with his solution so it can help others who may face similar situation :)

Regretful Morning
Registered User
Posts: 2
Joined: Thu Jun 25, 2009 4:48 am

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by Regretful Morning » Thu Jun 25, 2009 4:51 am

I'm also having this issue.

Regretful Morning
Registered User
Posts: 2
Joined: Thu Jun 25, 2009 4:48 am

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by Regretful Morning » Sun Jun 28, 2009 8:13 pm

WastedJoker wrote:Hopefully OP will reply with his solution so it can help others who may face similar situation :)
OK I got it working. Make sure "Check IP against DNS Blackhole List:" is marked no under security settings

naive
Registered User
Posts: 43
Joined: Sun Apr 15, 2007 3:32 pm

Re: Larger board with ~600.000 posts -> posting _very_ slow

Post by naive » Fri Sep 11, 2009 9:19 am

I never have "Check IP against DNS Blackhole List:" enabled but experience the same condition of very slow posting lately.
I have many IP banned in my banlist. I cleared them all and the speed of posting improves dramatically.
I do not know why since the banlist does not change lately.

Locked

Return to “[3.0.x] Support Forum”