 | Advanced Operations Guide (v9 SP2 (9.5) revision 1) |  |
|
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:
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.
Pervasive PSQL fully supports symmetric multi-processing (SMP) and multiple processors because it is fully multi-threaded and thread safe. However, Pervasive PSQL does not take direct advantage of any SMP-specific API calls.
In an SMP environment, the operating system schedules available threads, including Pervasive PSQL's threads, on the available processors. Since Pervasive PSQL 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 PSQL 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.
You can use Monitor to expose performance bottlenecks related to certain database engine configuration option. To start Monitor, choose Start 4 Programs 4 Pervasive PSQL v9 Service Pack 2 4 Other Utilities 4 Monitor.
Two different Monitor menu selections display performance readings related to configuration options:
The database engine dynamically manages several server configuration options, as shown in the following table.
You can make use of the information displayed in Monitor. Monitor displays three pieces of information about each type of resource. For example, the Total Remote Sessions display shows:
If the Peak value for a resource is the same as the Maximum value, then you may want to set the configuration property 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.
The sections below assume the following:
If you need assistance with this task, see Starting PCC of Pervasive PSQL User's Guide.
If you need assistance with this task, see To register a remote server engine of Pervasive PSQL User's Guide.
If you need assistance with this task, see Granting Administrative Rights for the Database Engine of Pervasive PSQL User's Guide.
The theory underlying minimal connection time revolves around three requirements. These requirements are summarized below, and detailed procedures follow:
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
The client is now prevented from attempting to communicate on protocols that are not being used.
If you are using only a local Workgroup engine, ensure that Use Remote MicroKernel Engine is not check marked. (That is, it is 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 (check marked).
In such a mixed environment with shared and unshared data, you can set Gateway 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 Use 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.
The client is now prevented from attempting to connect to any database engine types that are not in use.
To minimize server-side connection delays
The server is now prevented from attempting to communicate on protocols that are not being used.
Ensure that at least one protocol you have selected for the Server configuration is the same one as selected for the Client configuration. Your client and server cannot communicate if they are not using the same protocol.
This option specifies 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.
This option specifies 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.
This step does not apply to database engines on NetWare.
The theory behind maximum throughput relies on too many variables to list here. Several of the most significant factors are:
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.
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.
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.
To calculate the ideal size of the database memory cache
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:
|
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
|
||
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.
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.
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.
To determine how much total physical memory you need
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.
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 properties configuration setting Max MicroKernel Memory Usage. (Click Performance Tuning on the server Properties tree.)
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 PSQL runs with the system cache turned off. You can configure the setting System Cache (Windows/Linux engines only) to turn on the system cache. (Click Memory Usage on the server Properties tree.)
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.
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.
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.
To reduce disk I/O
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 ).
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.
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.
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.)
To ensure ODBC tracing is turned off, start ODBC Administrator from the Pervasive program on the Start menu. 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.)
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 handling
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, click MicroKernel 4 Resource 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.
|
Chapter contents
Prev topic: Configuration Through CLI Utility
|
