Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Bufferpool and log buffer size - are they related

    Hi,

    99% of my database are inserts like transactions. I have a IBMDEFAULTBP bufferpool with NPAGE 106248 of 4KB pages. The 4KB Log buffer size (LOGBUFSZ) is set to 8.

    In my case bufferpool is 13281-times bigger then log buffer size (formule: 106248 / 8 = 13281)

    1. Should LOGBUFSZ and bufferpool be in somekind of multiplication relation?
    2. Should I increase value of LOGBUFSZ and if so how big should it be?
    3. Could I speed up inserts if LOGBUFSZ would be increased?

    My system: DB2 v7.2 fp5 on Windows 2000

    Thanks,
    Grofaty
    Last edited by grofaty; 02-22-05 at 08:18.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    To the best of my knowledge, the value of one does not depend on another ...

    For high insert transactions, it is useful to have a high value for log buffer size .. I wouldn't go for anything less than 128,
    but if you can go for 512 or more, go for it ... 8 is very small ..

    BTW, if you commit after each record, then this value increase does not help ..

    DB2 flushes the log buffer contents into the log files whenever a commit is issued or the log buffer is full.. Increasing the log buffer size for high volume inserts is to reduce this log buffer flushing
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    sathyaram_s,

    Thanks a lot.
    1. Is there any way to find out if database is having a "log buffer full" problem for particular insert operation?
    2. Do I need to increase any other parameter if LOGBUFSZ is increased?
    3. Is there any decrease of performance if LOGBUFSZ is set too high?

    Grofaty

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You should increase the DBHEAP by the same amount as you increase LOGBUFSZ.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Marcus_A,
    Thanks.

    Is there any way to find out if database is having a "log buffer full" problem for particular insert operation? Is there any formula to find out that "log buffer size" is too small?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by grofaty
    sathyaram_s,

    1. Is there any way to find out if database is having a "log buffer full" problem for particular insert operation?
    In the database snapshot there's a value called "log buffer full"; it shows the number of times an agent had to wait for the log buffers being written to disk.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You should probably use a more aggressive page cleaning if you do a lot of inserts. On version 7 you can set CHNGPGS_THRESH = 20 or less, and set NUM_IOCLEANERS > 1 (at least one for each CPU). Both of these are in the DB CFG.

    in version 8.2, instead of CHNGPGS_THRESH you should set alternate page cleaning on:
    db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON
    and also increase NUM_IOCLEANERS > 1.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by grofaty
    Is there any way to find out if database is having a "log buffer full" problem for particular insert operation? Is there any formula to find out that "log buffer size" is too small?
    I would go with sathyaram's suggestion of 128, 256, or 512 pages, depending on your transaction rate, commit interval, and number of simultaneous applications.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Thanks n_i and Marcus_A,

    n_i,
    I have done the following snapshot but I just can't find out the "log buffer full" value.
    Code:
    db2 get snapshot for database on database_name
    .
    What is exact parameter name?

    Marcus_A,
    I have only one CPU-computer and CHNGPGS_THRESH = 40 and NUM_IOCLEANERS = 1
    Is it good?

    If LOGBFSZ in increased from 8 to 512, should I increased DBHEAP only for 512?

    Thanks,
    Grofaty
    Last edited by grofaty; 02-22-05 at 09:55.

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    My system is actualy not transaction environment. It is data warehouse environment. And there are more than 80% of SQL type:

    Code:
    INSERT INTO table SELECT from ... join of more then one table
    I am not considered about query. This data warehouse does NOT have on-line analysis - there are NO querys to this database made on-line. So SELECT statement are not the problem. Performance problem is on 'insert'.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have only one CPU-computer and CHNGPGS_THRESH = 40 and NUM_IOCLEANERS = 1
    Is it good?


    Even with 1 CPU, I would go with CHNGPGS_THRESH = 20 for very high volumne of inserts. Not sure about NUM_IOCLEANERS with only 1 CPU. You could try to experiment and see if throughput increases with higher value.

    If LOGBFSZ in increased from 8 to 512, should I increased DBHEAP only for 512?

    LOGBUSZ uses memory from the DBHEAP, so if your DBHEAP was 2000 and LOGBUFSZ was 8, then increasing DBHEAP to 2504 (2000 + (512 - 8)) would be correct. This assumes your DBHEAP was correct to begin with.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Feb 2005
    Posts
    118
    I read all the responses to this thread but I am not sure if i still follow Is there any way to find out if database is having a "log buffer full" problem for particular insert operation?

    Anil

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    AnilKale,
    That is excatly what I would like to find out before changing any parameter. So: How to find out if there is a "log buffer full" problem? How to measure it? N_i mentioned the 'database snapshot' but I can't find the parameter, he mentioned "log buffer full" value does NOT exist. Any tip would be very helpful.

    Thanks,
    Grofaty

  14. #14
    Join Date
    Mar 2004
    Posts
    448
    Use

    db2 get db cfg for <database_name>|egrep ' [L|l]og '

    and

    db2 get db cfg for <database_name>|egrep '^Log'

    You can also use the event monitor for getting specific information

    Also increase the db parameter mincommit.

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by grofaty
    N_i mentioned the 'database snapshot' but I can't find the parameter, he mentioned "log buffer full" value does NOT exist. Any tip would be very helpful.
    Sorry, I must have confused DB2 for Oracle - log full statistics is available for an Oracle database and I had an impression that was the case for DB2 as well.

    I don't think that the existing snapshots return this number. However, it is mentioned in the System Monitor Guide:
    num_log_buffer_full - Number of Full Log Buffers monitor element
    ...
    Description

    The number of times agents have to wait for log data to write to disk while copying log records into the log buffer. This value is incremented per agent per incident. For example, if two agents attempt to copy log data while the buffer is full, then this value is incremented by two.
    I should think that some DB2 management tools might be showing this statistics.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •