> Hi
>
> We got a problem with a slow database after deleting records using the
> MySQL released with RedHat 7.2 (Server version 3.23.41). Here is the short
> story:
>
> We have a table with a lot of data, at the moment there are 85 million
> records in our table. We developed a script that deleted old data during
> the night. The script runs as a cron job and starts each evening and runs
> trough the night. The script runs a loop where it selects the 10.000
> oldest records and runs a test to see if it is ok to delete them (some
> records are kept even if they are old if they have certain criterias
> fulfilled). The script builds a list and deletes 10 records at a time. The
> script runs fine, but after we have deleted a couple of million
> transactions the database is dog slow. We can see that a select statement
> that used to complete in seconds now takes 30 minutes and this select
> statement locks the table so that everything else must wait.
>
> Our best guess is that something happened to the indexes during the delete
> operation, but we did an explain on the select statement that locks
> everything and it does seem to use the indexes it should.
> We have read that a select statement will lock up the table if there are
> wholes in the database, but it indexes should not be affected? There are
> incoming inserts concurrently with running the large query. When the
> process is locking, it says "sending data" - nothing about temporary
> tables.
>
> Since we can afford a bit of downtime (we luckily have a backup system),
> and for debugging purposes, we have started an analyze table job, although
> we don't think it will make a huge difference. We hope this job will be
> completed by tomorrow. As a sidenote - is there any way of monitoring the
> progress of large jobs of this kind?
>
If you have experienced the same type of problems, or if anyone have
any thought on why this is happening and how to fix it we would really like
to hear it.
> Best Regards
>
> Gunnar Lunde
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
