List:General Discussion« Previous MessageNext Message »
From:Alex Arul Lurthu Date:June 13 2008 2:31pm
Subject:Re: Very slow inserts into InnoDB tables
View as plain text  
replication based inserts are serial whereas most of the time the inserts on
masters are concurrent. this leads to the slaves falling behind. to tackle
this we have used the following strategies :

1. Use raid 0 on the slaves (master users raid 10) so as to speed up writes.

2. pre fetch and cache the data that needs to be modified by the slave sql
thread.
3. set innodb flush trx log commit to 2 or even 0.
4. Out of desperation sometimes disable innodb double write and also xa
support.

On Fri, Jun 13, 2008 at 7:33 PM, Ian Simpson <ians@stripped> wrote:

>
>
> Hi guys, thanks for pitching in.
>
> The inserts are from replication; we're not using transactions on the
> master (yet), and I don't think there's a way of telling MySQL to batch
> incoming replication statements if they're not already in a transaction.
>
> Disk usage: the older server (the one that's running fine) is running
> more transactions per second, but has lower blocks written and read per
> second than the new server:
>
> The working server (which in addition to replicating is also handling a
> bunch of read queries)
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 88.47 782.20 998.77 9046888130 11551757459
>
> The new server, which is just trying to handle replication
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 77.83 1367.55 2914.72 358474084 764029986
>
> Thanks,
> 
> --
> Ian Simpson
>
>
>
>
> On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote:
> > also how often do you issue a commit. batching the inserts inside a
> > transaction might help.
> >
> > On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar <anandkl@gmail.com>
> > wrote:
> > check for iostat to see if the disk is heavly used.
> >
> >
> > On 6/13/08, Ian Simpson <ians@stripped> wrote:
> > Hi Alex,
> >
> > Configurations are identical, other than the
> > differences I initially
> > mentioned. I've diffed both the configuration files
> > and the output of
> > SHOW VARIABLES on both servers.
> >
> > I've contacted my hosting provider to ask about the
> > RAID settings.
> >
> > Variable_name: innodb_flush_log_at_trx_commit
> > Value: 1
> > Variable_name: sync_binlog
> > Value: 0
> > Variable_name: innodb_locks_unsafe_for_binlog
> > Value: OFF
> >
> > Thanks
> >
> > --
> > Ian Simpson
> >
> > On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu
> > wrote:
> > > Please check if the my.cnf configurations to be the
> > same.
> > >
> > > What are your configuration parameters in terms of
> > innodh flush log
> > > trx commit , bin logging, sync binlog and innodb
> > unsafe for binlog ?
> > >
> > > If the systems have raid, check if the BBWC is
> > enabled on the new host
> > > and WB is enabled.
> > >
> > >
> > > On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson
> > <ians@myjobgroup.co.uk>
> > > wrote:
> > > Hi list,
> > >
> > > Have a bit of a mystery here that I hope
> > somebody can help
> > > with.
> > >
> > > I've just got a new server that I'm using as
> > a dedicated MySQL
> > > server.
> > > In terms of hardware it's pretty much
> > identical, if not
> > > slightly
> > > superior to an existing server already in
> > production use.
> > >
> > > It's having a real struggle processing
> > INSERT statements to
> > > InnoDB
> > > tables; it's maxing out at around 100
> > inserts per second, even
> > > with very
> > > simple two column tables (inserts into
> > MyISAM tables run
> > > fine).
> > > Meanwhile, the original server can happily
> > process around 1000
> > > inserts/sec into an identical table.
> > >
> > > The MySQL configuration of the two databases
> > is identical,
> > > except for
> > > the tablespace file size (the new server has
> > a larger
> > > tablespace
> > > defined), and the InnoDB logs (again, new
> > server has larger
> > > logs).
> > >
> > > Can anybody suggest an area of investigation
> > as to the cause?
> > >
> > > Thanks,
> > > --
> > > Ian Simpson
> > >
> > > This email may contain confidential
> > information and is
> > > intended for the recipient(s) only. If an
> > addressing or
> > > transmission error has misdirected this
> > email, please notify
> > > the author by replying to this email. If you
> > are not the
> > > intended recipient(s) disclosure,
> > distribution, copying or
> > > printing of this email is strictly
> > prohibited and you should
> > > destroy this mail. Information or opinions
> > in this message
> > > shall not be treated as neither given nor
> > endorsed by the
> > > company. Neither the company nor the sender
> > accepts any
> > > responsibility for viruses or other
> > destructive elements and
> > > it is your responsibility to scan any
> > attachments.
> > >
> > >
> > >
> > > --
> > > Thanks
> > > Alex
> > > http://alexlurthu.wordpress.com
> >
> > This email may contain confidential information and is
> > intended for the recipient(s) only. If an addressing
> > or transmission error has misdirected this email,
> > please notify the author by replying to this email. If
> > you are not the intended recipient(s) disclosure,
> > distribution, copying or printing of this email is
> > strictly prohibited and you should destroy this mail.
> > Information or opinions in this message shall not be
> > treated as neither given nor endorsed by the company.
> > Neither the company nor the sender accepts any
> > responsibility for viruses or other destructive
> > elements and it is your responsibility to scan any
> > attachments.
> >
> >
> >
> >
> >
> > --
> > Thanks
> > Alex
> > http://alexlurthu.wordpress.com
>
>
> This email may contain confidential information and is intended for the
> recipient(s) only. If an addressing or transmission error has misdirected
> this email, please notify the author by replying to this email. If you are
> not the intended recipient(s) disclosure, distribution, copying or printing
> of this email is strictly prohibited and you should destroy this mail.
> Information or opinions in this message shall not be treated as neither
> given nor endorsed by the company. Neither the company nor the sender
> accepts any responsibility for viruses or other destructive elements and it
> is your responsibility to scan any attachments.
>



-- 
Thanks
Alex
http://alexlurthu.wordpress.com
Thread
Very slow inserts into InnoDB tablesIan Simpson13 Jun
  • Re: Very slow inserts into InnoDB tablesAlex Arul Lurthu13 Jun
    • Re: Very slow inserts into InnoDB tablesIan Simpson13 Jun
      • Re: Very slow inserts into InnoDB tablesAnanda Kumar13 Jun
        • Re: Very slow inserts into InnoDB tablesAlex Arul Lurthu13 Jun
          • Re: Very slow inserts into InnoDB tablesIan Simpson13 Jun
            • Re: Very slow inserts into InnoDB tablesAlex Arul Lurthu13 Jun
            • RE: Very slow inserts into InnoDB tablesJerry Schwartz13 Jun
              • RE: Very slow inserts into InnoDB tablesIan Simpson13 Jun
                • RE: Very slow inserts into InnoDB tablesJerry Schwartz13 Jun
                  • RE: Very slow inserts into InnoDB tablesIan Simpson13 Jun
                    • RE: Very slow inserts into InnoDB tablesWm Mussatto13 Jun
                      • RE: Very slow inserts into InnoDB tablesIan Simpson13 Jun
                        • Re: Very slow inserts into InnoDB tablesNick Adams15 Jun