Problem with DB2_USE_ALTERNATE_PAGE_CLEANING
in [DB2]
|
Prev: Perl DBD DB2
Next: Instance Crash
From: Hemant Shah on 7 Sep 2006 17:22 Folks, I spent better part of morning debugging a problem and it turned out to be DB2_USE_ALTERNATE_PAGE_CLEANING registry variable. I am running DB2 8.2 on AIX 5.3 system: # oslevel 5.3.0.0 # db2level DB21085I Instance "db2test" uses "32" bits and DB2 code release "SQL08023" with level identifier "03040106". Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak "10". Product is installed at "/usr/opt/db2_08_01". I had set DB2_USE_ALTERNATE_PAGE_CLEANING to see if it made any difference in performance, and forgot about it because aour test environment was not being used for several weeks. Today I tried to load large amount of data into the database using a C program. It reads a file in propriety format and INSERTs data into the table. It COMMITS data every 5000 rows and if it gets error it will do ROLLBACK and exit. Some of the smaller tables loaded without any problem, but larger tables got SQL -964 error (transaction log full). Couple things happened when I had set DB2_USE_ALTERNATE_PAGE_CLEANING to YES: 1) I would get -964 after inserting 30000 rows, I only tested this with one file and it always got -964 somewhere between 30000 and 35000 rows. 2) My last commit is done at 30000th row, then after that I get -964 my program does ROLLBACK and exits. When I run SELECT COUNT(*) on the table I have little more than 32000 rows. How did the extra rows got commited? The table should only have 30000 rows. I start out with an empty table. When I unset DB2_USE_ALTERNATE_PAGE_CLEANING registry variable, everything works fine. Is this a bug or a feature? The DB2 documentation on DB2_USE_ALTERNATE_PAGE_CLEANING does not clarify this. Here is what I found on IBM site: DB2_USE_ALTERNATE_PAGE_CLEANING: Default=not set: Values: ON, OFF Specifies whether DB2 uses the alternate method of page cleaning algorithms instead of the default method of page cleaning. When this variable is set to "ON," DB2 uses a proactive method of page cleaning, writing changed pages to disk, keeping ahead of LSN_GAP, and proactively finding victims. Doing this allows the page cleaners to better utilize available disk I/O bandwidth. When this variable is set to "ON," the chngpgs_thresh database configuration parameter is no longer relevant because it does not control page cleaner activity. Thanks for you help. -- Hemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah(a)xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own.
From: Mark A on 7 Sep 2006 18:29 "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message news:edq2j8$9u8$1(a)new7.xnet.com... > > Folks, > > I spent better part of morning debugging a problem and it turned out to > be > DB2_USE_ALTERNATE_PAGE_CLEANING registry variable. > > I am running DB2 8.2 on AIX 5.3 system: > > # oslevel > 5.3.0.0 > > # db2level > DB21085I Instance "db2test" uses "32" bits and DB2 code release > "SQL08023" > with level identifier "03040106". > Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak > "10". > Product is installed at "/usr/opt/db2_08_01". > > > I had set DB2_USE_ALTERNATE_PAGE_CLEANING to see if it made any difference > in performance, and forgot about it because aour test environment was not > being used for several weeks. Today I tried to load large amount of data > into > the database using a C program. It reads a file in propriety format and > INSERTs data into the table. It COMMITS data every 5000 rows and if it > gets > error it will do ROLLBACK and exit. > > Some of the smaller tables loaded without any problem, but larger tables > got SQL -964 error (transaction log full). > > Couple things happened when I had set DB2_USE_ALTERNATE_PAGE_CLEANING to > YES: > > 1) I would get -964 after inserting 30000 rows, I only tested this with > one > file and it always got -964 somewhere between 30000 and 35000 rows. > > 2) My last commit is done at 30000th row, then after that I get -964 my > program does ROLLBACK and exits. When I run SELECT COUNT(*) on the table > I have little more than 32000 rows. > How did the extra rows got commited? The table should only have 30000 > rows. > I start out with an empty table. > > > > When I unset DB2_USE_ALTERNATE_PAGE_CLEANING registry variable, everything > works fine. > > Is this a bug or a feature? > > The DB2 documentation on DB2_USE_ALTERNATE_PAGE_CLEANING does not clarify > this. > > Here is what I found on IBM site: > > DB2_USE_ALTERNATE_PAGE_CLEANING: Default=not set: Values: ON, OFF > > Specifies whether DB2 uses the alternate method of page cleaning > algorithms > instead of the default method of page cleaning. When this variable is set > to > "ON," DB2 uses a proactive method of page cleaning, writing changed pages > to > disk, keeping ahead of LSN_GAP, and proactively finding victims. Doing > this > allows the page cleaners to better utilize available disk I/O bandwidth. > > When this variable is set to "ON," the chngpgs_thresh database > configuration > parameter is no longer relevant because it does not control page cleaner > activity. > > > Thanks for you help. > > -- > Hemant Shah /"\ ASCII ribbon campaign You would be much better off if you commit every 1000 rows instead of 30,000. Many people over-estimate the cost of a commit, and DB2 will write the Log Buffer to disk anyway when the log buffer is full, or every one second, even if you don't take a commit. So putting off the commit for 30,000 inserts does not really help you, and could actually slow things down. You also should make sure that your LOGBUFSZ is set to at least 128 pages, and maybe a bit larger (default is a pitiful 8 pages). But don't make it too large because it could actually slow things down a bit if more than 512 pages. With a more frequent commit interval you will not run out of log space and you will improve performance, so the question you posted becomes moot and you can spend your time on more productive matters.
From: Hemant Shah on 7 Sep 2006 22:59 While stranded on information super highway Mark A wrote: > "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message > news:edq2j8$9u8$1(a)new7.xnet.com... >> >> Folks, >> >> I spent better part of morning debugging a problem and it turned out to >> be >> DB2_USE_ALTERNATE_PAGE_CLEANING registry variable. >> >> I am running DB2 8.2 on AIX 5.3 system: >> >> # oslevel >> 5.3.0.0 >> >> # db2level >> DB21085I Instance "db2test" uses "32" bits and DB2 code release >> "SQL08023" >> with level identifier "03040106". >> Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak >> "10". >> Product is installed at "/usr/opt/db2_08_01". >> >> >> I had set DB2_USE_ALTERNATE_PAGE_CLEANING to see if it made any difference >> in performance, and forgot about it because aour test environment was not >> being used for several weeks. Today I tried to load large amount of data >> into >> the database using a C program. It reads a file in propriety format and >> INSERTs data into the table. It COMMITS data every 5000 rows and if it >> gets >> error it will do ROLLBACK and exit. >> >> Some of the smaller tables loaded without any problem, but larger tables >> got SQL -964 error (transaction log full). >> >> Couple things happened when I had set DB2_USE_ALTERNATE_PAGE_CLEANING to >> YES: >> >> 1) I would get -964 after inserting 30000 rows, I only tested this with >> one >> file and it always got -964 somewhere between 30000 and 35000 rows. >> >> 2) My last commit is done at 30000th row, then after that I get -964 my >> program does ROLLBACK and exits. When I run SELECT COUNT(*) on the table >> I have little more than 32000 rows. >> How did the extra rows got commited? The table should only have 30000 >> rows. >> I start out with an empty table. >> >> >> >> When I unset DB2_USE_ALTERNATE_PAGE_CLEANING registry variable, everything >> works fine. >> >> Is this a bug or a feature? >> >> The DB2 documentation on DB2_USE_ALTERNATE_PAGE_CLEANING does not clarify >> this. >> >> Here is what I found on IBM site: >> >> DB2_USE_ALTERNATE_PAGE_CLEANING: Default=not set: Values: ON, OFF >> >> Specifies whether DB2 uses the alternate method of page cleaning >> algorithms >> instead of the default method of page cleaning. When this variable is set >> to >> "ON," DB2 uses a proactive method of page cleaning, writing changed pages >> to >> disk, keeping ahead of LSN_GAP, and proactively finding victims. Doing >> this >> allows the page cleaners to better utilize available disk I/O bandwidth. >> >> When this variable is set to "ON," the chngpgs_thresh database >> configuration >> parameter is no longer relevant because it does not control page cleaner >> activity. >> >> >> Thanks for you help. >> >> -- >> Hemant Shah /"\ ASCII ribbon campaign > > You would be much better off if you commit every 1000 rows instead of > 30,000. Many people over-estimate the cost of a commit, and DB2 will write > the Log Buffer to disk anyway when the log buffer is full, or every one > second, even if you don't take a commit. So putting off the commit for > 30,000 inserts does not really help you, and could actually slow things > down. I commit every 5000 rows, I even tried commit at 1000 rows with -964 error. If I unset DB2_USE_ALTERNATE_PAGE_CLEANING then I do not have problem committing every 5000 rows. > > You also should make sure that your LOGBUFSZ is set to at least 128 pages, > and maybe a bit larger (default is a pitiful 8 pages). But don't make it too > large because it could actually slow things down a bit if more than 512 > pages. LOGBUFSZ is set to 128. > > With a more frequent commit interval you will not run out of log space and > you will improve performance, so the question you posted becomes moot and > you can spend your time on more productive matters. I only run out of log space if DB2_USE_ALTERNATE_PAGE_CLEANING is set to yes. > > -- Hemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah(a)xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own.
From: Hemant Shah on 7 Sep 2006 23:48 While stranded on information super highway Hemant Shah wrote: > > Folks, > > I spent better part of morning debugging a problem and it turned out to be > DB2_USE_ALTERNATE_PAGE_CLEANING registry variable. > > I am running DB2 8.2 on AIX 5.3 system: > > # oslevel > 5.3.0.0 > > # db2level > DB21085I Instance "db2test" uses "32" bits and DB2 code release "SQL08023" > with level identifier "03040106". > Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak > "10". > Product is installed at "/usr/opt/db2_08_01". > > > I had set DB2_USE_ALTERNATE_PAGE_CLEANING to see if it made any difference > in performance, and forgot about it because aour test environment was not > being used for several weeks. Today I tried to load large amount of data into > the database using a C program. It reads a file in propriety format and > INSERTs data into the table. It COMMITS data every 5000 rows and if it gets > error it will do ROLLBACK and exit. > > Some of the smaller tables loaded without any problem, but larger tables > got SQL -964 error (transaction log full). > > Couple things happened when I had set DB2_USE_ALTERNATE_PAGE_CLEANING to YES: > > 1) I would get -964 after inserting 30000 rows, I only tested this with one > file and it always got -964 somewhere between 30000 and 35000 rows. > > 2) My last commit is done at 30000th row, then after that I get -964 my > program does ROLLBACK and exits. When I run SELECT COUNT(*) on the table > I have little more than 32000 rows. > How did the extra rows got commited? The table should only have 30000 rows. > I start out with an empty table. > > > > When I unset DB2_USE_ALTERNATE_PAGE_CLEANING registry variable, everything > works fine. > > Is this a bug or a feature? > > The DB2 documentation on DB2_USE_ALTERNATE_PAGE_CLEANING does not clarify > this. > > Here is what I found on IBM site: > > DB2_USE_ALTERNATE_PAGE_CLEANING: Default=not set: Values: ON, OFF > > Specifies whether DB2 uses the alternate method of page cleaning algorithms > instead of the default method of page cleaning. When this variable is set to > "ON," DB2 uses a proactive method of page cleaning, writing changed pages to > disk, keeping ahead of LSN_GAP, and proactively finding victims. Doing this > allows the page cleaners to better utilize available disk I/O bandwidth. > > When this variable is set to "ON," the chngpgs_thresh database configuration > parameter is no longer relevant because it does not control page cleaner > activity. > > > Thanks for you help. > I saw following message several times in my db2diag.log file. I have 4 CPU system and NUM_IOCLEANERS and NUM_IOSERVERS are set to 5, and SOFTMAX is set 2006-09-07-12.35.56.188413-240 E7527178C666 LEVEL: Warning PID : 971142 TID : 1 PROC : db2agent (CFG) 0 INSTANCE: db2prod NODE : 000 DB : CFG APPHDL : 0-18 APPID: *LOCAL.db2prod.060907163548 FUNCTION: DB2 UDB, data protection, sqlpgResSpace, probe:1660 MESSAGE : ADM1822W The active log is being held by dirty pages. This is not an error, but database performance may be impacted. If possible, reduce the database work load. If this problem persists, either decrease the SOFTMAX and/or increase the NUM_IOCLEANERS DB configuration parameters. 2006-09-07-12.35.56.189330-240 E7527845C501 LEVEL: Error PID : 971142 TID : 1 PROC : db2agent (CFG) 0 INSTANCE: db2prod NODE : 000 DB : CFG APPHDL : 0-18 APPID: *LOCAL.db2prod.060907163548 FUNCTION: DB2 UDB, data protection, sqlpgResSpace, probe:2860 MESSAGE : ADM1823E The active log is full and is held by application handle "18". Terminate this application by COMMIT, ROLLBACK or FORCE APPLICATION. 2006-09-07-12.35.56.189587-240 I7528347C466 LEVEL: Error PID : 971142 TID : 1 PROC : db2agent (CFG) 0 INSTANCE: db2prod NODE : 000 DB : CFG APPHDL : 0-18 APPID: *LOCAL.db2prod.060907163548 FUNCTION: DB2 UDB, data protection, sqlpWriteLR, probe:6680 RETCODE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE "Log File has reached its saturation point" DIA8309C Log file was full. -- Hemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah(a)xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own.
From: Mark A on 8 Sep 2006 00:49
"Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message news:edqp5q$opo$1(a)new7.xnet.com... > I saw following message several times in my db2diag.log file. I have 4 CPU > system and NUM_IOCLEANERS and NUM_IOSERVERS are set to 5, and SOFTMAX is > set > I would change the NUM_IOCLEANERS and NUM_IOSERVERS to 4. The default for SOFTMAX is 100. If it is not set to 100, I would try changing it back to the default. Make sure MINCOMMIT is 1. If you are still having problems, then change the DB2_USE_ALTERNATE_PAGE_CLEANING to OFF (db2set) and change CHNGPGS_THRESH to 20 (default is 60) in the database cfg, which will also speed up dirty page cleaning. What size are your bufferpools? (Select * from syscat.bufferpools). |