| Prev | Advanced Operations Guide | Next |
Tuning Performance
This section provides some general tips on how to maximize performance on the initial database connection and on runtime operations. While we can offer some general guidelines, the performance of any specific application is dependent on a great number of factors, including but not limited to the following:
- network bandwidth and utilization
- coding techniques in the application
- free disk space
- available memory
- processor speed
- application(s) usage patterns (write-heavy, read-heavy, transactions used/not used, small record size, large record size, complex queries, simple queries, ODBC, Btrieve only, and so on)
- unrelated applications competing for CPU cycles
- database engine configuration parameters
As you can see, the engine configuration plays a relatively limited role in the overall performance of any given application. Further, the database engine dynamically manages a variety of resources based on usage patterns. It tunes itself to your environment as needed. The sections provided below are offered only as helpful guidelines and are not a guarantee of any specific level of performance.
SMP Support
Pervasive.SQL fully supports symmetric multi-processing (SMP) and multiple processors because it is fully multi-threaded and thread safe. However, Pervasive.SQL does not take direct advantage of any SMP-specific API calls.
In an SMP environment, the operating system schedules available threads, including Pervasive.SQL's threads, on the available processors. Since Pervasive.SQL is fully multi-threaded and thread safe, this behavior can yield a significant performance boost up to and including at least 4 CPUs. Internal tests, however, have not shown any significant advantage to bypassing the OS scheduling by utilizing SMP specific calls to become SMP aware. The reason is because Pervasive.SQL performance is usually not limited by CPU utilization, but rather by I/O on either the disk or network interfaces.
Novell NetWare poses an exception to the multi-CPU performance gain in some cases because, though it is multiprocessor, it is not true SMP.
Spotting Performance Bottlenecks
You can use Monitor to expose performance bottlenecks related to certain database engine configuration parameters. To start Monitor, choose Start4Programs4Pervasive.SQL V84Other Utilities4Monitor.
Relating Monitor Displays to Configuration Parameters
Two different Monitor menu selections display performance readings related to configuration parameters:
The table below shows which configuration parameters correspond to the readings displayed in the Resource Usage window:
Table 4-3 Monitor Resource Usage Corresponding Configuration SettingsThe table below shows which configuration parameters correspond to the readings displayed in the Communications window:
Table 4-4 Monitor Communications Corresponding Configuration SettingsInterpreting the Displays and Taking Action
You can make use of the information displayed in Monitor. Monitor displays three pieces of information about each type of resource. For example, the Communications Threads display shows:
- Current. The current number of actual communication threads operating.
- Peak. The highest number of actual communication threads that have been used since the engine was started.
- Maximum. The maximum number of communication threads allowed.
If the Peak value for a resource is the same as the Maximum value, then you may want to use Configuration to increase the Maximum value for the resource, thus allowing the database engine to allocate additional instances of that particular resource when it needs to.
Before You Modify Configuration Parameters
The sections below assume the following:
- Pervasive Control Center (PCC) is already open.
If you need assistance with this task, see An Overview of Pervasive Control Center of Pervasive.SQL User's Guide.
- You have already registered (if applicable) the engine you wish to configure.
If you need assistance with this task, see Registering or Removing a Server of Pervasive.SQL User's Guide.
- You have already double-clicked on the icon representing the given engine. For the Server configuration settings, you have double-clicked Server or for Client configuration settings, you have double-clicked Client.
- You have appropriate operating system privileges to configure the given engine.
If you need assistance with this task, see Granting Administrative Rights for the Database Engine of Pervasive.SQL User's Guide.
- You must re-start the database engine after making Configuration changes to the engine parameters.
Minimizing Initial Connection Time
The theory underlying minimal connection time revolves around three requirements. These requirements are summarized below, and detailed procedures follow:
- Known communications protocol. You do not want the client or server to spend additional time trying to connect via protocols that are not supported in your environment. By removing client/server support for unavailable protocols, you prevent the networking components from trying to use them.
- Known location of database engine. You do not want the client to attempt to connect to an engine that does not exist. By specifying Workgroup-only or Server-only support, as appropriate, you prevent the client from timing out while attempting non-existent connections. In environments where you have both unshared and shared databases, you can use Gateway Durability to force the database engine to keep track of machines where no database engine is running, so that it never attempts to connect to an engine on these machines, but uses another method to access the data.
- Database engine ready to execute. When a sleeping engine receives a new connection request, it takes time to re-allocate resources and return to a runtime state. If connection speed is more important than resource usage on the server, you can prevent the server engine from sleeping when it is not in use.
Client Parameters
You must be at the client machine to change the client parameters. You must change the parameters at each workstation whose settings you wish to change.
To minimize client-side connection delays
- In Configuration, click on Client4Communication Protocols. Double click Supported Protocols.
- In the protocol selection window that appears, select the protocols that you are not using in the box labeled "Selected." Click < to move the protocols into the box labeled "Available."
- Click OK. You have now prevented the client from attempting to communicate on protocols that are not supported.
- Click on Client4Access. If you are using only a remote Server or remote Workgroup engine, double-click Use Local MicroKernel Engine and set it to Off.
If you are using only a local Workgroup engine, double-click Use Remote MicroKernel Engine and set it to Off.
If you sometimes use a Workgroup engine and you sometimes connect to a Server engine or a remote Workgroup engine, you must leave both settings On.
In such a mixed environment with shared and unshared data, you can set Access4Gateway Durability to On at each client. This setting forces the client software to keep a list of the names of any machines on which it is unable to connect to a database engine. In order for the client software to determine no engine exists on a given computer, it waits for all of its network protocol requests to time out. If your data is stored on a server that does not have a Pervasive database engine on it, and you have set Access4Use Remote MicroKernel Engine to Yes, the client must time out at least once to discover that there is no engine on that machine. Gateway Durability ensures that this time-out only happens the first time your application tries to access that data.
Note
Using Gateway Durability fixes the network topology. If you later install a Server or Workgroup engine on the remote computer, you must turn off Gateway Durability on each client so that the list of computers without database engines is deleted (thus allowing you to connect to the new database engine). You may turn Gateway Durability back on immediately, but it starts with an empty list.
- Click OK. You have now prevented the client from attempting to connect to any database engine types that are not in use.
Server Parameters
- In Configuration, click on Server4Communication Protocols. Double-click Supported Protocols.
- In the protocol selection window that appears, select the protocols that you are not using in the box labeled "Selected." Click < to move the protocols into the box labeled "Available."
Be sure that at least one protocol you have selected in the Server configuration is the same as selected in the Client configuration. Your client and server cannot communicate if they are not using the same protocol.
- Click OK. You have now prevented the server from attempting to communicate on protocols that are not supported.
- This step does not apply to database engines on NetWare. Click on Server4Memory Usage. Double-click Allocate Resources at Startup. Set the value to On.
You have now specified that the database engine should allocate all necessary memory when it starts up, rather than when the first connection request comes in. Choosing this value requires more memory, but from the client perspective allows the engine to become operational faster.
- This step does not apply to database engines on NetWare. Double-click Back to Minimal State if Inactive. Set the value to Off.
You have now specified that the database engine should not release resources back to the operating system if the engine is inactive. All resources remain allocated and ready for use at the next client connection.
- Click OK. You must re-start the Server engine for these changes to take effect.
Maximizing Runtime Throughput
The theory behind maximum throughput relies on too many variables to list here. Several of the most significant factors are:
- Adequate physical memory. If your host system has too little RAM, the system spends most of its time and CPU cycles swapping memory to disk and back, as different users and processes compete for limited resources.
- Adequate CPU capacity. If your CPU cannot keep up with the inflow of data processing requests, application performance suffers.
- Adequate network bandwidth. If your network is slow or suffers from a high collision rate, the database engine may sit idle between data access requests, while each client seems to display poor performance.
- Minimal disk I/O. Disk I/O is significantly slower than memory
I/O. You want to avoid accessing the disk as much as possible, by having sufficient cache.- Adequate resource allocations. Even with massive physical memory available, database performance may suffer if database engine configuration parameters are set artificially low.
In the end, optimal performance is a balancing act among network bottlenecks, disk I/O bottlenecks, memory bottlenecks, and CPU bottlenecks. This section provides some guidelines on how to reduce memory and disk I/O bottlenecks.
Fast Disk versus Fast CPU
If you want to maximize the effect of your hardware investment for performance gains, you must understand the existing constraints on your performance. If you have a database that is so large that you cannot reasonably buy and install enough memory to cache a significant part of the database, then performance is likely to be constrained by the disk I/O. Under these conditions, you may be better off to invest in a fast RAID disk array to maximize performance of the disk I/O.
In addition, if your application uses the SRDE and forces temporary files to be created frequently, you may want to ensure that the directory where these files are created is located on a fast disk drive. For more information about the location of this directory and the types of queries that generate temporary files, see Temporary Files in SQL Engine Reference.
If your database is small enough to be fully or near-fully cached in memory, then adding a fast disk array is unlikely to provide a significant performance boost. Under these conditions, upgrading the CPU or adding an additional CPU may provide the best performance improvement value.
Ensuring Adequate Physical Memory and Database Cache
Starting with Pervasive.SQL V8, the database engine offers Level 2 dynamic cache in addition to the Level 1 cache specified by the configuration setting, Cache Allocation Size . Assuming you do not turn off the Level 2 dynamic cache by setting Max MicroKernel Memory Usage to zero, the need to manually adjust the Level 1 cache size is much less critical than in previous releases. With that in mind, this section explains how to ensure that you have enough memory available for optimal performance of the database engine.
Ideally, your database engine should be able to allocate enough memory to cache full copies of every database it hosts, thus avoiding as much disk I/O as possible. Obviously, caching one or more entire databases is not practical in some situations, particularly when database size is very large. In addition, such measures as adding RAM to the machine only improve performance if the existing system resources are heavily loaded under normal usage.
The database engine dynamically selects a Level 1 cache size value when it starts up the first time. However, this value is based on available memory and may not be the ideal amount of cache for your environment.
If you have more than one database serviced by the engine, but they are never used at the same time, add up the file sizes of just the largest database.
For example, assume there are two databases on your server, with the following file sizes, and users access both databases at the same time:
The sum of all these files is 1,430 MB or 1,499,463,680 bytes.
The number you have now is the maximum amount of memory that the database engine would use if it cached all its hosted data. This number can be referred to as MaxCache.
You would never want to specify a value greater than this for Cache Allocation Size, because you would be allocating memory to the database engine that it would likely never use. In practice, the Level 2 cache adjusts dynamically to store as much data as possible in memory, given the constraints on your system. A reasonable rule of thumb is to set Cache Allocation Size to about 20% to 70% of MaxCache. Lower values in this range are best for read-intensive applications, and higher values are best for write-intensive applications since all write/update operations take place in the Level 1 cache.
If this situation applies to you, you may wish to estimate the average number of distinct pages that your application accesses within the given uptime period, multiply that by the page size, and obtain a more realistic value of MaxCache for your particular uptime scenario.
On Windows-based operating systems, all user processes are limited to 2 GB of memory. If you have calculated a value of MaxCache larger than 2 GB, and your database engine runs on Windows, then you should use the value 2 GB or 2,147,483,648 bytes for MaxCache.
In addition to the data cache (MaxCache) determined above, the database engine also allocates a separate cache for miscellaneous database management objects. The size of this cache depends on the attributes of the pages in the data cache, and can range from 10% to 30% of the size of the data cache. To be fairly conservative, we will estimate the ManagementCache as 25% of the data cache, or 374,865,920 bytes in our example. Thus, the database engine could consume as much as 1,874,329,600 bytes of memory given our example data files. Let's call this value DBMem. Now you need to evaluate this number in light of the load that is running on the computer where the database engine is located.
- Determine portion of physical memory available to database engine.
The portion of physical memory that you allow the database engine to allocate depends on the load on the machine. If other critical services (such as email server, web server, and so on) are running on the same machine, then you must allow for these other services to allocate the memory they require. If you have a dedicated database server machine, you might allow the database engine to consume 80-90% of physical memory. If you have many other services running on the same machine, you might not want the database engine to take up more than 20% of memory. Thus, the ideal amount of total physical memory for your particular machine is at least TotalMem = DBMem * (1/DbAllocation), where TotalMem is the total amount of physical memory on the machine, and DbAllocation is the portion of memory available to the database engine. You should specify the value of DbAllocation in the Configuration setting Server4Performance Tuning4Max MicroKernel Memory Usage.
The Level 2 cache adjusts as needed, but never more than the specified proportion of physical memory. If the database engine encounters heavy contention for memory, then it will use less memory, even when it is below the specified maximum limit.
The amount of memory actually used by the database engine at any given time always lies between the number of bytes specified in Cache Allocation Size and the portion of total physical memory specified in Max MicroKernel Memory Usage.
Continuing the example above, assuming that we have other services on the machine and thus we choose to limit the database engine memory to 40% of physical memory, the calculation yields 1,874,329,600 * (1/0.4) = 4,685,824,000 bytes or about 4.37 GB total physical memory on the machine.
This value is the total amount of memory that we need on the machine in order to be able to allocate enough memory to the database engine to fully cache our specified databases.
If your database engine runs on Windows and you reduced your calculated value of MaxCache to 2 GB due to the operating system limit, then the performance of your database may benefit from using the Windows system cache in addition to the MicroKernel cache. Unless your cache requirements exceed 2 GB, there is no performance benefit to using the system cache in addition to the MicroKernel cache. By default, Pervasive.SQL runs with the system cache turned off. You can use the Configuration setting Server4Performance Tuning4System Cache (Windows/Linux engines only) to turn the system cache on.
- If you have at least TotalMem memory installed on your server machine, then your memory configuration should now be satisfactory. If your application is highly write-intensive, then you may experience slightly better performance by setting Server4Performance Tuning4Cache Allocation Size to 50-70% of your calculated MaxCache value. If your application is highly read-intensive, then you may experience the best performance when Cache Allocation Size is set smaller, such as 20% of MaxCache.
Never set Cache Allocation Size larger than the amount of physical memory installed in the computer. Doing so forces the MicroKernel to make use of Virtual Memory on disk, which significantly degrades performance.
- If you do not have TotalMem of memory installed, you should consider installing enough memory to reach that amount.
- If it is not practical to add enough memory to reach TotalMem, then you may wish to off-load other services to a different computer so that the database engine can use a larger proportion of physical memory. Or, settle for a smaller cache and a somewhat greater level of disk I/O. Many applications will run almost as fast as 100% cached if the database is at least 25-30% cached. The specific performance ratio is dependent on the design and average usage model of the application.
Under these conditions, you may also consider investing in a fast disk array, such as a RAID 0 stripe array that splits disk writes across multiple volumes simultaneously. Such a configuration can have a significant effect on systems where the disk I/O is limiting the overall application performance.
Minimizing Disk I/O
Reading and writing data to/from disk is much slower than reading and writing to/from memory. Thus, one way to optimize performance is to minimize disk activity.
An important consideration in your attempts to minimize disk I/O is recoverability of data. Disk I/O is a direct trade off against transaction durability and recoverability. The more data you keep in memory without pausing to log changes to disk, the faster the database performs. On the other hand, the more data you keep in memory without pausing to log changes to disk, the more data you lose if the system experiences a failure.
- As discussed in the previous sub-section , Ensuring Adequate Physical Memory and Database Cache, one of the most important considerations is to ensure you have enough database memory cache to avoid frequently swapping data pages between disk and cache. See that section for details.
One of the best ways to reduce disk I/O is to make sure that the dynamic Level 2 cache is turned on. The Level 2 cache adjusts its size dynamically as application usage changes, storing as much data as possible in memory and thus avoiding disk I/O when cache demands exceed the capacity of the Level 1 fixed cache. By default, the Level 2 cache is turned on. To verify that your database engine is using Level 2 cache, check the Configuration setting Server4Performance Tuning4Max MicroKernel Memory Usage.
- In any networked configuration where the application and the database engine reside on different computers, use of the client cache improves performance under most usage scenarios. By default, the client cache is turned on. To verify that your client is using the client cache, check the Configuration setting Client4Performance Tuning4Use Cache Engine.
- The next step is to consider how much logging you require and what quantity of database operations you are willing to lose in a system failure. The greater the quantity of changes you are willing to lose, the more you can risk in the pursuit of performance.
Using Archival Logging, Transaction Durability, and Transaction Logging all require log files. By default, archival logging is turned off. Turn it on only if you perform regular backups and you need the capability to restore data up to the moment of a system failure. When you specify the files to be logged, be sure to specify only the files for which you absolutely must have logging. See Chapter 8, Logging, Backup, and Restore, for more information.
By default, transaction logging is turned on. Turning off transaction logging should improve performance slightly, but does not guarantee multi-file consistency and transaction atomicity. Before turning off transaction logging, check with your application vendor to be sure they allow the application to run without this feature.
Caution
The consistency of any multi-file database cannot be guaranteed if transaction logging is disabled.
By default, transaction durability is turned off. Turn on this feature only if your application requires completed transaction operations to be durable through a system crash. Transaction durability entails the highest performance penalty, and the trade off is the highest safety of your completed transactions.
- If you have any logging features turned on, you can specify how much data the engine stores in memory before writing to disk. This feature is important because the changed data builds up over time. The more log data you allow to build up in memory, the less frequent the disk writes are.
The setting Server4Performance Tuning4Log Buffer Size specifies the number of bytes of database operations that the engine stores in memory before writing them out to the log files.
If a system failure occurs, the data in the log buffer is lost.
- If you have transaction durability turned on, you can specify the maximum size of the log segments on disk. Specifying a larger log segment size can improve performance slightly, because fewer log segments have to be created and closed over time.
The setting Server4Performance Tuning4Transaction Log Size specifies the maximum number of bytes that can be stored in a log segment before closing it and opening a new segment.
- If your application usage is weighted heavily in favor of database read operations, you can increase performance by turning on Server4Performance Tuning4Index Balancing. Over time, index balancing increases the number of nodes on the average index page, allowing read operations to occur faster. However, for insert, update, and delete operations, additional time and disk I/O may be required because the engine balances the index nodes across adjacent pages.
- Be sure that tracing is turned off, both in the MicroKernel and/or at the ODBC level. Tracing may cause a significant reduction in performance because it can introduce a large amount of disk I/O.
To ensure ODBC tracing is turned off, choose Start4Programs4 Pervasive.SQL V84Other Utilities4ODBC Administrator and click on the Tracing tab. If tracing is off, you should see a button labeled "Start Tracing Now," and thus you should click Cancel. If tracing is on, click Stop Tracing Now, then click OK.
To ensure MicroKernel tracing is turned off, use Configuration to inspect the setting Server4Debugging4Trace Operation. Set the value to Off if it is not already.
Ensuring Adequate Resource Allocation
If your database server platform has adequate memory and CPU power, you should ensure that your database engine can take full advantage of the available hardware resources to service multiple clients and multiple data files most efficiently.
- The setting Server4Performance Tuning4Communications Threads allows you to specify how many threads are available to handle client connections.
For any network with up to 32 client workstations, you should assign one communications thread for each connected workstation. Above 32 clients, you should use Monitor to evaluate whether the peak usage is hitting the maximum allowed. See Spotting Performance Bottlenecks for more information.
After you increase the Communications Threads value and restart the database engine, wait until a period of peak usage has occurred, then evaluate the situation again using Monitor.
- The setting Server4Performance Tuning4Number of Input/Output Threads allows you to specify how many threads are available to handle file operations.
As a guideline, the value of this setting should be about 1/8 the number of files the application has open, on average. For example, if the application has 40 files open most of the time,
I/O Threads should be set to 5.Using Monitor, choose MicroKernel4Resource Usage from the menu. In the window that appears, the Files: display shows you current and peak number of files open. You can generate an average reading by recording several Current values over time. Then you can specify an appropriate setting for I/O Threads based on the average value.
| Prev Configuration Utility Tips |
Contents Up Check for Revisions | Next Configuration Reference |