Go Premium for a chance to win a PS4. Enter to Win


Pervasive PSQL v11

Posted on 2014-04-01
Medium Priority
Last Modified: 2015-04-29
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.
Question by:TheNerdherder
  • 5
  • 4
LVL 14

Expert Comment

by:Don Thomson
ID: 39969447
LVL 28

Expert Comment

by:Bill Bach
ID: 39969953
Just an off-the-wall question -- are they running Trend Micro AV?
LVL 28

Expert Comment

by:Bill Bach
ID: 39969969
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.
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.


Author Comment

ID: 39972170
 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.

Author Comment

ID: 39972174
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.

Author Comment

ID: 39972182
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?

LVL 28

Expert Comment

by:Bill Bach
ID: 39972243
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?

Author Comment

ID: 39985866
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.
LVL 28

Expert Comment

by:Bill Bach
ID: 39985895
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...

Accepted Solution

TheNerdherder earned 0 total points
ID: 40015232
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.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Web hosting control panels were first developed to make it faster and easier for most users to set up and operate websites. The graphical user interface (GUI) allows users to perform tasks by pointing and clicking rather than typing highly specific…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question