On Wed, Feb 28, 2001 at 02:38:40PM -0500, Michael Villalba wrote:
> I have a rather large table (~130 million rows) running under MySQL
> 3.23.30 and have been very pleased with the performance of the
> database. However, occasionally I have to delete millions of rows
> from the table, and it appears that MySQL hangs on these jobs. To
> be specific, I am running a delete job now that will remove about
> 4 million rows. It has been running for 11 hours on a 4-processor
> Sun E450 with 4Gb. The OS is Solaris 2.7. For about the last
> 10 hours the state displayed by 'show processlist' has been 'end'.
>
> I've read in the archives that large deletes take a long time. But
> should they take this long?
Yesterday, I deleted 1.6M records from a 17M record table, which
took 13 minutes. There are only two indexes on the table. And I
considered this to be quick, compared to the deletion of 467k rows
from a table which took almost half an hour. That table had about 8
indexes I think, with a larger record size as well. All on an
Ultra-250 dual processor, with just 512M of memory.
I did no optimization on the MySQL memory settings. Doing that might
help, as would more real memory, but I think most of the time would
be on disk writes anyway.
So, as far as I can tell, the answer is yes.
This is also the reason I never used this method to delete old records
from my logging tables. Instead, I rebuilt a seondary table from
scratch, starting at a later logging date and then switching tables.
This allowed me to keep the table available at all time.
Since a few weeks I switched to MySQL 3.23.33 and use MERGE tables.
I'm not sure what you're doing but you may want to look at them.
> Also, can anyone tell me what process
> state 'end' (from 'show processlist') means?
>
Sorry, I can't.
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
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
