Hello,
I am running mysql version 4.1.7 on SuSE professional 9.1.
I have a MyISAM table which I use for syslog messages. This table gets
about 40-70 inserts a second with peaks of over 400 inserts a second. I am
using INSERT DELAYED for all inserts, and only one thread of my application
has write access to the database.
Nightly I take messages older than three days, copy them to a new table and
then delete them from the primary table. After that I run OPTIMIZE TABLES
on the primary table.
My problem is that a few times a week the table gets corrupted and I can
not select from it until I run REPAIR TABLES on it. What do I need to do to
diagnose this problem so I can hopefully generate a repeatable bug report
on it?
The table format is below. I know I need to better optimize my indexes, for
instance I added the date_program index, but did not go back and remove the
date index. I do plan on fixing that soon, but did not want to change
anything before getting some direction on how to diagnose the problem. I
may remove all indexes except the primary key and date_program as those are
the only two that the majority of my selects hit.
describe todays_syslogs;
+----------+----------------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+----------+----------------------+------+-----+----------+----------------+
| id | bigint(20) unsigned | | PRI | NULL | auto_increment
|
| host | smallint(5) unsigned | | MUL | 0 |
|
| facility | smallint(5) unsigned | | MUL | 0 |
|
| priority | smallint(5) unsigned | | MUL | 0 |
|
| tag | char(2) | | | |
|
| program | smallint(5) unsigned | | MUL | 0 |
|
| date | smallint(5) unsigned | | MUL | 0 |
|
| time | time | | | 00:00:00 |
|
| msg | text | YES | | NULL |
|
+----------+----------------------+------+-----+----------+----------------+
show indexes from todays_syslogs;
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| todays_syslogs | 0 | PRIMARY | 1 | id |
A | 2933115 | NULL | NULL | | BTREE | |
| todays_syslogs | 1 | host | 1 | host |
A | 27 | NULL | NULL | | BTREE | |
| todays_syslogs | 1 | facility | 1 | facility |
A | 11 | NULL | NULL | | BTREE | |
| todays_syslogs | 1 | priority | 1 | priority |
A | 7 | NULL | NULL | | BTREE | |
| todays_syslogs | 1 | program | 1 | program |
A | 55 | NULL | NULL | | BTREE | |
| todays_syslogs | 1 | date | 1 | date |
A | 2 | NULL | NULL | | BTREE | |
| todays_syslogs | 1 | date_program | 1 | date |
A | 2 | NULL | NULL | | BTREE | |
| todays_syslogs | 1 | date_program | 2 | program |
A | 101 | NULL | NULL | | BTREE | |
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
