Bufferpool reduces performance.

[Hemant Shah]

Folks,

I am not sure what I am doing wrong, but We have an transaction that
does some serious calculation on small chink of data over and over
again.

It selects few rows from the table several times and does different
calculations. It was taking about 45 seconds to run. I thought that
moving the file to its own tablespace and giving it large bufferpool
would improve the performance. I experienced exactly opposite, the
transaction now took 4 minutes instead of 45 seconds.

Initially table was created as follows:

CREATE TABLE APDEV15.AADMPF00
(KEY0 CHAR (31) NOT NULL,
REC VARCHAR(3000));

CREATE UNIQUE INDEX AADMPF00_ASC ON APDEV15.AADMPF00 (KEY0 ASC)
ALLOW REVERSE SCAN;

Then I changed it as follows:

CREATE BUFFERPOOL PLANFILEPOOL SIZE 40 PAGESIZE 4K;

CREATE REGULAR TABLESPACE PLANFILESPACE PAGESIZE 4K MANAGED BY SYSTEM
USING ('/f2/dbap15/dbap15/PLANFILESPACE') BUFFERPOOL PLANFILEPOOL;

CREATE TABLE APDEV15.AADMPF00
(KEY0 CHAR (31) NOT NULL,
REC VARCHAR(3000)) IN PLANFILESPACE INDEX IN PLANFILESPACE;

CREATE UNIQUE INDEX AADMPF00_ASC ON APDEV15.AADMPF00 (KEY0 ASC) ALLOW REVERSE SCAN;
What am I doing wrong?

I am running DB2 UDB 8.1 FP5 under AIX 5.1
Thanks.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@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.

[Mark A]

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:cs**********@new7.xnet.com...

Folks,

I am not sure what I am doing wrong, but We have an transaction that
does some serious calculation on small chink of data over and over
again.

It selects few rows from the table several times and does different
calculations. It was taking about 45 seconds to run. I thought that
moving the file to its own tablespace and giving it large bufferpool
would improve the performance. I experienced exactly opposite, the
transaction now took 4 minutes instead of 45 seconds.

Initially table was created as follows:

CREATE TABLE APDEV15.AADMPF00
(KEY0 CHAR (31) NOT NULL,
REC VARCHAR(3000));

CREATE UNIQUE INDEX AADMPF00_ASC ON APDEV15.AADMPF00 (KEY0 ASC)
ALLOW REVERSE SCAN;

Then I changed it as follows:

CREATE BUFFERPOOL PLANFILEPOOL SIZE 40 PAGESIZE 4K;

CREATE REGULAR TABLESPACE PLANFILESPACE PAGESIZE 4K MANAGED BY SYSTEM
USING ('/f2/dbap15/dbap15/PLANFILESPACE') BUFFERPOOL PLANFILEPOOL;

CREATE TABLE APDEV15.AADMPF00
(KEY0 CHAR (31) NOT NULL,
REC VARCHAR(3000)) IN PLANFILESPACE INDEX IN PLANFILESPACE;

CREATE UNIQUE INDEX AADMPF00_ASC ON APDEV15.AADMPF00 (KEY0 ASC) ALLOW REVERSE SCAN;

What am I doing wrong?

I am running DB2 UDB 8.1 FP5 under AIX 5.1
Thanks.

The bufferpool has only 40 pages allocated to it. This is extremely small
and even less than the default size. Whether you should have a dedicated
bufferpool for this tablespace and make it much larger, or use and existing
bufferpool which has sufficient pages allocated, depends on many factors
including the total physical memory available, the database design, and the
workload.

[Hemant Shah]

While stranded on information super highway Mark A wrote:

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:cs**********@new7.xnet.com...

Folks,

I am not sure what I am doing wrong, but We have an transaction that
does some serious calculation on small chink of data over and over
again.

It selects few rows from the table several times and does different
calculations. It was taking about 45 seconds to run. I thought that
moving the file to its own tablespace and giving it large bufferpool
would improve the performance. I experienced exactly opposite, the
transaction now took 4 minutes instead of 45 seconds.

Initially table was created as follows:

CREATE TABLE APDEV15.AADMPF00
(KEY0 CHAR (31) NOT NULL,
REC VARCHAR(3000));

CREATE UNIQUE INDEX AADMPF00_ASC ON APDEV15.AADMPF00 (KEY0 ASC)
ALLOW REVERSE SCAN;

Then I changed it as follows:

CREATE BUFFERPOOL PLANFILEPOOL SIZE 40 PAGESIZE 4K;

CREATE REGULAR TABLESPACE PLANFILESPACE PAGESIZE 4K MANAGED BY SYSTEM
USING ('/f2/dbap15/dbap15/PLANFILESPACE') BUFFERPOOL PLANFILEPOOL;

CREATE TABLE APDEV15.AADMPF00
(KEY0 CHAR (31) NOT NULL,
REC VARCHAR(3000)) IN PLANFILESPACE INDEX IN PLANFILESPACE;

CREATE UNIQUE INDEX AADMPF00_ASC ON APDEV15.AADMPF00 (KEY0 ASC) ALLOW REVERSE SCAN;

What am I doing wrong?

I am running DB2 UDB 8.1 FP5 under AIX 5.1
Thanks.

The bufferpool has only 40 pages allocated to it. This is extremely small
and even less than the default size. Whether you should have a dedicated
bufferpool for this tablespace and make it much larger, or use and existing
bufferpool which has sufficient pages allocated, depends on many factors
including the total physical memory available, the database design, and the
workload.

Thanks. After more monitoring/debugging I found out that I needed to
create bufferpool of 1000 pages.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@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.

[Mark A]

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:cs**********@new7.xnet.com...

Thanks. After more monitoring/debugging I found out that I needed to
create bufferpool of 1000 pages.

NO. A bufferpool of 1000 pages is only 4 MB of memory. This is very small.
The total of all your bufferpools on that server should be about 50% of the
total available memory (allowing some memory for use by other DB2 resources,
the OS, and other non-DB2 application processes) on that machine.

So if your database server has 1024 MB (1 GB) of memory, and it is used only
as a DB2 database server, the total of all bufferpools should be about 512
MB or about 128,000 4K pages. Of course, there is no use in making the
bufferpools larger than the total size of all your tablespaces.

[Matt Emmerton]

"Mark A" <no****@nowhere.com> wrote in message
news:9P********************@comcast.com...

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:cs**********@new7.xnet.com...

Thanks. After more monitoring/debugging I found out that I needed to
create bufferpool of 1000 pages.
NO. A bufferpool of 1000 pages is only 4 MB of memory. This is very small.

But it's obviously probably enough to satisfy the usage requirements of this
frequently-updated table which has it's own bufferpool.
The total of all your bufferpools on that server should be about 50% of the total available memory (allowing some memory for use by other DB2 resources, the OS, and other non-DB2 application processes) on that machine.

YMMV. This really depends on how much memory your system has and the amount
of non-database processing you are hosing.

--
Matt Emmerton

[Mark A]

>

But it's obviously probably enough to satisfy the usage requirements of this frequently-updated table which has it's own bufferpool.
Obviously? Just how did you come to that conclusion? It is an SMS tablespace
for a table of unspecified number of rows. The row size is quite large.
YMMV. This really depends on how much memory your system has and the amount of non-database processing you are hosing.

That's what I said if you bothered to read my post. I said:

"allowing some memory for use by other DB2 resources, the OS, and other
non-DB2 application processes"

I also said to use about 50% of the available memory, which obviously is
dependent on the amount of memory for the system.