List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:June 13 2008 3:48pm
Subject:RE: Very slow inserts into InnoDB tables
View as plain text  
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

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