IBM DB2 Basic Performance Adjustment 3

Basic performance adjustment 3

Roger Sanders

Laughing paste

Original source: "DB2 Magazine" Quarter 1, 2004 · Vol. 9, Issue 1

English original (due to unauthorized article translation, please retain the original link when reprint)

In order to seek best performance, use the database object as the goal you are concerned.

In my earlier column, I showed how to clarify the registration variables and configuration parameters that affect DB2 UDB for Linux, UNIX, and Windows performance. In this column, I will surround another factor that may have a significant impact on database performance: database design is described. As you think, the DB2 UDB database is composed of some objects, how these objects are created and how they work together will have a significant impact on the pros and disadvantages of a database. Objects that produce significant impacts include buffer pools, table spaces and indexes; I will take you to the factors that need to be considered.

Buffer pool

If you have experience using DB2 UDB, you should know that the buffer pool is some space allocated to the DB2 Database Manager in memory, allowing it to add new data to the database or respond to a query back some data data from the disk. Page buffer. Since accessing data from memory is necessary to read more fast than from the disk, the overall performance of the database is improved by reducing the I / O operation buffer pool of the disk. In fact, research buffer pools are created and used in adjusting optimized database performance is the most important step.

Due to the importance of the buffer, each DBA should learn how DB2 UDB uses them. Here is the rapid review course about it. When new data increases to the database, it first adds a new page in the buffer. Eventually this page will be embodied in the database storage space. On the other hand, in response to the query When the data is retrieved from the database, the DB2 Database Manager will first put these pages containing data in the buffer pool before passing it to the application that needs it. user. Each time you perform a new query, you will search for pages that you have needed in the buffer pool that you can use in memory. If so, it will immediately pass it to the corresponding application or user. However, if you cannot find data required in these buffer pools, the DB2 Database Manager will tell these data from the memory and copy it to the buffer before passing the data. Once the page is copied to the buffer pool, this page will always reside in the buffer until the database is turned off or until it is located, you need to store other pages. (Since the loading and modification of all data first occurs in the buffer - the modified page will eventually refresh the disk storage - so the data stored in the buffer pool is always the latest) After the buffer pool is full, DB2 database management Which pages may be selected by the final reference time, page type, or page modification of the page, or page modification without affecting the change of the disk content, which may be referenced again. For example, the page that is retrieved in response to query is more easily overwritten than the page that is in response to the query.

A buffer pool (IBMDEFAULTBP) is created as part of the database creation process in DB2 UDB. In Linux and UNIX platforms, the buffer pool is assigned 1,000 4kb pages from memory; in the Windows platform, the buffer pool is assigned 250 4KB pages from memory. You can add or reduce 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 execute the ALTER BufferPool statement. You can also create additional buffers by using the same method or execute the CREATE BUFFERPOOL statement in the control center.

Due to the importance of the buffer, you should carefully consider how many buffered pools to adapt to your implementation needs; how much is it required; and how each buffer pool can be fully utilized. In most environments, the number of buffers that can be valid can depends on the size of the system memory available. If the available memory can retain 10,000 4K pages (or fewer), the usual use of a separate large buffer pool is better than using multiple small buffer pools. Using a plurality of small buffers will cause frequent access to the page frequently to exchange and exchange, in turn, in turn causes to store objects such as I / O competition or repeated access user tables and indexes for the storage object, such as the catalog table. However, if there is more memory, you should consider Creating and the respective buffers: • Each temporary table space is defined.

· Contains tablespaces that have been subject to some short-term update transactions or repeated access tables

· Contains a table space that is frequently updated with tables and indexes

· Table space that contains frequent queries and indexes but rarely updated

· Table space containing frequent queries that are frequently used

· Table space containing data that is rarely accessed by the application

· Contains some tablespaces you want to use and indexes.

In many cases, the big buffer pool is better than the smaller buffer pool. However, taking into account the total memory that can be used and how the buffer pool will be used. If you have an application to perform a lot of random access operations from a very large table, you should create and use a small buffer pool for this special table. In this case, it is not necessary to keep the data page in the buffer pool memory once they are used to perform a separate query. On the other hand, if you have an application that is frequently retrieved from several seemingly small tables, you should consider creating a large buffer pool to store all the data in these tables. With this design, the data can be loaded at a time, and it allows it to be re-acquired and there is no additional disk I / O.

Table space

An important part of database management includes a mapping of the logical database design to physical storage by using tablespace. DB2 UDB uses two types of tablespaces: System Management Table Space (SMS) and Database Management Table Space (DMS). Using SMS tablespaces, the file manager of the operating system is responsible for assigning and processing tablespace usage storage. Use the DMS tablespace, the tablespace creator (or in some cases, DB2 Database Manager) is responsible for allocation space, and the DB2 database manager is managed. Performance is usually a fast corresponding to the DMS table space. However, the SMS table space has no size limit (pages 16, 777, 215), which is different from DMS table space at this point. SMS tablespace is more easier to manage, in many cases, because the system can automatically get additional storage space when needed. Usually the DMS table space is used for those frequent tables, but growth is slow. The SMS table space is generally used for those continuously grown tables.

Earlier, I mentioned that data is transmitted between tablespace storage containers (such as file system catalog, files, and bare devices) and buffer pools are called some separation blocks that are called page. DB2 UDB provides four different page sizes (4kb, 8kb, 16kb, and 32kb). By default, the three tablespaces generated during the database creation process (SyscatSpace, UserSpace1, and Tempace1) are assigned a 4kb page size. Each table space must be combined with the buffer; a page size used in a special table space must match the page size of the buffer it associated. Also, if you create a page size is a table space other than 4KB, you should create a system temporary table space using the same page size. Otherwise, performance can be reduced (such as sorting and table reorganization) when performing an operational operation of a temporary table space.

When the table space across multiple containers, the data will be written to each container in the Round-Robin mode. The attribute of the extended length is to control how many page data is written in one container before the data is written to the next container. This method helps to balance between all containers belonging to the given space. In order to reduce the corresponding time of the query, the DB2 Database Manager uses a technique called PreFetching to retrieve (or acquire) those database managers to determine the data prior to actually executing. (The data is copied to the buffer with the actual page required; the PREFETCH size of the table space will decide how much additional data pages are copied into the buffer pool when responding to a query.)

By default, all tablespaces are created into extent and prefetches all of which are 32 pages. You can usually improve overall performance by exceeding this default extent and prefetch size. The following two calculations will determine the appropriate extent size:

MIN extent size = [Number of Physical Disks Used by the tablespace * 4096 (bytes)] / TABLESPACE Page Size (in Bytes)

Max extent size = 524288 (bytes) / TableSpace Page Size (in Bytes)

(If you feel like you have never seen these equations or the size of the prefetch, this is normal. Because I didn't find them in any IBM guide or literature. On the contrary, these are me as Members of the DB2 UDB Performance Group study results during the work of IBM laboratory in Toronto.)

The most suitable extent size should be the calculated minimum value to the value of somewhere in the middle of the maximum. Remember, for the size of Extent, more don't necessarily mean better. In the ideal state, you will find a suitable extent size, run performance test, and evaluate each setup of the result from the minimum value transition from the minimum value. Note that the extent size cannot be modified after the table space creation, so the table space will have to delete, create and recalculate at each test.

Once you decide the size of the extent, you can get the prefetch size by the following equation:

Min Prefetch size = (Extent Size * Number of Containers Used) * Factor

Factor is a constant, typically 3.

For Prefetch size, different from extent is that it is better under normal conditions.

Separation data

Carefully view most of the data in a DB2UDB database, how to save you, you will find three different objects: regular user data is stored as data objects; index data contacts index information defined in the table stores as an index object; long Field data is stored as a long field object (long field object exists only in the table) --Long Varchar, Long Vargraphic, Blob, Clob, DBClob). If the DMS type is used, these objects are separated separately and each is stored in its own separate table space. By default, these three objects are stored in the same table space; however, performance may be improved when they store data in the above three types of tablespaces.

About DB2EMPFA

In SMS tablespace, file system (not DB2 Database Manager) is responsible for allocating additional storage space when needed. And in the default, the SMS table space extends each time. However, under certain workloads (for example, when a large amount of insertion operation is performed) it may tend to use the storage space allocated in the extent instead of the page. This is the role of DB2EMPFA tools. When DB2EMPFA is running, the database configuration parameter MULTIPAGE_ALLOC is set bit YES (although it is a read-only configuration parameter), it will cause DB2 UDB to extend an extent instead of each extended SMS table space instead of a page. The DB2EMPFA tool inside the SQLLIB / BIN directory under the DB2 installation path. Index and performance

The main purpose of the index is to help DB2 Database Manager quickly detect records from the table. Creating an index for columns that are often used in the table usually helps to improve data access and update operation performance. In addition, the index also considers better concurrency when multiple transaction processing accesses the same table in the same time; this, the line inspectors are faster and the lock is quickly acquired and don't worry about it. But these advantages require costs. Indexes will occupy the database space, and they may result in a minority reduction in the execution process inserting and updating the operation. (All insertions and partial update operations must occur in the table and its corresponding index.)

So how can you tell you if you create an index will improve performance? DB2 UDB 8.1 encapsulates a toolkit to assist you, it can be accessed through the control center. It is called a design consultant that captures typical workloads for the database and specific modifications of the recommended modifications, such as creating new indexes or deletes unused indexes, as provided by the information provided.

Runstats Tools & Performance

Whenever the SQL statement is sent to the DB2 database manager, the SQL optimizer will read the system catalog to determine the characteristics of the referenced column and the index has been defined during the referenced table, and is simultaneously The size of each table referenced is also included. Based on these information, the optimizer can estimate the cost of each of the data access paths that can meet the SQL statement, and then recommend the best one. The optimizer is used to make decisions, database statistics collection data is a key element in the system catalog table. Therefore, the changes in the statistics may result in changes in the selection of access paths; if the information is lost or out of time, the access plan that the optimizer may be selected will cause the SQL statement to perform time than normal.

Have legitimate information becomes more critical when the complexity of SQL statements is increased. When only one table (no index is not defined), the number of optimizer selection is limited. However, when multiple tables are referenced (each table has one or more indexes), the number of options for optimizer will be greatly increased. But unfortunately, the statistics used by the optimizer will not be automatically updated. Instead, it is necessary to regenerate by using a running statistical tool (Runstats). The RunStats tool can be performed by the control center and the command line. The syntax is as follows:

Runstats On Table [Tablename] Indexes All | with distribution and