Re: Thoughts on Logical Log use requested
Following Colin Dawson's quote from the Admin Guide that unbuffered logging fills logical logs faster, Obnoxio The Clown reported tests that proved him wrong. TBP asked for a full onstat -l from a busy period and for onlog -l output, and suggested checking HDR, PTEXTENDS, temp tables or many small transactions. Colin would post it after a holiday. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Colin Dawson said: > Admin Guide page 11-11 Section on Unbuffered transaction logging > > "When the database server flushes the buffer, only the used pages are > written > to disk. Used pages include pages that are only partially full, however, > so > some space is wasted. For this reason, the logical-log files on disk fill > up > faster > than if all the databases on the same database server use buffered > logging." Some tests later: http://www.simpsoncrazy.com/gallery/screenshots/lists/news_175.jpg I stand chastened. Luckily, I'm not the only one. -- Bye now, Obnoxio "It's easier with pictures." -- Cosmo "But wait, it gets worse." -- Cosmo "Run, don't walk, for the nearest exit." -- Cosmo
Obnoxio The Clown wrote: > Colin Dawson said: >> Admin Guide page 11-11 Section on Unbuffered transaction logging >> >> "When the database server flushes the buffer, only the used pages are >> written >> to disk. Used pages include pages that are only partially full, however, >> so >> some space is wasted. For this reason, the logical-log files on disk fill >> up >> faster >> than if all the databases on the same database server use buffered >> logging." > > Some tests later: > http://www.simpsoncrazy.com/gallery/screenshots/lists/news_175.jpg > > I stand chastened. Luckily, I'm not the only one. > No ... you are not alone
TBP wrote:
> Obnoxio The Clown wrote:
>> Colin Dawson said:
>>> Admin Guide page 11-11 Section on Unbuffered transaction logging
>>>
>>> "When the database server flushes the buffer, only the used pages are
>>> written
>>> to disk. Used pages include pages that are only partially full, however,
>>> so
>>> some space is wasted. For this reason, the logical-log files on disk
>>> fill
>>> up
>>> faster
>>> than if all the databases on the same database server use buffered
>>> logging."
>>
>> Some tests later:
>> http://www.simpsoncrazy.com/gallery/screenshots/lists/news_175.jpg
>>
>> I stand chastened. Luckily, I'm not the only one.
>>
> No ... you are not alone
Still (choking a bit on the pie), post a complete onstat -l (including
the header) from your busy period (i.e. just after you have tanked
through 75 of them). Just to see if the logs are "complete" logs, if you
have HDR involved or PTEXTENDS or ... SOMETHING is using up loads and
loads of log space. Temp tables with logging without an extent size
being created and dropped??
Do you have loads of small oltp transactions? Maybe interesting to
actually see what activity IS going on when you go through them at a
high rate. onlog -l a couple of pages and see what is in them.
Certainly will, I'm on holiday until 27th March, I'll post something then.
Regards
Colin
There are 10 types of people in the world, those that understand binary and
those that don't
>From: TBP <TheBigPotato@NotHere.Co.Uk>
>To: informix-list@iiug.org
>Subject: Re: Thoughts on Logical Log use requested
>Date: Fri, 17 Mar 2006 20:13:26 GMT
>
>TBP wrote:
> > Obnoxio The Clown wrote:
> >> Colin Dawson said:
> >>> Admin Guide page 11-11 Section on Unbuffered transaction logging
> >>>
> >>> "When the database server flushes the buffer, only the used pages are
> >>> written
> >>> to disk. Used pages include pages that are only partially full,
>however,
> >>> so
> >>> some space is wasted. For this reason, the logical-log files on disk
> >>> fill
> >>> up
> >>> faster
> >>> than if all the databases on the same database server use buffered
> >>> logging."
> >>
> >> Some tests later:
> >> http://www.simpsoncrazy.com/gallery/screenshots/lists/news_175.jpg
> >>
> >> I stand chastened. Luckily, I'm not the only one.
> >>
> > No ... you are not alone
>
>Still (choking a bit on the pie), post a complete onstat -l (including
>the header) from your busy period (i.e. just after you have tanked
>through 75 of them). Just to see if the logs are "complete" logs, if you
>have HDR involved or PTEXTENDS or ... SOMETHING is using up loads and
>loads of log space. Temp tables with logging without an extent size
>being created and dropped??
>
>Do you have loads of small oltp transactions? Maybe interesting to
>actually see what activity IS going on when you go through them at a
>high rate. onlog -l a couple of pages and see what is in them.
>_______________________________________________
>Informix-list mailing list
>Informix-list@iiug.org
>http://www.iiug.org/mailman/listinfo/informix-list