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
