═══ 1. PURPOSE ═══ Provide a single point of reference for performance inspections. ABSTRACT GOALS ═══ 1.1. ABSTRACT ═══ Most customers are happy with the performance of IBM's database engine once it has been optimally tuned. Getting to the optimal state, though, can be a difficult prospect at best. Difficulties that contribute to this problem include the complexity of the operating system, the wide range of performance improvement techniques, and the lack of performance tools and documentation. This document is intended to ease this pain by consolidating many performance tips and techniques into a central forum. ═══ 1.2. GOALS ═══ 1. Reduce the time required to gather resources for performance tuning. 2. Provide general information detailing the relative costs and benefits of specific techniques that may be used to improve performance for the IBM OS/2 EE Database Manager. 3. Provide a comprehensive guide of performance improvement techniques for the IBM OS/2 EE Database Manager. 4. Provide general rules of thumb that apply to these techniques. ═══ 2. DATABASE DESIGN ═══ INDEX STRUCTURE TABLE DESIGN SINGLE DATABASE ═══ 2.1. INDEX STRUCTURE ═══ The impact of indexes cannot be over emphasized. In read-only situations, it is usually worth while to create an index on fields which will be used for joins or will otherwise appear in 'WHERE' or 'SORT' clauses of an SQL query. Indexes should always exist on primary keys of larger tables. Creating appropriate indexes can reduce response times by 90% or more in certain situations. In other situations, table access may not be required at all if all of the fields needed by a query exist in the index. There are situations where creating indexes can degrade system performance. Updating, deleting from, or adding to a table with indexes takes longer than updating a table without indexes, once the record is found. This is true because the indexes must also be updated. If a table is write-only or has more writes than reads, then this may be a serious consideration. Most tables, however, do not fit this description. Index performance has an interesting effect on imports. It is much faster to import a table, and then create the index than to create the index and then import the table. This is true because of the added overhead of updating the index. The 'EXPLAIN' tool can be used to see if an index is used in a particular query. Through intelligent use of this tool and good table design, significant performance improvements may be realized. Rules: 1) Use appropriate indexes in columns referenced in 'WHERE', 'ORDER BY' clauses to speed reads. 2) Use 'EXPLAIN' to see how indexes are used (static SQL only). 3) Remove seldomly used indexes to improve updates, deletes, and inserts. 4) Import tables before creating indexes. ═══ 2.2. TABLE DESIGN ═══ This area must be considered at application design time. The idea here is to minimize joins where possible. Though separating tables may be a good idea to reduce redundancy, sometimes it is worth while to merge these tables into one. If it is known that two tables will be joined frequently and the two tables could logically be thought of as a single table, then merging the tables could save many costly joins in the future. For example, consider two tables, person and address. It may make sense to separate these tables to reduce redundancy if more than one persons may live at the same address. This would avoid the same address from being entered several times. However, if most reports required information from person and address (for example, home city and state, and person's social security and employee numbers), then it may be better to merge these tables into one. Rules: 1) Normalize tables to decrease redundancy, and then 2) Merge large tables that will be frequently joined. ═══ 2.3. SINGLE DATABASES ═══ It is important to carefully limit the number of databases on a server. Significant overhead is involved whenever multiple databases are opened on a single server. A far better strategy is to place all of the tables from multiple databases within a single database. Rules: 1) When possible, have only a single database on a server. ═══ 3. APPLICATION DESIGN ═══ LOCKING COMMITS APPLICATION REMOTE INTERFACE (ARI) REMOTE DATA SERVICES ALTERNATIVES STATIC vs DYNAMIC SQL ═══ 3.1. LOCKING ═══ The idea here is to choose the best granularity possible for the sake of concurrency. A reading process and a writing process may not hold a lock on the same object. Processes may hold locks to read the same object. No two processes may hold locks to write the same object. Obviously, if some process has a table write-locked, then no one can touch the table until the process commits or rolls back. As a general rule, then, in a multi-user system, it is better to lock records than tables. If the locked list parameter is not set high enough, then locks will be upgraded to table locks, and significantly impact concurrency. In a situation where concurrent access will not be a problem, then it is better to take out a single table lock than row level locks, because there is less overhead. Rules: 1) In a multi user system, row level locking yields better concurrency, thus better overall performance. ═══ 3.2. COMMITS ═══ Managing commits involves two conflicting goals. Commits do take a certain amount of overhead, in the form of log maintenance and other things. Commits do, however, free resources that may be used by other processes, most notably locks. It is important to understand that locks are accumulated on the system tables even in read only SQL (selects) and even in the uncommitted read level of isolation, so even in these situations it is important to commit, to free up locks on system tables. Rules: 1) Reduce database operations between commits (ie increase commits) to increase concurrency. 2) Never commit in the middle of a logical unit of work. 3) Reduce the number of commits to improve the performance within a single process when the concurrency impact may be tolerated. ═══ 3.3. APPLICATION REMOTE INTERFACE (ARI) ═══ The Application Remote Interface is one of the most important, yet least used, of all performance techniques. It is used to execute a block of code on the server. When several different queries may be combined into a single procedure to be executed on the server, system performance is improved in several ways. First, communication costs are reduced. SQLCA's and SQLDA's must only be moved to and from the server once. Second, the faster microprocessor on the server may be utilized. Instructions that would be normally executed on the requester are moved to the server, which is normally a vastly superior machine. Finally, the intermediate control blocks required by Remote Data Services for each SQL call do not need to be packed for every call. This leads to further savings of the requester CPU, and usually offsets the additional responsibility of the server. To use the ARI, precompile, compile, and link the remote procedure to be called into a DLL. Place the DLL on the server, and use the database manager API to call the remote procedure. Data may be passed to and from the remote procedure using SQLDA's. Rules: 1) Use the ARI when several database calls may be packed into a single function on the server. 2) Use the ARI to take advantage of an underused server or to offload an overused requester. ═══ 3.4. REMOTE DATA SERVICES ALTERNATIVES ═══ For some platforms, Remote Data Services can not handle enough concurrent connections to perform the required service. This is true because each process connecting to the database uses an additional 120KB-150KB of memory on the server. Eventually, memory runs out, and performance degenerates drastically. There are several alternatives. Consider a localized solution. This may seem obvious, but in many situations, a local solution will suffice. Use named pipes to pass requests to a service process on the server. This process can then talk to the database through a single connection on the server. This solution has been used quite effectively by many customers. Implement a scheduler on the server. In this situation, there are several service processes on the server. Each process is scheduled to be serviced by one of the available server. Rules: 1) Use Remote Data Services when the platform will work. 2) Consider the above alternatives when it will not. ═══ 3.5. STATIC vs DYNAMIC SQL ═══ The database manager supports two different types of SQL: static and dynamic. Static SQL is bound before the program is executed, either at precompile time, or bind time. With static SQL, the statements that are to be run must be known in advance. Dynamic SQL is bound at run time. It must be bound each time the cursor for the statement is opened. Obviously, it is better to pay the bind penalty when the ".exe" file is created instead of when the program is executed. Therefore, when it is known which SQL statements will be run in advance, it is better to use static SQL. An exception is that if an SQL statement contains a statement like "LIKE :variable", then the optimizer does not have enough information to decide if an index is usable. If the like clause looks like "ABC%", then an index is in fact usable, and would be used for dynamic SQL. Rules: 1) Use STATIC SQL when SQL statements are known in advance. 2) For statements in the form "LIKE :var", use dynamic SQL. ═══ 4. PS/2 HARDWARE CONFIGURATION ═══ SPLITTING THE LOG AND DATABASE MEMORY PROCESSING POWER ═══ 4.1. SPLITTING THE LOG AND DATABASE ═══ Some performance improvements may be made based on the disk configuration. Better performance can be achieved by placing the database and the database logs on different physical hard disks. This is true because some degree of concurrency can be realized by accessing both drives at the same time. SCSI devices can make the improvements even more pronounced. If only one physical drive is available, then it is better to place the logs and the database on contiguous partitions or the same partition. Also, the partition(s) should be as close as possible to the center of the drive. For example, if a hard disk were partitioned into drives C,D,E, and F, an acceptable configuration would be to place the database on drive D and the log on drive E. This will minimize the lateral movement of the disk head. Rules: 1) Place the database and the database log on different physical hard disk drives when possible to maximize concurrency. 2) If 1) is not possible, then place the database and log on adjacent partitions, or the same partition. 3) Place the database and log on partitions as close to the center of the disk as possible. ═══ 4.2. MEMORY ═══ If there is not enough memory on the server to adequately handle the number of workstations, then performance will degenerate drastically as activity and the number of database connections increases.. Each requester will use an additional 120-150KB of memory. Rules: 1) Make sure that the server has adequate memory resources to handle the load expected of it. ═══ 4.3. PROCESSING POWER ═══ Contrary to intuition, the database manager is a CPU intensive application. Therefore, improvements to the CPU will usually have positive effects on transaction through put. There are exceptions, such as abnormally low concurrency, or high update activity which may reduce the impact of a different CPU on any given database server. Rules: 1) Make sure that the server has adequate processing resources to handle the load expected of it. ═══ 5. PERFORMANCE RELATED FEATURES ═══ RECORD BLOCKING ISOLATION LEVELS SQLLOO REORG/RUNSTATS ═══ 5.1. RECORD BLOCKING ═══ Record blocking can improve the performance of read-only queries which read multiple database rows. For such a query, when the requester does a single fetch, a block of data is moved to the requester. The requester then reads from its private block instead of requesting additional rows from the server. Record blocking is transparent to the user application. Record blocking is a bind time parameter. To ensure that record blocking is used only where it can be effective, use the '/K=UNAMBIG' option when binding, and specify cursors which will not be updated, deleted, or inserted as 'FOR FETCH ONLY'. This series of steps will ensure record blocking for all fetch transactions. The default is 'UNAMBIG' which uses record blocking for all cursors which are fetch-only. Record blocking is a way to significantly improve performance without doing much work. Gains will be most pronounced when many database rows are expected to be returned from frequently used queries. To ensure that record blocking will be used for dynamic SQL, then the record blocking parameter should be set to 'ALL' at bind time. Rules: 1) Use record blocking whenever possible for FETCH cursors. 2) Set the record blocking parameter to 'ALL' for dynamic SQL. ═══ 5.2. ISOLATION LEVELS ═══ Isolation levels, from strongest to weakest, are Repeatable Read, Cursor Stability, and Uncommitted Read. Isolation levels, from fastest to slowest, are Uncommitted Read, Cursor Stability, and Repeatable Read. If the isolation levels required are not clearly understood, then it is best to err on the side of caution and use the strongest isolation level. Otherwise, it is best to use the weakest (and thus fastest), isolation level that can be tolerated. It is important to note that an uncommitted read could give me a value that was never committed, and thus never existed for the purposes of the database! In general, the Repeatable Read will never yield inconsistent results. It implements an industry standard locking scheme known as two phased locking. Cursor Stability is acceptable unless there are dependencies between several rows of the same table that must be preserved. Uncommitted Read is only acceptable in situations which do not require consistent data, or in situations in which it is known that the data being examined will not change. Rules: 1) If isolation levels are not clearly understood, use the Repeatable Read isolation level. 2) Use the weakest, and thus fastest, isolation level that may be tolerated by the application. ═══ 5.3. SQLLOO ═══ SQLLOO (Lan Only Option) is a faster communication protocol than APPC. However, SQL/LOO is not always a viable alternative. LOO is the default communication protocol for RDS. APPN may be as fast as SQLLOO for some applications. APPN is available separately from IBM as an OS/2 EE extension. Rules: 1) When possible, use SQL/LOO as the communication protocol. ═══ 5.4. REORG/RUNSTATS ═══ After significant insert, delete, and update activity, a database table may become significantly fragmented, and the statistics used by the optimizer outdated, forcing bad optimization strategies and overall performance degradation. Running statistics can update the optimizer statistics for a table. Reorganizing the table resolves the fragmentation problems. It also puts the database records in the order of a specified index, which significantly improves performance when table scans are needed. Performance benefits of a 'Reorganize' will not be realized unless statistics are run afterwards. Running statistics, however, does not mandate a reorganization. Similarly, after a RUNSTATS, a rebind is necessary. Rules: 1) When the system will be quiesced for a period of time, such as overnight, reorganize the tables and run statistics. 2) Reorganize and run statistics after a significant number of deletes, which cause fragmentation. 3) Run statistics after the size of a table significantly changes. 4) Runstats and rebind dependant access plans after a reorg. ═══ 6. METHODOLOGY ═══ PERFORMANCE MANAGEMENT TOOLS SIMULATION/PROTOTYPING BENCHMARK AND TUNING METHODOLOGY ═══ 6.1. PERFORMANCE MANAGEMENT TOOLS ═══ In order to be able to make the best performing system possible, it is important to have the best performance data possible. The best way to gather this data is from trace points placed within an application. The trace points should record a unique identifier, the time that the trace point was encountered, and any other pertinent information. In order to get the most information without impacting system performance, this information should be written to shared memory, which could be dumped on demand. The dump can then be analyzed and recorded. It is important to save data from old runs, so that an accurate picture of an application's performance history may be assimilated and the performance characteristics of all components of the current system assessed. Rules: 1) Build performance gathering tools into the application. 2) Maintain an accurate and complete performance history. ═══ 6.2. SIMULATION/PROTOTYPING ═══ One of the most common mistakes made in any field is assuming. This is an especially dangerous practice with OS/2 EE. Because there are so many tightly coupled variables, it is almost impossible to predict what the overall system performance will be. For this reason, it is important to prototype a working benchmark system as quickly as possible. A good benchmark will represent the real world as closely as possible in as many areas as possible, including the number of users, size and composition of database requests, the size of the database, and the LAN topology and system configurations. The prototype does not have to LOOK like the final product, but merely to ACT like the final product. For this reason, many people use a script of some type. Care should be taken, though, to consider the amount of conflict that is to be expected in the real world. For example, a script containing updates to the same record on all machines would generate locking conflict that would not be expected in the real world. Similarly, if none of the data sets overlapped, the amount of conflict could be artificially low. Many people use some form of random number stream which is recorded in a file, with a different stream on each machine. This has the desired effect of being reproducible, and represents many real world situations closely. Rules: 1) Prototype early. 2) Prototype often. 3) Prototype late. 4) Represent the real world as closely as is practical. ═══ 6.3. BENCHMARK AND TUNING METHODOLOGY ═══ When tuning the database manager for performance, it is important to tightly control the entire process. There are several things to keep in mind. First, several performance runs must be made. The initial run should be discarded, as DLL's must be swapped in, and the DBM initialized. Afterwards, from two to five runs should be made under identical conditions and the average of all runs taken. Second, only a single parameter should be changed between performance runs. This is the only way that the results of each benchmark can be accurately assessed. Finally, the parameters for which the largest gain is expected should be changed first. In this way, the system may be tuned until some goal is reached, and the tuning process completed with the confidence that the most significant parameters have been refined. Rules: 1) Control the environment. 2) Discard the first run. 3) Average more than one run. 4) Tune one parameter at a time, starting with 'heavy hitters'. 5) Set realistic goals, and stop when the goals are realized. ═══ 7. OS/2 EE CONFIGURATION ═══ TURN TRACE OFF FILE SYSTEMS - HPFS vs FAT QUERY MANAGER ROW POOL SUGGESTED DATABASE MANAGER CONFIG PARAMETERS SUGGESTIONS FOR LOG CONFIGURATIONS SUGGESTED DATABASE CONFIG PARAMETERS ═══ 7.1. TURN TRACE OFF ═══ The OS/2 trace can significantly slow the system, sometimes by a factor of 2 or 3. If performance degenerates unexpectedly, first make sure that the OS/2 trace has not been turned on inadvertently. Rules: 1) Make sure that the OS/2 Trace is not on. ═══ 7.2. FILE SYSTEMS - HPFS vs FAT ═══ The OS/2 database manager cannot take full advantage of all features of the High Performance File System. This is particularly true of the cache. Since data reliability is so important to the database manager, all log records must be immediately written to the disk. This defeats the purpose of the cache, which tries to avoid disk activity by keeping frequently accessed information in memory. The database implements its own cache, called the buffer pool. Even so, the database is slightly, but not significantly, faster on HPFS. The log is faster on FAT. Rules: 1) Use HPFS for the database and FAT for the logs. ═══ 7.3. QUERY MANAGER ROW POOL ═══ Query Manager scrolling performance can be improved by increasing the size of the Query Manager row pool. Beginning with release 1.2, Query Manager began formatting an entire row of data, instead of just a screenfull of data. This made horizontal scrolling smoother, but required a larger row pool. For this reason, the default row pool is no longer adequate for many applications. Note that this impacts the scrolling performance in Query Manager only. This is a Query Manager startup parameter. Rules: 1) To improve the vertical scrolling performance in Query Manager, specify a larger row pool. ═══ 7.4. SUGGESTED DATABASE MANAGER CONFIG PARAMETERS ═══ - Shared segments: 802 Since this is a 'high water mark' (ie the system takes only what it needs, not to exceed 802), this parameter should be kept at the maximum. - Databases: (number of databases) This parameter should be set to the number of DATABASES that may be opened AT THE SAME TIME. This parameter should not be set higher than the required number. - Req I/O Block Size: 4096 The default is OK here. - Srv I/O Block Size: 4096 The default is OK here. - Remote Connections This is used to estimate the optimal communications buffer size. If the number is too small, the system will function, but with slightly lower performance. ═══ 7.5. SUGGESTIONS FOR LOG CONFIGURATIONS ═══ A small number of large logs is better than a large number of small logs. The size of the logs required will depend on the application. Some general information about logs would be appropriate to discuss here. Logs in OS/2 1.2 are circular in structure. The same log space is used over and over. When a piece of a log is no longer needed, the space is then reclaimed. In this process, secondary logs are reduced to a file of size zero. Primary logs are initialized. This reclaim process is most likely to occur when the entire log space (primary + secondary) is completely full. At this point, users will see a noticeable delay. The bigger the log space, the longer the delay. The primary logs should be large enough to handle most situations, but secondary logs should be allocated to avoid secondary log initialization delays. Operations that require a large log space are reorgs and imports of large tables. Read-only transactions are not logged. Update, delete, and insert transactions are always logged. As discussed previously, for performance, it is better to have the logs on a different physical hard disk than the database. The database runs faster when the log is placed on a FAT (non-HPFS) partition. Rules: 1) Place the database on HPFS, and the LOG on a separate physical FAT partition 2) A small number of large logs is better than a large number of small logs. ═══ 7.6. SUGGESTED DATABASE CONFIG PARAMETERS ═══ - buffer pages >= 250 (server only) This parameter is one of the most important parameters to consider when tuning database performance. If it is not large enough, performance will be significantly degraded. If the parameter is much too large, then additional system resources will be used (memory). It is more important to have the parameter large enough than small enough. - deadlock check interval = (default) This parameter does not significantly impact system performance. It can be set to the default value and increased if deadlock is a problem. - Lock List Pages = 25 This parameter will impact concurrency if it is too small. If the application is expected to hold many locks concurrently (ie long transactions with Repeatable Read level of isolation; long transactions that update, insert, or delete large numbers of records), then this parameter must be increased. Increase if concurrency problems show up. - Lock List Percent per Application = 6 This parameter should be large enough for each connected application to have access to the locks that it requires. - Max Open Files = 255 (high water mark) - Max Open Files Per Application = 25 (high water mark) - Application Heap = 5 The application will fail with a specific return code if this parameter is not set high enough. - Database Heap = 5 The application will fail as above if this parameter is not high enough. - Communications Heap = (RQRIOBLK * m + 65535) / 65535 where m is the maximum number of concurrently open cursors used by an application. - Sort List Heap = 2 This should be increased if large tables are sorted or joined. This is also an important parameter to tune. - Statement Heap = (?) This parameter has no impact on runtime performance. Only precompiler performance is affected. Rules: 1) 'Buffer Pages' and Sort Heap are the most significant. ═══ 8. APPENDIX ═══ PERFORMANCE TECHNIQUES PERFORMANCE PARAMETERS ═══ 8.1. PERFORMANCE TECHNIQUES ═══ ┌───────────────────┬──────────────┬─────────────┬────────┬──────┐ │ │ │ │ │ │ │Technique │Implementation│Expected │Cost │Heavy │ │ │ Time │Payoff │ │Hitter│ │ │ │ │ │ │ ├───────────────────┼──────────────┼─────────────┼────────┼──────┤ │Increase Memory │ Any │High │Low ($) │ Yes │ │ │ │ │ │ │ │Turn Trace Off │ Any │High (40-70%)│None │ Yes │ │ │ │ │ │ │ │Ap Remote Interface│ Early in Dev.│High (10-60%)│High │ Yes │ │ │ │ │ │ │ │Record Blocking │ Any │High (5-40%) │Low │ Yes │ │ │ │ │ │ │ │Index Restructure │ Any │High (0%-90%)│Low │ Yes │ │ │ │ │ │ │ │Split Log/2 Drives │ Any │Medium(5-30%)│Low ($) │ Yes │ │ │ │ │ │ │ │Commits │ Development │High │High │ Yes │ │ │ │ │ │ │ │Query Mgr Row Pool │ Any │High (5-60%) │Low │ │ │ │ │ │ │ │ │Reorg/Runstats │ In Production│Medium │Medium │ │ │ │ │ │ │ │ │Performance Bnchmk │ All │High │High │ │ │ │ │ │ │ │ │Comm Mgr Xmit Block│ Any │Medium │Medium │ │ │ │ │ │ │ │ │Table Restructure │ Early │Medium │High │ │ │ │ │ │ │ │ │Locking │ Development │Medium │High │ │ │ │ │ │ │ │ │SQLLOO │ Any │Low │Low │ │ │ │ │ │ │ │ │HPFS/FAT partitions│ Any │Low (2-5%) │Low │ │ │ │ │ │ │ │ │Isolation Levels │ Late in Dev. │Low │Medium │ │ │ │ │ │ │ │ │Static SQL │ Development │Low │Medium │ │ └───────────────────┴──────────────┴─────────────┴────────┴──────┘ Heavy hitters are parameters with high payoff/cost ratios. Implementation Time deals with the point in the development cycle that is most appropriate for the implementation of the technique. ═══ 8.2. PERFORMANCE PARAMETERS ═══ ┌───────────────────┬──────────────────┬────────┬───────────┬──────┐ │ │ │ │ │ │ │Parameter │Tradeoff │Expected│Suggested │Heavy │ │ │(Give / Take) │Payoff │ Value │Hitter│ │ │ │ │ │ │ ├───────────────────┼──────────────────┼────────┼───────────┼──────┤ │Shared Segments │None │Low │802 │ │ │ │ │ │ │ │ │Databases │# db's / memory │Low │# databases│ │ │ │ │ │ │ │ │Req I/O block size │size Xmit / memory│Low │default │ │ │ │ │ │ │ │ │Srv I/O block size │size Xmit / memory│Low │default │ │ │ │ │ │ │ │ │Remote Connections │speed / memory │Low │# remote cn│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │Log File Size │log KB/DASD,speed │Medium │high │ │ │ │ │ │ │ │ │Primary Logs │log KB/DASD,speed │Medium │low │ │ │ │ │ │ │ │ │Secondary Logs │init time/run time│Medium │none │ │ │ │ │ │ │ │ │Soft Check │reclaim / speed │Low │default │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │Buffer Pages │memory/speed │High │250 │ Yes │ │ │ │ │ │ │ │Deadlock Check Int │speed/check time │Low │default │ │ │ │ │ │ │ │ │Lock List Pages │memory/concurrency│Medium │25 │ Yes │ │ │ │ │ │ │ │Lock List % per App│ │Low │100/num_aps│ │ │ │ │ │ │ │ │Max Open Files │ │None │Default │ │ │ │ │ │ │ │ │Max Appl Open Files│ │None │Default │ │ │ │ │ │ │ │ │Maximum Appls │memory/# connectns│Medium │Number Cons│ │ │ │ │ │ │ │ │Application Heap │ │None │Default (*)│ │ │ │ │ │ │ │ │Communications Heap│memory/ comm heap │None │see text │ │ │ │ │ │ │ │ │Sort List Heap │memory/speed │High │6 │ Yes │ │ │ │ │ │ │ └───────────────────┴──────────────────┴────────┴───────────┴──────┘ (*): (RQRIOBLK * m + 65535) / 65535 ($): The solution will require additional hardware (GREENware). Heavy hitters are parameters with high payoff/cost ratios.