The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'General Discussion' started by mpierre, Dec 28, 2004.

  1. mpierre

    mpierre Well-Known Member

    Jun 30, 2002
    Likes Received:
    Trophy Points:
    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.
  2. mpierre

    mpierre Well-Known Member

    Jun 30, 2002
    Likes Received:
    Trophy Points:
    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,
  3. JamesSmith

    JamesSmith Well-Known Member

    Sep 17, 2003
    Likes Received:
    Trophy Points:
    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.

Share This Page