List:General Discussion« Previous MessageNext Message »
From:Ian Simpson Date:June 13 2008 4:04pm
Subject:RE: Very slow inserts into InnoDB tables
View as plain text  
That's pretty much what I've been doing to get that the drive is running
at 100% bandwidth.

What I'd like is something that just gives the bandwidth of the device
in terms of Mb/s: you can probably work it out using that iostat
command, seeing how much it wrote and what percentage of the bandwidth
it's using, and then doing a calculation with those numbers to get the
100% value, but I don't know if that's valid, since there are generally
a number of other operations going on at the same time.

Thanks

-- 
Ian Simpson

On Fri, 2008-06-13 at 08:48 -0700, Wm Mussatto wrote:
> On Fri, June 13, 2008 08:26, Ian Simpson wrote:
> > Hi Jerry,
> >
> > It could be a kernel issue; however, currently I'm suspecting that the
> > drive in the new server simply doesn't have the same bandwidth
> > capability. The iostat results I'm getting (although I'm not an expert
> > in reading them, having only learned of it about 3 hours ago) suggest
> > that the older server is handling roughly the same data quantities, but
> > just using a much lower percentage of the drive's bandwidth.
> >
> > I can't seem to find a tool which reports on exactly how much write
> > bandwidth a drive has; everything seems to focus on reading speed.
> >
> > Thanks,
> >
> > 
> > --
> > Ian Simpson
> Try something like:
> iostat -xk /dev/sda /dev/sdb /dev/sdc 10
> where the /dev/... are the drives you want to examine and '10' is the
> redisplay rate. last column is %util.
> 
> Hope this helps.
> 
> >
> >
> > On Fri, 2008-06-13 at 11:18 -0400, Jerry Schwartz wrote:
> >> >Having delved a little more into the capabilities of iostat, I've
> >> >discovered that the drive bandwidth seems to be maxed out while MySQL
> >> is
> >> >running, which I'd peg as the primary candidate for the problem.
> >> [JS] That suggests even more strongly that there is a difference in the
> >> kernel
> >> configuration. More physical I/O would drive the traffic up, by
> >> definition.
> >> Either MySQL is causing this, or the system file system is causing it.
> >> >
> >> >Looks like I'll be having more words with my hosting company about
> >> >this...
> >> >
> >> >Thanks for all your help
> >> >?
> >> >--
> >> >Ian Simpson
> >> >
> >> >On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote:
> >> >> >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:
> >> >> [JS] That, to me, suggests that the difference might be in the way
> >> the
> >> >systems
> >> >> themselves are configured. Unfortunately, I don't know how Linux
> >> >handles file
> >> >> system buffering.
> >> >> >
> >> >> >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@stripped>
> >> >> >> 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@stripped>
> >> >> >>                 > 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.
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >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.
> >>
> >>
> ------
> William R. Mussatto
> Systems Engineer
> http://www.csz.com
> 909-920-9154
> 
> 

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.
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