Shulou(Shulou.com)05/31 Report--

How to carry out DB2 performance tuning work analysis, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Recently, I was responsible for the server performance maintenance of the Metric project, and did some research on the performance adjustment of DB2. The overall feeling that the key point of database tuning should be in the database construction stage, good queries can get better performance. In the later stage, the adjustment results of database parameters are not very obvious.

1,Monitoring

Db2 get database manager monitor switches

/ / display the status of the monitoring switch

Db2 update dbm cfg using DFT_MON_BUFPOOL ON DFT_MON_LOCK ON

Db2 update dbm cfg using DFT_MON_SORT ON DFT_MON_STMT ON

Db2 update dbm cfg using DFT_MON_TABLE ON DFT_MON_UOS ON

Db2 terminate

Db2stop

Db2start

/ / turn on the monitoring switch at the instance level, so that when the instance is restarted, the switch takes effect

Db2 get database manager monitor switches

Db2 get monitor switches

/ / found that both the instance level and the following database monitoring switches are turned on

Db2 deacivate database tp1

Db2 activate database tp1

/ / reactivate the database and refresh the monitoring data

Select agent_id,rows_read,rows_written,rows_selected,rows_inserted from sysibmadm.snapappl

/ / Monitor how each agent reads and writes queries. If the number of read is much higher than the number of select, consider whether the index is missing.

/ / in my work, I seldom encounter the situation of writing too much, so I don't go deep into this aspect.

Db2 get snapshot for tables on tp1 > sntab1.txt

/ / next, monitor the read and write of all tables under the tp1 database.

# # the next step is to capture the table with a large number of reads greater than writes, and then extract the query SQL on the table

# # two situations to be considered here, static or dynamic

# # @ static, extract from the package

Db2bfd-s sqltp1st.bnd

# # @ is dynamic and can be crawled with snapshot SQL STATEMENT, which is not written here

/ / and then extract the query SQL that we are concerned about reading a large number of times.

/ / I don't like this one very much, tired eyes, but also cumbersome! If you have a large number of queries about SQL, you need to find out for yourself.

Db2 describe indexes for table acct show detail

/ / then find the table from the proposed SQL and see if there is any index in the table. If not, create a new one.

# # after that, you can see if the index is in effect from the access plan

# # static SQL can be obtained from the package using db2expln. I prefer db2exfmt because both static and static SQL can be done.

# # there is an example of db2exfmt about movement. I am used to taking out SQL statement.

# # then put it into the text, db2expln-d GTSSTGMS-f SQL.txt-g-z\;-o GTSSTGMS_sort.txt

# # or db2 connect to tp1

# # db2 set current explain mode explain

# # db2 set current explain snapshot explain

# # db2 "select name,address from acct where."

# # db2exfmt-l-d tp1-o extp2.txt = > vi extp2.txt

# 2,Talespace and I/O Performance#

Db2 select bpname,bufferpoolid,npages,pagesize from syscat.bufferpools

/ / View the buffer pool of the database, the bufferpoolid field and sysibmadm.snapdb_memory_pool in syscat.bufferpools

The pool_secondary_id of / / is related, and the buffer pool between users and the buffer pool built by the system are recorded from the latter table.

/ / CURRENT_SIZE current size; POOL_CONFIG_SIZE setting size; HIGH_WATERMARK maximum record

/ / I found that this corresponds to the PhySz PhyUpBnd PhyHWM of using db2pd-db GTSSTGMS-mempools

/ / use db2pd-db GTSSTGMS-memset to merge similar memory sets

/ / introduce the self-tuning function of inserting a buffer pool here

@ @

Let's create an example buffer pool, MYBP1, which uses the self-tuning feature (notice that its create bufferpool statement uses automatic) with an initial size of 400K, as shown in listing 4:

Create a sample buffer pool MYBP1 that uses the auto-tuning feature

Db2 create bufferpool mybp1 immediate size 100 automatic pagesize 4k

Db2 "select BPNAME, NPAGES from sysibm.sysbufferpools"

When self-tuning is enabled in the buffer pool, the NPAGES field in the sysibm.sysbufferpools table of that particular buffer pool is set to-2. When self-tuning is disabled, the NPAGES field is set to the current size of the buffer pool.

Db2 alter bufferpool mybp2 immediate size 100 automatic

Table space when reading big data, it is useless to specify a buffer for the table space. In this case, DB2 will contact big data with direct I _ par O.

Db2 create bufferpool BP8K size 1000 pagesize 8k

Db2 create system temporary tablespace TMP8K pagesize 8k managed by system using ('TMP8K') EXTENTSIZE 8 PREFETCHSIZE 8 bufferpool BP8K

@ @

# next, of course, depends on the hit rate. Of course, you can use db2 get snapshot for database on tp1.

# here is another method

Select data_physical_reads,index_physical_reads,total_physical_reads,bp_name from sysibmadm.bp_hitratio where bp_name not like 'IBMSYSTEM%'

Select data_logical_reads,index_logical_reads,total_logical_reads,bp_name from sysibmadm.bp_hitratio where bp_name not like 'IBMSYSTEM%'

Select data_hit_ratio_percent,index_hit_ratio_percent,total_hit_ratio_percent,bp_name from sysibmadm.bp_hitratio where bp_name not like 'IBMSYSTEM%'

/ / above, you can detect the hit rate of each buffer.

# next, take a look at the read and write of the buffer

Select substr (bp_name,1,15) as bp_name,total_physical_reads,average_read_time_ms from sysibmadm.bp_read_io where bp_name not like 'IBMSYSTEM%'

Select substr (bp_name,1,15) as bp_name, total_write,average_write_time_ms from sysibmadm.bp_write_io where bp_name not like 'IBMSYSTEM%'

/ / that bufferpool is busy, used for that tablespace, which tables are in the tablespace, check.

# check the table space below

Select pool_data_p_reads as Total_Data_Reads, Pool_async_data_reads as Asynch_Data_Reads, pool_async_read_time from sysibmadm.snapbp where bp_name='IBMDEFAULTBP'

Select pool_async_data_read_reqs as Data_Prefetch_Requests, decimal (POOL_ASYNC_DATA_READS) / decimal (POOL_ASYNC_DATA_READ_REQS) as Data_Page_Per_Prefetch, pages_from_block_ios from sysibmadm.snapbp where bp_name='IBMDEFAULTBP'

# where DATA_PAGES_PER_PREFETCH needs extent size on match, in order to get extent size

# (it is better to open Automatic Prefetch size so that the value NUM of Containers * Extent Size is automatically calculated) use

Db2 get snapshot for tablespace on tp1

# it is best to set IOSERVERS and IOCLEANERS to automatic

Db2 get db cfg show detail | grep NUM_IO

NUM_IOSERVERS AUTOMATIC?

NUM_IOCLEANERS AUTOMATIC?

# 3,Memory Management#

# of course it depends on the current use of memory

Db2 deactivate db tp1

Db2 activate db tp1

Db2 connect to tp1

Db2mtrk-d-v

Db2pd-db tp1-mempools

Db2pd-db tp1-memsets

/ / I won't say much here, a lot of which I don't understand, the specific analysis of the specific situation.

# 4,Memory Management#

Db2 get snapshot for database on tp1 | grep-I sort

/ / need to take a look at total sorts and Sort Overflows

/ / temporary tablespaces will be used if sort heap is not enough.

/ / you can snapshot this temporary table space

/ / pay special attention to Buffer pool temprorary data logical reads and Buffer pool temporary data physical reads

/ / Buffer pool data writes,Asynchronous pool data page writes

# next, we need to evaluate whether sortheap is sufficient.

Db2expln-d tp1-f sortquery.sql-g-z\;-o expsort.txt

# here we mainly see the size of Rows * Row Width and sortheap

# 5pm access plan (Optimizer plan) #

Monitor-Static SQL

Cd $HOME/sqllib/bin

Db2 connect to tp1

Db2 bind sqltp1st.bnd explain yes explsnap yes

Db2exfmt-l-d tp1-n SQLTP1ST-# /-o exptp1.txt

Sort heap size * 4K (PAGE) and numrows*rowwidth (BYTE) compare sizes to determine sort heap size

PCTFREE & FREEPAGE = > 1) read-only table, if there is no task update activity for a table, it can be defined as no free space, and there is no comparison reorg, because paging is not generated.

Monitor-Dynamic SQL

Cd $HOME/sqllib/bin

Db2 connect to tp1

Db2 set current explain mode explain

Db2 set current explain snapshot explain

Db2 "select name,address from acct.where acct_grp < 50 order by name"

Db2exfmt-l-d tp1-o extp2.txt

Insert optimization

Your question is relatively simple, I personally think, db2 insert 6000 pieces of data, certainly within 30 seconds, I suggest you make the following adjustments:

1 、 update db cfg using logfilsz 51200

2 、 update db cfg using logbufsz 1024

3. Where and how did the 6000 pieces of data you inserted come from? that is to say, if you use select behind your insert, you need to see if your select is slow.

4. Execute all the tables involved in your insert statement using the following statement:

Db2 reorg table.

Db2 runstats on table. With distribution and detailed indexes all

5. If the bufferpool used in your tablespace is too small, you must increase your bufferpool. Use the db2 "select bpname,pagesize,npages from syscat.bufferpools" command to check the total size of your bufferpool, and then increase the size of buffer as much as possible while ensuring that the total size of all bufferpool is not more than 1G. Resize the bufferpool using db2 "alter bufferpool ibmdefaultbp size 51200".

This is the answer to the question on how to analyze the performance of DB2. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.