MySQL Forums
Forum List  »  Performance

MySQl crashes several times per day with a table > 300 Mb
Posted by: Martin-Pierre Frenette
Date: December 28, 2004 08:22AM

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.

Options: ReplyQuote

Written By
MySQl crashes several times per day with a table > 300 Mb
December 28, 2004 08:22AM

Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.