After the initial design and development of your OpenEdge database it is time to tune the database server for optimal performance. The tools for managing and monitoring your database servers are presented and tuning tips for database servers are provided.

Monitor OpenEdge database servers with Progress tools

The main tools for monitoring and managing performance include test text:

  • OpenEdge Management—A browser-based management tool to monitor database servers, files, networks, OpenEdge components, and system resources in an OpenEdge environment.
  • PROMON—An OpenEdge database administration utility to monitor and manage your database servers. PROMON cannot be run remotely, PROMON must run the same machine as the database server.

    For more information, see PROMON utility in Manage the OpenEdge Database.

  • Virtual system tables (VSTs)—A way to provide ABL and SQL applications access to the same database information that is collected with the PROMON utility. The virtual system tables, or schema tables, have no physical records until the database manager generates them at run time. You can write ABL or SQL application to retrieve information as run-time data for those table to monitor and collect data for analysis. You can archive performance data for further review and analysis. Using the data gather, you can write utilities for monitoring database performance that match your monitoring needs. With VSTs, you can perform remote monitoring; this is an advantage over monitoring with PROMON.

    For more information, see Access VST files with ABL or SQL in Manage the OpenEdge Database.

    Progress provides a complete guide for managing database server performance. For more information, see Managing database performance in Manage the OpenEdge Database.

Apply best practices for tuning database servers

These recommendations focus on things you may want to tune after your database server has been running for a while.
Note: These suggestions are dependent on your hardware and database server licensing.
Start your database server tuning with the following:
  • Review the indexes for your application. It is important to index properly. As applications evolve, developers and database administrators must periodically review the indexes. Having the appropriate indexes defined allows the database to find records with a minimal amount of disk I/O. On the downside, if you have too many indexes, then the database spends time accessing unnecessary indexes during record updates. For example, one record update might require multiple I/O operations to maintain each index where the field is used.
  • Tune your database buffer parameters. Tuning the number of available database buffers can improve your overall performance by reducing disk I/O. PROUTIL is used to set the buffer size online. Use PROMON to monitor how often the database is accessed to gain insight into the database buffer performance.

    This video demonstrates how to increase the buffer size online:

  • Stripe data extents on as many separate spindles as possible. One disk can do one data transfer at a time. Two disk can do two transfers at a time. The more disks you have, the better. Be sure to evenly balance the I/O load among the available disks. The most effective way to balance the load across multiple disks is to create a stripe set that combines all the disks into one logical disk, and spread the data evenly across them. But, if you lose one disk, then you lose all the disks, so you must combine striping with mirroring to get reliability.

    Another way to balance the I/O load is to create data extents, with each extent containing a piece of the database. For example, if you have 4 disk drives, then create 16 extents, and put 4 on each disk drive. Put extent 1 on the first disk drive, extent 2 on the second, extent 3 on the third, and so on. A drawback to this manual striping is that as the database grows the balance is disturbed when you add extents.

  • Run the before-image writer (BIW). The before-image writer's job is to write filled BI buffers to disk so the database server does not have to do it. This gives the database server more time to do useful work. With self-service clients, the server and the client are in the same process, but you still want the server to do useful work.

  • Manage before-image file growth You can use the-biscantime parameter to tune the behavior for before-image space management. In some rare cases, BI files may keep growing even when the BI space management is running. This could happen under heavy database activities with very fast checkpoints. Reducing the value of -biscantime lets the before-image manager (BIM) process detect and reuse BI clusters more frequently at a cost of minor performance impact when compared to a bigger -biscantime value. Note that, in most cases, the default value of -biscantime (10) works well without any tuning. Setting the value of -biscantime to zero (0) turns off BI space management. (If you set -biscantime to 0 online, the before-image writer stays idle.) Whenever you set -biscantime to a non-zero value, you must start the before-image manager.

    For more information, see Before-image scan time (-biscantime) and Start and stop a BIM in Manage the OpenEdge Database.

    In addition, you can truncate the before-image file on a replication source or target database. Truncating the BI file lets a DBA adjust the BI cluster or block size on the replication target to improve database performance.

    For more information, see PROUTIL TRUNCATE BI qualifier and Manage before-image file growth in Manage the OpenEdge Database.

  • If you use after-image journaling (you should, but not for performance reasons), run the after-image writer (AIW).

    The after-image writer's job is to write filled AI buffers so the server does not have to do it. This gives the server more time to do useful work. With self-service clients, the server and the client are in the same process, but you still want the server to do useful work.

  • Set the AI block size to the same value as the BI block size. Increasing the size of AI blocks lets the engine read and write more AI data at one time. This can reduce I/O rates on disks where the AI files are located. In general, the default AI block size (8 KB) is sufficient for systems with low transaction rates. However, if performance monitoring indicates that AI writes are a performance bottleneck and your platform's I/O subsystem can take advantage of larger writes, then increasing the AI block size may improve performance. A larger AI block size may also improve performance for roll-forward recovery processing.

    For more information, see Increase the AI block size in Manage the OpenEdge Database.

  • Set -spin to fifty thousand

    Finding the optimal value for spin is hard. By setting the -spin value higher, you can reduce the resource waits. With fast systems, use the number of processors times 20,000. Fifty thousand is a good place to start. If you set the -spin value higher and that value does not reduce the resource waits, then increasing it further can adversely affect the CPU utilization. To view the resource waits value:

    1. Access PROMON, and enter R&D at the main menu.
    2. Choose Other Displays.
    3. Choose Performance Indicators, to view the resource waits. Resource waits is the last item in the list.

  • Perform database dump and load.

    Depending on the database structure, it is possible that over time records will fragment within database extent files, especially if there are many disparate tables located in the same area. Run a database analysis to determine if there is record fragmentation. All large tables with a Scatter Factor greater than 2 benefit from dump and load, or they could be moved into their own data area (with the appropriate records-per-block factor).

Monitor with third-party tools for your OpenEdge database server

  • ProMonitor—An OpenEdge performance monitoring tool available through Progress Professional Services.
  • OpenTelemetry—A monitoring framework that enables you to collect and export performance metrics of your software systems. This helps you easily identify issues with availability and performance of your systems. OpenEdge provides the OpenTelemetry Agent for the OpenEdge database to capture the performance data of your OpenEdge database and then forward the data to a variety of analysis or application performance monitoring tools. As an administrator, you can configure the performance metrics that are essential to your busines and share critical information about the database performance with your stakeholders.

Learn more about OpenEdge database performance tuning

Progress offers the Progress OpenEdge Database Performance Tuning course. This course is available as instructor-led training or self-paced training on the Progress Education Community.