Introduction

The documentation included with your copy of Pervasive.SQL gives a thorough recommendation for the minimum system configuration required for a successful installation of Pervasive.SQL. That's all well and good, and the more recent releases of Pervasive.SQL do an excellent job of self-configuration. However, Pervasive.SQL can only work with what's available, and the minimum system configuration will not ensure a well-performing application, and won't do justice to either your application (whether painstakingly developed in house or purchased) or Pervasive.SQL.

If you are experiencing sub-par performance from your application, you are not alone. Many Pervasive.SQL users are in the same boat. The fault is not necessarily with Pervasive Software, your application vendor, Pervasive.SQL, nor your application software (although of course, there could be some issue with any of them that affects system performance). Much of the fault, if there is any, lies in the Low Cost of Ownership, Self-Administrative, No-DBA nature of Pervasive.SQL, which is a major advantage, for the most part. Like most good things, it is a double-edge sword.

Consider this: If your application required an Oracle database or a Microsoft SQL database, you would probably be required to have on-staff a Data Base Administrator (notice the capitalization; it's expensive). If you were paying a DBA the usual $150,000+ per year, you would probably be inclined to listen to her or him. If s/he said that you needed a server with quad 4.8-gigahertz processors, 64 gigabytes of memory, and 32 terabytes of RAID disk or your application would roll over dead, you would either issue the purchase order or you would know why your application rolled over dead. Pervasive.SQL users need no and usually have no such in-house advocates for the database.

The purpose of this document, then, is to act as that advocate and present a blueprint for a server configuration that will serve as the best platform for both Pervasive.SQL and your applications and increase your satisfaction with the performance of both. The scale of some of the recommendations will probably surprise you.

Server Hardware

Here are our recommendations for your server hardware setup.

Processor

• Intel Processor
• 3+ gigahertz
• At least a dual-core
• Depending on overall usage, you might consider dual processors. Pervasive.SQL as of yet does not support dual processors, but if you intend to use your server as more than just a database server, dual processors would allow the O/S to balance the load between the database engine and the O/S and other applications.

Memory

• Calculate memory requirement:
• Add up the number of bytes in your database. Do this on a steady-state database; that is, not one on which you have recently purged many records and rebuilt the files. Let's call this DBSize.
• Add a number for data management overhead. 25% of DBSize is a typical conservative number.
• Estimate the load required for other non-O/S applications you may want to have running on the server. It's generally a very good idea to severely limit these kinds of things, for at least a couple of reasons:
  1. It's difficult to know exactly how much memory they're going to require.
  2. At best, they will consume server resources.
  3. At worst, they will increase the possibility of a server crash.
• About the best you can do to estimate this load is to ask yourself whether this server is going to be a dedicated data base server, or if it's going to be used for a lot of other things such as an email server, web server, etc. If it's going to be a dedicated database server, you might be able to allow Pervasive.SQL to consume up to 80-90% of physical memory. If it's going to be used for lots of other applications, then you might not want it to consume more than 20% of physical memory. Let's just say, for example purposes, that your server will be mostly used as a dedicated data base server but might occasionally run other services, so we'll allow Pervasive.SQL to consume up to 60% of physical memory. Let's remember this number and call it DBLoad, we'll use it later.
• Example:
• Using the principles above, the formula for calculating the required physical memory is as follows: PhysMem = (DBSize * 1.25) * (1 / DBLoad)
• The Database totals 4.8 gigabytes.
• Above, we decided that DBLoad would be 60%.
• So: PhysMem = (4.8 * 1.25) * (1 / 0.6) = 6 * 1.67 = 10.02 gigabytes. This is the total amount of memory that we need on the server in order to for the database engine to be able to allocate enough memory for itself to fully cache our database.
• You should have installed in your server at least 10 gb of RAM.
• Engine Configuration:
  Now, let's configure the Pervasive.SQL engine to properly utilize these 10 gigabytes of memory we have just installed in the system.
• Start up the Pervasive Control Center (PCC)
• In the namespace (left-hand column), right-click on the name of the Engine and click on “Properties”.
• Click on “Performance Tuning”.
• Now, a few words of background. Pervasive.SQL uses two levels of cache memory, called L1 and L2 cache. L1 cache is always available and L2 cache is assigned dynamically by the engine. The amount of memory used by the engine at any time always lies between the amount of L1 memory and L1 + L2, which should equal DBSize.
• Continuing, set the value of the setting “Cache Allocation Size” equal to anywhere between 20% and 70% of DBSize. If your application is mostly read-intensive, set it toward the lower part of this range, and if it is mostly write-intensive, set it toward the upper part of this range.
• Set the value of the setting “Max Microkernel Memory Usage” to the value of DBLoad, which in our example is 60.
• NOTE: If you are using a Windows Server O/S, there's a catch. Under Windows Server, every user process is limited to 2gb of total address space. That means our cache allocation plus whatever other memory resources the engine may need to allocate for itself. Therefore, even though we have calculated that we would need at least 4.8gb to cache the entire database, we can't even set “Cache Allocation Size” to as much as 2,147,483,648 bytes. It gets worse: the resources that the Pervasive.SQL engine needs to allocate for itself vary with the load that out application(s) place on it (how many sessions, threads, open files, whether the SQL engine is being invoked) and we don't really know what that is from minute to minute. So, we have to allow for some overhead. Conventional wisdom says that up to 1.7gb might be safe. But then, it might not be, and if we set the Cache Allocation to 1.7gb, we risk a failure of the Pervasive.SQL engine. What to do? Here's what:
1. Set Cache Allocation way down to around 500mb.
2. Set Max Microkernel Memory Usage to 0.
3. Click on the property “Memory Usage”.
4. Check the box entitled “System Cache”.
5. This turns off L2 cache and will allow the engine to utilize Windows System Cache memory in place of L2 cache to cache the remainder of the database, as needed (allowing the operating system to manage that memory), and our calculations above have determined the proper amount of total system memory to make this possible.
• More important “Memory Usage” settings
• Pervasive.SQL installs with “Back to Minimal State if Inactive” ON and “Allocate Resources at Startup” OFF by default. This is not optimum; rather, these are set this way to have minimum impact on the server when the Pervasive.SQL engines are first started. You will be much happier if you change these settings to:
  1. “Allocate Resources at Startup” ON and
  2. “Back to Minimal State if Inactive” OFF
• What these new settings will ensure is that when the entire gang goes to lunch or goes home at night, after a period of inactivity, the nicely-populated cache memory isn't flushed out and everything has to start afresh. It generally takes several days of operation for Pervasive.SQL to attain a “steady state” wherein most of the most-used data pages are nicely ensconced in cache memory. Clearing those resources or unnecessarily restarting the engines or rebooting the server defeats the purpose and will really deflate the performance of the database. Notice that I included “unnecessarily rebooting the server”. That bears emphasis: Don't restart your server every morning!
• If all this is more than you want to tackle, please contact us at Analytica Business Systems for help!
• One final note about available physical memory and cache memory: If, for whatever reason (small budget, huge database), it isn't possible for you to add enough memory to reach PhysMem, you can configure a smaller cache and settle for a somewhat lower performance level due to increased disk I/O. The principles of cache memory usage have the almost-magical ability to make an application run almost as fast at a 25-30% cache level as at a 100% cache level. Still, given the extremely low cost of memory, I would recommend lowering the cache level if your database size exceeds the RAM capacity of your server. In such instances, you can ameliorate the effects of increased disk I/O by investing in very fast disk, which brings us to:

Disk

I don't have a lot to say in this regard, except the obvious, that you should install the very best, fastest, most reliable disk subsystem that your budget can afford. If you can justify a RAID, that would be excellent. If your database cannot be fully cached, as I've just descriped in the previous bullet point, a RAID 0 stripe array, which splits disk writes across multiple volumes simultaneously can have a very beneficial effect on performance. One recommendation I have is to consider mirroring, for data security purposes. Pervasive Software produces an excellent mirroring product, Pervasive DataExchange, which is configurable as either a real-time backup or as a data synchronization system. You can read more about Pervasive DataExchange here.

Network

Again, here I will present just a few general, probably obvious points regarding network configuration. Since you're running your business on this database, you don't want to scrimp. Almost half of all database-performance-related issues can be traced to sub-par network performance.

• Make sure that your NICs (Network Interface Cards), routers and/or hubs, and cabling are of a high quality. These electronic components are not expensive to begin with, so it's not difficult to afford the best.
• Have your network components and cabling professionally installed. If you have the capability in-house, fine. If not, don't try to have your landlord's nephew cable your office (unless he's a networking professional). Many network performance issues are subtle and thus not easily detected. You will need a professional on tap to troubleshoot your network from time to time.
• I don't recommend wi-fi for heavy-duty or even medium-duty database applications. The connections just aren't fast enough (yet) and can be unreliable due to interference from many sources, like microwave ovens.