List:General Discussion« Previous MessageNext Message »
From:Jason J. W. Williams Date:December 6 2006 6:19pm
Subject:Re: MySQL Performance Degrades Significantly Over Time
View as plain text  
Hi Daniel,

We were using a software RAID-5 on top of hardware RAID-5 across 3
4-disk volume groups. (1 LUN from each array volume group built the
software RAID-5). So we were able to lose 3 disks in a worst case
scenario.

It seems to me that neither RAID-1 or RAID-5 can lose more than one
disk without losing data, please correct me if I'm wrong.

Our data is 70% write/30% read, so the write latency is important. The
filesystem is ZFS.  Thanks again.

Best Regards,
Jason

On 12/4/06, Daniel da Veiga <danieldaveiga@stripped> wrote:
> On 12/4/06, Jason J. W. Williams <jasonjwwilliams@stripped> wrote:
> > Hi Daniel,
> >
> > Thank you very much for your help and advice. After some examination,
> > we discovered a couple of things. It looks like our storage array
> > layout was really bad for the IOPS MySQL was throwing at it, as a
> > result the InnoDB transactions started to back-up under heavy load.
> > Changing the array layout from RAID-5 to RAID-1 as well as moving the
> > logs to their own spindles corrected the issue. Also, moving the
> > InnoDB fsync log flushing interval from every commit to a 2 second
> > interval helped dramatically.
> >
> > We found the storage was the problem by looking at SHOW INNODB STATUS
> > while looking at the SCSI IOP latency.
> >
> > Does this sound reasonable to you?
> >
>
> Disk IO is one of innodb's bottleneck anyway, but I doubt this could
> hurt performance as you suggested, making it unusable. You're the one
> with access to the system, and thus the only one who can test it and
> be sure ;) . Making a RAID 5 should increase read performance (if you
> calculate the best segment size), but the write operations would be
> not as fast as with a RAID 1, and you're risking data loss if more
> than one of your disks go away. I never trade security for speed, and
> if I were you I would check for another option. What's the most
> frequent operation (read/write) on your tables?
>
> Anyway, glad you solved your problem.
>
> Just out of curiosity, what is your filesystem?
>
> --
> Daniel da Veiga
> Computer Operator - RS - Brazil
> -----BEGIN GEEK CODE BLOCK-----
> Version: 3.1
> GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> ------END GEEK CODE BLOCK------
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
Thread
MySQL Performance Degrades Significantly Over TimeJason J. W. Williams27 Nov
  • Re: MySQL Performance Degrades Significantly Over TimeDaniel da Veiga27 Nov
    • Re: MySQL Performance Degrades Significantly Over TimeJason J. W. Williams4 Dec
      • Re: MySQL Performance Degrades Significantly Over TimeDaniel da Veiga5 Dec
        • Re: MySQL Performance Degrades Significantly Over TimeJason J. W. Williams6 Dec
  • Re: MySQL Performance Degrades Significantly Over TimeDan Buettner27 Nov