Re: MySQL Performance Degrades Significantly Over Time
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 <[EMAIL PROTECTED]> wrote: On 12/4/06, Jason J. W. Williams <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
On 12/4/06, Jason J. W. Williams <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
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?
Best Regards,
Jason
On 11/27/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
On 11/27/06, Jason J. W. Williams <[EMAIL PROTECTED]> wrote:
> Hi,
>
> We're running MySQL 5.0.27 under Solaris 10 on both Opteron and
> UltraSparc T1 machines. The performance on both boxes starts out great
> when the process is fresh, however over the course of a week of heavy
> use the performance degrades to the point where its nearly unusable.
>
> The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to
> what to look for that might cause performance to degrade over time.
> Any pointers are greatly appreciated.
>
> On a side note, when the Opteron is a slave of the T1, when the T1 has
> heavy load the Opteron slave falls behind on its replication duties.
> The whole thing is kind of strange. Thank you again in advance.
>
First, enable (if you don't have it already) logging, without any
warnings or errors its kinda complicated to check for a real problem.
From what you say, I can assume your server is probably eating memory
on dead process or its trying to launch multiple threads to answer
requests.
Check the logs, check process ("show processlist" at mysql), check
threads ("ps" on *ix), if there are dead process on the list, check
your applications (web or standalone) and see if the connections are
being closed correctly, decrease the wait_timeout and
interactive_timeout variables to automatically clean this process, but
be careful with those options, as they may kill your idle clients too
fast. If there are many threads, check the variables that deal with
thread launching, and your OS for limits on memory or cpu time. Also,
while you're at it:
http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
http://www.mysql.com/news-and-events/on-demand-webinars/mysql-performance-tuning.php
Go for it.
--
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
Jason, in addition to Daniel's suggestions, I'll throw this out there: I had a somewhat similar problem with a database I used to own, where a handful of very hard-hit tables would become progressively slower over time, despite the fact that (due to daily archiving and purging) they were not growing in size. For me, running OPTIMIZE operations periodically did the trick, keeping the tables performing fast. Daily wasn't sufficient, actually - I ended up optimizing the key tables every other hour, though that was probably more often than needed. I think the tables were becoming fragmented in memory, possibly along with the index data. This was with MyISAM, and I do not know whether performance would have improved with mysqld restarts, as we never really had occasion to restart mysqld except during major upgrades. HTH, Dan On 11/27/06, Jason J. W. Williams <[EMAIL PROTECTED]> wrote: Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
On 11/27/06, Jason J. W. Williams <[EMAIL PROTECTED]> wrote:
Hi,
We're running MySQL 5.0.27 under Solaris 10 on both Opteron and
UltraSparc T1 machines. The performance on both boxes starts out great
when the process is fresh, however over the course of a week of heavy
use the performance degrades to the point where its nearly unusable.
The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to
what to look for that might cause performance to degrade over time.
Any pointers are greatly appreciated.
On a side note, when the Opteron is a slave of the T1, when the T1 has
heavy load the Opteron slave falls behind on its replication duties.
The whole thing is kind of strange. Thank you again in advance.
First, enable (if you don't have it already) logging, without any
warnings or errors its kinda complicated to check for a real problem.
From what you say, I can assume your server is probably eating memory
on dead process or its trying to launch multiple threads to answer
requests.
Check the logs, check process ("show processlist" at mysql), check
threads ("ps" on *ix), if there are dead process on the list, check
your applications (web or standalone) and see if the connections are
being closed correctly, decrease the wait_timeout and
interactive_timeout variables to automatically clean this process, but
be careful with those options, as they may kill your idle clients too
fast. If there are many threads, check the variables that deal with
thread launching, and your OS for limits on memory or cpu time. Also,
while you're at it:
http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
http://www.mysql.com/news-and-events/on-demand-webinars/mysql-performance-tuning.php
Go for it.
--
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/[EMAIL PROTECTED]
MySQL Performance Degrades Significantly Over Time
Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
