Feel kind of like we are grasping at straws with this at the moment. We are trying not to do too much change at one time but we keep trying different things. We did have a performance increase this morning after a reboot but now are back to a crawl on the transactional DB side. That was the 4th or 5th reboot since the service we did last night. However reports are much more snappy but we are still far from pleased with overall performance.
What exactly is slower? How many users were connected?
As far as what is slower, pretty much everything we do. We run an ERP system on top of pervasive (transactional mainly) and query the system using Crystal Reports on top of it. We use the relational and transactional engine extensively. We have about 60 users of the database at peak times and off peak 5-10 light users. Relational queries don't seem to be impacted by how many users are in the system. As far as we can tell virtually everything we do slowed down by several factors in both engines. The one thing we do notice is heavy ODBC relational calls via a Crystal Report cause a slow down even in the transactional engine. Even without touching overall server performance.
How big is the database?
About 40 GB
What is your cache set to?
Cache Engine is off. In Performance tuning Cache Allocation size in MB is 32 GB. Log Buffer 8MB, Trans logs = 16MB, I/O threads =32, Communication threads = 4, Max MicroKernel Memory Usage = 60, File Growth Factor is 1. Both Index Balancing and Limit Segment Size are unchecked. Yes I have my Gigs and Megs right before you ask. Something to note all our files compatibility is set to 9.5 except one that is 9.0 so we are running 9.0 compatibility.
What changes did you make?
We upgraded from v9 to v11... That was pretty much the only change. We are running 11.1 on the server. The install for base 11 was followed up by a "patch" provided by our vendor.
We have a strong network running Gig connections, Cat6 and completely Cisco Gear. The desktops are a mix of older XP systems and new i5/i7 Win 7 systems and we don't really see any difference in performance at the desktop level. It is in a virtualized environment using VMWare. I agree 4 GB is low, but when you are running only a 32-bit DB on the server that was pretty much all we needed. We went through and tuned it last night it has a full Quad-Core Xeon E5640 (2.67 GHz) CPU at it's disposal and 36 GB of RAM. The DB has grown to only use 11GB of RAM so far today. The total DB size is roughly 40GB. We have turned off SMB2 on the server. We really are not touching performance potential of the box.
We did make a change in the VM to make it "high performance" on the power settings after reviewing the PSQL KB article link above, i am not sure it will make a difference being that it is virtualized. It is strange to watch the system when you launch a query though we hare hitting files that are typically 2 GB to 4 GB with queries but you watch the system performance and the CPU takes a hit but the disk really doesn't to me it seems it should be pounding the files to get the data.As
far as connecting an 11 client to 9 server, transactional (Btrieve)
might work but relational (ODBC, OLEBD, JDBC, etc) will probably fail.
Everything seemed to work - it was something I accidentally stumbled on. So after I did some testing to verify everything worked correctly, I made the call to roll out clients during the day and then update the server overnight. As soon as clients were installed everything slowed down. We are in the process of reloading clients as a precautionary measure (wiping all files and registry entries for pervasive). We wiped out the install on the server last night and reinstalled from scratch.
One of the admins here started reading the white papers here: http://www.goldstarsoftware.com/press.asp That has made us wonder because a lot of it goes against what we have been told by our vendor.This one was also good read: http://www.pervasivedb.com/psqlv11/Documents/PSQLv11_SP1_Known_Issues.pdf
Could adjusting the paging file of the OS or even turning it off boost performance? I am just not sure... There is nothing else running on the system other than the ERP/Pervasive software of any significance. We don't even run AV on it.