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



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