Create OpenEdge databases that perform well

Identify database components

When you work with a database, it is important to identify the difference between the logical database components and the physical database components. Logical database components are important because they help determine how the ABL compiler queries and returns data to your applications. The related tables and indexes you create are used by the ABL code to efficiently retrieve your data. The physical database with its storage areas, clusters, blocks, and extents allow you to organize the data on the disk for efficient retrieval. This diagram shows both logical and physical components.

Design logical database components

Application developers rely on the foundation built by the database administrator's logical and physical database design choices. Database administrators design and build tables, indexes, and sequences to support an application developer's performance requirements. The choices made in the logical design support the coding guidelines for ABL performance content. Developers cannot build efficient queries if database administrators do not provide the necessary indexes.

Apply indexing guidelines to the logical database

Create the right indexes for your application. Having the appropriate indexes defined allows the database to retrieve the right records with the minimal amount of disk I/O. On the downside, if you have too many indexes, then the database may spend time updating unnecessary indexes during record updates. For example, a one record update might need multiple I/O operations to maintain each index where the field is used. Database administrators and application developers must discuss the index requirements for overall application performance.

Design physical database components

Based on the logical database design decisions, a database administrator builds the physical database. The physical database components include:

• Storage area—The largest storage unit of a physical database that enables you to control the types, sizes, and locations of your database files. To design for performance, you can customize the number and type of storage areas, and group related information into areas that operate efficiently. For more information, see Storage areas in OpenEdge Database Essentials.
• Extent—Extents correspond to a physical file on a disk. Extents allow you to allocate space based on your application requirements. A storage area is made up of one or more extents. Sizing extents correctly in the design phase, reduces the need to dynamically increase the size of the extent on production systems later. To design for performance, you can customize the number and size of the extents. For more information, see Extents.
• Cluster—A database structure that contains a range of contiguous database blocks that belong to the same object. Each extent consists of one or more clusters. To design for performance, you can customize the number of database blocks per cluster. Clusters improve performance of OpenEdge databases by reducing record fragmentation and record scatter. For more information, see Clusters.
• Database block—The smallest storage unit of a physical database. Each cluster consists of multiple database blocks. To design for performance, you can customize the number of database blocks per cluster. Your block size should match your OS default block size for efficiency. For more information, see Blocks.

Apply the best practices for creating an optimized physical database

The top tips for creating a physical database that performs well include:

Here is a video that demonstrates designing an OpenEdge database for performance:

Apply best practices to database creation

Progress recommends that you consider these best practices when you create your database:Based on expert advice, here's a short list of impactful decisions that can make when you initially create your database.

• Place the database on a dedicated machine.

  If the only thing running on the machine is your database server, you know immediately that the database is the source of the performance issues.

• Use a database block size that matches your operating system's block size.

  Larger block sizes provide greater I/O efficiency and are a more efficient use of storage. If your operating system supports 8-KB blocks, then use 8 KB for your block size. If your operating system uses 4-KB blocks, then use 4KB for your block size. For example, on Windows, use 4-KB block size to match the Windows' default block size. You get the best performance when the database block size matches the file system's page size or is a multiple of the file systems page size.

• Determine when to use variable database extents.

  In the past, there were warnings against using variable database extents because it takes time to extend whenever database needs to write more blocks onto disk. Using fixed database extents, which use pre-allocated space, are faster because the database doesn't need to lose time extending them on disk. Additionally, variable database extents are more likely to get very fragmented over time, whereas fixed database extents may allocate more continuous disk space. With the improvements in disk speed, this is no longer an issue.

• Considerations for using RAID 5 and RAID 6 storage systems

  Progress previously cautioned against using RAID 5 and RAID 6 storage systems due to performance issues. These systems improved over time, and their reliability increased dramatically. RAID 5 and 6 are good options for read-oriented operations. RAID 5 and 6 can experience a degradation of performance for high-volume write operations if the volume of data being written exceeds the cache memory available to the RAID controller. The degree of performance loss could be significant. If you are doing online backups of the entire operating system, then these performance issues may appear.