Can't get rid of "Prefetch Queue Full" error!

Matthew Scott

Can't get rid of "Prefetch Queue Full" error!
I'm new to DB2 on NT and am running an imaging application. I get the
following error on a consistent basis. I have increased the num_ioservers
parm to twice what the manuals suggest (I have 3 physical disks and 12
ioservers). I have made sure that the prefetch size matches or is a factor
of the extent size (both are 32k) . I have increased the size of my buffer
pools to where my hit ratio is acceptable (above 85%). I have also
increased the number of page cleaners. I don't seem to be able to get rid
of this error or find any documentation on it. It would be great if someone
could tell me where obscure errors are addressed or could give me the right
combination to get rid of this error. Any assistance would be greatly
appreciated.

2001-01-02-17.25.28.187000 Instance:DB2 Node:000
PID:167(db2syscs.exe) TID:275 Appid:*LOCAL.DB2.010102121521
buffer_pool_services sqlbpfAddToQ Probe:298 Database:OBJSRVRN

Prefetch Queue Full: request not queued. You may need to increase
num_ioservers optimal performance!

Thanks,

Matthew Scott

Database Administrator

Alfa Insurance

(334) 613-4895

[login to unmask email] <mailto:[login to unmask email]>

Philip Gunning

Re: Can't get rid of "Prefetch Queue Full" error!
(in response to Matthew Scott)
Matthew, Sounds like your problem could be due to a few possibilities:

1. You say you have a buffer hit ratio of 85%. This isn't bad but I would
shoot for high 90's if you have the memory. I would increase the size of the
bufferpool and see if it takes care of the problem. What is the size of it
now? How big are the objects? Do you have tables and indexes broken out
into separate table spaces?

2. Is this imaging application a vendor package or was it developed
in-house? You may have very long running transactions that are not
committing.

3. You might try adjusting the chngpgs_thresh to a lower percent if you are
using the default and monitor the results.

4. Check num_iocleaners and increase if pool data or index writes are much
greater than pool_asyn_data_writes or index writes. The default is 1 and
you may need to increase this.

5. Verify that your num_ioservers is in fact what you think it is.

I would suggest making 1 or two changes if parameters are related and then
monitoring the results of your change.

Quest Central for DB2 can capture this type of information in a central
location and enable rapid tuning of production problems.
HTH Phil

Philip K. Gunning
Sr. Systems Consultant
Quest Software
www.quest.com/db2/index1.asp
Assoc List Owner DB2-L
IBM Certified Solutions Expert -- DB2 UDB Database Administration
IBM Certified Solutions Expert -- DB2 Application Development Unix, Win,OS/2
IBM Certified Advanced Technical Expert -- DB2 UDB DRDA (Connect)
IBM Certified Solutions Expert -- CICS Transaction Server

-----Original Message-----
From: Scott, Matthew
To: [login to unmask email]
Sent: 1/3/01 6:33 AM
Subject: Can't get rid of "Prefetch Queue Full" error!

I'm new to DB2 on NT and am running an imaging application. I get the
following error on a consistent basis. I have increased the
num_ioservers parm to twice what the manuals suggest (I have 3 physical
disks and 12 ioservers). I have made sure that the prefetch size
matches or is a factor of the extent size (both are 32k) . I have
increased the size of my buffer pools to where my hit ratio is
acceptable (above 85%). I have also increased the number of page
cleaners. I don't seem to be able to get rid of this error or find any
documentation on it. It would be great if someone could tell me where
obscure errors are addressed or could give me the right combination to
get rid of this error. Any assistance would be greatly appreciated.

2001-01-02-17.25.28.187000 Instance:DB2 Node:000
PID:167(db2syscs.exe) TID:275 Appid:*LOCAL.DB2.010102121521
buffer_pool_services sqlbpfAddToQ Probe:298 Database:OBJSRVRN

Prefetch Queue Full: request not queued. You may need to increase
num_ioservers optimal performance!

Thanks,

Matthew Scott

Database Administrator

Alfa Insurance

(334) 613-4895

[login to unmask email] <mailto:[login to unmask email]>

Scott Hayes

Fw: Can't get rid of "Prefetch Queue Full" error!
(in response to Philip Gunning)
Matthew,

Set your NUM_IOSERVERS to 4 (disks + 1).
12 is way excessive for this hardware configuration.

Alter the tablespace and set the PREFETCHSIZE =
Number of Containers X Extentsize.

I am guessing your tablespace is SMS and that there is
a single container on one of your 3 disks. If this is
the case, try using PREFETCHSIZE = 64.

If you are running with DIAGLEVEL 4 on the database manager
configuration, this is sort of like driving down the highway
with your emergency parking brake on.

Of course, to really tune this baby up, there's
more questions we should ask you... how many cpus?
how much memory? What's intra_parallel set to? Is this
Database transactional or decision support? What are max
and default degrees?

Hope this helps,
Scott

Scott Hayes, Database-GUYS Inc.
Find DB2 UDB Performance Tips and Information at
http://www.database-guys.com/resources.shtml


Scott, Matthew <[login to unmask email]> wrote in message news:
<[login to unmask email]>...
> I'm new to DB2 on NT and am running an imaging application. I get the
> following error on a consistent basis. I have increased the num_ioservers
> parm to twice what the manuals suggest (I have 3 physical disks and 12
> ioservers). I have made sure that the prefetch size matches or is a factor
> of the extent size (both are 32k) . I have increased the size of my buffer
> pools to where my hit ratio is acceptable (above 85%). I have also
> increased the number of page cleaners. I don't seem to be able to get rid
> of this error or find any documentation on it. It would be great if someone
> could tell me where obscure errors are addressed or could give me the right
> combination to get rid of this error. Any assistance would be greatly
> appreciated.
>
> 2001-01-02-17.25.28.187000 Instance:DB2 Node:000
> PID:167(db2syscs.exe) TID:275 Appid:*LOCAL.DB2.010102121521
> buffer_pool_services sqlbpfAddToQ Probe:298 Database:OBJSRVRN
>
> Prefetch Queue Full: request not queued. You may need to increase
> num_ioservers optimal performance!
>
> Thanks,
>
> Matthew Scott
>
> Database Administrator
>
> Alfa Insurance
>
> (334) 613-4895
>
> [login to unmask email] <mailto:[login to unmask email]>
>
>



Ulrich KUHN

Re: Can't get rid of "Prefetch Queue Full" error!
(in response to Scott Hayes)
Scott,

while I agree in theory with your first statement, in practive we found that
by increasing NUM_IOSERVERS dramatically, not only did we get rid of
thousands of those "Prefetch Queue Full" messages in the db2diag.log, we
also noticed an improvement in response times, and we no longer had DB2
freezing on us (V6.1, fp4).

Some background: We have our tables spread across 9 disks (all mirrored).
We initially set NUM_IOSERVERS to 9 - and got heaps of those "Prefetch Queue
Full" messages. We gradually increased the value to 12, 15, 20, 25 - and
still kept getting the messages. Unfortunately, during that time DB2 also
completely froze 3 times (over a period of 10 days) and had to be restarted.
We finally decided to take a drastic step and increase NUM_IOSERVERS to 50.
The messages stopped, and DB2 has not frozen since.

IBM had a look at the log and the dumps. They are not sure what caused DB2
to freeze, but they recommended to keep the high setting of NUM_IOSERVERS.

Cheers,
Ulrich

> -----Original Message-----
> From: Scott Hayes [SMTP:[login to unmask email]
> Sent: Thursday, January 04, 2001 3:10 AM
> To: [login to unmask email]
> Subject: Fw: Can't get rid of "Prefetch Queue Full" error!
>
> Matthew,
>
> Set your NUM_IOSERVERS to 4 (disks + 1).
> 12 is way excessive for this hardware configuration.
>
... (edited - "exceeds 250 lines"...)

> Scott, Matthew <[login to unmask email]> wrote in message news:
> > I'm new to DB2 on NT and am running an imaging application. I get the
> > following error on a consistent basis. I have increased the
> num_ioservers
> > parm to twice what the manuals suggest (I have 3 physical disks and 12
> > ioservers). I have made sure that the prefetch size matches or is a
> factor
> > of the extent size (both are 32k) . I have increased the size of my
> buffer
> > pools to where my hit ratio is acceptable (above 85%). I have also
> > increased the number of page cleaners. I don't seem to be able to get
> rid
> > of this error or find any documentation on it. It would be great if
> someone
> > could tell me where obscure errors are addressed or could give me the
> right
> > combination to get rid of this error. Any assistance would be greatly
> > appreciated.
> >
> > 2001-01-02-17.25.28.187000 Instance:DB2 Node:000
> > PID:167(db2syscs.exe) TID:275 Appid:*LOCAL.DB2.010102121521
> > buffer_pool_services sqlbpfAddToQ Probe:298 Database:OBJSRVRN
> >
> > Prefetch Queue Full: request not queued. You may need to increase
> > num_ioservers optimal performance!
> >
> > Thanks,
> > Matthew Scott
>

Edward Vassie

Re: Can't get rid of "Prefetch Queue Full" error!
(in response to Ulrich KUHN)
5 cents worth from a mainframe perspective.

DB2 for OS/390 has allowed 300 I-O engines in V4 and V5. An I-O engine can
be used for any outstanding read, prefetch, write task. Due to this causing
measurable constraint on I-O throughput, V6 allows 600 I-O engines. Large
sites are hoping this number will go up in future releases.

Therefore, if you need to do a lot of prefetch or other I-O work in DB2 for
AIX, NT, etc, then even 50 IOSERVERS looks a bit low. I would suggest
increasing the NUM_IOSERVERS until it either resolves your I-O problems or
the CPU overhead becomes counter-productive (do not forget to increase DB2
memory allowances). For a very busy DB2 instance, this could even result in
NUM_IOSERVERS getting into 3 digits.

From Edward Vassie...



-----Original Message-----
From: KUHN,Ulrich [mailto:[login to unmask email]
Sent: 08 January 2001 06:06
To: [login to unmask email]
Subject: Re: Can't get rid of "Prefetch Queue Full" error!



Scott,

while I agree in theory with your first statement, in practive we found that
by increasing NUM_IOSERVERS dramatically, not only did we get rid of
thousands of those "Prefetch Queue Full" messages in the db2diag.log, we
also noticed an improvement in response times, and we no longer had DB2
freezing on us (V6.1, fp4).

Some background: We have our tables spread across 9 disks (all mirrored).
We initially set NUM_IOSERVERS to 9 - and got heaps of those "Prefetch Queue
Full" messages. We gradually increased the value to 12, 15, 20, 25 - and
still kept getting the messages. Unfortunately, during that time DB2 also
completely froze 3 times (over a period of 10 days) and had to be restarted.
We finally decided to take a drastic step and increase NUM_IOSERVERS to 50.
The messages stopped, and DB2 has not frozen since.

IBM had a look at the log and the dumps. They are not sure what caused DB2
to freeze, but they recommended to keep the high setting of NUM_IOSERVERS.

Cheers,
Ulrich

-----Original Message-----
From: Scott Hayes [SMTP:[login to unmask email]
Sent: Thursday, January 04, 2001 3:10 AM
To: [login to unmask email]
Subject: Fw: Can't get rid of "Prefetch Queue Full" error!

Matthew,

Set your NUM_IOSERVERS to 4 (disks + 1).
12 is way excessive for this hardware configuration.

... (edited - "exceeds 250 lines"...)

Scott, Matthew <[login to unmask email]> wrote in message news:
> I'm new to DB2 on NT and am running an imaging application. I get the
> following error on a consistent basis. I have increased the num_ioservers

> parm to twice what the manuals suggest (I have 3 physical disks and 12
> ioservers). I have made sure that the prefetch size matches or is a
factor
> of the extent size (both are 32k) . I have increased the size of my
buffer
> pools to where my hit ratio is acceptable (above 85%). I have also
> increased the number of page cleaners. I don't seem to be able to get rid

> of this error or find any documentation on it. It would be great if
someone
> could tell me where obscure errors are addressed or could give me the
right
> combination to get rid of this error. Any assistance would be greatly
> appreciated.
>
> 2001-01-02-17.25.28.187000 Instance:DB2 Node:000
> PID:167(db2syscs.exe) TID:275 Appid:*LOCAL.DB2.010102121521
> buffer_pool_services sqlbpfAddToQ Probe:298 Database:OBJSRVRN
>
> Prefetch Queue Full: request not queued. You may need to increase
> num_ioservers optimal performance!
>
> Thanks,
> Matthew Scott