Thanks for the quick followup Baron..
vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
3 0 100 499380 139256 5604000 0 0 190 693 11 11 20 2 70 8 0
iostat -dx 5 5
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sda 0.02 187.72 0.28 10.36 4.66 1584.73 149.44 2.03 191.16
5.23 5.56
sdb 1.85 371.84 21.72 56.86 1514.44 3956.67 69.63 2.63 33.43
3.44 27.03
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sda 0.00 1527.80 0.40 73.80 3.20 12812.80 172.72 1.59 21.46
0.24 1.76
sdb 0.00 11.60 2.40 10.80 65.60 950.40 76.97 0.08 5.82
3.67 4.84
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sda 0.00 173.00 0.00 11.60 0.00 1476.80 127.31 0.58 50.24
8.22 9.54
sdb 0.00 5.80 0.00 13.40 0.00 153.60 11.46 2.61 195.13
5.63 7.54
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sda 0.00 1624.40 0.00 126.00 0.00 14003.20 111.14 5.31 42.14
0.38 4.84
sdb 0.00 599.60 0.00 82.20 0.00 15697.60 190.97 1.79 21.75
2.32 19.08
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await
svctm %util
sda 0.00 1538.80 0.20 61.60 1.60 12803.20 207.20 5.10 82.48
2.37 14.62
sdb 0.20 1.00 7.40 2.40 200.00 27.20 23.18 0.22 22.73
10.73 10.52
The above is when low to medium load..
________________________________
From: Baron Schwartz <baron@stripped>
To: dbrb2002-sql@stripped
Cc: mysql@stripped
Sent: Friday, February 27, 2009 5:50:11 AM
Subject: Re: MySQL Closing/Opening tables
On Fri, Feb 27, 2009 at 4:19 AM, <dbrb2002-sql@stripped> wrote:
> Hi
>
> Recently I noticed the server takes lot of time on and off when opening and closing
> tables. And I tried to increase the table_cache more the the total tables (file_limit is
> properly set); and the problem still continues and lowering it also continues.. and tried
> to set in middle.. same
>
> Any thoughts on fixing this ? I am going crazy..
>
> Sometimes the threads spin 10-60secs in just opening and closing tables state..
Have you checked to see if your disk is saturated with requests? Try this:
vmstat 5 5
iostat -dx 5 5
Assuming you're on a Unix-like OS.
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
