I just changed back to the 'order by id LIMIT 5000' and I have not seen further corruption.
I also wanted to mention the other details about the system: The system is running Linux 2.6.16.21-0.8-smp (SLES10) mysql-5.0.18-20.5 And I do grant that we probably need to change the key cache. mysql> show status like 'key%'; +------------------------+-----------+ | Variable_name | Value | +------------------------+-----------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 0 | | Key_blocks_used | 13396 | | Key_read_requests | 996090198 | | Key_reads | 26009940 | | Key_write_requests | 154990787 | | Key_writes | 59259346 | +------------------------+-----------+ 7 rows in set (0.00 sec) # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M #expire_logs_days = 1 [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M I am open to other tuning also. mark Mark Dalton wrote: > I am getting table corruption on one machine that I was testing a change > to get better performance. > > This is using the C API to connect to the database. With the delete > without > the sort by id, I started getting the database key corruption. There > were about > 2.6 million rows matched to be deleted, so I also limited to delete only > normally less > than 6000 at a time out of about 3.9 million rows. It is still > happening with 2.6 million > records and 1.3 million marked for deletion. > > mysql> check table mzLog; > +--------------+-------+----------+-------------------------------+ > | Table | Op | Msg_type | Msg_text | > +--------------+-------+----------+-------------------------------+ > | mazama.mzLog | check | warning | Table is marked as crashed | > | mazama.mzLog | check | error | Found 2636585 keys of 2636586 | > | mazama.mzLog | check | error | Corrupt | > +--------------+-------+----------+-------------------------------+ > > My understanding was that if the table was locked write when deleting or > writing that the keys should be safe. Perhaps there is something else > I could > do to resolve this. > > 1. Add a entry: > Lock the table.. > add a entry > unlock the table > > or > 2. Delete old entries no longer needed. > lock > Count the number of entries that match. > unlock > Lock the table > delete any logs marked for deletion up to 5000 at a time > (previously I did a sort by id LIMIT 5000), but the > sort was taking too much time, since 2.7 million > records > matched). > unlock table > > or > 3. List/search/query entries. > lock > count the number of matches (since we need to take a > limited number at time) > unlock > lock > query (perhaps loop with taking 5000 matches at a time > in get the next with the last id). > unlock > > > > ------------------------------------------------------------------------ > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]