MyISAM Table Crashes

MySQL & MariaDB Other MySQL® Questions
1

After migrating data centers we’ve been experiencing a number of table crashes on our new server (supposedly better server). This is affecting two tables.

sessions

sesskey varchar(32) primary
expiry int(11) unsigned
value text

This table is just used for temporary storage of customer sessions and typically has anywhere from a few hundred to just over a 1,000 records in it. I know the varchar primary key is bad, but its old code and a b*tch to rewrite at this point. Here are errors:

100909 9:10:08 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘./ereplace/sessions.MYI’; try to repair it100909 9:10:08 [ERROR] Got error 127 when reading table './ereplace/sessions’100909 9:10:11 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘./ereplace/sessions.MYI’; try to repair it100913 14:50:48 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘./ereplace/sessions.MYI’; try to repair it100913 14:50:48 [ERROR] /usr/libexec/mysqld: Table ‘./ereplace/sessions’ is marked as crashed and should be repaired

product_popularity

popularity_id int(11) primary
categories_id int(11) index
timestamp datetime index
ip_address varchar(15)
parent_id int(11)

This one is designed a bit better byt the index on the timestamp is useless and should really be split into an indexed date column and an unindexed time column. This table is a bit larger with 35,649 records but by no means big. Here are errors:

100910 5:30:58 [ERROR] Got error 134 when reading table './ereplace/tool_popularity’100910 9:04:13 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘./ereplace/tool_popularity.MYI’; try to repair it100910 9:04:15 [ERROR] /usr/libexec/mysqld: Table ‘./ereplace/tool_popularity’ is marked as crashed and should be repaired

Finally here is our my.cnf file. Our key_buffer_size still looks to low, but I am slowly adjusting it up to measure impact. Our system actually has 12 GB RAM on hand.

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1max_connections=600#key_buffer_size=512Mkey_buffer_size=1024M # changed by chris, buffer to lowthread_cache_size=200max_allowed_packet = 8MB# Disabling symbolic-links is recommended to prevent assorted security risks;# to do so, uncomment this line:# symbolic-links=0# added by chris log-slow-queries = /var/log/mysql-slow.loglong_query_time = 5# end chris edits[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Here is output from mysqladmin -debug:

Key caches:defaultBuffer_size: 536870912Block_size: 1024Division_limit: 100Age_limit: 300blocks used: 42392not flushed: 0w_requests: 32626writes: 18197r_requests: 73205023reads: 1022745handler status:read_key: 34806965read_next: 99528256read_rnd 9035676read_first: 42954write: 12774959delete 3320update: 10343520Table status:Opened tables: 38651Open tables: 64Open files: 119Open streams: 0Alarm status:Active alarms: 70Max used alarms: 88Next alarm time: 28531

and from mysqladmin -status:

Uptime: 3178 Threads: 44 Questions: 13720774 Slow queries: 6 Opens: 134342 Flush tables: 1 Open tables: 64 Queries per second avg: 4317.424

Ideas I’ve been playing around with to improve performance and end table crash:

  • enabled slow query log today to log everything over 5 seconds (will slowly adjust down over days)
  • adjusted key buffer size up as mentioned above
  • THINKING about dropping and recreating the tables mentioned above as there could be a problem with the table files, but thats just a guess.

Any help or suggestions would be much appreciated.

2
[B]systematical wrote on Tue, 14 September 2010 21:48[/B]
After migrating data centers we've been experiencing a number of table crashes on our new server (supposedly better server). This is affecting two tables.
I think I would start by looking at hardware, RAM or disks.

I’m guessing that these tables are the most frequently used/updated hence why they are mostly affected.

As for your configuration I would suggest you to check the tables_open_cache setting.
According to the output from mysqladmin -status every 100th query is opening a table which feels a bit high. But beware of possible OS filehandle limitations so that you don’t set it too high.

3

Thanks for the response. We are working on getting hardware diagnostics done. In reading: [URL]http://dev.mysql.com/doc/refman/5.1/en/table-cache.html[/URL] it says “table_open_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.”

I’m not sure exactly what it means when it says “where N is the maximum number of tables per join in any of the queries which you execute”.

Obviously 64 is to low given our max_connections though. Any idea on this?

4

Actually which OS are you running?

I started thinking that your entire problem might be due to that you are running out of file descriptors.

Run ulimit -a to see if there is a limit in your OS.

Read more here.

[B]Quote:[/B]

I’m not sure exactly what it means when it says “where N is the maximum number of tables per join in any of the queries which you execute”.

It means that if the biggest query you have joins 2 tables. Then your total table_open_cache should be: 600 connections X 2 tables = 1200 open tables

But this will mean that your total file descriptors used will probably be somewhere around:

1200 open tables* 2(for two filedescriptors per open table)+ number of tables in your database since the file descriptor for the index file is shared between all connections)= about 2450 file descriptors (my guess about how many tables you got in your database)

Which is when you must start checking the ulimit for the OS etc so that it can handle so many open files.

5

Running Centos 5.5 and MySQL 5.0.77

shell > ulimit -a:

core file size (blocks, -c) 0data seg size (kbytes, -d) unlimitedscheduling priority (-e) 0file size (blocks, -f) unlimitedpending signals (-i) 104448max locked memory (kbytes, -l) 32max memory size (kbytes, -m) unlimitedopen files (-n) 1024pipe size (512 bytes, -p) 8POSIX message queues (bytes, -q) 819200real-time priority (-r) 0stack size (kbytes, -s) 10240cpu time (seconds, -t) unlimitedmax user processes (-u) 104448virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimited

Also just upped key_buffer_size size to 3072 MB which is 30% of total system memory (per recommendation on mysqlperformanceblog.com)

6

As you can see you have a limit of 1024 open files.

open files (-n) 1024

Which is small compared to your 600 connections limit.

This is how you can change it.

I’m guessing a limit of 5000 would be enough for your current needs.

And by executing this in MySQL:

SHOW VARIABLES LIKE ‘open_files_limit’;

You can check that the limit has in fact been changed for the mysql user that the mysql server is running as.
Ref: open_files_limit