Pervasive PSQL v11

I have upgraded everything (I do mean everything), desktops, servers, cabling, switches, you get the point.  I have also upgraded Timberline to (new name) Sage 300 construction with comes with Pervasive PSQL v11.  Since the upgrade the software runs terrible.  Freezes throughout the day, long delays processing data (read or write) and lots of complaints.  I have been working with Sage tech support and they have made it better (made some adjustments to the server and memory allocation) however we are at a standstill for improving the performance.  Everything else on the server runs great, just not Sage construction.  Anyone have any ideas what could be happening and how it could be corrected?  Here are the details:

     Lenovo RD630 with dual Xeon CPU's
     80GB RAM
     1.8tb of storage in a RAID 1+0
     3 network cards gigabit - 1 for the users and 2 for the VM environment
     3 virtual servers (low usage servers)
     Windows server 2012 standard
     i3 CPU
     4GB ram
     160gb hard drive
     1gbs NIC
     windows 7 64-bit

Sage 300 construction (timberline) runs great from the server.  I have tested with limited users which works OK.  Its better with less users than our normal 9.  However I have not performed good testing with limited users to make an accurate comparison.

Watching the servers performance tells me there is nothing wrong with the server.  Working with file transfers yields the highest network throughput possible.  Working with the MSQL databases are fine.  Basically I am at the WTF point with this software system.  Any help would be great.
Who is Participating?
TheNerdherderConnect With a Mentor Author Commented:
I have with some help from Sage fixed the problem.  Its not perfect but there is no productivity lost.  Some more background:
This was an upgrade from 9.7.  The old version did not have most of its reports in the correct place so I ran the migration tool.  Also it was installed on a completely new windows server 2012 (not R2).  Being that TL needs to have a printer installed before it will work I installed and mapped a network printer (printers where scheduled to be moved to a new print server but have not been moved as of the install).  Installed TL and tested.  Started to setup workstations.  At this point the printers were also moved to new server.  Also found out that TL 13 does still use NetBios and does not like FQDN.

This is what I have done.
Scrubbed registry of all workstations and server and removed all pointers to old print server
Installed WINS server on network and added static mapping to TL server
unchecked IP6 on workstations
disabled network AV scanner
fixed misc pointers to bad file paths in all reports (migration tool did not fix all reports)
found and removed pointers to old pervasive server (this should have not been in there but it was a left over from the uninstall)

It has been working well for about a week.  I hope this information helps others who have a Sage 300 construction (Timberline) or pervasive PSQL issue.
Don ThomsonCommented:
Bill BachPresidentCommented:
Just an off-the-wall question -- are they running Trend Micro AV?
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Bill BachPresidentCommented:
Can you grab a Wireshark trace of a database process running, so that we can see it?  This will give you a good idea of actual response times, both server and network/client, and give you an idea of whether you need to adjust the server or the network/client.
TheNerdherderAuthor Commented:
 thanks - we are currently at version 13.1.24 rev 5.  We have received another update yesterday from sage and will be installing it today.
TheNerdherderAuthor Commented:
we do not use Trend products.  I have also added all of the exclusions to our current AV software and have removed AV from the server.
TheNerdherderAuthor Commented:
I will have to set that up.  What I can tell you from the new performance monitor in Windows 2012, the clients have anywhere from a 0 to 213ms latency.  As far as network throughput goes, all the clients have no issues with speed.  I can move large files at gigabit speeds.  
with wireshark what are you looking for?  SQL transactions start and end times, network issues, etc?

Bill BachPresidentCommented:
My understanding is that a majority of TL still uses the Btrieve interface, so you'll want to look at the traffic coming in on TCP port 3351 on the server side.  Look for "nominal response time" on both sides of the link -- and try to calculate an average response time.  If you are taking the trace from the server, then you'll see the true database engine response time on the server side, while the client side will include client think time as well as network round trip time (RTT).  If you need to isolate the RTT, then take a second trace from the client side instead.  Then, subtract the response time of the server from the response time from the server on the client side -- and you're left with RTT.

Most database applications will issue thousands of small requests -- VERY different from network file I/O over SMB.  If your RTT is high, then performance will suffer.  Further, if you are losing any packets within the network, the retransmission time can be 250ms or more.  

If your server is "typical", then you'll probably see server side response time at 0.1ms or lower, and with a decent network (RTT time of 0.3ms or less), you're looking at 0.4ms total per request.  Based on this estimate, a process reading 1000 records will complete in 0.4 seconds.  Now, let's assume that you lose just TWO packets out of that 1000 -- adding in the 250ms delay for each lost packet changes your total process time to 0.9s -- more than DOUBLE the time!  Now, extend this to 100,000 requests, and you see where even minimal packet loss can be REALLY bad.  (Now you see why I worry about your 213ms latency estimates.)

A few tips: Be sure to check the expert analysis for TCP Retransmission counts in Wireshark, because this is much easier than digging through the trace, and be sure to change the Time Display Format to Seconds Since Previous Displayed Packet, so that you don't have to calculate response times in your head.

Another thing is to check your PSQL configuration.  I need this data:
- Which PSQL engine is installed -- Workgroup or Server?
- How big is your database file set?
- What is your database engine setting for:
   - Cache Allocation
   - Max Microkernel Memory Usage
   - Use System Cache
- How much FREE memory is available in your OS?
TheNerdherderAuthor Commented:
I started to gather all your requested information however the director of the accounting department has now went over my head and we have a new tech person from SAGE working on the issue.  I will try and get the info but I have been taken off the problem.
Bill BachPresidentCommented:
Understandable.  Depending on who you get, they will either suggest reinstalling and then give up, or they can troubleshoot the environment properly.  You might do well to keep this open in the meantime...
All Courses

From novice to tech pro — start learning today.