- IBM DB2 9.7 Advanced Administration Cookbook
- Adrian Neagu Robert Pelletier
- 938字
- 2021-08-20 15:33:20
Using Configuration Advisor
The configuration advisor will help you configure the best settings for the database mission, using this server's hardware configuration. You can then accept or cancel the proposed settings.
Getting ready
Obtain as much information as possible on the database size, future growth, number of concurrent users, and so on.
How to do it...
- Select database Configuration Advisor....
Go to the left pane of Control Center, and expand the databases node. Right-click on the NAV database, and select Configuration Advisor....
- A first screen will ask you to confirm whether this is the database you want to configure; then, click Next.
- Choose how much memory you want to allocate to this database.
You will see a slider bar and the amount of physical memory available on the server. Allow around 300-500 MB for the operating system, or ask your system administrator how much space is needed. Then, you will have to divide the remaining space for each of the active databases. Click Next.
- In our example, I have two gigabytes available for system memory, so I leave 500 MB for the operating system; that leaves me with 1,500 MB available. The
NAVdatabase is quite small, so even 175 MB would be enough. Now, click Next.
- In our example, I have two gigabytes available for system memory, so I leave 500 MB for the operating system; that leaves me with 1,500 MB available. The
- Select type of work load.
For an OLTP database, select Transactions (order entry), and click Next.
- Choose your typical transaction's characteristics.On this screen, we estimate the load on the database. We never really know how many queries we may encounter per unit of work, so it's safe to select More than 10 (long transactions). Estimate or obtain the number of transactions per minute on the database; 3000 transactions a minute should be fairly representative of a production OLTP database. Click Next.
- Choose the database's priority between performance and recovery:
For OLTP, we would recommend choosing performance. We will leave the setting on both, for this scenario. This will choose the best values to balance between transaction performance and recovery time.
- Tell the advisor if the database contains data:
If you have loaded data since the database has been created, select yes and click Next.
- Estimate the number of applications connected to the database:
Enter the number of local and remote applications and click Next.
- Select the appropriate isolation level for your database:
Click Next. I chose the default value, Cursor stability, to minimize lock duration.
- Review the proposed configuration recommendations.
You can take some time to review the proposed configuration recommendations; when you are ready to continue, click Next.
- Schedule this as a task or do it right now.
You can choose to create a task with the proposed configuration recommendations, and schedule this task for another time frame.
Or, select Run now... and click Next.
Review the actions that will apply the new settings and click Finish. You will see the progress indicator and the results of the action.
How it works...
The configuration advisor takes the current setting for the database configuration and optimizes those parameters to suit the database for OLTP processing. Commands used are: get database or get database manager (db/dbm) configuration (cfg) and update database or update database manager (db/dbm) configuration (cfg).
We suggest you copy the results into a script and run it manually through the command line, for historic and documentation purposes. You might want to track performance improvements with parameter change history.
There's more...
The configuration advisor is not only used for a newly created database; you might just as well run this utility on an existing database. For an existing database, you want to make sure table and index statistics are fairly recent. See the Collecting object statistics and the RUNSTAT utility recipe in Chapter 13,DB2 Tuning and Optimization
Since an OLTP database requires many users to frequently read small amounts of data, it is best to allow more memory for buffer pools and less for sorts.
Log archiving is used in order to enable online and/or table space backups without quiescing the database. It is also required if you wish to set up HADR, so that archived logs can be sent over to the DR site.
Recommendations:
- Smaller extents
- Less prefetch
- Small page size
- Separate indexes and data
- Multiple buffer pools
Since a DSS database requires a few users to read a large amount of data, it is best to allow more memory for sorts and less for buffer pools. Data is usually extracted from production OLTP databases, so your backup strategy may be different.
Recommendations:
We have both the advantages and the inconvenience of both configurations. Perhaps the best approach here would be to optimize the database for transaction processing during the day and DSS processing, or analytic processing, outside daytime hours.
This may require down time between settings.
Another solution would be to somehow throttle the resources to ensure optimal response for OLTP while permitting let's say 25 percent of CPU time and disk I/O activity for OLAP.
We recommend having separate resources for different missions, when possible. Have one or several machines for use in a partitioned database for DSS processing and one separate machine for OLTP processing. Avoid having one machine for multiple missions as this may result in a single point of failure.
See also
- Chapter 4—Using DB2 Table Spaces
- Chapter 5,DB2 Buffer Pools
- Chapter 13,DB2 Tuning and Optimization
- 軟件安全技術
- VMware View Security Essentials
- 深入理解Android（卷I）
- R語言數據分析從入門到精通
- C語言程序設計（第2版）
- 深入淺出Windows API程序設計：編程基礎篇
- HTML5+CSS3+JavaScript Web開發案例教程（在線實訓版）
- Python機器學習算法與實戰
- R Data Analysis Cookbook（Second Edition）
- Developing SSRS Reports for Dynamics AX
- 小程序從0到1：微信全棧工程師一本通
- CodeIgniter Web Application Blueprints
- Advanced Python Programming
- Python Social Media Analytics
- Java從入門到精通（視頻實戰版）