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]

Reply via email to