Re: Diagnosing i/o thrashing
When you say 70% iowait are you referring to vmstat results? There are a lot of things that can be causing iowait, the most obvious being the disks are busy. In which case giving MySQL more memory won't really help, unless it's something that can be solved with caching. What does your context switching rate look like? It's possible your system is busy juggling without actually accomplishing anything. What constitutes a high context switch rate is dependent on the hardware and os. In MySQL, what are your threads_created and opened _tables numbers look like from show status? (not show innodb status). - Original Message - From: Marcus Bointon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 08, 2007 1:20 PM Subject: Diagnosing i/o thrashing I'm trying to diagnose a server which is frequently racking up over 70% iowait when running a big PHP/MySQL app. I've increased mysql memory allocations as far as I dare, but it's just not clear to me where the problem is. My tables are all innodb, but the numbers in 'show innodb status' are not terribly meaningful. It's also unclear whether all the disk activity is down to mysql or the php driving it (and if I stop one, of course the other stops too). Though it is showing some swap usage, there's also a fair amount shown as free or cached. The diagnostic suggestions on phpMyAdmin's status page have been useful to date, but I'm just not sure what measures will be the most effective. How might I best track down the root of the problem? Alternatively, anyone up for a few hours of consultancy? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- 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: Diagnosing i/o thrashing
On 9 Mar 2007, at 15:22, Brent Baisley wrote: When you say 70% iowait are you referring to vmstat results? There are a lot of things that can be causing iowait, the most obvious being the disks are busy. In which case giving MySQL more memory won't really help, unless it's something that can be solved with caching. Both top and vmstat. It seems even the most trivial traffic makes the disks go nuts. The root of the problem is whatever is causing so much disk activity. I guess if MySQL buffers are set too big, it could be squeezing other apps, OTOH, if they are too small, MySQL itself will be generating more disk activity. However, there's no particular evidence that I'm hitting swap. A key issue is that I can't tell if it's MySQL or PHP that's causing it - if I stop one, of course the other stops too. In top, mysql and php processes are often only showing a few % CPU, yet iowait can be over 80%, so it's really not clear what's eating it. From what I've read, next time I'll host mysql on XFS partitions... What does your context switching rate look like? It's possible your system is busy juggling without actually accomplishing anything. What constitutes a high context switch rate is dependent on the hardware and os. That sounds plausible, and I'd not looked at that. Most of the time that seems to be around 200, with busy periods of 500 and peaks of peaks of 1100. I don't know if that's high, low or about right. In MySQL, what are your threads_created and opened _tables numbers look like from show status? (not show innodb status). I have 14,000 opens after 1.5 million queries. There are some databases that are low traffic but that have around 175 tables each. To all of you that have offered help, thank you very much. I've now got Peter Zaitsev on the case, so hopefully he'll be able to pinpoint my problem areas. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of [EMAIL PROTECTED] CRM solutions [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Diagnosing i/o thrashing
I'm trying to diagnose a server which is frequently racking up over 70% iowait when running a big PHP/MySQL app. I've increased mysql memory allocations as far as I dare, but it's just not clear to me where the problem is. My tables are all innodb, but the numbers in 'show innodb status' are not terribly meaningful. It's also unclear whether all the disk activity is down to mysql or the php driving it (and if I stop one, of course the other stops too). Though it is showing some swap usage, there's also a fair amount shown as free or cached. The diagnostic suggestions on phpMyAdmin's status page have been useful to date, but I'm just not sure what measures will be the most effective. How might I best track down the root of the problem? Alternatively, anyone up for a few hours of consultancy? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
