Pervasive performance hit in migrating from v9.71 to v11.01

Last post 10-10-2013 5:47 PM by BrownJ. 7 replies.
Page 1 of 1 (8 items)
Sort Posts: Previous Next
  • 08-22-2011 3:53 PM

    Pervasive performance hit in migrating from v9.71 to v11.01

    Everything we have read and heard was that we were going to see tremendous performance increases with this migration. I would also like to add it was 32-bit to 64-bit as well. The problem is instead of things being 4-6 times faster, they are 10 times slower. We followed configuration and tuning instructions from the reseller but nothing has helped. It started after testing on a server and noticing that I could also connect to the production server still running v9 and everything executed properly. After that we rolled out to all users and had issues with performance on the Production server. We moved up the upgrade by a couple days to try and help but the performance became no better.

    If it helps we are on a year old HP DL 380 G7 server and hooked to an HP P2000 SAN running a 15k array for disk via FC. I only have 4 GB RAM for the database server at the moment but I have 24 GB sitting on my desk if that will help it. We are running Server 2008 R2 fully patched.

    On a related note - is there any issue with running the current 32-bit Pervasive 11.01 client to connect a 9.71 database?

     

  • 08-22-2011 6:44 PM In reply to

    Re: Pervasive performance hit in migrating from v9.71 to v11.01

    What exactly is slower? How many users were connected? 

    How big is the database?

    What is your cache set to?  

    What changes did you make?  

     As far as connecting an 11 client to 9 server, transactional (Btrieve) might work but relational (ODBC, OLEBD, JDBC, etc) will probably fail.

  • 08-22-2011 10:07 PM In reply to

    Re: Pervasive performance hit in migrating from v9.71 to v11.01

    ...and have you checked the power management settings on the Windows 2008 R2 system?  See the PSQL KB "PSQL Performance Can Be Affected by Power Management Settings".

    ...you might also want to post info about any other software running on the database server that may require a lot of memory (4GB RAM seems a bit low for a multi-core server - although I can't tell how many cores your server has from searching "HP DL380 G7" so you may want to include that information in your response as well).

  • 08-23-2011 1:30 PM In reply to

    Re: Pervasive performance hit in migrating from v9.71 to v11.01

    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.

     

  • 08-23-2011 1:48 PM In reply to

    Re: Pervasive performance hit in migrating from v9.71 to v11.01

    First, if your L1 cache is 32GB, then you should disable the L2 cache.  In this case, though, it looks like you've not even filled up the L1, let alone started to fill L2.  

    Second, if you issue a LOT of SQL queries, then the database engine could be actually lacking "free" memory in order to create temporary index files in memory.  Try dropping the L1 cache from  32GB down to 26GB and see what happens.  Again, you've not used more than 11GB since your last reboot, so you won't notice this at all.

    Third, you may wish to consider moving to PSQLv11.11 by applying the v11.10 Service Pack 1, followed by the v11.11 Update 1 patch.  This should be a bit later than your v11.01 update, and may include additional fixes. 

    Fourth and fifth, you mention that this is on a VM -- perhaps the host machine is set for power saving mode?  Check the Goldstar white paper on checking your server's performance, and double-check the one on why running databases on virtual servers is a bad idea:
        http://www.goldstarsoftware.com/papers/CheckingYourDatabaseServerCPUPerformance.pdf
        http://www.goldstarsoftware.com/papers/VirtualizingDatabaseServers.pdf
    If your PSQLv9 server was a physical box and you moved to a VM, then I would certainly expect a 40% performance degradation, regardless of the server capabilities, due to increased latency alone.

     

  • 10-01-2013 8:55 AM In reply to

    Re: Pervasive performance hit in migrating from v9.71 to v11.01

    Bill, is there a way to get the temp indexes in memory besides a RAM Disk?
  • 10-01-2013 10:40 AM In reply to

    Re: Pervasive performance hit in migrating from v9.71 to v11.01

    Currently the SRDE will flush to disk if the internal temp table file size reaches 256K. The following registry key will be supported to allow users to configure this:
         HKEY_LOCAL_MACHINE->SOFTWARE ->Pervasive Software->SQL Relational Engine ->TempTableFlushSize
        The entry is of type DWORD.
        The value indicates a number of bytes. The default is 256000 and if the value is set to less than 256000 then internally the value of 256000 is used.

  • 10-10-2013 5:47 PM In reply to

    • BrownJ
    • Not Ranked
    • Joined on 09-03-2011
    • Posts 7

    Re: Pervasive performance hit in migrating from v9.71 to v11.01

    I experienced this in one of the operations using our software (which uses Pervasive as the database manager). What had been taking about two minutes on XP 32-bit SP3 was taking 25-30 minutes on the newer/faster machines. In my case the problem turned out *not* to be the PSQL. It was the new SMB2  that was creating the problem ("SMB" is Server Message Block and is the method of communication between workstation and server, and SMB2 is supposed to process very large files - terabytes - more efficiently). The problem only occurs when you have Win 7 64-bit machines as clients of a Win 2008 R2 64 bit server. I do not remember where I found it, but there is on this forum the instructions for turning off SMB2 and reverting to the original SMB. Once we did the switch, the performance increased by a factor of ten. If the switchover does not help your speed, the instructions are there to switch it back.

    I have found that PSQL 11.3 on a core i7 CPU is *really* quick. Core i7 is far better than cpu's with fewer cores, and the PSQL 11 is good at using multi-core cpu's. 

Page 1 of 1 (8 items)