SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast BogusRed's Avatar
    Join Date
    Dec 2008
    Location
    San Jose, CA, USA
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to prevent table from crashing?

    I just had one of the tables in my database crash. It's never happened before so I freaked out at first. But I was able to just do a quick repair on it to restore it. Doesn't appear to be any data loss which is great.

    The table that crashed does not have a lot inserts/updates/deletes (maybe 20 per day). But it does have a fair amount of selects on it.

    My question is, what causes this sort of thing to happen? Is it too much traffic accessing it? How would I check how many queries per day it's getting? How much is a lot? Is there maintenance I should be doing on my mysql tables to prevent this sort of thing?

    I would like to know so I can prevent this from happening again. When this table is inaccessible like this, my site is useless.

    Thanks!

  2. #2
    SitePoint Enthusiast BogusRed's Avatar
    Join Date
    Dec 2008
    Location
    San Jose, CA, USA
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The same table crashed again today.

    The messages that HeidiSQL gave me when I did a quick repair are as follows:
    Wrong bytesec: 35- 0-206 at 7262208; skipped
    Wrong bytesec: 35- 0-206 at 7262208; skipped
    Found link that points at 44473047334272639 (outside data file) at 7263380
    Found block with too small length at 7263604; skipped
    found block that points outside data file at 7264264
    Found block that points outside data file at 7265840
    Number of rows has changed from 16817 to 20558

    Anyone have suggestions for what I should do?

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,909
    Mentioned
    96 Post(s)
    Tagged
    0 Thread(s)
    Any cron jobs or unusual queries running at the time, anything stand out in php's (or whatever server-side language your using) error log or in the web server's error log?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Enthusiast BogusRed's Avatar
    Join Date
    Dec 2008
    Location
    San Jose, CA, USA
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apache log file had nothing useful.

    I checked the Mysql error logs. The first read error was at 101228 2:25:09 for this most recent crash. the first for the previous crash was 101221 10:32:04.

    101228 11:26:07 [ERROR] Got error 127 when reading table './b0gusred_bogusred/BR_art'
    101228 11:27:45 [ERROR] /usr/sbin/mysqld: Table 'BR_art' is marked as crashed and should be repaired
    101228 11:27:45 [ERROR] /usr/sbin/mysqld: Sort aborted
    101228 11:30:57 [ERROR] Got error 127 when reading table './b0gusred_bogusred/BR_art'
    101228 11:31:03 [ERROR] Got error 127 when reading table './b0gusred_bogusred/BR_art'
    101228 11:31:05 [ERROR] /usr/sbin/mysqld: Table 'BR_art' is marked as crashed and should be repaired
    101228 11:31:05 [ERROR] /usr/sbin/mysqld: Sort aborted
    101228 11:32:10 [Note] Retrying repair of: './b0gusred_bogusred/BR_art' with keycache
    101228 11:32:11 [Note] Found 20558 of 20572 rows when repairing './b0gusred_bogusred/BR_art'
    101229 1:38:56 [Note] Found 2415908 of 2415939 rows when repairing './b0gusred_bogusred/BR_online'


    Looks like I have two tables that crashed. The BR_online sometimes crashes because it has a lot of data and is written to frequently. I don't care if it crashes. In fact, I went ahead and truncated it. For some reason, my automatic deletions aren't working. it's supposed to clear out the old records every day. I'll look into fixing it.

    But the BR_art has more critical data and is the one that crashed last week on the 21st and again yesterday on the 28th.

    I've had higher traffic as of late, which could be why I'm experiencing these crashes now all of a sudden. Could this be caused by multiple select queries? I have some heavy complex select queries for the main page.

  5. #5
    SitePoint Enthusiast BogusRed's Avatar
    Join Date
    Dec 2008
    Location
    San Jose, CA, USA
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had another table crash yesterday that hadn't crashed before. I've asked my host to investigate to see if it's a bad hard drive. I hope that's what caused this. If it's not the drive, I have no idea what to do next other than try to optimize my indexes and queries.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •