HADR performance - part 1

HADR stands for high availability disaster recovery .This solution is used for disaster recovery purpose on db2 udb databases.HADR should be configured properly inorder to have optimal performance:

1)HADR synchronization mode:HADR can be run in 3 different modes SYNC,NEARSYNC,ASYNC.SYNC mode gives the best protection to data.In this mode primary has to wait until the changes are committed and written on the standby.Primary waits for the acknowledgement from the standby server.In NEARSYNC mode, standby sends acknowledgement as soon as the logs are in memory of standby server.And in ASYNC mode , primary does not wait for any kind of acknowledgement from the standby.Proper synchronization mode has to be chosen for optimal performance

2)DB2_HADR_BUF_SIZE:This registry variable controls the size of the receive buffer .Receive buffer is the area of memory where the logs are received before they are replayed.You can use the db2pd -db dbname -hadr on standby to monitor the usage of receive buffer.If you see it reaching 100 during the workload, you need to increase the value of DB2_HADR_BUF_SIZE

3)DB2_HADR_SOSNDBUF and DB2_HADR_SORCVBUF:There are the socket send buffer size and socket receive buffer size respectively.If the size for these parameters is too small then the full bandwidth can not be utilized.Generally increasing this to a bigger value would not impact performance negatively.


4)Logfilsz:Size of the logfile plays an important role in the performance,Generally this size should be few hundred MB.

Alter table not logged initially

Here is a little code that descripts the usage of "alter table not logged initially"


db2 "connect to SAMPLE";

DB2CMD1="alter table SAMPLE.employee activate not logged initially"

DB2CMD2="INSERT INTO SAMPLE.employee values(,,,,,,,,,,)"
DB2CMD3="commit"

db2 +c -tv "${DB2CMD1}"; db2 "${DB2CMD2}"; db2 "${DB2CMD3}";

Checking the instance peaks

If you want to check the peak usage of memory on any instance use the following


db2 "select * from table (sysproc.admin_get_dbp_mem_usage(-1) ) as t" more
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM PEAK_PARTITION_MEM-------------- -------------------- --------------------- -------------------- 0 1597186048 447676416 450101248


DBPARTITIONNUM::The database partition number from which memory usage statistics is retrieved.
MAX_PARTITION_MEM::The maximum amount of instance memory (in bytes) allowed to be consumed in the database partition.
CURRENT_PARTITION_MEM::The amount of instance memory (in bytes) currently consumed in the database partition.
PEAK_PARTITION_MEM::The peak or high watermark consumption of instance memory (in bytes) in the database

Important Admin views

REORG---->>

SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15) AS TAB_SCHEMA, REORG_PHASE, SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE, REORG_STATUS, REORG_COMPLETION, DBPARTITIONNUM FROM SYSIBMADM.SNAPTAB_REORG ORDER BY DBPARTITIONNUM

LOCK WAIT--->>>

SELECT AGENT_ID, LOCK_MODE, LOCK_OBJECT_TYPE, AGENT_ID_HOLDING_LK, LOCK_MODE_REQUESTED FROM SYSIBMADM.SNAPLOCKWAIT WHERE DBPARTITIONNUM = 0

BPHIT RATIO ------>>>

SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,14) AS BP_NAME, TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT, XDA_HIT_RATIO_PERCENT, DBPARTITIONNUM FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM

TOP DYNAMIC SQL ------>>>

SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY NUM_EXECUTIONS DESC FETCH FIRST 5 ROWS ONLY

UTILITY PROGRESS ------->>

SELECT UTILITY_ID, PROGRESS_TOTAL_UNITS, PROGRESS_COMPLETED_UNITS, DBPARTITIONNUM FROM SYSIBMADM.SNAPUTIL_PROGRESSMONITOR

LOG UTILIZATION ----------->>

SELECT * FROM SYSIBMADM.LOG_UTILIZATION

Monitoring STMM changes

How to monitor the activity of STMM??
STMM logs and db2diag.log provides information about the changes that happen through STMM.db2diag.log gives the information about the changes in the SORTHEAP,BUFFERPOOLS,LOCKLIST,PACKAGECACHE.

Monitoring bufferpool changes on the diaglog:
db2diag -g "message:=Altering bufferpool" db2diag.log

Monitoring the configuration changes by STMM:
db2diag -g "changeevent:=CFG DB" db2diag.log


Interpreting the STMM logs:

Interpreting the STMM logs is not an easy task.IBM has come up with a perl based parser that can parse the STMM logs and throw out some readable results.Below is an example as to how we can call the perl script
perl testperl.pl stmm0.log SAMPLE s

Options for calling the script:
s gives the history of all the memory heap tuning by STMM
o database memory resizes(database_memory)
v sortheap resizes
m minsize information for the consumers
b benefit data for the consumer


You can download the script from http://www.ibm.com/developerworks/data/library/techarticle/dm-0708naqvi/.. There is a dowload link towards the bottom


Gnuplot to build a graph from the output of the perl script:
Gnuplot can be used to plot the data against the data collected from the perl script.

Plot ‘test.dat’ using 1:5 with lines

STMM internals

OS limitations:
For Linux servers,prior to V9.5 setting DATABASE_MEMORY to automatic is not allowed.So essentially sharing of memory between OS and database was not allowed.However with V9.5 DATABASE_MEMORY can be set to automatic when INSTANCE_MEMORY is set to a static value.
STMM Controller:
How does STMM know where to take and where to give?This process is controlled by a component called STMM Controller.STMM does a cost/benefit analysis by using a generic performance rule to assess all the memory consumers.Bufferpools,sotheap,locklist,package cache are the memory consumers that participate in STMM.Bufferpool hit ratio,lock escalations,sort overflows and package cache hit ratio are the indicators of the performance for bufferpools,locklist,sortoverflow,package cache respectively.But there should be a common indicator for these consumers so that STMM can compare the cost/benefits between various consumers.The common indicator can be savings in the I/O or savings in CPU or savings in agent processing time.
Minsize for each consumer:
Each memory consumer will have a minsize limit and it can not donate beyond that limit.Insufficient memory is always dangerous and so STMM uses the minsize constraint when it distributes the memory between the memory consumers.
Tuning interval:
STMM can adjust its tuning interval as quickly as 30 seconds or as infrequently as 10 minutes.If the work load consists of shorter transactions(OLTP) , STMM might use shorter intervals
Free memory target:
STMM steals from OS memory when it needs some for DATABASE_MEMORY.But there is a minimum limit of free memory that has to be left on OS .On smaller servers a higher amount of memory is left out for middleware and other applications.
STMM and sorts:
Setting Sheapthres to a value of 0 and setting sortheap,sheapthres_shr to automatic will allow STMM to tune sort memory.With this setting all the sorts will happen in shared memory and not in private memory.

Temporary tablespace and space limitations

System temporary tablespaces are used for sorts and joins.They are supposed to release the space after the operation , but there is a registry variable DB2_SMS_TRUNC_TMPTABLE_THRESH that controls the number of extents that can be left out after the operation.If it is set to zero then all the extents are deleted after the sort/join operation