This is what MYSQL manual 3.23.41 says, may be it helps you OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.
Rgds Vikash K Agarwal -----Original Message----- From: Gunnar Lunde [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 4:05 PM To: '[EMAIL PROTECTED]' Subject: Performance problems after record deletion. > 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 --------------------------------------------------------------------- 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
