List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:March 19 2010 4:41am
Subject:Re: Innodb and bulk writes
View as plain text  
How big is your /tmp folder, can u move this to other fast moving disk.

On Fri, Mar 19, 2010 at 4:27 AM, Raj Shekhar <spamme@stripped> wrote:

> Max Bube <maxbube <at> gmail.com> writes:
>
>
> > The problem starts when I run bulk writes like an alter table or a
> restore
> > from mysqldump, its starts processing more than 50000 rows/s but suddenly
> > the ratio goes down to 100 rows /sec. and then its stucked at this ratio
> > even if I restart MySQL. The only way to get good perfomance again is
> > deleting all innodb files (ibdata, iblog files) and restoring the DB
> again.
> >
> > The DBs are relative small about 70M rows and 10Gb size. I can repeat
> this
> > behavior all the time just running 2 restores of the same database.
> >
> > Another example when its stucked:
> >
> > I want to delete 1M rows
> > "delete from table where id IN (select id from ....)"  deletes 100 rows /
> > sec
> > but if I run 1 Million "delete from table where id = xxx" deletes 10000
> rows
> > / sec
>
> How busy are your disks when you start seeing slowdown in the delete
> process?  Are there blobs or big varchars in the deletes that you are
> doing?  Innodb might be filling up its log files and when you see a
> slow down, it might be flushing the log to the disk.
>
> One workaround for this is to not delete million rows, but to delete
> in batches of 1000 rows.  My guess would be that if each row is of
> size B, and you delete in a batch size of [innodb_log_file_size (in
> bytes) - 100 MB (in bytes)]/B , you should not see a slowdown.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

Thread
Innodb and bulk writesMax Bube18 Mar
  • Re: Innodb and bulk writesAnanda Kumar18 Mar
    • Re: Innodb and bulk writesMax Bube18 Mar
  • Re: Innodb and bulk writesRaj Shekhar19 Mar
    • Re: Innodb and bulk writesAnanda Kumar19 Mar
    • Re: Innodb and bulk writesMax Bube19 Mar