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/a0000000301.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]
