IDS Forum

Re: LOGBUFF size

Posted By: FRANK
Date: Friday, 27 May 2016, at 10:34 a.m.

In Response To: Re: LOGBUFF size (Fernando Nunes)

Hi, Fernando,

I tried "onstat -l" a couple of times( see below for its top part. today
might not be a busy day). I saw buffer waiting some times
I am very interested in your paper if available!

Thanks
Frank

IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:12:22 -- 19140800 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 121 128 29978045 237725 126.10

phybegin physize phypos phyused %used

2:53 1950000 6520 4015 0.21
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-1 0 128 1053794263 129546559 106542993 8.1 1.2

Subsystem numrecs Log Space used

OLDRSAM 1053523516 124822591348

SBLOB 268379 40393852

CDR 111 6216

HA 2257 99308

IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:13:07 -- 19140800 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 107 128 29978941 237732 126.10

phybegin physize phypos phyused %used

2:53 1950000 7416 4897 0.25
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-2 9 128 1053872795 129553345 106547469 8.1 1.2

Subsystem numrecs Log Space used

OLDRSAM 1053602048 124831042356

SBLOB 268379 40393852

CDR 111 6216

HA 2257 99308

IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:15:08 -- 19140800 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 43 128 29985168 237783 126.10

phybegin physize phypos phyused %used

2:53 1950000 13771 1826 0.09
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-3 0 128 1054240644 129578725 106557089 8.1 1.2

Subsystem numrecs Log Space used

OLDRSAM 1053969896 124872195792

SBLOB 268379 40393852

CDR 111 6216

HA 2258 99352

IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:15:29 -- 19140800 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 34 128 29985808 237788 126.10

phybegin physize phypos phyused %used

2:53 1950000 14411 2457 0.13
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-1 9 128 1054283654 129582401 106558728 8.1 1.2

Subsystem numrecs Log Space used

OLDRSAM 1054012906 124877581612

SBLOB 268379 40393852

CDR 111 6216

HA 2258 99352

IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:17:21 -- 19140800 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 37 128 29989907 237820 126.10

phybegin physize phypos phyused %used

2:53 1950000 18642 6691 0.34
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-3 0 128 1054691356 129616696 106577152 8.1 1.2

Subsystem numrecs Log Space used

OLDRSAM 1054399845 124920773552

SBLOB 289142 47318172

CDR 111 6216

HA 2258 99352
Buffer Waiting
Buffer ioproc flags
L-2 0 0x61 0

IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:18:37 -- 19140800 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 49 128 29994283 237854 126.10

phybegin physize phypos phyused %used

2:53 1950000 23015 11076 0.57
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-2 0 128 1054944817 129649345 106606670 8.1 1.2

Subsystem numrecs Log Space used

OLDRSAM 1054653306 124947683616

SBLOB 289142 47318172

CDR 111 6216

HA 2258 99352
Buffer Waiting
Buffer ioproc flags
L-1 0 0x61 0

IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:19:03 -- 19140800 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 48 128 29996341 237870 126.10

phybegin physize phypos phyused %used

2:53 1950000 25201 13261 0.68
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-2 0 128 1055095956 129667578 106622661 8.1 1.2

Subsystem numrecs Log Space used

OLDRSAM 1054804445 124964063960

SBLOB 289142 47318172

CDR 111 6216

HA 2258 99352

On Thu, May 26, 2016 at 3:18 PM, Fernando Nunes <domusonline@gmail.com>
wrote:

> "irrelevant" may be a too strong work...
> But do a teste...run "onstat -l"
> Somewhere near the top you'll see on average how many pages were used when
> it was flushed... in busy OLTP systems this is often around 1.x... Not even
> two pages.
> Ceck your values...
>
> Again... for those busy jobs... if you think you could live with a
> potential "COMMIT" that is lost... and if the do many COMMITS, try to run
> them with "SET BUFFEED LOG".
> This would make their COMMITS NOT to force a flush....
> I had the idea of writing an article about this... not sure if I did....
> Time permitting I must get back to it...
> Regards
>
> On Thu, May 26, 2016 at 7:26 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > HI, Fernando,
> >
> > Our database is No HDR and Unbuffered logging.
> >
> > I think , as you suggested, I can ignore it for now.
> >
> > This is not happening often. It happens just sometimes with some busier
> > update jobs involved , it did not last long in our current database.
> > Yes, no one noticed it.
> >
> > We will have three times of more volumes of transactions soon, I just
> > want to make sure it would not get worse next. By the way, we have lot of
> > memory ( hundred of GBs), we can give it as much as it needs :-)
> >
> > This is the first time I heard the size of LOGBUFF is irrelevant for
> > unbuffered logging, interesting ...
> >
> > Thanks
> > Frank
> >
> > On Thu, May 26, 2016 at 1:27 PM, Fernando Nunes <domusonline@gmail.com>
> > wrote:
> >
> > > A couple of questions:
> > >
> > > 1- Do you have HDR?
> > > 2- What is the logmode of your databases? Buffered, Unbuffered?
> > >
> > > The reasons behind the questions are:
> > >
> > > - If you hae HDR, when the buffers are flushed in the primary (to disk)
> > > they are also copied to the "HDR buffers". We have 12 of these but
> > > occasionally if the secondary or the network is dragging they may fill
> up
> > > and cause waits on the primary
> > >
> > > - If you have unbuffered logging like most customers do, the size of
> the
> > > LOGBUFF becomes rather irrelevant because most of the times it will
> never
> > > get full. Because each time a transaction is closed the buffer is
> > flushed.
> > > And this may cause some extra contention.... And there's little you can
> > > do... the options would be:
> > >
> > > a) Change your databases to buffered logging -> BAD IDEA as you may
> cause
> > > inconsistencies between the database and external systems
> > > b) Change the buffer mode at session level.... sessions that could live
> > > with buffered can be set to it. But the other sessions will continue to
> > > force flushing
> > > c) Put your logs on the fasted disk you have... keepin mind that seek
> > > timemay not be very important and the writes are sequential... the
> number
> > > of IOPs is crucial
> > >
> > > Now... after describing a bad scenario.... what really i your problem?
> A
> > > few sessions with G flag once in a while? If yes, ignore it... no one
> > will
> > > notice...
> > > Keep in mind that the flush occurs many times per second in an heavily
> > used
> > > systems.... When you see a session in "G" chances are it went htough
> many
> > > other "G" cycles before you process that information...
> > >
> > > Now... if you want this solved, please vote on this feature:
> > >
> > >
> >
> http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=45166
> > >
> > > It would help.... The basic idea is simple and in some formi already
> used
> > > in DB2 and Oracle. SQL Server seems to have implemented what we have in
> > one
> > > of the latest releases:
> > >
> > > The problem of buffering is that you can tell the application a COMMIT
> > was
> > > ok, when you still can loose it. So why not BUFFER anyway and delay the
> > > answer to the application until you actually flush?
> > > The implementation can't be that simple... the worst case scenario
> would
> > be
> > > a single application running on a server... the buffer would never
> fill,
> > so
> > > it would be stuck... some sort of timer must be implemented to force
> the
> > > flush if meanwhile the buffer hasn't fill up.
> > >
> > > Regards
> > >
> > > On Thu, May 26, 2016 at 5:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
> > >
> > > > Hi,
> > > >
> > > > We set ,
> > > >
> > > > LOGBUFF 256
> > > >
> > > > Occasionally, I still see this sometimes,
> > > >
> > > > 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> > > > 1 0 0
> > > > 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> > > > 2 29 48411
> > > > 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> > > > 1 116 14986
> > > >
> > > > So, a session is waiting for the log buffer .
> > > >
> > > > The doc ( below) seems not to recommend to set it higher or .... .
> > > >
> > > > What do you think or comments ?
> > > >
> > > > Thanks
> > > > Frank
> > > >
> > > > LOGBUFF configuration parameter
> > > >
> > > > Use the LOGBUFF configuration parameter to specify the size in
> > kilobytes
> > > > for the three logical-log buffers in shared memory.
> > > > onconfig.std value LOGBUFF 64 units Kilobytes values An integer in
> the
> > > > range of 32 - (32767 * pagesize / 1024), where pagesize is the
> default
> > > > system page size. The value must be evenly divisible by the default
> > > system
> > > > page size. If the value is not evenly divisible by the page size, the
> > > > database server rounds down the size to the nearest value that is
> > evenly
> > > > divisible by the page size. takes effect After you edit your onconfig
> > > file
> > > > and restart the database server.
> > > > Usage
> > > >
> > > > The three logical log buffers permit user threads to write to the
> > active
> > > > buffer while one of the other buffers is being flushed to disk. If
> > > flushing
> > > > is not complete by the time the active buffer fills, the user thread
> > > begins
> > > > writing to the third buffer.
> > > >
> > > > If the RTO_SERVER_RESTART configuration parameter is enabled, set the
> > > value
> > > > of the LOGBUFF configuration parameter to 256 kilobytes. If the value
> > of
> > > > the LOGBUFF configuration parameter is less than 256 kilobytes, a
> > warning
> > > > message displays when you restart the server.
> > > >
> > > > Otherwise, set the value of the LOGBUFF configuration parameter to 32
> > > > kilobytes for standard workloads or 64 kilobytes for heavy workloads.
> > The
> > > > database server uses the LOGBUFF parameter to set the size of
> internal
> > > > buffers that are used during recovery. If you set LOGBUFF too high,
> the
> > > > database server can run out of memory and shut down during recovery.
> > > >
> > > > If you log user data in smart large objects, increase the size of the
> > log
> > > > buffer to make the system more efficient. The database server logs
> only
> > > the
> > > > portion of a smart-large-object page that changed.
> > > >
> > > > You can view information about the logical log buffers by running the
> > > > onstat
> > > > -l command.
> > > > *Parent topic:* Database configuration parameters
> > > >
> > > > <
> > > >
> > >
> > >
> >
> >
>
> http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_adr_0007.htm?lang=en-us
> > > > >
> > > > *Related reference*:
> > > > onstat -l command: Print physical and logical log information
> > > >
> > > > <
> > > >
> > >
> > >
> >
> >
>
> http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_adr_0598.htm?lang=en-us
> > > > >
> > > >
> > > > --001a11c1728c309b400533c148a4
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --
> > > Fernando Nunes
> > > Portugal
> > >
> > > http://informix-technology.blogspot.com
> > > My email works... but I don't check it frequently...
> > >
> > > --001a1146d40e5dfa2f0533c21b43
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a114222c8ddb0220533c2eb82
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --001a1146d40e07b6be0533c3a634
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001a11356f761b90d20533d3ce2c

Messages In This Thread

• LOGBUFF size
  FRANK -- Thursday, 26 May 2016, at 12:28 p.m.
  • Re: LOGBUFF size
    Fernando Nunes -- Thursday, 26 May 2016, at 1:27 p.m.
    • Re: LOGBUFF size
      FRANK -- Thursday, 26 May 2016, at 2:26 p.m.
      • Re: LOGBUFF size
        Fernando Nunes -- Thursday, 26 May 2016, at 3:18 p.m.
        • RE: LOGBUFF size
          Andrew Ford -- Thursday, 26 May 2016, at 3:30 p.m.
        • Re: LOGBUFF size
          FRANK -- Friday, 27 May 2016, at 10:34 a.m.
          • Re: LOGBUFF size
            Fernando Nunes -- Sunday, 29 May 2016, at 4:37 a.m.
          • Re: LOGBUFF size
            Fernando Nunes -- Wednesday, 3 August 2016, at 12:30 p.m.
  • Re: LOGBUFF size
    Art Kagel -- Thursday, 26 May 2016, at 4:19 p.m.
    • Re: LOGBUFF size
      FRANK -- Friday, 27 May 2016, at 10:51 a.m.
      • Re: LOGBUFF size
        Art Kagel -- Saturday, 28 May 2016, at 4:11 a.m.
        • Re: LOGBUFF size
          Madison Pruet -- Saturday, 28 May 2016, at 8:15 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.