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



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