The db2 tutorial I am watching is: IBM DB2 basic performance tuning. Buffer pool
If you have experience using DB2 UDB, you should know that a buffer pool is some space in memory allocated separately to the DB2 database manager, allowing it to add new data to the database or respond to a query to return some data pages from disk. Since accessing data from memory is bound to be much faster than reading from disk, the overall performance of the database is improved by reducing the disk's I/O operation buffer pool. In fact, studying how buffer pools are created and used in tuning optimized database performance is the most important step.
Due to the importance and status of buffers, every DBA should understand how DB2 UDB uses them. Here is a quick review course on it. When new data is added to the database, it first adds a new page in the buffer. Finally, this page will be embodied into the database storage space. On the other hand, in response to a query, when data is retrieved from the database, the DB2 database manager will first store these pages containing the data in the buffer pool, and then pass it to the application or user that needs it. Each time a new query is executed, each buffer pool that can be exploited will search for whether pages of the required data already reside in memory. If that happens, it will be immediately passed to the corresponding application or user. However, if the required data cannot be found in these buffer pools, the DB2 database manager will say that the data is retrieved from memory and copy it into the buffer before passing the data. Once the page is copied to the buffer pool, the page will remain in the buffer until the database is closed or until the space it is located needs to store other pages. (Since all data loading and modification occurs first in the buffer - the modified pages will eventually refresh the disk storage - so the data stored in the buffer pool is always the latest) When the buffer pool is full, the DB2 database manager will select which pages to remove by detecting the last reference time of the page, the page type, or the page modification will not affect the change in disk content. These pages may be referenced again. For example, a page retrieved 30 minutes ago in response to a query will be more likely to be overwritten than a page that contains update operations without executing an update.
DB2 UDB creates a buffer pool (IBMDEFAULTBP) by default as part of the database creation process. On Linux and Unix platforms, the buffer pool is allocated 1,000 4KB pages from memory; on Windows platforms, the buffer pool is allocated 250 4KB pages from memory. You can increase or decrease the number of 4KB pages of this buffer pool by finding the buffer pool menu in the Control Center and selecting the appropriate operation or executing the ALTER BUFFERPOOL statement. You can also create another buffer by using the same method in the Control Center or executing the CREATE BUFFERPOOL statement.
Due to the importance of buffers, you should carefully consider how many buffer pools to use to suit your implementation needs; how big each one needs to be; and how each buffer pool can be fully utilized. In most environments, the number of buffers that can be used effectively depends on the size of the available system memory. If the available memory can retain 10,000 4k pages (or less), it is generally better to use a separate large buffer pool than to use multiple small buffer pools. Using multiple small buffers will result in frequent access to pages to frequently exchange in and out of memory, which in turn will lead to I/O competition for storage objects such as catalog tables or repeated access to user tables and indexes. However, if there is more memory, you should consider creating the respective buffers as follows:
· �
· �
· �
· �
· �
· Tablespace containing data that is rarely accessed by the application
· �
In many cases, larger buffer pools are better than smaller ones. However, considering the total amount of memory available and how the buffer pool will be used. If you have an application that wants to perform many random access operations from a very large table, then you should create and use a small buffer pool for this special table. In this case, it is not necessary to keep data pages in the buffer pool memory once they are used to perform a separate query. On the other hand, if you have an application that wants to frequently retrieve data from several seemingly small tables, you should consider creating a large enough buffer pool to store all the data in these tables without the data. Using this design, data can be loaded into memory at once and allows it to be retrieved repeatedly without the need for additional disk I/O.
If you have experience using DB2 UDB, you should know that a buffer pool is some space in memory allocated separately to the DB2 database manager, allowing it to add new data to the database or respond to a query to return some data pages from disk. Since accessing data from memory is bound to be much faster than reading from disk, the overall performance of the database is improved by reducing the disk's I/O operation buffer pool. In fact, studying how buffer pools are created and used in tuning optimized database performance is the most important step.
Due to the importance and status of buffers, every DBA should understand how DB2 UDB uses them. Here is a quick review course on it. When new data is added to the database, it first adds a new page in the buffer. Finally, this page will be embodied into the database storage space. On the other hand, in response to a query, when data is retrieved from the database, the DB2 database manager will first store these pages containing the data in the buffer pool, and then pass it to the application or user that needs it. Each time a new query is executed, each buffer pool that can be exploited will search for whether pages of the required data already reside in memory. If that happens, it will be immediately passed to the corresponding application or user. However, if the required data cannot be found in these buffer pools, the DB2 database manager will say that the data is retrieved from memory and copy it into the buffer before passing the data. Once the page is copied to the buffer pool, the page will remain in the buffer until the database is closed or until the space it is located needs to store other pages. (Since all data loading and modification occurs first in the buffer - the modified pages will eventually refresh the disk storage - so the data stored in the buffer pool is always the latest) When the buffer pool is full, the DB2 database manager will select which pages to remove by detecting the last reference time of the page, the page type, or the page modification will not affect the change in disk content. These pages may be referenced again. For example, a page retrieved 30 minutes ago in response to a query will be more likely to be overwritten than a page that contains update operations without executing an update.
DB2 UDB creates a buffer pool (IBMDEFAULTBP) by default as part of the database creation process. On Linux and Unix platforms, the buffer pool is allocated 1,000 4KB pages from memory; on Windows platforms, the buffer pool is allocated 250 4KB pages from memory. You can increase or decrease the number of 4KB pages of this buffer pool by finding the buffer pool menu in the Control Center and selecting the appropriate operation or executing the ALTER BUFFERPOOL statement. You can also create another buffer by using the same method in the Control Center or executing the CREATE BUFFERPOOL statement.
Due to the importance of buffers, you should carefully consider how many buffer pools to use to suit your implementation needs; how big each one needs to be; and how each buffer pool can be fully utilized. In most environments, the number of buffers that can be used effectively depends on the size of the available system memory. If the available memory can retain 10,000 4k pages (or less), it is generally better to use a separate large buffer pool than to use multiple small buffer pools. Using multiple small buffers will result in frequent access to pages to frequently exchange in and out of memory, which in turn will lead to I/O competition for storage objects such as catalog tables or repeated access to user tables and indexes. However, if there is more memory, you should consider creating the respective buffers as follows:
· �
· �
· �
· �
· �
· Tablespace containing data that is rarely accessed by the application
· �
In many cases, larger buffer pools are better than smaller ones. However, considering the total amount of memory available and how the buffer pool will be used. If you have an application that wants to perform many random access operations from a very large table, then you should create and use a small buffer pool for this special table. In this case, it is not necessary to keep data pages in the buffer pool memory once they are used to perform a separate query. On the other hand, if you have an application that wants to frequently retrieve data from several seemingly small tables, you should consider creating a large enough buffer pool to store all the data in these tables without the data. Using this design, data can be loaded into memory at once and allows it to be retrieved repeatedly without the need for additional disk I/O.
This news has 3 pages in total, currently on page 1 1 2 3