Gunnar Something you can try:
1. Export all data (mysqldump, SELECT INTO OUTFILE), import it in a new table (mysqlimport, LOAD DATA INFILE), create indexes on the new table, drop the old table. This would remove the holes from data and indexes wud be fresh again. This wud also not hold up nething. BUT wud require space and some time. vikash -----Original Message----- From: Gunnar Lunde [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 5:17 PM To: '[EMAIL PROTECTED]' Subject: RE: Performance problems after record deletion. Thank you for your reply, Vikash We have decided not to use optimize because of the size of the table. Optimize would halt the system to long. So we decided just to reuse the space. Our problem is that after the deletion a select query that used to take a second now takes 30 minutes and locks up everything. So we suspect that something happened to the indexes and the select query does a full table scan. However an explain on the query suggest that the indexes are working. During normal operation reusing the space is ok, and it is ok that the select statement locks up everything, but we can not live with a select statement that takes 30 minutes when it used to complete in no time. Ideas to fix this is more than welcome! Best regards Gunnar Lunde > -----Original Message----- > From: Vikash K Agarwal [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 14, 2003 12:22 PM > To: 'Gunnar Lunde'; [EMAIL PROTECTED] > Subject: RE: Performance problems after record deletion. > > > 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 --------------------------------------------------------------------- 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
