IDS Forum

RE: Threads waiting on buffers

Posted By: Javier Zayas
Date: Tuesday, 4 November 2003, at 1:52 p.m.

In Response To: Re: Threads waiting on buffers (Danny Wright)

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

Messages In This Thread

• Threads waiting on buffers
  John Bejarano -- Monday, 3 November 2003, at 11:15 a.m.
  • Re: Threads waiting on buffers
    Madison Pruet -- Monday, 3 November 2003, at 3:05 p.m.
  • Re: Threads waiting on buffers
    John Bejarano -- Monday, 3 November 2003, at 4:43 p.m.
    • Re: Threads waiting on buffers
      Danny Wright -- Tuesday, 4 November 2003, at 11:41 a.m.
      • RE: Threads waiting on buffers
        Darrell Murphy -- Tuesday, 4 November 2003, at 1:33 p.m.
      • RE: Threads waiting on buffers
        Javier Zayas -- Tuesday, 4 November 2003, at 1:52 p.m.
      • Re: Threads waiting on buffers
        ART KAGEL, BLOOMBERG/ 65E 55TH -- Tuesday, 4 November 2003, at 2:11 p.m.
        • RE: Threads waiting on buffers
          Meza, Silvana -- Wednesday, 5 November 2003, at 3:47 p.m.
      • Re: Threads waiting on buffers
        Martin Fuerderer -- Wednesday, 5 November 2003, at 3:50 a.m.
      • Re: Threads waiting on buffers
        Obnoxio The Clown -- Friday, 7 November 2003, at 4:06 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.