Db2 for z/OS buffers

Daniel Luksetich

Db2 for z/OS buffers
A quick survey of Db2 buffers.



What is the size of your Db2 buffers? The size of the largest single buffer,
and the overall size of all buffers? I've had one customer who had lots of
memory and very generous buffer pools, but most of my customers have been
really conservative.



One current very large customer has a single pool set at 24GB, but only 33%
is allocated. Total buffer storage is at 64GB. Total mass storage is in the
many dozens of terabytes so I consider the memory usage quite low.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Sam Baugh

Db2 for z/OS buffers
(in response to Daniel Luksetich)
BP1 - 210G, BP2 - 174G, total of all 812G

On Thu, Sep 26, 2019 at 7:55 AM Daniel L Luksetich <[login to unmask email]>
wrote:

> A quick survey of Db2 buffers.
>
>
>
> What is the size of your Db2 buffers? The size of the largest single
> buffer, and the overall size of all buffers? I’ve had one customer who had
> lots of memory and very generous buffer pools, but most of my customers
> have been really conservative.
>
>
>
> One current very large customer has a single pool set at 24GB, but only
> 33% is allocated. Total buffer storage is at 64GB. Total mass storage is in
> the many dozens of terabytes so I consider the memory usage quite low.
>
>
>
> Dan
>
>
>
>
> +--------------------------------------+-----------------------------------------------------------+
>
> | Daniel L Luksetich | IBM Certified Advanced Database
> Administrator – |
>
> | IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
> Windows |
>
> | IDUG Content Committee Past-Chairman | IBM Certified Database
> Adminstrator – Db2 11 DBA for z/OS |
>
> | IDUG DB2-L Administrator | IBM Certified System
> Administrator – Db2 11 for z/OS |
>
> | URL: https://db2expert.com | IBM Certified Application
> Developer – Db2 11 for z/OS |
>
>
> +--------------------------------------+-----------------------------------------------------------+
>
>
>
> -----End Original Message-----
>
Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Joel Goldstein

Db2 for z/OS buffers
(in response to Daniel Luksetich)
Dan,



What do you mean that only 33% of the 24 Gig pool is allocated?

What is leading you to that conclusion?



Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 5:55 AM
To: [login to unmask email]
Subject: [DB2-L] - Db2 for z/OS buffers



A quick survey of Db2 buffers.



What is the size of your Db2 buffers? The size of the largest single buffer,
and the overall size of all buffers? I've had one customer who had lots of
memory and very generous buffer pools, but most of my customers have been
really conservative.



One current very large customer has a single pool set at 24GB, but only 33%
is allocated. Total buffer storage is at 64GB. Total mass storage is in the
many dozens of terabytes so I consider the memory usage quite low.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+







-----End Original Message-----

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Daniel Luksetich

Db2 for z/OS buffers
(in response to Joel Goldstein)
The pool is designated as a contiguous buffer pool. It is sized at 24GB, but
so far all the objects assigned only fill 1/3rd of it. So, the additional
memory is not yet allocated by Db2.



BTW, contiguous buffer pool = PGSTEAL(NONE) AND VPSEQT(100)! When first
implemented the contiguous buffer pool it was our highest consumer of
synchronous I/O's due to page sets slipping in and out of GBP dependency.
VPSEQT(100) solved that problem. PI59168. Still applies in Db2 12.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 10:21 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Dan,



What do you mean that only 33% of the 24 Gig pool is allocated?

What is leading you to that conclusion?



Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

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



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 5:55 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 for z/OS buffers



A quick survey of Db2 buffers.



What is the size of your Db2 buffers? The size of the largest single buffer,
and the overall size of all buffers? I've had one customer who had lots of
memory and very generous buffer pools, but most of my customers have been
really conservative.



One current very large customer has a single pool set at 24GB, but only 33%
is allocated. Total buffer storage is at 64GB. Total mass storage is in the
many dozens of terabytes so I consider the memory usage quite low.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+







-----End Original Message-----



-----End Original Message-----

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Joel Goldstein

Db2 for z/OS buffers
(in response to Daniel Luksetich)
Hi Dan,



I don't have any customers using in-memory yet, so don't have any data to
look at. Have several running pools of 1.5 million and 2 million buffers.



I can't find anything that says the overall pool memory for in-memory pools
is not allocated when the pool is created.



I only find this below. that discussed the overflow area not being backed
by real storage unless it's used.



To make this feature more resilient, DB2 12 introduces an overflow area
that's automatically managed by DB2. An overflow area is reserved by DB2
from the buffer pool VPSIZE specification, and represents 10 percent of the
buffer pool size, up to a maximum of 6400 buffers. It's only used by DB2 if
the objects assigned to the buffer pool don't fit into the "main" area of
the buffer pool-90 percent of VPSIZE. The overflow area is allocated when
the buffer pool is allocated, but is only backed by real storage when it's
actually used for overflow pages. Any pages in the overflow area are
automatically managed by DB2 on a least recently used (LRU) basis, not a
FIFO basis. DB2 doesn't allow page stealing within the main buffer pool
area, but it's possible in the overflow area.



What is showing you that the pool is only 33% allocated?



Thanks,

Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 9:06 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



The pool is designated as a contiguous buffer pool. It is sized at 24GB, but
so far all the objects assigned only fill 1/3rd of it. So, the additional
memory is not yet allocated by Db2.



BTW, contiguous buffer pool = PGSTEAL(NONE) AND VPSEQT(100)! When first
implemented the contiguous buffer pool it was our highest consumer of
synchronous I/O's due to page sets slipping in and out of GBP dependency.
VPSEQT(100) solved that problem. PI59168. Still applies in Db2 12.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 10:21 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Dan,



What do you mean that only 33% of the 24 Gig pool is allocated?

What is leading you to that conclusion?



Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 5:55 AM
To: [login to unmask email]
Subject: [DB2-L] - Db2 for z/OS buffers



A quick survey of Db2 buffers.



What is the size of your Db2 buffers? The size of the largest single buffer,
and the overall size of all buffers? I've had one customer who had lots of
memory and very generous buffer pools, but most of my customers have been
really conservative.



One current very large customer has a single pool set at 24GB, but only 33%
is allocated. Total buffer storage is at 64GB. Total mass storage is in the
many dozens of terabytes so I consider the memory usage quite low.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+







-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Daniel Luksetich

Db2 for z/OS buffers
(in response to Joel Goldstein)
Yes, display bufferpool command gives the allocated size at about 1/3 the
assigned size of the pool.



2 million buffers is small. 8GB. Sam's using 812GB, and one of my customers
(the really big on) is at 64GB. We've got simulation on several pools,
however. Hopefully we find benefit and increase.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 11:52 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Hi Dan,



I don't have any customers using in-memory yet, so don't have any data to
look at. Have several running pools of 1.5 million and 2 million buffers.



I can't find anything that says the overall pool memory for in-memory pools
is not allocated when the pool is created.



I only find this below. that discussed the overflow area not being backed
by real storage unless it's used.



To make this feature more resilient, DB2 12 introduces an overflow area
that's automatically managed by DB2. An overflow area is reserved by DB2
from the buffer pool VPSIZE specification, and represents 10 percent of the
buffer pool size, up to a maximum of 6400 buffers. It's only used by DB2 if
the objects assigned to the buffer pool don't fit into the "main" area of
the buffer pool-90 percent of VPSIZE. The overflow area is allocated when
the buffer pool is allocated, but is only backed by real storage when it's
actually used for overflow pages. Any pages in the overflow area are
automatically managed by DB2 on a least recently used (LRU) basis, not a
FIFO basis. DB2 doesn't allow page stealing within the main buffer pool
area, but it's possible in the overflow area.



What is showing you that the pool is only 33% allocated?



Thanks,

Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

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



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 9:06 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 for z/OS buffers



The pool is designated as a contiguous buffer pool. It is sized at 24GB, but
so far all the objects assigned only fill 1/3rd of it. So, the additional
memory is not yet allocated by Db2.



BTW, contiguous buffer pool = PGSTEAL(NONE) AND VPSEQT(100)! When first
implemented the contiguous buffer pool it was our highest consumer of
synchronous I/O's due to page sets slipping in and out of GBP dependency.
VPSEQT(100) solved that problem. PI59168. Still applies in Db2 12.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]
<mailto:[login to unmask email]> >
Sent: Thursday, September 26, 2019 10:21 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Dan,



What do you mean that only 33% of the 24 Gig pool is allocated?

What is leading you to that conclusion?



Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

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



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 5:55 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 for z/OS buffers



A quick survey of Db2 buffers.



What is the size of your Db2 buffers? The size of the largest single buffer,
and the overall size of all buffers? I've had one customer who had lots of
memory and very generous buffer pools, but most of my customers have been
really conservative.



One current very large customer has a single pool set at 24GB, but only 33%
is allocated. Total buffer storage is at 64GB. Total mass storage is in the
many dozens of terabytes so I consider the memory usage quite low.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+







-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Joel Goldstein

Db2 for z/OS buffers
(in response to Daniel Luksetich)
Interesting, can you share the display BP results?



You do mean "allocated" and not the " in-use" amount?





812 GB for the system, or one pool?



Thanks,

Joel









Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 10:18 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Yes, display bufferpool command gives the allocated size at about 1/3 the
assigned size of the pool.



2 million buffers is small. 8GB. Sam's using 812GB, and one of my customers
(the really big on) is at 64GB. We've got simulation on several pools,
however. Hopefully we find benefit and increase.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 11:52 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Hi Dan,



I don't have any customers using in-memory yet, so don't have any data to
look at. Have several running pools of 1.5 million and 2 million buffers.



I can't find anything that says the overall pool memory for in-memory pools
is not allocated when the pool is created.



I only find this below. that discussed the overflow area not being backed
by real storage unless it's used.



To make this feature more resilient, DB2 12 introduces an overflow area
that's automatically managed by DB2. An overflow area is reserved by DB2
from the buffer pool VPSIZE specification, and represents 10 percent of the
buffer pool size, up to a maximum of 6400 buffers. It's only used by DB2 if
the objects assigned to the buffer pool don't fit into the "main" area of
the buffer pool-90 percent of VPSIZE. The overflow area is allocated when
the buffer pool is allocated, but is only backed by real storage when it's
actually used for overflow pages. Any pages in the overflow area are
automatically managed by DB2 on a least recently used (LRU) basis, not a
FIFO basis. DB2 doesn't allow page stealing within the main buffer pool
area, but it's possible in the overflow area.



What is showing you that the pool is only 33% allocated?



Thanks,

Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 9:06 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



The pool is designated as a contiguous buffer pool. It is sized at 24GB, but
so far all the objects assigned only fill 1/3rd of it. So, the additional
memory is not yet allocated by Db2.



BTW, contiguous buffer pool = PGSTEAL(NONE) AND VPSEQT(100)! When first
implemented the contiguous buffer pool it was our highest consumer of
synchronous I/O's due to page sets slipping in and out of GBP dependency.
VPSEQT(100) solved that problem. PI59168. Still applies in Db2 12.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 10:21 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Dan,



What do you mean that only 33% of the 24 Gig pool is allocated?

What is leading you to that conclusion?



Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 5:55 AM
To: [login to unmask email]
Subject: [DB2-L] - Db2 for z/OS buffers



A quick survey of Db2 buffers.



What is the size of your Db2 buffers? The size of the largest single buffer,
and the overall size of all buffers? I've had one customer who had lots of
memory and very generous buffer pools, but most of my customers have been
really conservative.



One current very large customer has a single pool set at 24GB, but only 33%
is allocated. Total buffer storage is at 64GB. Total mass storage is in the
many dozens of terabytes so I consider the memory usage quite low.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+







-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Daniel Luksetich

Db2 for z/OS buffers
(in response to Joel Goldstein)
Sam's response is in the thread, but I can repeat it here:



BP1 - 210G, BP2 - 174G, total of all 812G



Here is the first couple messages from our display of the contiguous BP
(redacted)



DSNB401I XXXXX BUFFERPOOL NAME XXXX, BUFFERPOOL ID XX, USE COUNT XXX

DSNB402I XXXXX BUFFER POOL SIZE = 6000000 BUFFERS AUTOSIZE = NO

VPSIZE MINIMUM = 0 VPSIZE MAXIMUM = 0

ALLOCATED = 2695595 TO BE DELETED = 0

IN-USE/UPDATED = 2689195 OVERFLOW ALLOC = 6400





+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 12:53 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Interesting, can you share the display BP results?



You do mean "allocated" and not the " in-use" amount?





812 GB for the system, or one pool?



Thanks,

Joel









Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

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



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 10:18 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Yes, display bufferpool command gives the allocated size at about 1/3 the
assigned size of the pool.



2 million buffers is small. 8GB. Sam's using 812GB, and one of my customers
(the really big on) is at 64GB. We've got simulation on several pools,
however. Hopefully we find benefit and increase.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]
<mailto:[login to unmask email]> >
Sent: Thursday, September 26, 2019 11:52 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Hi Dan,



I don't have any customers using in-memory yet, so don't have any data to
look at. Have several running pools of 1.5 million and 2 million buffers.



I can't find anything that says the overall pool memory for in-memory pools
is not allocated when the pool is created.



I only find this below. that discussed the overflow area not being backed
by real storage unless it's used.



To make this feature more resilient, DB2 12 introduces an overflow area
that's automatically managed by DB2. An overflow area is reserved by DB2
from the buffer pool VPSIZE specification, and represents 10 percent of the
buffer pool size, up to a maximum of 6400 buffers. It's only used by DB2 if
the objects assigned to the buffer pool don't fit into the "main" area of
the buffer pool-90 percent of VPSIZE. The overflow area is allocated when
the buffer pool is allocated, but is only backed by real storage when it's
actually used for overflow pages. Any pages in the overflow area are
automatically managed by DB2 on a least recently used (LRU) basis, not a
FIFO basis. DB2 doesn't allow page stealing within the main buffer pool
area, but it's possible in the overflow area.



What is showing you that the pool is only 33% allocated?



Thanks,

Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

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



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 9:06 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 for z/OS buffers



The pool is designated as a contiguous buffer pool. It is sized at 24GB, but
so far all the objects assigned only fill 1/3rd of it. So, the additional
memory is not yet allocated by Db2.



BTW, contiguous buffer pool = PGSTEAL(NONE) AND VPSEQT(100)! When first
implemented the contiguous buffer pool it was our highest consumer of
synchronous I/O's due to page sets slipping in and out of GBP dependency.
VPSEQT(100) solved that problem. PI59168. Still applies in Db2 12.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]
<mailto:[login to unmask email]> >
Sent: Thursday, September 26, 2019 10:21 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Dan,



What do you mean that only 33% of the 24 Gig pool is allocated?

What is leading you to that conclusion?



Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

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



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 5:55 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 for z/OS buffers



A quick survey of Db2 buffers.



What is the size of your Db2 buffers? The size of the largest single buffer,
and the overall size of all buffers? I've had one customer who had lots of
memory and very generous buffer pools, but most of my customers have been
really conservative.



One current very large customer has a single pool set at 24GB, but only 33%
is allocated. Total buffer storage is at 64GB. Total mass storage is in the
many dozens of terabytes so I consider the memory usage quite low.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+







-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Joel Goldstein

Db2 for z/OS buffers
(in response to Daniel Luksetich)
That's really interesting,



Thanks Dan !!



Saw Sams post after I sent my last email.





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 11:14 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Sam's response is in the thread, but I can repeat it here:



BP1 - 210G, BP2 - 174G, total of all 812G



Here is the first couple messages from our display of the contiguous BP
(redacted)



DSNB401I XXXXX BUFFERPOOL NAME XXXX, BUFFERPOOL ID XX, USE COUNT XXX

DSNB402I XXXXX BUFFER POOL SIZE = 6000000 BUFFERS AUTOSIZE = NO

VPSIZE MINIMUM = 0 VPSIZE MAXIMUM = 0

ALLOCATED = 2695595 TO BE DELETED = 0

IN-USE/UPDATED = 2689195 OVERFLOW ALLOC = 6400





+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 12:53 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Interesting, can you share the display BP results?



You do mean "allocated" and not the " in-use" amount?





812 GB for the system, or one pool?



Thanks,

Joel









Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 10:18 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Yes, display bufferpool command gives the allocated size at about 1/3 the
assigned size of the pool.



2 million buffers is small. 8GB. Sam's using 812GB, and one of my customers
(the really big on) is at 64GB. We've got simulation on several pools,
however. Hopefully we find benefit and increase.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 11:52 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Hi Dan,



I don't have any customers using in-memory yet, so don't have any data to
look at. Have several running pools of 1.5 million and 2 million buffers.



I can't find anything that says the overall pool memory for in-memory pools
is not allocated when the pool is created.



I only find this below. that discussed the overflow area not being backed
by real storage unless it's used.



To make this feature more resilient, DB2 12 introduces an overflow area
that's automatically managed by DB2. An overflow area is reserved by DB2
from the buffer pool VPSIZE specification, and represents 10 percent of the
buffer pool size, up to a maximum of 6400 buffers. It's only used by DB2 if
the objects assigned to the buffer pool don't fit into the "main" area of
the buffer pool-90 percent of VPSIZE. The overflow area is allocated when
the buffer pool is allocated, but is only backed by real storage when it's
actually used for overflow pages. Any pages in the overflow area are
automatically managed by DB2 on a least recently used (LRU) basis, not a
FIFO basis. DB2 doesn't allow page stealing within the main buffer pool
area, but it's possible in the overflow area.



What is showing you that the pool is only 33% allocated?



Thanks,

Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 9:06 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



The pool is designated as a contiguous buffer pool. It is sized at 24GB, but
so far all the objects assigned only fill 1/3rd of it. So, the additional
memory is not yet allocated by Db2.



BTW, contiguous buffer pool = PGSTEAL(NONE) AND VPSEQT(100)! When first
implemented the contiguous buffer pool it was our highest consumer of
synchronous I/O's due to page sets slipping in and out of GBP dependency.
VPSEQT(100) solved that problem. PI59168. Still applies in Db2 12.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Joel Goldstein - Responsive Systems <[login to unmask email]>
Sent: Thursday, September 26, 2019 10:21 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



Dan,



What do you mean that only 33% of the 24 Gig pool is allocated?

What is leading you to that conclusion?



Joel





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, September 26, 2019 5:55 AM
To: [login to unmask email]
Subject: [DB2-L] - Db2 for z/OS buffers



A quick survey of Db2 buffers.



What is the size of your Db2 buffers? The size of the largest single buffer,
and the overall size of all buffers? I've had one customer who had lots of
memory and very generous buffer pools, but most of my customers have been
really conservative.



One current very large customer has a single pool set at 24GB, but only 33%
is allocated. Total buffer storage is at 64GB. Total mass storage is in the
many dozens of terabytes so I consider the memory usage quite low.



Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+







-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Steven Lamb

RE: Db2 for z/OS buffers
(in response to Joel Goldstein)

34 Gb total for the buffer pools, with the biggest (4K page size) at 16 Gb.

 

With the large pool sizes and data sharing, are you seeing any transaction delays when you have to rebuild a duplexed Group buffer pool? We can get delays of 10 - 15 seconds when the GBPs linked to the bigger buffer pools get rebuilt.

 

Regards,

Steve

Daniel Luksetich

Db2 for z/OS buffers
(in response to Steven Lamb)
I am not aware of any large delays at my customer sites.

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Steven Lamb <[login to unmask email]>
Sent: Monday, September 30, 2019 3:01 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 for z/OS buffers



34 Gb total for the buffer pools, with the biggest (4K page size) at 16 Gb.



With the large pool sizes and data sharing, are you seeing any transaction delays when you have to rebuild a duplexed Group buffer pool? We can get delays of 10 - 15 seconds when the GBPs linked to the bigger buffer pools get rebuilt.



Regards,

Steve



-----End Original Message-----

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)