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.
sesskey varchar(32) primary
expiry int(11) unsigned
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
popularity_id int(11) primary
categories_id int(11) index
timestamp datetime index
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.