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]
