Problem with DB2_USE_ALTERNATE_PAGE_CLEANING
in [DB2]
|
Prev: Perl DBD DB2
Next: Instance Crash
From: Hemant Shah on 10 Sep 2006 17:10 While stranded on information super highway Mark A wrote: > "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. Percent log file reclaimed before soft chckpt (SOFTMAX) = 100 > > Make sure MINCOMMIT is 1. Group commit count (MINCOMMIT) = 1 > > If you are still having problems, then change the > DB2_USE_ALTERNATE_PAGE_CLEANING to OFF (db2set) and change CHNGPGS_THRESH to I unset DB2_USE_ALTERNATE_PAGE_CLEANING and the problem was gone. I am trying to find out what DB2_USE_ALTERNATE_PAGE_CLEANING does and why does it run out of log space. > 20 (default is 60) in the database cfg, which will also speed up dirty page > cleaning. Changed pages threshold (CHNGPGS_THRESH) = 60 I changed it to 20. > > What size are your bufferpools? (Select * from syscat.bufferpools). > BPNAME NPAGES PAGESIZE -------------- ----------- ----------- IBMDEFAULTBP 1000 4096 PLANFILEPOOL 16384 32768 TBDSP1POOL 3276 32768 TBDSP2POOL 3276 32768 TBDSP3POOL 3276 32768 TBDSP4POOL 3276 32768 TBDSP5POOL 3276 32768 CFG32KPOOL 200 32768 TBDSPOLPOOL 3276 32768 TBDSPBTHPOOL 3276 32768 One of the table Mentioned above) that was getting -964 error is using TBDSP5POOL bufferpool. > -- 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 10 Sep 2006 17:35 "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message news:ee1v08$du4$1(a)new7.xnet.com... > While stranded on information super highway Mark A wrote: >> "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. > > Percent log file reclaimed before soft chckpt (SOFTMAX) = 100 > >> >> Make sure MINCOMMIT is 1. > > Group commit count (MINCOMMIT) = 1 > >> >> If you are still having problems, then change the >> DB2_USE_ALTERNATE_PAGE_CLEANING to OFF (db2set) and change CHNGPGS_THRESH >> to > > I unset DB2_USE_ALTERNATE_PAGE_CLEANING and the problem was gone. > > I am trying to find out what DB2_USE_ALTERNATE_PAGE_CLEANING does and why > does it run out of log space. > > >> 20 (default is 60) in the database cfg, which will also speed up dirty >> page >> cleaning. > > Changed pages threshold (CHNGPGS_THRESH) = 60 > > > I changed it to 20. > > >> >> What size are your bufferpools? (Select * from syscat.bufferpools). >> > > > BPNAME NPAGES PAGESIZE > -------------- ----------- ----------- > IBMDEFAULTBP 1000 4096 > PLANFILEPOOL 16384 32768 > TBDSP1POOL 3276 32768 > TBDSP2POOL 3276 32768 > TBDSP3POOL 3276 32768 > TBDSP4POOL 3276 32768 > TBDSP5POOL 3276 32768 > CFG32KPOOL 200 32768 > TBDSPOLPOOL 3276 32768 > TBDSPBTHPOOL 3276 32768 > > > One of the table Mentioned above) that was getting -964 error is using > TBDSP5POOL bufferpool. > >> > > -- > 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. Basically DB2_USE_ALTERNATE_PAGE_CLEANING starts page cleaning (writing dirty "updated" pages to disk) sooner than the default page cleaning algorithm. Lowering CHNGPGS_THRESH has a similar effect, although not exactly the same formula is used. But setting CHNGPGS_THRESH to 20% should be fine (DB2 will start cleaning pages to disk when 20% of the pages in a bufferpool are dirty). You have way too many bufferpools, and unless you have a data warehouse, or the row size is too large, you should be using 4K pages for most tablespaces and bufferpools. Assuming that it will be too difficult to move the tables to new tablespaces, at the very least you need to consolidate the existing 32K bufferpools. If you have an OLTP application you would be better off with one large bufferpool than what you have now. It is possible that 2 (or 3 at the very most) bufferpools would be optimum, but based on what has been so far I guarantee that you will not be able to figure out the optimum 2-3 bufferpool configuration, so just create 1 large 32K bufferpool of size 39516 pages (the sum of the existing 32K bufferpools). This is a very easy alter bufferpool alter tablespace operation that you can do in a few minutes. Restart DB2 when you are finished. Assuming that SYSCATSPACE is using the default bufferpool, then I would increase the size to 5000 pages.
From: Hemant Shah on 11 Sep 2006 11:44 While stranded on information super highway Mark A wrote: > "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message > news:ee1v08$du4$1(a)new7.xnet.com... >> While stranded on information super highway Mark A wrote: >>> "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. >> >> Percent log file reclaimed before soft chckpt (SOFTMAX) = 100 >> >>> >>> Make sure MINCOMMIT is 1. >> >> Group commit count (MINCOMMIT) = 1 >> >>> >>> If you are still having problems, then change the >>> DB2_USE_ALTERNATE_PAGE_CLEANING to OFF (db2set) and change CHNGPGS_THRESH >>> to >> >> I unset DB2_USE_ALTERNATE_PAGE_CLEANING and the problem was gone. >> >> I am trying to find out what DB2_USE_ALTERNATE_PAGE_CLEANING does and why >> does it run out of log space. >> >> >>> 20 (default is 60) in the database cfg, which will also speed up dirty >>> page >>> cleaning. >> >> Changed pages threshold (CHNGPGS_THRESH) = 60 >> >> >> I changed it to 20. >> >> >>> >>> What size are your bufferpools? (Select * from syscat.bufferpools). >>> >> >> >> BPNAME NPAGES PAGESIZE >> -------------- ----------- ----------- >> IBMDEFAULTBP 1000 4096 >> PLANFILEPOOL 16384 32768 >> TBDSP1POOL 3276 32768 >> TBDSP2POOL 3276 32768 >> TBDSP3POOL 3276 32768 >> TBDSP4POOL 3276 32768 >> TBDSP5POOL 3276 32768 >> CFG32KPOOL 200 32768 >> TBDSPOLPOOL 3276 32768 >> TBDSPBTHPOOL 3276 32768 >> >> >> One of the table Mentioned above) that was getting -964 error is using >> TBDSP5POOL bufferpool. >> >>> >> >> -- >> 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. > > Basically DB2_USE_ALTERNATE_PAGE_CLEANING starts page cleaning (writing > dirty "updated" pages to disk) sooner than the default page cleaning > algorithm. Lowering CHNGPGS_THRESH has a similar effect, although not > exactly the same formula is used. But setting CHNGPGS_THRESH to 20% should > be fine (DB2 will start cleaning pages to disk when 20% of the pages in a > bufferpool are dirty). > > You have way too many bufferpools, and unless you have a data warehouse, or > the row size is too large, you should be using 4K pages for most tablespaces > and bufferpools. > > Assuming that it will be too difficult to move the tables to new > tablespaces, at the very least you need to consolidate the existing 32K > bufferpools. If you have an OLTP application you would be better off with > one large bufferpool than what you have now. This is an OLTP environment, but I have tables in different tablespaces so that they can be spread across different disks for performance reasons. Can multiple tablespaces share same bufferpool? > > It is possible that 2 (or 3 at the very most) bufferpools would be optimum, > but based on what has been so far I guarantee that you will not be able to > figure out the optimum 2-3 bufferpool configuration, so just create 1 large > 32K bufferpool of size 39516 pages (the sum of the existing 32K > bufferpools). This is a very easy alter bufferpool alter tablespace > operation that you can do in a few minutes. Restart DB2 when you are > finished. > > Assuming that SYSCATSPACE is using the default bufferpool, then I would > increase the size to 5000 pages. > > -- 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 11 Sep 2006 13:02 "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message news:ee409j$m3r$1(a)new7.xnet.com... > > This is an OLTP environment, but I have tables in different tablespaces > so > that they can be spread across different disks for performance reasons. > > Can multiple tablespaces share same bufferpool? > Yes, multiple tablespaces can share same bufferpool, if the page sizes match. If you have an OLTP system, put them all in one large 32K bufferpool that is the sum of the all the smaller 32K bufferpools. For OLTP, it would be preferable if the pages size was 4K (unless the row will not fit in 4K), but I realize that it would be a little bit of work to change it.
From: Hemant Shah on 12 Sep 2006 10:02
While stranded on information super highway Mark A wrote: > "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message > news:ee409j$m3r$1(a)new7.xnet.com... >> >> This is an OLTP environment, but I have tables in different tablespaces >> so >> that they can be spread across different disks for performance reasons. >> >> Can multiple tablespaces share same bufferpool? >> > > Yes, multiple tablespaces can share same bufferpool, if the page sizes > match. > > If you have an OLTP system, put them all in one large 32K bufferpool that is > the sum of the all the smaller 32K bufferpools. > > For OLTP, it would be preferable if the pages size was 4K (unless the row > will not fit in 4K), but I realize that it would be a little bit of work to > change it. > > Mark, Thanks for all your help, I will tyr to make the recommended changes, but I am still not clear about the 2 initial problems: 1) Why do I get -964 when DB2_USE_ALTERNATE_PAGE_CLEANING is set to YES. 2) If my last commit was at 30000th row how did the extra rows get commited? -- 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. |