MySQl crashes several times per day with a table > 300 Mb


Well-Known Member
Jun 30, 2002
I have noticed a problem in MySQL in the past few months.

My version is 4.0.22-standard

I have a Intel(R) Xeon(TM) DUAL CPU 2.40GHz cache size: 512 KB
1 GB of ram.

Linux 2.4.20-31.9smp #1 SMP Tue Apr 13 17:40:10 EDT 2004 i686 i686 i386 GNU/Linux
RedHat 9 i686

It is running using Cpanel versions : WHM 9.9.9 cPanel 9.9.9-C13

It works fine, until a table crosses the 300 MB threshold.

Then, every few hours, the table does a kind of "refresh" which takes from 20 to 45 minutes to do, with the load of the server going up to 500-800, and the number of processes increasing to above 1000, often "crashing" the server.

If we reboot it, we need to repair the faulty table, but it otherwise works.

The load of the server is otherwise below 1 or 2.

It does it on ANY table,. In the past, it was simple logging table, so we could flush it out, but now, it is doing it on the ibf_posts of an Invision forum, so we can't reduce it without losing some messages.

Here is the table format :

CREATE TABLE `ibf_posts` (
`pid` int(10) NOT NULL auto_increment,
`append_edit` tinyint(1) default '0',
`edit_time` int(10) default NULL,
`author_id` mediumint(8) NOT NULL default '0',
`author_name` varchar(32) default NULL,
`use_sig` tinyint(1) NOT NULL default '0',
`use_emo` tinyint(1) NOT NULL default '0',
`ip_address` varchar(16) NOT NULL default '',
`post_date` int(10) default NULL,
`icon_id` smallint(3) default NULL,
`post` text,
`queued` tinyint(1) NOT NULL default '0',
`topic_id` int(10) NOT NULL default '0',
`post_title` varchar(255) default NULL,
`new_topic` tinyint(1) default '0',
`edit_name` varchar(255) default NULL,
`post_parent` int(10) NOT NULL default '0',
`post_key` varchar(32) NOT NULL default '0',
`post_htmlstate` smallint(1) NOT NULL default '0',
PRIMARY KEY (`pid`),
KEY `topic_id` (`topic_id`,`queued`,`pid`),
KEY `author_id` (`author_id`,`topic_id`),
KEY `post_date` (`post_date`),
KEY `topic_id_2` (`topic_id`),
KEY `post_key` (`post_key`),
KEY `author_id_2` (`author_id`),
KEY `post_parent` (`post_parent`),
FULLTEXT KEY `post` (`post`)

Here are the row statistics :
Row Statistic:
Statements Value
Format dynamic
Rows 239,662
Row length ø 866
Row size ø 1,358 Bytes
Next Autoindex 242,788

Here are the space usage :

pace usage:
Type Usage
Data 202,834 KB
Index 114,959 KB
Total 317,793 KB

It is as if, every few hours the database had to increase it's size, and has such, was rewritting it's whole content, taking a long time to update.

The forum in question gets a LOT of posts every minute.

We have activated caching in Mysql ( 50 MB now ), to no effect.

If we restart MySQL as soon as we see the load increasing, the server returns to normal, the table is in use so we need to repair it.


Well-Known Member
Jun 30, 2002
I want to update everyone on this...

We tried every possible software solution we could find and suddenly, we decided to try for more RAM.

We doubled the ram from 1GB to 2GB and the server went back as normal !

It is Probable that MySQL was reading the whole database ( with the 300 MB table ) into ram, and thus forcing too much swapping.

With 2GB of ram, it is much more smooth,


Well-Known Member
Sep 17, 2003
UK, Luton
We're experiencing a similar problem with our databases on a web server with 500 sites. We plan to move the databases to their own server, but in the interim we may need to consider upgrading the memory.

If we proceed with such an upgrade I'll reply back to this thread with the results.