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.
Tuning Performance
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
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.
³
To calculate the ideal size of the database memorycache
1 Start by adding up the file sizes of all the data files serviced by the database engine.
Note 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.
The number you have now is the maximum amount of memory that the database engine would use if it cached all its hosted data.
Database A Database B
file1.mkd 223 MB Afile.mkd 675 MB file2.mkd 54 MB Bfile.mkd 54 MB file3.mkd 92 MB Cfile.mkd 318 MB file4.mkd 14 MB
Tuning Performance
Note File pages are only written to the database cache when they are accessed. Thus, for a database engine to use MaxCache amount of memory requires every page in the database to be accessed. This system of estimating assumes a long-term steady state for database usage. If you bring the database engine down nightly or weekly, it may be unlikely that the database engine would access every page in the database within the given period of uptime.
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.
See also Counters for MicroKernel Cache.
On Windows 32-bit 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 a 32-bit Windows operating system, then you should use the value 2 GB for MaxCache.
³
To determine how much total physical memory youneed
Use the following equation to determine the approximate amount of total physical memory required by the database engine.
The L1 cache is a fixed size based on Cache Allocation Size. It does not expand or contract based on database operations. If your application performs numerous WRITE operations, increase the L1 cache as much as possible.
The greatest performance is obtained if all of the data can fit into the L1 cache. If all of the data will not fit into the L1 cache, adjust Cache Allocation Size and Max MicroKernel Memory Usage to use a reasonable amount of system memory.
The L2 cache expands and contracts based on memory load of the system. For example, if other applications require more memory, the L2 cache contracts. If ample memory is available, the L2 cache reaches a maximum based on the equation above. The expansion and contraction affects performance. Contraction causes data pages to be removed from the L2 cache, for example. Reading the pages back from disk storage takes longer than if the pages could have been retained in the cache.
The L2 cache contains compressed data pages (more pages in less space). Accessing pages from the L2 cache takes longer than from the L1 cache, but is faster than accessing the pages from disk storage. The L2 cache is helpful for applications that perform numerous READ operations but cannot fit all of the read data pages into the L1 cache.
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.
Tuning Performance
³
To reduce disk I/O1 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 properties configuration setting Max MicroKernel Memory Usage (see
Max MicroKernel Memory Usage).
2 Next, 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 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.
3 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 Log Buffer Size specifies the number of bytes of database operations that the engine stores in memory before writing them out to the log files. (Click Performance Tuning on the server Properties tree.)
If a system failure occurs, the data in the log buffer is lost.
4 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 Transaction Log Size specifies the maximum number of bytes that can be stored in a log segment before closing it and opening a new segment.(Click Performance Tuning on the server Properties tree.)
5 If your database is highly used, consider configuring your system to maintain the logs on a separate physical volume from the volume where the data files are located. Under heavy load, performance is typically better when the writes to the log files and to the data file are split across different drives instead of competing for I/O bandwidth on a single drive. The overall disk
Tuning Performance
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.
7 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, start ODBC
Administrator from Pervasive PSQL Control Center. In ODBC Administrator, 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, set the properties configuration Trace Operation to Off (not check marked). (Click Debugging on the server Properties tree.)
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.
³
To configure multiple client and file handling1 The setting Number of Input/Output Threads allows you to specify how many threads are available to handle file operations.