List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:February 27 2009 9:15pm
Subject:Re: MySQL Closing/Opening tables
View as plain text  
Looks like the system is doing a lot of disk WRITES.  Your writes/sec are
much higher than your reads/sec. But the time stuff waits in the queue is
low. Did you try top -i with the x option entered?  That will produce a
colored line if a dask is I/O bound.

On Fri, February 27, 2009 11:51, dbrb2002-sql@stripped wrote:
> 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