Threads waiting on buffers
A DBA on IDS 7.31 (Solaris, 8-way Sunfire) saw sudden stalls where hundreds of threads waited on one buffer holding a BTREE page of the 'tabname' index on systables, usually requiring an engine bounce. Art Kagel suggested data dictionary cache thrashing: with ~300 tables and similar names, onstat -g dic showed many of the default 31 hash buckets full (10 entries), forcing repeated systables reads. Fix proposed was raising DD_HASHSIZE/DD_HASHMAX (support suggested 53/20; Art cited manual values 503/4, plus DS_HASHSIZE 503 and DS_POOLSIZE 2000). The thread ends with discussion of table counts; no confirmation of the fix's result is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Ok, so Informix tech support and I haven't been able
to figure this one out. I'm hoping someone else is
familiar with this problem and knows how to fix it.
Every now and then, often while we are experiencing
peak load, but not necessarily, our Informix engine
gets into a state where a huge number of users all of
a sudden begin waiting on a single buffer in the
buffer cache. Turns out this buffer page contains a
BTREE page for an index on the systables table in the
system catalog for our main database. Specifically,
the last page (alphabetically) of the index 'tabname'
on systables which contains keys on (tabname, owner).
When running onstat -b or onstat -X, the waiters on
this buffer seem to all appear, disappear, and
reappear, but when you run onstat -u, a HUGE number of
threads always have the 'B' flag for waiting on a
buffer. Needless to say, the entire database's
performance goes in the tank when this happens. One
time, the stack traces seemed to indicate that a ton
of threads were attempting to build a referential
constraint, but no such SQL commands were issued by
any thread. Plus, other times, the stack traces have
not necessarily shown this, but this may be related to
the appear/disappear/reappear thing we see on these
waiters in onstat -b and -X.
Our dbclients are configured with their own connection
pool managers. Any time they need additional
connections to accomplish something on the database,
they establish new connections. Thus, while we
usually have between 1000 and 1500 connections total
from all of the dbclient machines, the number of
connections will usually shoot up over 3500 when this
problem occurs (since the original ones do not respond
quickly any longer). Virtual memory usage goes up
concomitantly as well.
Most of the time, the database will not pull out of
this state. Usually, we need to bounce the engine in
order to get it out of this. The one time it did come
out, we were trying to have Informix dial in to look
at it while it was in progress, and it cleared up
before they could connect. There was no appearant
reason why it cleared up, but this was the ONE time
that this occurred when we were not experiencing peak
load. When we bounce the engine during one of these
episodes, as there is so much activity, the logical
recovery can take up to 10 minutes or so to complete.
While it does seem to occur most often with peak load,
we have experienced other times when load was even
higher, and we had no problems.
The engine is IDS 7.31.UD1XF running on Solaris 9.
The box is an 8-way Sunfire 4800. I have 800,000
buffers configured. (I'd raised it in a couple of
steps from 300,000 hoping that that would relieve this
issue.) We do have in place the LRUAGE=1 fix to
prevent BTREE pages from taking up all the buffers.
We have 100 LRU queues and MAX and MIN set to 2 and 1,
respectively.
We are about to go into our peak season, and having an
unstable database is really freaking out my
management, not to mention me. Any help or insight
that anyone can offer would be GREATLY appreciated.
I'm losing sleep on this one. Thanks very much.
--John Bejarano
DBA, Shutterfly, Inc.
What is LRU Min/Max set to?
"John Bejarano "
<jbejarano@sbcglo To: ids@iiug.org
bal.net> cc:
Sent by: Subject: Threads waiting on buffers [2118]
forum.subscriber@
iiug.org
11/03/2003 10:15
AM
Ok, so Informix tech support and I haven't been able
to figure this one out. I'm hoping someone else is
familiar with this problem and knows how to fix it.
Every now and then, often while we are experiencing
peak load, but not necessarily, our Informix engine
gets into a state where a huge number of users all of
a sudden begin waiting on a single buffer in the
buffer cache. Turns out this buffer page contains a
BTREE page for an index on the systables table in the
system catalog for our main database. Specifically,
the last page (alphabetically) of the index 'tabname'
on systables which contains keys on (tabname, owner).
When running onstat -b or onstat -X, the waiters on
this buffer seem to all appear, disappear, and
reappear, but when you run onstat -u, a HUGE number of
threads always have the 'B' flag for waiting on a
buffer. Needless to say, the entire database's
performance goes in the tank when this happens. One
time, the stack traces seemed to indicate that a ton
of threads were attempting to build a referential
constraint, but no such SQL commands were issued by
any thread. Plus, other times, the stack traces have
not necessarily shown this, but this may be related to
the appear/disappear/reappear thing we see on these
waiters in onstat -b and -X.
Our dbclients are configured with their own connection
pool managers. Any time they need additional
connections to accomplish something on the database,
they establish new connections. Thus, while we
usually have between 1000 and 1500 connections total
from all of the dbclient machines, the number of
connections will usually shoot up over 3500 when this
problem occurs (since the original ones do not respond
quickly any longer). Virtual memory usage goes up
concomitantly as well.
Most of the time, the database will not pull out of
this state. Usually, we need to bounce the engine in
order to get it out of this. The one time it did come
out, we were trying to have Informix dial in to look
at it while it was in progress, and it cleared up
before they could connect. There was no appearant
reason why it cleared up, but this was the ONE time
that this occurred when we were not experiencing peak
load. When we bounce the engine during one of these
episodes, as there is so much activity, the logical
recovery can take up to 10 minutes or so to complete.
While it does seem to occur most often with peak load,
we have experienced other times when load was even
higher, and we had no problems.
The engine is IDS 7.31.UD1XF running on Solaris 9.
The box is an 8-way Sunfire 4800. I have 800,000
buffers configured. (I'd raised it in a couple of
steps from 300,000 hoping that that would relieve this
issue.) We do have in place the LRUAGE=1 fix to
prevent BTREE pages from taking up all the buffers.
We have 100 LRU queues and MAX and MIN set to 2 and 1,
respectively.
We are about to go into our peak season, and having an
unstable database is really freaking out my
management, not to mention me. Any help or insight
that anyone can offer would be GREATLY appreciated.
I'm losing sleep on this one. Thanks very much.
--John Bejarano
DBA, Shutterfly, Inc.
So,
yes, we do have over 200 tables. Including system
catalog tables, there are 306 in this one database.
And looking at onstat -g dic (for the first time), I
see that 10 of the default 31 hash buckets are full
with 10 entries each. Many others are 8 or 9. Art, I
think you're showing your genius again. Thanks very
much for the suggestion.
Having brought this up with technical support, they
recommend bringing DD_HASHSIZE up to 53 and DD_HASHMAX
up to 20. Those values seem reasonable to me, I think
we're going to go with them. (Shame this isn't
documented anywhere.) While tech support won't say
for sure that this could cause the issue we're seeing,
they seem to agree it might. Has anyone else had
their data dictionary thrash before? What effects did
you see?
Madison, my LRU MAX and MIN are set to 2 and 1.
Thanks,
--John Bejarano
DBA, Shutterfly, Inc.
--- "ART KAGEL, BLOOMBERG/ 65E 55TH"
<KAGEL@bloomberg.net> wrote:
> Do you have a large number of tables over all
> databases on this instance (over
> 200 or so)? Does onstat -g dic show the size column
> value on most lines that
> have an entry in the list# column are close to the
> Maximum list size value shown
> at the top? Do you have a relatively large number
> of tables with similar names
> that might all be hashing to the same one or two
> data dictionary buckets?
>
> If so it could be that the Data Dictionary cache is
> thrashing causing the
> dictionary entries in the cache for some active
> tables to be read from disk
> frequently. At peak this causes the buffer access
> storm on systables entries to
> reload the dictionary cache. Try increasing the
> data dictionary cache size.
> The ONCONFIG parameters are:
>
> DD_HASHSIZE - This must be prime. It is the number
> of hash table buckets unless
> you have a large number of tables with similar
> names increase this parameter
> before you play with DD_HASHMAX. (Default: 31)
> DD_HASHMAX - The number of slots in each hash
> bucket, if more than this number
> of tablenames hash to the same value because their
> names are very similar then
> increasing this one will remove the thrashing.
> (Default: 10)
>
> Art S. Kagel
>
I haven't been keeping up - is 200 tables really all that
much?
Has anyone experienced performance problems or anything else from having a
large number of tables? In our case, we're talking about 879 tables.
----- Original Message -----
From: "John Bejarano " <jbejarano@sbcglobal.net>
To: <ids@iiug.org>
Sent: Monday, November 03, 2003 2:43 PM
Subject: Re: Threads waiting on buffers [2122]
> So, yes, we do have over 200 tables. Including system
> catalog tables, there are 306 in this one database.
> And looking at onstat -g dic (for the first time), I
> see that 10 of the default 31 hash buckets are full
> with 10 entries each. Many others are 8 or 9. Art, I
> think you're showing your genius again. Thanks very
> much for the suggestion.
>
> Having brought this up with technical support, they
> recommend bringing DD_HASHSIZE up to 53 and DD_HASHMAX
> up to 20. Those values seem reasonable to me, I think
> we're going to go with them. (Shame this isn't
> documented anywhere.) While tech support won't say
> for sure that this could cause the issue we're seeing,
> they seem to agree it might. Has anyone else had
> their data dictionary thrash before? What effects did
> you see?
>
> Madison, my LRU MAX and MIN are set to 2 and 1.
>
> Thanks,
>
> --John Bejarano
> DBA, Shutterfly, Inc.
>
>
> --- "ART KAGEL, BLOOMBERG/ 65E 55TH"
> <KAGEL@bloomberg.net> wrote:
> > Do you have a large number of tables over all
> > databases on this instance (over
> > 200 or so)? Does onstat -g dic show the size column
> > value on most lines that
> > have an entry in the list# column are close to the
> > Maximum list size value shown
> > at the top? Do you have a relatively large number
> > of tables with similar names
> > that might all be hashing to the same one or two
> > data dictionary buckets?
> >
> > If so it could be that the Data Dictionary cache is
> > thrashing causing the
> > dictionary entries in the cache for some active
> > tables to be read from disk
> > frequently. At peak this causes the buffer access
> > storm on systables entries to
> > reload the dictionary cache. Try increasing the
> > data dictionary cache size.
> > The ONCONFIG parameters are:
> >
> > DD_HASHSIZE - This must be prime. It is the number
> > of hash table buckets unless
> > you have a large number of tables with similar
> > names increase this parameter
> > before you play with DD_HASHMAX. (Default: 31)
> > DD_HASHMAX - The number of slots in each hash
> > bucket, if more than this number
> > of tablenames hash to the same value because their
> > names are very similar then
> > increasing this one will remove the thrashing.
> > (Default: 10)
> >
> > Art S. Kagel
> >
>
Those of
us running SAP have between 10,000 and 12,000 (yeah, the comma and
zeroes are correct) tables defined in the database. Hard to know how many
of those tables actually get used in any specific system. Using an 80/20
thing, we probably have at least 2,000 active tables but that's just a
guess. Running onstat -g dic the "number of dictionary entries" is usually
around 4,000 on my system. DD_HASSIZE is 613 here and DD_HASHMAX is 30. I
haven't been experiencing any of thrashing (that I know of). After this
issue though, I'm going to check that aspect more often. There are times
when things just seem to arbitrarily run slower. Or for some reason, a
checkpoint will take 10 minutes when they normally run 3 - 10 seconds. I'll
be including onstat -g dic as part of the checks I run when things seem
sluggish.
Darrell Murphy
Burton Snowboards
-----Original Message-----
From: Danny Wright [mailto:dwright@sherwoodfoods.com]
Sent: Tuesday, November 04, 2003 11:41 AM
To: ids@iiug.org
Subject: Re: Threads waiting on buffers [2126]
I haven't been keeping up - is 200 tables really all that much?
Has anyone experienced performance problems or anything else from having a
large number of tables? In our case, we're talking about 879 tables.
----- Original Message -----
From: "John Bejarano " <jbejarano@sbcglobal.net>
To: <ids@iiug.org>
Sent: Monday, November 03, 2003 2:43 PM
Subject: Re: Threads waiting on buffers [2122]
> So, yes, we do have over 200 tables. Including system
> catalog tables, there are 306 in this one database.
> And looking at onstat -g dic (for the first time), I
> see that 10 of the default 31 hash buckets are full
> with 10 entries each. Many others are 8 or 9. Art, I
> think you're showing your genius again. Thanks very
> much for the suggestion.
>
> Having brought this up with technical support, they
> recommend bringing DD_HASHSIZE up to 53 and DD_HASHMAX
> up to 20. Those values seem reasonable to me, I think
> we're going to go with them. (Shame this isn't
> documented anywhere.) While tech support won't say
> for sure that this could cause the issue we're seeing,
> they seem to agree it might. Has anyone else had
> their data dictionary thrash before? What effects did
> you see?
>
> Madison, my LRU MAX and MIN are set to 2 and 1.
>
> Thanks,
>
> --John Bejarano
> DBA, Shutterfly, Inc.
>
>
> --- "ART KAGEL, BLOOMBERG/ 65E 55TH"
> <KAGEL@bloomberg.net> wrote:
> > Do you have a large number of tables over all
> > databases on this instance (over
> > 200 or so)? Does onstat -g dic show the size column
> > value on most lines that
> > have an entry in the list# column are close to the
> > Maximum list size value shown
> > at the top? Do you have a relatively large number
> > of tables with similar names
> > that might all be hashing to the same one or two
> > data dictionary buckets?
> >
> > If so it could be that the Data Dictionary cache is
> > thrashing causing the
> > dictionary entries in the cache for some active
> > tables to be read from disk
> > frequently. At peak this causes the buffer access
> > storm on systables entries to
> > reload the dictionary cache. Try increasing the
> > data dictionary cache size.
> > The ONCONFIG parameters are:
> >
> > DD_HASHSIZE - This must be prime. It is the number
> > of hash table buckets unless
> > you have a large number of tables with similar
> > names increase this parameter
> > before you play with DD_HASHMAX. (Default: 31)
> > DD_HASHMAX - The number of slots in each hash
> > bucket, if more than this number
> > of tablenames hash to the same value because their
> > names are very similar then
> > increasing this one will remove the thrashing.
> > (Default: 10)
> >
> > Art S. Kagel
> >
>
------_=_NextPart_001_01C3A301.A25D1590
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2653.12">
<TITLE>RE: Threads waiting on buffers [2126] </TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>Those of us running SAP have between 10,000 and =
12,000 (yeah, the comma and zeroes are correct) tables defined in the =
database. Hard to know how many of those tables actually get used =
in any specific system. Using an 80/20 thing, we probably have at =
least 2,000 active tables but that's just a guess. Running onstat =
-g dic the "number of dictionary entries" is usually around =
4,000 on my system. DD_HASSIZE is 613 here and DD_HASHMAX is =
30. I haven't been experiencing any of thrashing (that I know =
of). After this issue though, I'm going to check that =
aspect more often. There are times when things just seem to =
arbitrarily run slower. Or for some reason, a checkpoint will =
take 10 minutes when they normally run 3 - 10 seconds. I'll be =
including onstat -g dic as part of the checks I run when things seem =
sluggish.</FONT></P>
<P><FONT SIZE=3D2>Darrell Murphy</FONT>
<BR><FONT SIZE=3D2>Burton Snowboards</FONT>
</P>
<P><FONT SIZE=3D2> -----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Danny Wright [<A =
HREF=3D"mailto:dwright@sherwoodfoods.com">mailto:dwright@sherwoodfoods.c=
om</A>] </FONT>
<BR><FONT SIZE=3D2>Sent: Tuesday, November 04, 2003 11:41 =
AM</FONT>
<BR><FONT SIZE=3D2>To: ids@iiug.org</FONT>
<BR><FONT SIZE=3D2>Subject: =
Re: Threads waiting on buffers [2126] </FONT>
</P>
<P><FONT SIZE=3D2>I haven't been keeping up - is 200 tables really all =
that much?</FONT>
</P>
<P><FONT SIZE=3D2>Has anyone experienced performance problems or =
anything else from having a</FONT>
<BR><FONT SIZE=3D2>large number of tables? In our case, we're =
talking about 879 tables.</FONT>
</P>
<P><FONT SIZE=3D2>----- Original Message ----- </FONT>
<BR><FONT SIZE=3D2>From: "John Bejarano " =
<jbejarano@sbcglobal.net></FONT>
<BR><FONT SIZE=3D2>To: <ids@iiug.org></FONT>
<BR><FONT SIZE=3D2>Sent: Monday, November 03, 2003 2:43 PM</FONT>
<BR><FONT SIZE=3D2>Subject: Re: Threads waiting on buffers =
[2122]</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>> So, yes, we do have over 200 tables. =
Including system</FONT>
<BR><FONT SIZE=3D2>> catalog tables, there are 306 in this one =
database.</FONT>
<BR><FONT SIZE=3D2>> And looking at onstat -g dic (for the first =
time), I</FONT>
<BR><FONT SIZE=3D2>> see that 10 of the default 31 hash buckets are =
full</FONT>
<BR><FONT SIZE=3D2>> with 10 entries each. Many others are 8 =
or 9. Art, I</FONT>
<BR><FONT SIZE=3D2>> think you're showing your genius again. =
Thanks very</FONT>
<BR><FONT SIZE=3D2>> much for the suggestion
On our
server, we run multiple instances of Informix and each instance has
over 1200 tables without any performance issues. We run 7.41.UC4 on HP N4000
and L2000 servers running 11.0 HP/UX.
Javier Zayas
System Administrator
x73143
-----Original Message-----
From: Danny Wright [mailto:dwright@sherwoodfoods.com]
Sent: Tuesday, November 04, 2003 8:41 AM
To: ids@iiug.org
Subject: Re: Threads waiting on buffers [2126]
I haven't been keeping up - is 200 tables really all that much?
Has anyone experienced performance problems or anything else from having a
large number of tables? In our case, we're talking about 879 tables.
----- Original Message -----
From: "John Bejarano " <jbejarano@sbcglobal.net>
To: <ids@iiug.org>
Sent: Monday, November 03, 2003 2:43 PM
Subject: Re: Threads waiting on buffers [2122]
> So, yes, we do have over 200 tables. Including system
> catalog tables, there are 306 in this one database.
> And looking at onstat -g dic (for the first time), I
> see that 10 of the default 31 hash buckets are full
> with 10 entries each. Many others are 8 or 9. Art, I
> think you're showing your genius again. Thanks very
> much for the suggestion.
>
> Having brought this up with technical support, they
> recommend bringing DD_HASHSIZE up to 53 and DD_HASHMAX
> up to 20. Those values seem reasonable to me, I think
> we're going to go with them. (Shame this isn't
> documented anywhere.) While tech support won't say
> for sure that this could cause the issue we're seeing,
> they seem to agree it might. Has anyone else had
> their data dictionary thrash before? What effects did
> you see?
>
> Madison, my LRU MAX and MIN are set to 2 and 1.
>
> Thanks,
>
> --John Bejarano
> DBA, Shutterfly, Inc.
>
>
> --- "ART KAGEL, BLOOMBERG/ 65E 55TH"
> <KAGEL@bloomberg.net> wrote:
> > Do you have a large number of tables over all
> > databases on this instance (over
> > 200 or so)? Does onstat -g dic show the size column
> > value on most lines that
> > have an entry in the list# column are close to the
> > Maximum list size value shown
> > at the top? Do you have a relatively large number
> > of tables with similar names
> > that might all be hashing to the same one or two
> > data dictionary buckets?
> >
> > If so it could be that the Data Dictionary cache is
> > thrashing causing the
> > dictionary entries in the cache for some active
> > tables to be read from disk
> > frequently. At peak this causes the buffer access
> > storm on systables entries to
> > reload the dictionary cache. Try increasing the
> > data dictionary cache size.
> > The ONCONFIG parameters are:
> >
> > DD_HASHSIZE - This must be prime. It is the number
> > of hash table buckets unless
> > you have a large number of tables with similar
> > names increase this parameter
> > before you play with DD_HASHMAX. (Default: 31)
> > DD_HASHMAX - The number of slots in each hash
> > bucket, if more than this number
> > of tablenames hash to the same value because their
> > names are very similar then
> > increasing this one will remove the thrashing.
> > (Default: 10)
> >
> > Art S. Kagel
> >
>
----- Original Message -----
To: dwright@sherwoodfoods.com
At: 11/ 4 14:07
With that many tables also look into increasing the Data Distribution Cache
(parameters DS_HASHSIZE & DS_POOLSIZE). Check out chapter 4 in the Performance
Guide for details of all of these parameters, it recommends using:
DD_HASHSIZE 503
DD_HASHMAX 4
DS_HASHSIZE 503
DS_POOLSIZE 2000
For even medium sized servers. You may not have a hard bottleneck like John
did, caused by many tables with similar names thrashing 1/3 of the dictionary
cache entries, but unless you have fewer than 300 active tables that all have
rather unique names, it is likely that performance could be improved by
increasing the cache. The cache parameters themselves only allocate additional
hash headers which are small so if you don't need the extra cache it will not
cost much additional memory to increase the entries anyway.
Art S. Kagel
----- Original Message -----
From: Danny Wright <dwright@sherwoodfoods.com>
At: 11/ 4 12:41
> I haven't been keeping up - is 200 tables really all that much?
>
> Has anyone experienced performance problems or anything else from having a
> large number of tables? In our case, we're talking about 879 tables.
>
> ----- Original Message -----
> From: "John Bejarano " <jbejarano@sbcglobal.net>
> To: <ids@iiug.org>
> Sent: Monday, November 03, 2003 2:43 PM
> Subject: Re: Threads waiting on buffers [2122]
>
>
> > So, yes, we do have over 200 tables. Including system
> > catalog tables, there are 306 in this one database.
> > And looking at onstat -g dic (for the first time), I
> > see that 10 of the default 31 hash buckets are full
> > with 10 entries each. Many others are 8 or 9. Art, I
> > think you're showing your genius again. Thanks very
> > much for the suggestion.
> >
> > Having brought this up with technical support, they
> > recommend bringing DD_HASHSIZE up to 53 and DD_HASHMAX
> > up to 20. Those values seem reasonable to me, I think
> > we're going to go with them. (Shame this isn't
> > documented anywhere.) While tech support won't say
> > for sure that this could cause the issue we're seeing,
> > they seem to agree it might. Has anyone else had
> > their data dictionary thrash before? What effects did
> > you see?
> >
> > Madison, my LRU MAX and MIN are set to 2 and 1.
> >
> > Thanks,
> >
> > --John Bejarano
> > DBA, Shutterfly, Inc.
> >
> >
> > --- "ART KAGEL, BLOOMBERG/ 65E 55TH"
> > <KAGEL@bloomberg.net> wrote:
> > > Do you have a large number of tables over all
> > > databases on this instance (over
> > > 200 or so)? Does onstat -g dic show the size column
> > > value on most lines that
> > > have an entry in the list# column are close to the
> > > Maximum list size value shown
> > > at the top? Do you have a relatively large number
> > > of tables with similar names
> > > that might all be hashing to the same one or two
> > > data dictionary buckets?
> > >
> > > If so it could be that the Data Dictionary cache is
> > > thrashing causing the
> > > dictionary entries in the cache for some active
> > > tables to be read from disk
> > > frequently. At peak this causes the buffer access
> > > storm on systables entries to
> > > reload the dictionary cache. Try increasing the
> > > data dictionary cache size.
> > > The ONCONFIG parameters are:
> > >
> > > DD_HASHSIZE - This must be prime. It is the number
> > > of hash table buckets unless
> > > you have a large number of tables with similar
> > > names increase this parameter
> > > before you play with DD_HASHMAX. (Default: 31)
> > > DD_HASHMAX - The number of slots in each hash
> > > bucket, if more than this number
> > > of tablenames hash to the same value because their
> > > names are very similar then
> > > increasing this one will remove the thrashing.
> > > (Default: 10)
> > >
> > > Art S. Kagel
> > >
> >
Hi,
200 tables is not that much.
An average SAP system has between 2000 and 3000 tables as far as I know.
Maybe more these days (the numbers are a couple of years old ...).
Of course they come with a $ONCONFIG file, chunk layout, etc. that is
adapted to these needs according to their experience ... :)
Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich
Data Management Solutions
"Danny Wright" <dwright@sherwoodfoods.com>
Sent by: forum.subscriber@iiug.org
04.11.2003 17:41
To: ids@iiug.org
cc:
Subject: Re: Threads waiting on buffers [2126]
I haven't been keeping up - is 200 tables really all that much?
Has anyone experienced performance problems or anything else from having a
large number of tables? In our case, we're talking about 879 tables.
----- Original Message -----
From: "John Bejarano " <jbejarano@sbcglobal.net>
To: <ids@iiug.org>
Sent: Monday, November 03, 2003 2:43 PM
Subject: Re: Threads waiting on buffers [2122]
> So, yes, we do have over 200 tables. Including system
> catalog tables, there are 306 in this one database.
> And looking at onstat -g dic (for the first time), I
> see that 10 of the default 31 hash buckets are full
> with 10 entries each. Many others are 8 or 9. Art, I
> think you're showing your genius again. Thanks very
> much for the suggestion.
>
> Having brought this up with technical support, they
> recommend bringing DD_HASHSIZE up to 53 and DD_HASHMAX
> up to 20. Those values seem reasonable to me, I think
> we're going to go with them. (Shame this isn't
> documented anywhere.) While tech support won't say
> for sure that this could cause the issue we're seeing,
> they seem to agree it might. Has anyone else had
> their data dictionary thrash before? What effects did
> you see?
>
> Madison, my LRU MAX and MIN are set to 2 and 1.
>
> Thanks,
>
> --John Bejarano
> DBA, Shutterfly, Inc.
>
>
> --- "ART KAGEL, BLOOMBERG/ 65E 55TH"
> <KAGEL@bloomberg.net> wrote:
> > Do you have a large number of tables over all
> > databases on this instance (over
> > 200 or so)? Does onstat -g dic show the size column
> > value on most lines that
> > have an entry in the list# column are close to the
> > Maximum list size value shown
> > at the top? Do you have a relatively large number
> > of tables with similar names
> > that might all be hashing to the same one or two
> > data dictionary buckets?
> >
> > If so it could be that the Data Dictionary cache is
> > thrashing causing the
> > dictionary entries in the cache for some active
> > tables to be read from disk
> > frequently. At peak this causes the buffer access
> > storm on systables entries to
> > reload the dictionary cache. Try increasing the
> > data dictionary cache size.
> > The ONCONFIG parameters are:
> >
> > DD_HASHSIZE - This must be prime. It is the number
> > of hash table buckets unless
> > you have a large number of tables with similar
> > names increase this parameter
> > before you play with DD_HASHMAX. (Default: 31)
> > DD_HASHMAX - The number of slots in each hash
> > bucket, if more than this number
> > of tablenames hash to the same value because their
> > names are very similar then
> > increasing this one will remove the thrashing.
> > (Default: 10)
> >
> > Art S. Kagel
> >
>
I could not find any information on these parameter... this is where I
looked.
http://www-3.ibm.com/software/data/informix/pubs/library/ids_73.html
Thanks
Silvana
P.S. Anyone who made these changes please report what happened.
-----Original Message-----
From: ART KAGEL, .... [mailto:KAGEL@bloomberg.net]
Sent: Tuesday, November 04, 2003 1:11 PM
To: ids@iiug.org
Subject: Re: Threads waiting on buffers [2130]
----- Original Message -----
To: dwright@sherwoodfoods.com
At: 11/ 4 14:07
With that many tables also look into increasing the Data Distribution Cache
(parameters DS_HASHSIZE & DS_POOLSIZE). Check out chapter 4 in the
Performance
Guide for details of all of these parameters, it recommends using:
DD_HASHSIZE 503
DD_HASHMAX 4
DS_HASHSIZE 503
DS_POOLSIZE 2000
For even medium sized servers. You may not have a hard bottleneck like John
did, caused by many tables with similar names thrashing 1/3 of the
dictionary
cache entries, but unless you have fewer than 300 active tables that all
have
rather unique names, it is likely that performance could be improved by
increasing the cache. The cache parameters themselves only allocate
additional
hash headers which are small so if you don't need the extra cache it will
not
cost much additional memory to increase the entries anyway.
Art S. Kagel
----- Original Message -----
From: Danny Wright <dwright@sherwoodfoods.com>
At: 11/ 4 12:41
> I haven't been keeping up - is 200 tables really all that much?
>
> Has anyone experienced performance problems or anything else from having a
> large number of tables? In our case, we're talking about 879 tables.
>
> ----- Original Message -----
> From: "John Bejarano " <jbejarano@sbcglobal.net>
> To: <ids@iiug.org>
> Sent: Monday, November 03, 2003 2:43 PM
> Subject: Re: Threads waiting on buffers [2122]
>
>
> > So, yes, we do have over 200 tables. Including system
> > catalog tables, there are 306 in this one database.
> > And looking at onstat -g dic (for the first time), I
> > see that 10 of the default 31 hash buckets are full
> > with 10 entries each. Many others are 8 or 9. Art, I
> > think you're showing your genius again. Thanks very
> > much for the suggestion.
> >
> > Having brought this up with technical support, they
> > recommend bringing DD_HASHSIZE up to 53 and DD_HASHMAX
> > up to 20. Those values seem reasonable to me, I think
> > we're going to go with them. (Shame this isn't
> > documented anywhere.) While tech support won't say
> > for sure that this could cause the issue we're seeing,
> > they seem to agree it might. Has anyone else had
> > their data dictionary thrash before? What effects did
> > you see?
> >
> > Madison, my LRU MAX and MIN are set to 2 and 1.
> >
> > Thanks,
> >
> > --John Bejarano
> > DBA, Shutterfly, Inc.
> >
> >
> > --- "ART KAGEL, BLOOMBERG/ 65E 55TH"
> > <KAGEL@bloomberg.net> wrote:
> > > Do you have a large number of tables over all
> > > databases on this instance (over
> > > 200 or so)? Does onstat -g dic show the size column
> > > value on most lines that
> > > have an entry in the list# column are close to the
> > > Maximum list size value shown
> > > at the top? Do you have a relatively large number
> > > of tables with similar names
> > > that might all be hashing to the same one or two
> > > data dictionary buckets?
> > >
> > > If so it could be that the Data Dictionary cache is
> > > thrashing causing the
> > > dictionary entries in the cache for some active
> > > tables to be read from disk
> > > frequently. At peak this causes the buffer access
> > > storm on systables entries to
> > > reload the dictionary cache. Try increasing the
> > > data dictionary cache size.
> > > The ONCONFIG parameters are:
> > >
> > > DD_HASHSIZE - This must be prime. It is the number
> > > of hash table buckets unless
> > > you have a large number of tables with similar
> > > names increase this parameter
> > > before you play with DD_HASHMAX. (Default: 31)
> > > DD_HASHMAX - The number of slots in each hash
> > > bucket, if more than this number
> > > of tablenames hash to the same value because their
> > > names are very similar then
> > > increasing this one will remove the thrashing.
> > > (Default: 10)
> > >
> > > Art S. Kagel
> > >
> >
No, but I have seen problems
with about that many poorly-designed,
badly-indexed and badly used tables. :o)
--
Bye now,
Obnoxio
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche
"Necrophilia means never having to say ... well, anything!"
- Captain Pedantic
"Ogni uomo mi guarda come se fossi una testa di cazzo"
- Marco
From: "Danny Wright" <dwright@sherwoodfoods.com>
>
>I haven't been keeping up - is 200 tables really all that much?
>
>Has anyone experienced performance problems or anything else from having a
>large number of tables? In our case, we're talking about 879 tables.
>
>----- Original Message -----
>From: "John Bejarano " <jbejarano@sbcglobal.net>
>
> > So, yes, we do have over 200 tables. Including system
> > catalog tables, there are 306 in this one database.
> > And looking at onstat -g dic (for the first time), I
> > see that 10 of the default 31 hash buckets are full
> > with 10 entries each. Many others are 8 or 9. Art, I
> > think you're showing your genius again. Thanks very
> > much for the suggestion.
> >
> > Having brought this up with technical support, they
> > recommend bringing DD_HASHSIZE up to 53 and DD_HASHMAX
> > up to 20. Those values seem reasonable to me, I think
> > we're going to go with them. (Shame this isn't
> > documented anywhere.) While tech support won't say
> > for sure that this could cause the issue we're seeing,
> > they seem to agree it might. Has anyone else had
> > their data dictionary thrash before? What effects did
> > you see?
> >
> > Madison, my LRU MAX and MIN are set to 2 and 1.
> >
> > --- "ART KAGEL, BLOOMBERG/ 65E 55TH"
> > <KAGEL@bloomberg.net> wrote:
> > > Do you have a large number of tables over all
> > > databases on this instance (over
> > > 200 or so)? Does onstat -g dic show the size column
> > > value on most lines that
> > > have an entry in the list# column are close to the
> > > Maximum list size value shown
> > > at the top? Do you have a relatively large number
> > > of tables with similar names
> > > that might all be hashing to the same one or two
> > > data dictionary buckets?
> > >
> > > If so it could be that the Data Dictionary cache is
> > > thrashing causing the
> > > dictionary entries in the cache for some active
> > > tables to be read from disk
> > > frequently. At peak this causes the buffer access
> > > storm on systables entries to
> > > reload the dictionary cache. Try increasing the
> > > data dictionary cache size.
> > > The ONCONFIG parameters are:
> > >
> > > DD_HASHSIZE - This must be prime. It is the number
> > > of hash table buckets unless
> > > you have a large number of tables with similar
> > > names increase this parameter
> > > before you play with DD_HASHMAX. (Default: 31)
> > > DD_HASHMAX - The number of slots in each hash
> > > bucket, if more than this number
> > > of tablenames hash to the same value because their
> > > names are very similar then
> > > increasing this one will remove the thrashing.
> > > (Default: 10)
_________________________________________________________________
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile
Related threads
- onbar -c -F in Windows Informix instance
- Anyone... SQLCODE=-668, ISAM error=-1
- Not using the 100% logical log page size alloacted to informix