Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

w2ksp4.exe, WwwxxXdbf, procmail "FROM_MAILER" patch, Use of assignment to $[ is deprecated at /usr/local/sbin/apxs line 86. , wwwxxx vim, mysql closing table and opening table, 800c5000, setgid operation not permitted, pciehp: acpi_pciehprm on IBM, WWWXXX.DBF

Links

XODOX
Impressum

#1: MySQL Closing/Opening tables

Posted on 2009-02-27 10:19:18 by dbrb2002-sql

--0-728681590-1235726358=:83904
Content-Type: text/plain; charset=us-ascii

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..

Thanks for reply
Rick
--0-728681590-1235726358=:83904--

Report this message

#2: Re: MySQL Closing/Opening tables

Posted on 2009-02-27 14:50:11 by Baron Schwartz

On Fri, Feb 27, 2009 at 4:19 AM, <dbrb2002-sql@yahoo.com> wrote:
> Hi
>
> Recently I noticed the server takes lot of time on and off when opening a=
nd closing tables. And I tried to increase the table_cache more the the tot=
al 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 s=
tate..

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.

--=20
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message

#3: Re: MySQL Closing/Opening tables

Posted on 2009-02-27 17:35:03 by mussatto

On Fri, February 27, 2009 05:50, Baron Schwartz wrote:
> On Fri, Feb 27, 2009 at 4:19 AM, <dbrb2002-sql@yahoo.com> 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
Slight variant if you use logical volumes.
iostat -x 10 /dev/sda /dev/sdb /dev/sdc /dev/sdd /dev/sde
Where the /dev/...'s are the actual base disks. W/O the -d you get cpu
loads as well. I use top -i (then z for color) if I need to know what
processes are running. The is on Debian GNU Linux.
Look at the await column:
"The average time (in milliseconds) for I/O requests issued to the device
to be served. This includes the time spent by the requests in queue and
the time spent servicing them. "

> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mussatto@csz.com
>
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Report this message

#4: Re: MySQL Closing/Opening tables

Posted on 2009-02-27 20:51:38 by dbrb2002-sql

--0-749897875-1235764298=:67841
Content-Type: text/plain; charset=us-ascii

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@xaprb.com>
To: dbrb2002-sql@yahoo.com
Cc: mysql@lists.mysql.com
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@yahoo.com> 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

--0-749897875-1235764298=:67841--

Report this message

#5: Re: MySQL Closing/Opening tables

Posted on 2009-02-27 22:15:25 by Dan Nelson

In the last episode (Feb 27), dbrb2002-sql@yahoo.com said:
> 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

MyISAM tables flush dirty index blocks at the end of every update; this can
cause a long wait inside "closing tables". If you have just deleted a lot
of rows or did some other update touching many rows, you might have to flush
a lot of dirty blocks. Running "show status like 'Key_blocks_not_flushed'"
during one of these periods should show the count starting out large,
dropping rapidly, then leveling off when that table's blocks have been
flushed.

Fixes include:

* Altering your troublesome tables and adding the DELAY_KEY_WRITE=1 option.
This will force you to repair those tables after a mysql or OS crash,
since the on-disk copies of the index will almost always be out of synch.

* Switching to an engine with logging like InnoDB will allow mysql to write
the changes to a transaction log immediately, then trickle out the actual
key block updates over time. If you want to try out mysql 6.0, the maria
engine is basically MyISAM with logging.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Report this message

#6: Re: MySQL Closing/Opening tables

Posted on 2009-02-27 22:15:42 by mussatto

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@yahoo.com 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@xaprb.com>
> To: dbrb2002-sql@yahoo.com
> Cc: mysql@lists.mysql.com
> 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@yahoo.com> 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
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Report this message

#7: Re: MySQL Closing/Opening tables

Posted on 2009-02-27 23:53:46 by dbrb2002-sql

--0-60541020-1235775226=:70837
Content-Type: text/plain; charset=us-ascii

Thanks Dan.. thats a valuable point.. and this actually happening with MyISAM tables only..

But the question is; when I set the table_cache to higher than total tables.. then it should stop closing the table in first place..so that only un-opened tables will be opened and kept in cache.. it will avoid closing and re-opening.. but looks like it is not the case..

Unless the table_cache is also used(unlikely) for temporary tables which are created by select queries..




________________________________
From: Dan Nelson <dnelson@allantgroup.com>
To: dbrb2002-sql@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Friday, February 27, 2009 1:15:25 PM
Subject: Re: MySQL Closing/Opening tables

In the last episode (Feb 27), dbrb2002-sql@yahoo.com said:
> 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

MyISAM tables flush dirty index blocks at the end of every update; this can
cause a long wait inside "closing tables". If you have just deleted a lot
of rows or did some other update touching many rows, you might have to flush
a lot of dirty blocks. Running "show status like 'Key_blocks_not_flushed'"
during one of these periods should show the count starting out large,
dropping rapidly, then leveling off when that table's blocks have been
flushed.

Fixes include:

* Altering your troublesome tables and adding the DELAY_KEY_WRITE=1 option.
This will force you to repair those tables after a mysql or OS crash,
since the on-disk copies of the index will almost always be out of synch.

* Switching to an engine with logging like InnoDB will allow mysql to write
the changes to a transaction log immediately, then trickle out the actual
key block updates over time. If you want to try out mysql 6.0, the maria
engine is basically MyISAM with logging.

--
Dan Nelson
dnelson@allantgroup.com

--0-60541020-1235775226=:70837--

Report this message

#8: Re: MySQL Closing/Opening tables

Posted on 2009-02-28 00:25:37 by Eric Bergen

MySQL can open a single table multiple times depending on how many
clients need to use it. This means that having a table_cache the same
as the total_tables will only work if your mysql server only has one
client.

For more details read:
http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

On Fri, Feb 27, 2009 at 2:53 PM, <dbrb2002-sql@yahoo.com> wrote:
> Thanks Dan.. thats a valuable point.. and this actually happening with My=
ISAM tables only..
>
> But the question is; when I set the table_cache to higher than total tabl=
es.. then it should stop closing the table in first place..so that only un-=
opened tables will be opened and kept in cache.. it will avoid closing and =
re-opening.. but looks like it is not the case..
>
> Unless the table_cache is also used(unlikely) for temporary tables which =
are created by select queries..
>
>
>
>
> ________________________________
> From: Dan Nelson <dnelson@allantgroup.com>
> To: dbrb2002-sql@yahoo.com
> Cc: mysql@lists.mysql.com
> Sent: Friday, February 27, 2009 1:15:25 PM
> Subject: Re: MySQL Closing/Opening tables
>
> In the last episode (Feb 27), dbrb2002-sql@yahoo.com said:
>> Recently I noticed the server takes lot of time on and off when opening
>> and closing tables. =A0And I tried to increase the table_cache more the =
the
>> total tables (file_limit is properly set); and the problem still continu=
es
>> and lowering it also continues.. =A0and tried to set in middle.. =A0same
>
> MyISAM tables flush dirty index blocks at the end of every update; this c=
an
> cause a long wait inside "closing tables". =A0If you have just deleted a =
lot
> of rows or did some other update touching many rows, you might have to fl=
ush
> a lot of dirty blocks. =A0Running "show status like 'Key_blocks_not_flush=
ed'"
> during one of these periods should show the count starting out large,
> dropping rapidly, then leveling off when that table's blocks have been
> flushed.
>
> Fixes include:
>
> * Altering your troublesome tables and adding the DELAY_KEY_WRITE=3D1 opt=
ion.
> =A0This will force you to repair those tables after a mysql or OS crash,
> =A0since the on-disk copies of the index will almost always be out of syn=
ch.
>
> * Switching to an engine with logging like InnoDB will allow mysql to wri=
te
> =A0the changes to a transaction log immediately, then trickle out the act=
ual
> =A0key block updates over time. =A0If you want to try out mysql 6.0, the =
maria
> =A0engine is basically MyISAM with logging.
>
> --
> =A0 =A0Dan Nelson
> =A0 =A0dnelson@allantgroup.com
>



--=20
Eric Bergen
eric.bergen@provenscaling.com
http://www.provenscaling.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message

#9: Re: MySQL Closing/Opening tables

Posted on 2009-02-28 03:55:21 by Dan Nelson

In the last episode (Feb 27), dbrb2002-sql@yahoo.com said:
> Thanks Dan.. thats a valuable point.. and this actually happening with
> MyISAM tables only..
>
> But the question is; when I set the table_cache to higher than total
> tables.. then it should stop closing the table in first place..so that
> only un-opened tables will be opened and kept in cache.. it will avoid
> closing and re-opening.. but looks like it is not the case..

Even though the State column says "closing tables", it's just flushing dirty
key blocks. I bet if you ran "show status like 'Opened_tables'" you
wouldn't see that number increment at all.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Report this message

#10: Re: MySQL Closing/Opening tables

Posted on 2009-02-28 16:24:59 by Baron Schwartz

Hi,

On Fri, Feb 27, 2009 at 2:51 PM, <dbrb2002-sql@yahoo.com> wrote:
> Thanks for the quick followup Baron..
>
> =A0vmstat
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
> =A0r=A0 b   swpd   free   buff=A0 cache   si   so=A0=
   bi  =A0 bo   in   cs us sy id
> wa st
> =A03=A0 0  =A0 100 499380 139256 5604000  =A0 0  =A0 0   =
190   693   11   11 20=A0 2 70
> 8=A0 0

The first line of output is averages since boot, you need to let it
run for several iterations to see what's happening NOW. But on
average we can see that you're spending 8% of CPU time waiting for
I/O, which may be significant. If you have for example a 4-core
system, that could mean one core is spending 32% of its time. And
that's on average, which means peaks are higher. If you run "mpstat
-P ALL 5" you will be able to see the iowait for each CPU or core.
But based on iostat output you pasted, I can pretty much predict
you're going to see high I/O wait. Looking at iostat, I can see your
await (average wait time) is pretty long. I like to see await in the
low-single-digit ms range. And you've got reasonably high utilization
percent too. All this while not doing many writes per second, and
with a short disk queue, in a non-peak time. Look at iostat during
the times of high stalls and I bet you'll see the problem clearly.

I think the answer is probably that you have slow disks. Get more and
faster disks. If you need high performance, upgrade to a RAID array
with a battery-backed write cache on the controller, set to writeback
policy. Get 10k or 15k RPM disks. You'll see *much* higher
performance. A typical Percona client has an LSI MegaRAID card (the
Dell PERC is basically the same thing) with 4 or 6 15k RPM 2.5"
server-grade SAS drives in a RAID 10 array. You don't need to go this
high-end -- maybe you can get perfectly fine performance with three
7200RPM or 10kRPM disks in RAID 5, I don't know, that's up to you. It
would be cheaper and if it's good enough, that's great.

We always benchmark drives to make sure they are installed correctly.
Here's a set of benchmarks with iozone from a recent job that has this
setup. You may need to view this in fixed-width font:

../iozone -c -n 128M -g 32G -r 16k -k -l -o -O -P -T -f -x -S 4096 -a C 1 +D

Command line used: ./iozone -c -n 128M -g 32G -r 16k -k -l -o -O -P
-T -f -x -S 4096 -a C 1 +D
Time Resolution =3D 0.000001 seconds.
Processor cache size set to 4096 Kbytes.
Processor cache line size set to 32 bytes.
File stride size set to 17 * record size.
random
random bkwd record stride
KB reclen write rewrite read reread read
write read rewrite read
131072 16 175 3184 73943 72501 73101
932 74864 3232 72611
262144 16 173 3214 74557 73412 73468
1120 74673 3280 73020
524288 16 181 3266 75108 72978 72991
912 74291 3268 72524
1048576 16 186 3267 74741 73103 72578
769 74096 3271 73487
2097152 16 184 3267 74730 73474 72316
645 38541 3035 73862

Look ma, only 175 writes per second! Slow as a dog! So I checked the
RAID configuration and found out that the hosting provider had
mistakenly set the controller to WriteThrough policy... after fixing
that, look at the difference:

Command line used: ./iozone -c -n 128M -g 32G -r 16k -k -l -o
-O -P -T -f -x -S 4096 -a C 1 +D
Time Resolution =3D 0.000001 seconds.
Processor cache size set to 4096 Kbytes.
Processor cache line size set to 32 bytes.
File stride size set to 17 * record size.
random
random bkwd record stride
KB reclen write rewrite read reread read
write read rewrite read
131072 16 3260 7215 76574 75236 74495
1396 75960 7299 74788
262144 16 3122 7342 76255 75272 73661
1259 75304 7422 74755
524288 16 3118 7346 76156 75214 73629
1114 75390 7400 74256
1048576 16 3112 7454 75981 74478 73206
1029 75029 7571 73901
2097152 16 3110 7468 76100 74780 73218
926 75292 7573 74316

That's more like it. Over 3000 synchronous writes per second (TO
DURABLE STORAGE, not just the OS cache), while maintaining await in
the 1 to 3 ms range (as shown by iostat, not shown here). This is
what I consider to be a "real" disk subsystem for a database server
:-) You might consider benchmarking your disks to see what level of
performance they can achieve. It is always worth doing IMO.

Dan Nelson's advice is also right on target. And Eric Bergen's too,
hi Eric :) I disagree with Mr. Musatto -- just because you're doing
more writes than reads doesn't mean your writes are high. 70, 80,
even 126 writes per second is trivial unless you have a single slow
disk, and the await you showed us is high, not low. You've got some
5-second periods where the await is nearly 1/5th of a second, which
means the spikes are even higher -- how many writes per second do you
expect your disks to do? A database server needs to do more than 5 or
10 writes per second.

I would also install some monitoring/trending system like cacti or
munin so you can have forensics for looking at what's happening on
your systems.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message