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 <[EMAIL PROTECTED]> >> >> >> wrote: >> >> >> check for iostat to see if the disk is heavly used. >> >> >> >> >> >> >> >> >> On 6/13/08, Ian Simpson <[EMAIL PROTECTED]> 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 >> >> >> <[EMAIL PROTECTED]> >> >> >> > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
