Best Performance Settings

[JoshBreitbach]

We just recently bought a brand new server to run our pervasive databases on. The server is replacing one that is 5 years old so we are expecting the see performance improvements.

The new server:
Dual 3.0 GHz Intel Xeon Processors
6 GB RAM
400 GB Total SCSI Hard Disk Space in RAID
Pervasive Btrieve/Scalable SQL 7 SP6
Windows 2003 Server

A few of the tests that I have run so far (a rebuild on a 1.3GB file) have been faster than the current server but not as fast as I would expect or like. In fact, I can watch the server's performance during a stressful transaction such as this rebuild and it's hardly even scratching the surface of the server's power (2-4% CPU Usage).

I know there must be some settings that I can change to improve performance. I have already increased the Cache Allocation to 1.25 GB but when I try to increase to 2GB, the services will not start properly.

Another thing that bothers me is that the CPU usage is so low. I would expect that the database engine would use as much CPU as was available to perform its tasks.

Can someone suggest some settings that would help me realize the full potential of our new server.

Thank you,
Josh Breitbach
jpbreitb@rockwellcollins.com

 

[TomKane]

Do you access your data using the transactional engine or the relational (ODBC) engine? If it's relational it's probably not going to be all that quick as there are a couple of layers between the program and the data that you don't have if you go the transactional route.

 

[noodles1]

The benefits from an increase in Pervasive Cache size is generally observed from accessing data that has previously been read.

Pervasive documentation states that the ideal Cache size is just slightly larger than your total DB size (to allow for some overheads in the Cache). This way your whole database can fit into the Cache and future accessing of this data can be serviced from Cache.

However, the Cache size should not exceed 40% of the total server RAM, so that the O/S has sufficient resources to operate correctly.

Given that you are attemtping a setting that is less than the 40% limit, what sort of error condition on the Pervasive Service startup are you receiving? Pervasive 7 will attempt to grab the memory for the Cache at Startup, and if this memory is unavailable, it will not start and indicate the cause. What other apps are loaded on this server that are using significant amoutns of RAM. Taks Manager should tell you this?


The different Pervasive Thread Pools also have a significant bearing on performance, and this optimum settings are largely dependent on the particular DB access behaviour of your application as well as concurrent users levels?

Is the test you that outlined above the only test you have yet performed?

 

[noodles1]

I believe that only Pervasive.SQL V8 and 2000i Service Pack 4 are fully supported on Windows Server 2003.

Notwithstanding, I would still expect V7 to operate correctly on Windows Server 2003

 

[noodles1]

You might need to check that your O/S has access to the full 6GB. It will need to use Physical Address Extension switch (/PAE in the boot.ini file).

For a full desription take a look at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;283037&Product=winsvr2003

Also, the Pervasive V7 & 2000i engines need the memory to be alloated for Cache to be a contiguous block. If this is not available, then the Pervasive Service would fail to start correctly.