FW: waiting for log buffer??

James, Are You sure that Your temp table is REALLY created in temporary dbspace? Can You make proper diagnostics (e.g. onstat -d/-D)? Is there more intensive log writing during the index creation? ------------------------------------------ Alexey Sonkin Senior Database Administrator > -----Original Message----- > From: james [mailto:hohoho@erewhon.com] > Sent: Sunday, July 27, 2003 11:14 PM > To: informix-list@iiug.org > Subject: Re: waiting for log buffer?? > > The 'load' is really a select into, creating aggregates for a weekly > summary. However, at the point where trouble ocurrs, the load script > is building an index on a temporary table. > > What could hold up a log buffer flush, or what would prevent access to > a log buffer? I imagine a log switch would prevent access to a log > buffer, if they were all full at that point in execution. But I can't > fathom how that could happen. And it was not a short period of time > where other sessions were in stasis, it was minutes. > > > > On Mon, 28 Jul 2003 12:11:16 +1000, "Andrew Hamm" <ahamm@mail.com> > wrote: > > >james wrote: > >> There are 8 log files of 500 MB each. > > > >I'd prefer 80 at 50Mb. Apart from the water marks, there's another > >granularity effect that kicks in that might be a problem with only 8. > >Probably doubtful though... > > > >Waiting for a log buffer .... hmmmmm. > > > >Exactly what is in the load script? How is it being loaded? Just a plain > >LOAD statement in SQL, or are you using dbload? > > Regards, > > James White > white_a_james@notsohotmail.com sending to informix-list

I was sure at the time; the index build was executing in parallel, with multiple sort and exec threads; this could only happen if tempdbs was being used, as it is the only location with multiple dbspaces. However, I did not think to check if the table was *for sure* in temp. I'm reasonably sure, as the SQL that creates the table includes the "WITH NOLOG" keyword. As I think about it, what happens if you create a temporary table, connected to a logged database, within an explicit transaction (begin work ...), with nolog? There was not more log writing during the index creation, but there was a steady stream of transactions. I still think that there must be some resource locking or other bottleneck to cause such a lengthy delay. Even if the temp table was being logged, that by itself shouldn't cause all other sessions to wait for a log buffer while an index is being built; an index build ddoesn't write all that much to the logical log, even on a 'real' table. On Mon, 28 Jul 2003 11:29:17 -0400, Alexey Sonkin <alexeis@grandvirtual.com> wrote: > >James, > >Are You sure that Your temp table is REALLY created >in temporary dbspace? Can You make proper diagnostics >(e.g. onstat -d/-D)? > >Is there more intensive log writing during the index creation? > > >------------------------------------------ >Alexey Sonkin >Senior Database Administrator > > >> -----Original Message----- >> From: james [mailto:hohoho@erewhon.com] >> Sent: Sunday, July 27, 2003 11:14 PM >> To: informix-list@iiug.org >> Subject: Re: waiting for log buffer?? >> >> The 'load' is really a select into, creating aggregates for a weekly >> summary. However, at the point where trouble ocurrs, the load script >> is building an index on a temporary table. >> >> What could hold up a log buffer flush, or what would prevent access to >> a log buffer? I imagine a log switch would prevent access to a log >> buffer, if they were all full at that point in execution. But I can't >> fathom how that could happen. And it was not a short period of time >> where other sessions were in stasis, it was minutes. >> >> >> >> On Mon, 28 Jul 2003 12:11:16 +1000, "Andrew Hamm" <ahamm@mail.com> >> wrote: >> >> >james wrote: >> >> There are 8 log files of 500 MB each. >> > >> >I'd prefer 80 at 50Mb. Apart from the water marks, there's another >> >granularity effect that kicks in that might be a problem with only 8. >> >Probably doubtful though... >> > >> >Waiting for a log buffer .... hmmmmm. >> > >> >Exactly what is in the load script? How is it being loaded? Just a plain >> >LOAD statement in SQL, or are you using dbload? >> >> Regards, >> >> James White >> white_a_james@notsohotmail.com >sending to informix-list