MySQL Forums
Forum List  »  Performance

InnoDB performace with deletes
Posted by: Jan Svab
Date: December 14, 2006 04:49AM

Hello,

I have problem when deleting from InnoDB database. I have to delete quite a large amount of data from one InnoDB table. While deleting is in process, the database responds OK. But when the deleting finishes, an enormous disk activity begins and MySQL performance significantly drops. It seems that MySQL is rebuilding its b-trees at that moment and can't anwer queries for the table that is being rebuilt. Unfortunately this post-delete phase takes much longer than the deleting itself. From InnoDB status during this operation I can see only that there are many disk writes and that "1 tablespace extents now reserved for B-tree split operations" and that "main thread state: purging".

Are there any MySQL settings that might affect this performance? Or is there something else that I can do? I tried deleting the records at once and also one by one each in its own transaction (in order to make many small checkpoints at place on one large), but with no difference...


The scenario is as follows:

- MySQL 5.0 on Intel with 32-bit Windows
- InnoDB database with quite a large table (several millions of records, size about 200GB)
- table has only one index, which is clustered primary index, data are stored in blob column
- the table resides in preallocated InnodDB tablespace file with enough space
- continuous flow of inserts into this table at rate of about 20-100 records (5-10MB) per minute, delays between groups of inserts vary approximately from 0.5 second to 15 seconds, there are always three inserts in one group
- deleting a continous block of rows (succesive index entries), of about 40GB

Observed results are:

- when deleting with one SQL statement, deleting takes about 40 minutes
- when deleting with many transactions, deleting takes about 6 hours
- while deleting, it is perfectly possible to insert new rows, without any performance issues
- after deleting, database is occupied by its index activity for about 20 hours (for both deleting techniques)
- while it does this, the inserts are still processed, but at much lower rate than required

If there is anything else that I should specify, I would be happy to add more informations.
Thanks in advance for any help.

Jan

Options: ReplyQuote


Subject
Views
Written By
Posted
InnoDB performace with deletes
1296
December 14, 2006 04:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.