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
> > 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
> > The DBs are relative small about 70M rows and 10Gb size. I can repeat
> > 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
> > / 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