Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.


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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

If you're a modern-day technology professional, you may be wondering if certifications are really necessary. They are. Here's why.
What we learned in Webroot's webinar on multi-vector protection.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

670 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