Thanks, Fred. The delete job has been running for 21 hours
at this point, and now rather simple queries on other tables hang
(e.g. SELECT COUNT(*) FROM other_table WHERE index=some_value).
I'll let it run overnight to see what happens, but I'm not hopeful.
Here's some addition information for those interested. The table
in question has 3 indices. The data and index files for the table
each occupy about 5Gb.
I'm running the 3.23.30 binaries downloaded from MySQL.com
Ominously, the data and index files for the table have not been
touched since about 1 hour after the job started.
When I tried to kill the long query on the unrelated table
described above, the "Command" field from "SHOW PROCESSLIST"
went from "Query" to "Killed" but the thread won't go away.
>From prior experience, I know that trying to shut the server
down using mysqladmin won't work and I'll have to execute a
kill -9 which will damage the database.
Is it possible that the server tried to create a temporary file
but ran out of space and failed silently? This actually
happened to me using REPAIR TABLE. There was not enough space
in the temporary directory, and it hung completely. I did not
realize what had happened until I tried to repair the table using
myisamchk, which reported the problem.
Michael
>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
