MySQL Forums
Forum List  »  Performance

Re: MySQl crashes several times per day with a table > 300 Mb
Posted by: James Day
Date: December 29, 2004 04:44PM

I don't immediately recognise what you're seeing but here are the thoughts which come to my mind. Sorry if I cover some basic things you already know - I want to be sure nothing obvious has been missed.:)

Do you have a my.cnf file? Are you using one of the MySQL initial suggestions, like my-large.cnf? Is the same server handling both database and Apache/whatever for serving web requests or is it a dedicated database server? If it is shared and you aren't currently using much in your my.cnf file, I suggest trying the my-medium.cnf example but set key_buffer (now normally called key_buffer_size) to 50M or 100M. If it's dedicated to database work, try the my-large.cnf instead. The key_buffer_size is one of the most significant speed options at your disposal.

Do you have flush_time=3600 or similar set? That flushes the MyISAM indexes to disk every hour. If you've something similar, or maybe a cron job doing flush tables every few hours, that might explain slowdowns.

To monitor this you might try looking at SHOW PROCESSLIST while the server is having trouble. Maybe with a cron job or other process to run it once every 30 or 60 seconds until it catches the start of one of these incidents. For routine monitoring I use the excellent MYTOP third party tool, using the i option to sort the longest running query to the top of the list. That often quickly identifies a problematic query.

How many posts per minute is a lot? 50? 500? 5000?

The following comments aren't related to your current problem but maybe of use.

The forum probably displays many posts per topic at the same time, but the primary key is pid (presumably sequential post id in roughly time order). You might try ALTER TABLE ibf_posts ORDER BY topic_id, pid; . That will be very slow because it will rearrange the records into topic and post order, so a topic display will tend to be reading records from the same part of the disk and more are likely to be in the same cache block. That may make display of old posts significantly faster. The bad news is that it only affects data in the table now, not any later data. So it's mostly useful for older topics found through search which will benefit.

The InnoDB table type might be helpful because it may do better caching. It holds changed database records and index entries in RAM in 'dirty" blocks and flushes them to disk gradually, decreasing the total number of disk writes. A transaction log ensures that nothing is lost if there's a power failure or other crash and it has excellent automatic rebuilding features if there is a crash. The ALTER TABLE ... ORDER BY doesn't work with InnoDB but with lots of write buffering that may not matter.

If you're truly out of ideas and in need of a solution fast you might consider whether one of the MySQL consulting options might save enough time to be worth its cost. Nothing quite like an expert looking over a problem sometimes.

I'm not a MySQL employee.

Options: ReplyQuote

Written By
Re: MySQl crashes several times per day with a table > 300 Mb
December 29, 2004 04:44PM

Sorry, you can't reply to this topic. It has been closed.

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.