List:General Discussion« Previous MessageNext Message »
From:dbrb2002-sql Date:February 27 2009 7:51pm
Subject:Re: MySQL Closing/Opening tables
View as plain text  
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

Thread
MySQL Closing/Opening tablesdbrb2002-sql27 Feb
  • Re: MySQL Closing/Opening tablesBaron Schwartz27 Feb
    • Re: MySQL Closing/Opening tablesWm Mussatto27 Feb
    • Re: MySQL Closing/Opening tablesdbrb2002-sql27 Feb
      • Re: MySQL Closing/Opening tablesWm Mussatto27 Feb
      • Re: MySQL Closing/Opening tablesBaron Schwartz28 Feb
  • Re: MySQL Closing/Opening tablesDan Nelson27 Feb
    • Re: MySQL Closing/Opening tablesdbrb2002-sql27 Feb
      • Re: MySQL Closing/Opening tablesEric Bergen28 Feb
      • Re: MySQL Closing/Opening tablesDan Nelson28 Feb