If you have the onconfig parameter MAX_FILL_DATA_PAGES set to 1,
Informix Server tries to fill the data pages in tables
containing VARCHAR columns to their maximum. The instance uses
so called 'page free space cache' (PFSC) which holds information
about recently used data pages and an amount of free space on
them. When a session inserts a new row, it looks into PFSC at
first and if no page with enough space for the new row is found
there, the session searches the bitmap pages in the table,
looking for pages marked as semifull (0x4). If some is found,
the session reads the page and tries to insert the new row. If
the free space on that page is not enough, the session continues
in searching the bitmaps and trying the next semifull data page.
This goes until the a page with enough free space is found and
the row inserted. This whole process is done as a part of one
critical section.
In case all or majority of the pages in the table are marked as
semifull, but the free space on them is not enough for the new
row, the session can potentially read the whole table before it
finds the proper data page. In case there are more sessions
doing the same type of 'long row' insert into the same table at
the same time and they enter their critical section just before
a checkpoint occurs, the instance can get blocked in the
checkpoint for very long time (possibly up to tens of minutes;
depends on the configuration and performance of your system).
The symptoms are:
- 'onstat -u' output shows several sessions in critical section,
all but one waiting on the same buffer:
Userthreads
address flags sessid user tty wait
tout locks nreads nwrites
7000000805412c8 ---PX-- 21 informix 3 0
0 2 42 0
700000080542338 B--PX-- 22 informix 6
700000012c377b0 0 2 0 0
700000080543be0 B--PX-- 25 informix 6
700000012c377b0 0 2 0 0
700000080544418 B--PX-- 24 informix 6
700000012c377b0 0 2 0 0
700000080544c50 B--PX-- 23 informix 6
700000012c377b0 0 2 0 0
700000080545488 B--PX-- 26 informix 6
700000012c377b0 0 2 0 0
700000080545cc0 B--PX-- 27 informix 6
700000012c377b0 0 2 0 0
7000000805464f8 B--PX-- 28 informix 6
700000012c377b0 0 2 0 0
700000080546d30 B--PX-- 29 informix 6
700000012c377b0 0 2 0 0
700000080547568 B--PX-- 30 informix 6
700000012c377b0 0 2 0 0
700000080547da0 B--PX-- 31 informix 6
700000012c377b0 0 2 0 0
7000000805485d8 B--PX-- 32 informix 6
700000012c377b0 0 2 0 0
700000080548e10 B--PX-- 33 informix 6
700000012c377b0 0 2 0 0
700000080549648 B--PX-- 34 informix 6
700000012c377b0 0 2 0 0
700000080549e80 B--PX-- 35 informix 6
700000012c377b0 0 2 0 0
70000008054a6b8 B--PX-- 36 informix 6
700000012c377b0 0 2 0 0
70000008054aef0 B--PX-- 37 informix 6
700000012c377b0 0 2 0 0
...
- 'onstat -X' shows that the buffer everyone is waiting on holds
a bitmap page (pgflgs 0x804)
Buffers (Access)
address owner flags pagenum memaddr
nslots pgflgs scount waiter
700000012c377b0 7000000805412c8 80 3:1023
70000001f6ea000 0 804 0 700000080542338
700000080544418
700000080545488
700000080546d30
....
- 'onstat -g stk' for the main_loop thread shows it's in
wait4critex() call
- 'onstat -g ppf ' for the table into which the
sessions are inserting will show a huge and ever growing numbers
in bfrd and bfwrt
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd
bfwrt seqsc rhitratio
0x300043 90 0 0 0 0 31 0 0
26403161 24799999 0 94 |
