Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My MYSQL table is very large (250GB, around 200M rows). Recently I've been putting it through a lot -- parallel processes that do a large number of reads.

It has started locking and crashing with alarming regularity. About once per week. The recovery is obviously time consuming.

Does this seem consistent with overloading, or should I suspect another cause (e.g., hardware failure)?

Next, what are the best steps for resolving it? I think switching to InnoDB will probably take a very long time. I can dial down the processes accessing it, but I'm unclear how low to go to stop being "abusive". Any advice?

share|improve this question
    
Multiple concurrent reads (or even writes) shouldn't crash a table. What do your error logs say? Additionally, if you're not on the latest MySQL, you may want to go through changelogs and see if any relevant bugs have been fixed. –  derobert Feb 6 '12 at 22:34
add comment

2 Answers 2

Have you considered sharding your database? It might help with the issue you are experiencing and increase look up time speeds.

share|improve this answer
add comment

In most cases, crashing a MyISAM table is nothing more than throwing mud on the MyISAM file header. It keeps a running count of the number of times the file has been opened. It increments upon opening and decrements upon closing.

You may want to look at the value for open-files-limit. This is an option that you cannot dynamically set with mysqld running. It can be configured at startup. What is interesting is its explanation in the MySQL Documentation:

Changes the number of file descriptors available to mysqld. You should try increasing the value of this option if mysqld gives you the error Too many open files. mysqld uses the option value to reserve descriptors with setrlimit(). If the requested number of file descriptors cannot be allocated, mysqld writes a warning to the error log.

mysqld may attempt to allocate more than the requested number of descriptors (if they are available), using the values of max_connections and table_open_cache to estimate whether more descriptors will be needed.

By default, mysqld sets this values based on the OS and how many maximum open file handles mysqld believes the OS will give to it.

You can take a risk and raise that number in /etc/my.cnf and restart mysql. As mentioned in the MySQL Documentation, a warning will be posted.

Talk to your sysadmin to see if your OS can have its file handle limit raised.

Once you can get it raised, restart mysql and see if the open_files_limit was raised as well.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.