Threads waiting on buffers (TWOB)

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

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