Scaling of DB2 for Linux, UNIX, and Windows memory-related configuration parameters on a test system

This article describes the scaling of the number of users, IBM® DB2® instance memory, and memory-related configuration parameters on a test system, based on configuration of a production system for IBM DB2 for Linux®, UNIX®, and Windows®. The article includes a sample script automating the scaling process. This assists in creating a realistic test system, helping to identify potential robustness and performance issues related to changes in the DB2 environment.

Enzo Cialini (ecialini@ca.ibm.com), STSM - Chief Architect, DB2 Quality Assurance, IBM

Enzo Cialini photoEnzo Cialini is a Senior Technical Staff Member in the IBM Information Management Software division at the IBM Toronto Laboratory. He is currently the Chief Quality Assurance Architect responsible for management and technical test strategy for DB2 LUW Engine (Warehouse and OLTP) and Customer Operational Profiling. Enzo joined IBM in 1991 and has been working on the DB2 development team since 1992. He has more than 20 years experience in software development, testing and support. Enzo is actively engaged with customers and the field in OLTP and Warehouse deployments, has authored The High Availability Guide for DB2 and has numerous patents and publications on DB2 and Integration.



Andriy Miranskyy (andriy@ca.ibm.com), DB2 Quality Assurance Developer, IBM

Photo of author Andriy MiranskyyDr. Miranskyy is a Quality Assurance developer in the IBM Information Management division at the IBM Toronto Laboratory. His work and research interests are in the area of mitigating risk in software engineering, focusing on software quality assurance, program comprehension, software requirements, software architecture, and project risk management. Andriy received his Ph.D. in Applied Mathematics at the University of Western Ontario. He has 15 years of software engineering experience in information management and pharmaceutical industries. Andriy has numerous publications and patents in the field of software engineering.



01 December 2011

Also available in Chinese

Introduction

The replay of production workloads in a test environment helps DBAs to identify potential robustness and performance issues related to changes in the DB2 environment. Examples of these changes are:

  • Migration or upgrade of the DB2 engine
  • Refresh of tables and indexes statistics
  • Installation of new security plugins
  • Modification of an application server

The test system is usually not identical to the production system. For example, it may have a smaller amount of memory. In order to realistically simulate a workload from the production system on the test system you need to scale the number of users and amount of instance memory, as well as the values of memory-related parameters.

The variables that will be used to formalize scaling are defined in the next section. The article then discusses the scaling of the number of users and instance memory size. Finally, it describes the scaling of the remaining memory-related parameters and automation sample script.

Dictionary

The following seven variables are defined and will be used to formalize the scaling process in the following sections.

  • Ip – The size of the INSTANCE_MEMORY parameter on a production system
  • It – The size of the INSTANCE_MEMORY parameter on a test system
  • Up – The number of users on the production system
  • Ut – The number of users on the test system
  • N – The total number of memory-related parameters
  • Mi,p – The size of the i-th memory-related parameter on the production system
  • Mi,t – The size of the i-th memory-related parameter on the test system

User and instance memory

This section discusses relations between the number of users and instance memory on the production and test systems. In order to ensure consistent behavior, the amount of memory allocated to a given user should be the same on the production and test systems.

If the It is fixed, then the maximum number of users on the test system is given by Ut = It × Up ÷ Ip.

Consider the following example. Suppose that Ip = 100Mb, Up = 10 users, and It = 70Mb. The maximum number of users on the test system is then equal to Ut = 70 × 10 ÷ 100 = 7 users.

If you want to mimic a specific number of users on the test system, then you can calculate the test system’s instance memory size that is needed to achieve this task, using It = Ut × Ip ÷ Up.

For example, if you want to simulate five users on the test system (Ut = 5 users), and Ip = 100Mb and Up=10 users then It = 5 × 100 ÷ 10 = 50Mb.

Once the number of users and instance memory size are set, you can determine the size of the remaining memory configuration parameters.

Remaining memory configuration parameters

The remaining memory configuration parameters can be determined using one of the following approaches.

  • Constrained approach – This preserves the same ratios of all memory-related configuration parameters’ values on the production and test systems. This approach can be used to mimic a production system with fixed memory-related parameter values, or a production system with uniform workload.
  • Relaxed approach – This preserves the same ratios of some memory-related configuration parameters’ values on the production and test systems. This approach can be used to test a production system with memory-related parameters managed by the DB2 engine.

Additional information on memory management

If a parameter value is set to AUTOMATIC, DB2 will increase the value of the parameter if necessary.

STMM automatically sets optimal values (the values can increase or decrease) for most memory configuration parameters based on the current workload.

A constrained approach is ideal if all the memory configuration parameters on the production system are fixed. Preservation of the memory parameters' values ratios will yield a realistic test system. However, if some of the parameters are set to AUTOMATIC and/or managed by Self-Tuning Memory Management (STMM), then the values of the parameters may fluctuate as workload changes. This would imply that fixing the values will not yield a realistic picture. An exception to this rule exists. If your workload is uniform, then the parameters' values are stable. This means that even if it is set to AUTOMATIC, the values will likely not change. In this case the constrained approach will yield good results, even if some parameters are managed automatically.

If some of the memory parameters are managed automatically and are volatile, the relaxed approach may provide a more realistic solution. However, since the size of memory instance on the test and production systems are different, the behavior of automatic memory managers may differ on the systems, hence not always leading to perfect scaling results.

A compromise technique, mixing constrained and relaxed approaches, lies in capturing the sets of parameters’ values under different loads on your production system and then running similar scaled loads on your test system with scaled parameters’ values for each set of parameters.

The rest of the section is structured as follows. First, additional details are provided on implementation of the constrained and relaxed approaches. Then the automation sample script section describes a sample Perl script automating implementation of these approaches.

Constrained approach implementation

In order to preserve the same ratios of all memory parameters on the test system you should disable STMM and set AUTOMATIC values to fixed values, applying the same ratios for memory parameters on the test system as on the production system. The process can be summarized using the following algorithm.

Disable STMM on the test system by executing 
   "UPDATE DB CFG USING self_tuning_mem OFF"
For i = 1 .. N
   Set Mi,t = It × Mi,p ÷ Ip
   Execute "UPDATE [DB|DBM] CFG USING i-th_config_keyword Mi,t"

Relaxed approach implementation

The relaxed approach is similar in nature to the constrained approach. However, you will allow the test system to manage memory parameters that were managed automatically on the production system. You need to enable STMM and initialize memory parameters with scaled values. If the values were managed automatically on the production system, then let them be managed automatically on the test system. Otherwise, you should manage them manually.

The process can be summarized as follows.

Enable STMM on the test system by executing
   "UPDATE DB CFG USING self_tuning_mem ON"
For i = 1 .. N
   Set Mi,t = It × Mi,p ÷ Ip
   If the i-th parameter was set to AUTOMATIC on the production machine 
   Then Execute "UPDATE [DB|DBM] CFG USING i-th_config_keyword Mi,t AUTOMATIC"
   Else Execute "UPDATE [DB|DBM] CFG USING i-th_config_keyword Mi,t"

Automation sample script

A sample Perl script called memscaling.pm is included with this article that automates the constrained and relaxed approaches algorithms. The script calculates the scaling factor and outputs "update [DB|DBM] cfg" statements for the test system to a text file.

Once the data is captured, execute memscaling.pm on any computer, providing the following options.

  • --approach_type or -a <c|r>
    • The type of memory scaling approach: 'c', constrained, or 'r', relaxed.
  • --db_cfg or -db <file containing db config>
    • The name of the file containing database configuration.
  • --dbm_cfg or -dbm <file containing dbm config>
    • The name of the file containing database manager configuration.
  • --output or -o <output file>
    • The name of the output configuration file.
  • --param or -p <file containing parameters of interest>
    • The name of the file containing memory-related configuration parameters of interest being scaled.
  • --test_inst_mem_sz or -s <test instance memory size>
    • The size of the memory available for the DB2 instance on the test system in 4K pages.
  • --help or -h
    • Provides additional documentation.

In order to use the script, you need to store the production system's database and database manager configurations in text files, for example, you could use the following.

$db2 GET DB CFG > db.cfg
$db2 GET DBM CFG > dbm.cfg

The file containing memory-related configuration parameters should have the following format: one parameter (namely a keyword enclosed by parenthesis in the output of "get DB | DBM cfg") per line in no particular order, as shown in the following example.

instance_memory
db_memory
mon_heap_sz

Conclusion

This article has described scaling of the number of users, DB2 instance memory, and memory-related configuration parameters on a test DB2 system, based on configuration of a production DB2 system. This information should help you to create a realistic test system that is scaled from production, improving analysis of changes in the test environment.


Download

DescriptionNameSize
Sample Perl script automating the algorithmsmemscaling.zip4KB

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=777197
ArticleTitle=Scaling of DB2 for Linux, UNIX, and Windows memory-related configuration parameters on a test system
publish-date=12012011