MyISAM tables crashing

[s0c0]

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 it
100909  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 it
100913 14:50:48 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './ereplace/sessions.MYI'; try to repair it
100913 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 it
100910  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/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections=600
#key_buffer_size=512M
key_buffer_size=1024M # changed by chris, buffer to low
thread_cache_size=200
max_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.log
long_query_time = 5
# end chris edits

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

Here is output from mysqladmin -debug:

Key caches:
default
Buffer_size:     536870912
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:         42392
not flushed:             0
w_requests:          32626
writes:              18197
r_requests:       73205023
reads:             1022745


handler status:
read_key:     34806965
read_next:    99528256
read_rnd       9035676
read_first:      42954
write:        12774959
delete            3320
update:       10343520

Table status:
Opened tables:      38651
Open tables:           64
Open files:           119
Open streams:           0

Alarm status:
Active alarms:   70
Max used alarms: 88
Next 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.

 

[fenway]

Sounds like just the MYI file is an issue -- which is just the index data -- you should be able to rebuilt it without any data loss.

[s0c0]

Thanks for the response fenway.  By rebuild do you just mean mysqldump the table, drop the table, then rebuild the table from the dump file? Or are you referring to something a bit more elegant?

 

Sounds like just the MYI file is an issue -- which is just the index data -- you should be able to rebuilt it without any data loss.

[ignace]

REPAIR TABLE sessions;

 

You should had been using InnoDB as an engine as it by design doesn't need repairing (it all takes care of that by itself).

[fenway]

Actually, I meant just rebuild the index file from the existing data -- though if you have a recent enough backup, your method works too.

[s0c0]

fenyway - Then we will just completely DROP and rebuild from the mysqldump file.

 

ignace - Innodb is not an option for us.  Our data structure is in bad condition and lots of dev resources would be needed.

[fenway]

Ok -- but repair from frm should work, too, AFAIK.

[s0c0]

We have run repair on these tables multiple times in the past two weeks and they continue to periodically crash.

[Mchl]

fenyway - Then we will just completely DROP and rebuild from the mysqldump file.

 

ignace - Innodb is not an option for us.  Our data structure is in bad condition and lots of dev resources would be needed.

 

How much dev rosources do you need to run two ALTER TABLE queries?

 

Ok I understand that some testing needs to be done beforehand, but moving to InnoDB when you don't need full text searches (and it doesn't look like you do) is next to trivial, and you get rid of those crashes immediately and forever.

[s0c0]

fenyway - Then we will just completely DROP and rebuild from the mysqldump file.

 

ignace - Innodb is not an option for us.  Our data structure is in bad condition and lots of dev resources would be needed.

 

How much dev rosources do you need to run two ALTER TABLE queries?

 

Ok I understand that some testing needs to be done beforehand, but moving to InnoDB when you don't need full text searches (and it doesn't look like you do) is next to trivial, and you get rid of those crashes immediately and forever.

 

We will move to InnoDB if complete drop/create of the tables does not fix the issue.  InnoDb is known to be slower than MyISAM, but the nominal drop in performance is worth the uptime.  That doesn't need dev time, when I think of InnoDB I think of using foreign keys and referential integrity which would cause significant resources to fix bad data.  Obviously just running an alter statement can be done very quickly. We are also having a hardware diagnostic performed.

[Mchl]

In recent versions InnoDB's performance is very often similar or even higher than MyISAM's, although it can ary greatly with the actual queries being run.

 

I will argue, that for sessions table InnoDB is likely to bring some performance improement due to it's row-level locking, and I presume this table is often written to by many concurrent users.

[fenway]

InnoDB's an entirely different beast -- and if you're not used to working with it on at least 7 different levels, it's going to be very, very painful transition.