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
Wednesday, March 25, 2009
Sunday, March 8, 2009
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.
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.
Friday, March 6, 2009
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
Subscribe to:
Posts (Atom)