Sudden 'insert' performance degradation (Informix 9.2)

Hi Informix group, Three weeks ago, we encountered a very sudden performance degradation issue on our Informix 9.2 system. So far, and despite the Informix support efforts, the problem has not been fixed. The performance issue affects "insert" requests, each insert taking now 30-50 milliseconds instead of the usual 0.3 millisecond or so (as measured on a strictly identical system loaded with the same data). No hardware or software modification of any kind have occurred before the "crash", no particular growth of tables or index creation/deletion (the size of the table actually seems to have no incidence on the insert abnormal duration, nor the existence of indexes, several table in different dbspaces have been tested with the same result). Moreover and strangely enough, when the same table is 'dbloaded' from a .unl file, the response times are normal : 2 million rows are loaded in 12 minutes, i.e. 0.36 millisecond per row. All system measures (cpu charge, io stats) are correct, the system itself never showing any sign of abnormal activity or excessive loading at any time, even during massive inserts. Environment : Informix 9.21 / AIX 4.3 (Risc 6000). Has anybody already encounter this strange behaviour, or has any idea of the cause of the problem ? Thanks for any help, -- Christian

Christian Can you post the results of oncheck -pt on the affected table? Are the number of pages used close to the number allocated: ie are you about to break out a new extent? regards Neil "Christian Fauchier" <nospam@nowhere.com> wrote in message news:1g7ny2i.1kneysx14hp0bdN%nospam@nowhere.com... > Hi Informix group, > > Three weeks ago, we encountered a very sudden performance degradation > issue on our Informix 9.2 system. So far, and despite the Informix > support efforts, the problem has not been fixed. The performance issue > affects "insert" requests, each insert taking now 30-50 milliseconds > instead of the usual 0.3 millisecond or so (as measured on a strictly > identical system loaded with the same data). > > No hardware or software modification of any kind have occurred before > the "crash", no particular growth of tables or index creation/deletion > (the size of the table actually seems to have no incidence on the insert > abnormal duration, nor the existence of indexes, several table in > different dbspaces have been tested with the same result). > > Moreover and strangely enough, when the same table is 'dbloaded' from a > .unl file, the response times are normal : 2 million rows are loaded in > 12 minutes, i.e. 0.36 millisecond per row. All system measures (cpu > charge, io stats) are correct, the system itself never showing any sign > of abnormal activity or excessive loading at any time, even during > massive inserts. > > Environment : Informix 9.21 / AIX 4.3 (Risc 6000). > > Has anybody already encounter this strange behaviour, or has any idea of > the cause of the problem ? > > Thanks for any help, > > -- > Christian

Neil Truby <neil.truby@ardenta.com> wrote: > Christian > > Can you post the results of oncheck -pt on the affected table? > > Are the number of pages used close to the number allocated: ie are you about > to break out a new extent? Hi Neil, Thank you for you quick answer. I am not currently at my office so I can't do the oncheck right now. However, extents do not seem to be involved in our problem, which affects inserts in whatever table. The last test I carried on was made on a new table, created for this purpose with an initial extent size large enough to hold all the rows. The table is initially empty (no indexes defined on this table). The test program (4gl) consists in a foreach loop that reads rows sequentially from a table of identical structure and insert them into the new table. Timestamp is printed just before and after the insert, the (excessive) duration of which remains constant throughout the whole process (i.e. even at the begining when the table is still nearly empty). By the way, writing this post makes me wonder if I a esql/C program instead of a 4gl would give the same results. I think that will be my next move... Thanks for your help, -- Christian