Hi Raj, Ananda, the schema is very simple, we don't have any blob or text
column.
I thought the same about the log files, so I tried with diferent sizes but
nothing change.
This is the output of iostat -x 1 when the performance is slow running a
restore
avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.00 0.00 0.00 100.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
md0 0.00 0.00 0.00 3.92 0.00 59.80 15.25
0.00 0.00 0.00 0.00
sdh 0.00 0.00 0.00 0.98 0.00 31.37 32.00
0.00 0.00 0.00 0.00
sdi 0.00 0.00 0.00 0.98 0.00 23.53 24.00
0.00 0.00 0.00 0.00
sdj 0.00 0.00 0.00 1.96 0.00 4.90 2.50
0.00 0.00 0.00 0.00
sdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.00 0.00 0.00 100.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
md0 0.00 0.00 0.00 1.98 0.00 17.82 9.00
0.00 0.00 0.00 0.00
sdh 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdi 0.00 0.00 0.00 0.99 0.00 15.84 16.00
0.00 0.00 0.00 0.00
sdj 0.00 0.00 0.00 0.99 0.00 1.98 2.00
0.00 0.00 0.00 0.00
sdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
2010/3/18 Raj Shekhar <spa...@rajshekhar.net>
> 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=maxb...@gmail.com
>
>
