IDS Forum

RE: Threads waiting on buffers (TWOB)

Posted By: Alexey Sonkin
Date: Monday, 5 January 2004, at 7:13 p.m.

In Response To: Threads waiting on buffers (TWOB) (John Bejarano )

John,

This time it looks like application design issue:
many clients try to execute some SQL at the same time,
and this SQL requires very extensive reading of some particular index.

Please, try to identify the SQL. Most probably, execution plan can be
optimized
by constructing proper composite index.

Also, make sure that You do not have any index on heavily duplicated
column for that table.

Best regards,
Alexey Sonkin


-----Original Message-----
From: John Bejarano [mailto:jbejarano@sbcglobal.net]
Sent: Monday, January 05, 2004 1:57 AM
To: ids@iiug.org
Subject: Threads waiting on buffers (TWOB) [2402]


Well,

Once again, our company is faced with the "threads
waiting on buffers" (TWOB) issue. At a certain point,
a ton of threads suddenly start waiting on a specific
buffer (they show 'B' in the first flag of onstat -u).
Onstat -X shows that they're almost all waiting on
the same buffer. What's more the 'owner' column shows
0 for this buffer. Informix indicates that that may
have something to do with read-ahead, but they're
quite vague on the matter.

The last time it happened the buffer that everyone was
waiting on contained an index page for an index on
systables. Art Kagel wisely suggested upping the size
of the data dictionary cache, so that systables would
have to be read from less frequently, and the problem
went away.

This time, it is again on an index page, but it's an
index on one of our own (application) tables. This
particular table and the index in question (the
table's primary key index), are both fragmented. The
table is quite large (nearly 1/2 billion rows), and is
heavily used. Plus, this is a peak time for us. But,
the fact that this is happening has my management
screaming that Informix isn't scalable.

Any insights as to why this could happen on an index
page of a user table? Or what the significance of an
owner of '0' or 'ffffffff' might be in onstat -b or
onstat -X output? Pretty mysterious.

Using IDS 7.31.UD1XF on Solaris 9.

Currently RA_PAGES is set to 48, and RA_THRESHOLD is
32. There are a total of 800,000 buffers which should
be plenty.

Thanks very much for any insights.

Kind regards,

John Bejarano,
Shutterfly

Messages In This Thread

• Threads waiting on buffers (TWOB)
  John Bejarano -- Monday, 5 January 2004, at 1:56 a.m.
  • RE: Threads waiting on buffers (TWOB)
    Alexey Sonkin -- Monday, 5 January 2004, at 7:13 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.