Database Advisor Findings

The OpenEdge Database Advisor is intended to give you a head start on identifying the most common database issues. The suggestions below are based on best practices and years of experience tuning numerous OpenEdge deployments of various sizes. There is also an assumption that you have at least a basic knowledge of database administration but does not assume you are a tuning expert.

This tool is not intended as a replacement for qualifed help or as a real time performance monitoring tool, but it will help you resolve some of the most common issues before you start digging into the details required to fully tune your system. After you have resolved some of the common issues I highly recommend that you download and install ProTop. It will provide much more detailed information as well as provide the real time monitoring required to analyze your system during peak times.

Severity Category Issue Description
HIGH After Imaging After Imaging is not enabled After Imaging helps prevent data loss between database backups and should be running for all production databases.
HIGH Before Image BI Writer is not running The BI Writer helps offload work from other processes for writes to the BI files.
Warning Alternate Buffer Pool Alternate buffer pool not enabled The alternate buffer allows you to keep frequently read tables and indexes in memory instead of reading them from disk.
HIGH Database Database Writers not running Database Writers (APWs) help improve performance by offloading database writes from other sessions.
HIGH Database The spin parameter is not set Spin helps reduce wait times by letting processes retry for database resources instead of sleeping.
Warning Database Database Uptime: 3 Hours 40 Minutes Longer samples will generally provide better results from this tool. A minimum of 5 business days is recommended.
Warning Database Database Block Size is 4K An 8K block size is highly recommended for most databases.
HIGH Database 4 Tables with storage issues Some of your tables are either in Type I areas and/or do not have proper record per block settings.
HIGH Database 14 Indexes with storage issues Some of your indexes are in Type I areas.
Information DB Activity Table Reads Detailed information on which tables are the most active for reads.
Information DB Activity Index Reads Detailed information on which indexes are the most active for reads.
Information DB Activity Table Activity Detailed information on which tables are the most active.
Information DB Activity Index Activity This section provides detailed information on which indexes are the most active for your database.

After ImagingBack to Summary

After imaging helps protect you from data loss and corruption by: If you have a hardware or software issue that causes data corruption or the complete loss of one or more of your database files you will have to restore from the last good backup. All work done since the last backup will have to be reentered and verified.

With AI enabled these are the steps you would follow: You can also use AI to replicate databases for disaster recovery and/or create a read only reporting copy of your database. There is very minimal overhead to implement AI. You can read more about AI in the OpenEdge documentation (Database Administration - Protecting Your Data).

Recommendations:

Before Image WriterBack to Summary

The Before Image Writer (BIW) is responsible for writing filled BI buffers to disk so that other processes don't have to. If the BIW isn't running then your other processes are spending time writing those buffers.

You can start the BIW through OpenEdge Explorer or by modifying your startup scripts to include probiw YourDB.

You will also need to make sure you add the bibufs parameter -bibufs 200 to your database startup scripts or parameter file. This will be a decent starting point for most databases but might need to be tuned on highly active databases.

Alternate Buffer PoolBack to Summary

Buffer pools cache recently used database blocks (not records) for the purpose of increasing performance by allowing future reads to come from memory instead of reading them from disk. Once the buffer reaches the allocated size the database will decide to evict the least frequently used blocks from memory to make space for new blocks that are read.

OpenEdge supports two buffer pools Primary (-B) and Alternate (-B2).By default all objects belong to the Primary buffer pool but you can assign objects to the Alternate buffer pool either through the Data Dictionary at a table/index level (Type 2 areas only) or by using proutil -C enableB2 on a storage area level.

Because you rarely have enough memory to store the entire database in memory the Alternate pool is useful to store smaller but frequently read tables/indexes. Make sure to size the B2 parameter large enough to make sure that blocks are never evicted from memory.The Table Reads and Index Reads sections will provide you with a starting point to decide which objects should be placed in the Alternate pool.

Database WritersBack to Summary

Database Writers (APWs) are responsible for writing changed DB buffers to disk so that other processes don't have to. Without APWs running your other processes are spending time writing those buffers.

You can start one or more APWs through OpenEdge Explorer or by modifying your startup scripts to include proapw YourDB for each APW you want to start.

A good starting point is betwen 2 and 4 APWs for most OpenEdge installs. Highly active may require additional APWs and/or an increase in the BI cluster size.

Database Spin RetriesBack to Summary

The spin parameter allows database clients to retry accessing a database resource (Latches) instead of going to sleep when they are blocked.You can enable spin by adding -spin 1000 to your database startup scripts or parameter file.

1000 is a safe starting point that allows you to benefit from the retries without wasting CPU cycles.Tuning -spin to a "perfect" value for your database is a much more involved process and usually involves a mixture of detailed latching information, knowledge of your hardware and your application.

Download ProTop for much more detailed information on how to tune the spin setting for your environment.

Database UptimeBack to Summary

This tool makes a number of assumptions based on the statistics gathered in the OpenEdge VSTs (Virtual System Tables). If the sample time is too small you are likely to miss certain issues unless your application runs a truly repeatable workload every day/hour. This is not the case for most applications since there are always peak times or jobs that only run on certain days.

If you are shutting down your database on a regular basis for backups you should seriously consider using probkup online instead of offline backups. Not for the sake of this tool but because you are starting from scratch every time you restart the database. Losing the benefits of the buffer pools and having to read more records from disk instead of memory.

Database Block SizeBack to Summary

Larger database block sizes along with proper record per block settings will allow you to store your data more efficiently as well as improve performance. For Unix/Linux based databases an 8K block size is widely accepted as the best option. For Windows based databases there are differing opinions on whether 4K or 8K is the optimal block size. I personally prefer 8K on all platforms. You may not see as large an improvement from 4K to 8K as you do on Unix based systems but the improvements do exist.

Note that this requires a complete dump and reload of the database since OpenEdge does not allow you to change the block size of a database. While this suggestion is still valid and can make substantial performance improvements you might consider fixing the other issues identified first.

Table Storage Area IssuesBack to Summary

Type 1 Areas Explained

Type 1 storage areas are the first generation of database storage implemented in the early 1980s. No major improvements have been made for Type 1 areas since the release of Type 2 areas in 2003, arguably even before then. Using Type 1 areas is highly discouraged for any area other than the Schema Area (required).

The biggest day to day issue with Type 1 areas is the fact that a single database block can contain data from multiple tables. This causes extra database reads to occur and reduces the effectiveness of the database buffer pools, especially for smaller tables that are heavily scattered. Instead of reading and caching a small number of database blocks you can end up reading a substantial number of extra database blocks.

Initially you should focus on smaller tables that are highly active. That is where you are most likely to see improvements. Larger tables or seldom used tables will usually not show as much improvement but they should still be on your list to move.

Type 2 Areas Explained

Type 2 areas should be the only storage area type defined for your database. With the exception of the Schema Area which is currently required to be Type 1 by Progress. You should never store your tables or indexes in the Schema Area.

Type 2 areas will never let a single database block store records from more than one table at once. This helps to keep your data organized and enables numerous performance enhancements. When creating storage areas you should avoid creating areas for logical groups of data (Orders, Customers, etc.) and instead create storage areas based on the RPB settings (1 for 32 RPB, 1 for 64 RPB, etc). Tables 1GB in size or larger might deserve their own storage area, tables 10GB in size or larger certainly do. Indexes should not be stored in the same storage area as tables. For clarity you should have a corresponding index area for each table area (Indexes for 32 RPB, Indexes for 64 RPB, etc.). This is not required but will make your administration life easier.

Records Per Block (RPB) Explained

The RPB is set at a storage area level and controls how many records the database will attempt to store in a single database block. It is important to set RPB properly for the proper balance of space utilization and performance. Setting RPB too low means space is wasted and more database blocks have to be read to retrieve your records. Setting RPB too high is less of a danger but still has drawbacks. The calculations for RPB below are widely used and should result in the best settings for each table.

Moving Tables Between Areas

Tables with fewer than 100,000 records can usually be moved using the proutil tablemove command. Larger tables will usually require a dump and reload but there is some wiggle room based on the speed of your system and how much downtime you can allocate.

If your application stores RECIDs in the database you will have to develop your own methods to move the tables because this will cause all of the RECIDs and ROWIDs to change. Regardless of the approach it is always safer to test these table moves on a development or test environment before you attempt to modify production.

Table Name Area Name Area Type Record Count Record Fragments Mean Record Size Area RPB Suggested RPB
(Current DB Block Size)
Suggested RPB
(8K Blocks)
Estimated Blocks Optimal Blocks
for Suggested RPB
Optimal Blocks
for 8K DB
Total Activity % of DB Activity
Item Info Area 1 55 55 92 32 64128 2 1 1 10,186,281 40.9
Invoice Info Area 1 147 147 38 32128256 5 2 1 460,487 1.8
Order Customer/Order Area 2 207 207 56 32 64128 7 4 2 0 -
Order-Line Customer/Order Area 2 873 873 35 32128256 28 7 4 0 -

Index Storage Area IssuesBack to Summary

While slightly less important than moving tables from Type 1 to Type 2 areas, all indexes should be stored in Type 2 areas. Do not store indexes in the same storage area as tables. The records per block (RPB) should be set to 1 for all index only areas. If you decide to mix tables and indexes in the same area (not recommended) use the suggested RPB setting for the tables. Using a RPB of 1 for tables will result in extremely excessive database growth and extremely poor performance.
Table Name Index Name Area Name Area Type Total Activity
Salesrep Sales-Rep Info Area 1 13,652,938
Item Item-Num Info Area 1 10,145,862
Invoice Invoice-Num Info Area 1 418,100
State State Info Area 1 172,076
Ref-Call Call-Num Info Area 1 37,618
Local-Default default Schema Area 1 29,557
Invoice Cust-Num Info Area 1 0
Invoice Invoice-Date Info Area 1 0
Item Item-Name Info Area 1 0
Item Cat-Description Info Area 1 0
Invoice Order-Num Info Area 1 0
Ref-Call Cust-Num Info Area 1 0
Ref-Call Sibling Info Area 1 0
Ref-Call Txt Info Area 1 0

Table Read ActivityBack to Summary

Most databases are heavily skewed towards read activity rather than changing data. Not surprisingly most performance issues are related to how quickly you can read data from the database. The metrics below combined with your knowledge of the application will give you an idea of which tables need maintenance and/or application changes to improve performance.

Most Important Metrics
Table Name % of DB Reads Total Reads Disk Reads Cached Reads
Per Disk Read
Reads Per Second Reads Per Minute % of Table
Read Per Minute
Record Count Area Name Area Type % Reads of
Total Activity
Current
Buffer Pool
Recommended
Buffer Pool
Salesrep 55.0 13,694,640 Not Supported Not Supported 1,033 62,004 688,934.5 9 Info Area 1 100 PrimarySecondary
Item 40.9 10,186,281 Not Supported Not Supported 769 46,120 83,853.8 55 Info Area 1 100 PrimarySecondary
Invoice 1.8 460,487 Not Supported Not Supported 35 2,085 1,418.3 147 Info Area 1 100 PrimaryPrimary
State 0.7 180,545 Not Supported Not Supported 14 817 1,602.8 51 Info Area 1 100 PrimaryPrimary
Ref-Call 0.3 71,558 Not Supported Not Supported 5 324 2,492.2 13 Info Area 1 100 PrimaryPrimary
Local-Default 0.3 62,807 Not Supported Not Supported 5 284 2,843.7 10 Info Area 1 100 PrimaryPrimary

Index Read ActivityBack to Summary

Most databases are heavily skewed towards read activity rather than changing data. Not surprisingly most performance issues are related to how quickly you can read data from the database. The metrics below combined with your knowledge of the application will give you an idea of which indexes need maintenance and/or application changes to improve performance.

Most Important Metrics
Table Name Index Name % of DB Reads Total Reads Disk Reads Cached Reads
Per Disk Read
Reads Per Second Reads Per Minute Area Name Area Type % Reads of
Total Activity
Current
Buffer Pool
Recommended
Buffer Pool
Salesrep Sales-Rep 55.5 13,652,938 Not Supported Not Supported 1,030 61,815 Info Area 1 100 PrimarySecondary
Item Item-Num 41.2 10,145,862 Not Supported Not Supported 766 45,937 Info Area 1 100 PrimarySecondary
Invoice Invoice-Num 1.7 418,100 Not Supported Not Supported 32 1,893 Info Area 1 100 PrimaryPrimary
State State 0.7 172,076 Not Supported Not Supported 13 779 Info Area 1 100 PrimaryPrimary
Ref-Call Call-Num 0.2 37,618 Not Supported Not Supported 3 170 Info Area 1 100 PrimaryPrimary
Local-Default default 0.1 29,557 Not Supported Not Supported 2 134 Schema Area 1 100 PrimaryPrimary

Table ActivityBack to Summary

Table Name % of DB Activity Total Activity % of DB Reads Total Reads % of DB Creates Total Creates % of DB Updates Total Updates % of DB Deletes Total Deletes
Salesrep 55.0 13,694,640 55.0 13,694,640 0.0 0 0.0 0 0.0 0
Item 40.9 10,186,281 40.9 10,186,281 0.0 0 0.0 0 0.0 0
Invoice 1.8 460,487 1.8 460,487 0.0 0 0.0 0 0.0 0
State 0.7 180,545 0.7 180,545 0.0 0 0.0 0 0.0 0
Ref-Call 0.3 71,558 0.3 71,558 0.0 0 0.0 0 0.0 0
Local-Default 0.3 62,807 0.3 62,807 0.0 0 0.0 0 0.0 0

Index ActivityBack to Summary

Table Name Index Name % of DB Activity Total Activity % of DB Reads Total Reads % of DB Creates Total Creates % of DB Deletes Total Deletes
Salesrep Sales-Rep 55.5 13,652,938 55.5 13,652,938 0.0 0 0.0 0
Item Item-Num 41.2 10,145,862 41.2 10,145,862 0.0 0 0.0 0
Invoice Invoice-Num 1.7 418,100 1.7 418,100 0.0 0 0.0 0
State State 0.7 172,076 0.7 172,076 0.0 0 0.0 0
Ref-Call Call-Num 0.2 37,618 0.2 37,618 0.0 0 0.0 0
Local-Default default 0.1 29,557 0.1 29,557 0.0 0 0.0 0