Re: Delete performance
Sorry, semantic miscommunication. It's a heavily used table and the performance of other queries done during the mass deletion slows, which is a problem since this is a near-realtime system where delays in queries cause performance issues in other dependent modules. Jeremy Zawodny wrote: On Mon, Oct 29, 2001 at 05:49:57PM -0500, C. Williams wrote: Does anyone know how to speed the performance of deletes on a large table? I have a large table w/ approx. 180,000,000 rows, with the following table structure: [snip] It takes approximately 45 seconds to delete an hour's worth of data( 90323 rows) from the table, which tends to be a performance problem since it's a table with a fair amount of contention. Platform: x86 OS:RH Linux 2.4.6 MySQL V: 3.23.42-max DB File Type: InnoDB Memory:1 GB HD:130GB SCSI If you're using InnoDB, where's the contention? Jeremy - 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
Re: Delete performance
On Tue, Oct 30, 2001 at 10:50:50AM -0500, C. Williams wrote:
Sorry, semantic miscommunication. It's a heavily used table and the
performance of other queries done during the mass deletion slows,
which is a problem since this is a near-realtime system where delays
in queries cause performance issues in other dependent modules.
Ah, okay.
A couple ideas:
(1) Try running smaller batches of deletes.
(2) Add a 'deleted' ENUM('N','Y') column to the table. Mark records
deleted but only run the delete processing when traffic is less
demandinng.
(3) See if you can safely increase the size of InnoDB's buffer
pool to help performance.
Jeremy
--
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 55 days, processed 1,220,547,898 queries (256/sec. avg)
-
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
Re: Delete performance
On Mon, Oct 29, 2001 at 05:49:57PM -0500, C. Williams wrote: Does anyone know how to speed the performance of deletes on a large table? I have a large table w/ approx. 180,000,000 rows, with the following table structure: [snip] It takes approximately 45 seconds to delete an hour's worth of data( 90323 rows) from the table, which tends to be a performance problem since it's a table with a fair amount of contention. Platform: x86 OS:RH Linux 2.4.6 MySQL V: 3.23.42-max DB File Type: InnoDB Memory:1 GB HD:130GB SCSI If you're using InnoDB, where's the contention? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 53 days, processed 1,200,141,477 queries (258/sec. avg) - 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
