Solved

Pervasive PSQL v11

Posted on 2014-04-01
12
375 Views
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:

server
     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
     
workstations
     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.
0
Comment
Question by:TheNerdherder
  • 5
  • 4
12 Comments
 
LVL 14

Expert Comment

by:Don Thomson
ID: 39969447
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39969953
Just an off-the-wall question -- are they running Trend Micro AV?
0
 
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.
0
 

Author Comment

by:TheNerdherder
ID: 39972170
DTH
 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.
0
 

Author Comment

by:TheNerdherder
ID: 39972174
Bill,
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:TheNerdherder
ID: 39972182
Bill,
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?

Thanks
0
 
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?
0
 

Author Comment

by:TheNerdherder
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.
0
 
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...
0
 

Accepted Solution

by:
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If your app took Google’s lash recently, here are the 5 most likely reasons.
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now