Solved

ODBC driver locks up, will using multiple drivers help?

Posted on 2008-10-21
9
750 Views
Last Modified: 2012-05-05
I have an AccPac Order Entry system which runs on Pervasive.  All the reports in AccPac are written in Crystal and use the ODBC driver.  I have developed several programs that also connect into the Pervasive database via the same ODBC driver.   These programs both read and write data to the database.

The ODBC driver has started to intermittently lock up and only restarting the server seems to clear it.  
The database still works, just the ODBC connections freeze.  Since all programs are using the same ODBC driver name, I was wondering if I created 3 or 4 ODBC drivers to the database and have certain programs use certain named drivers, I might be able to see which group of programs is the culprit.
(I have been unable to find the cause of the lock ups)

This would only work if the ODBC drivers would work independantly of each other.  So my question is this concept plausible?  If I create 3 ODBC drivers on the server to the same database, will they be completely separate and run via different threads?  Any thoughts much appreciated.

Thanks, -Bob
0
Comment
Question by:HouseofFara
  • 5
  • 4
9 Comments
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
Comment Utility
There are two types of DSN's in Pervasive, Engine DSN's (that exist at the engine machine, or server), and Client DSN's (that exist on the client machine, or workstation).  

You could first try creating split Client DSN's at the workstation and see if that helps, but it might not.  Most problems like what you describe are problems inside the engine itself (on the server).  I also doubt that the problem is in just one DSN, as it is likely an issue with the entire SQL engine itself.  (You may see that Accpac continues to work because it uses the Btrieve interface, or the Transaction service, instead of the Relational/SQL layer.)  However, I would suspect that the entire SQL engine is failing, based on the understanding that each new SQL connection gets its own thread on the server from the communications layer, and should be totally independent outside of the engine itself.

So, what do we do?  

First, check your memory.  Start up PerfMon and add a parameter from the Process Group, and select Virtual Bytes (Peak) on the left side, and NTDBSMGR.EXE on the right side.  Monitor this value.  It MUST always be far below 2.1M bytes (2GB).  The PSQL engine utilizes Winsock for communications, and Winsock has an issue when the calling application runs low on the virtual memory space.  (The 32-bit Windows OS limits virtual address space to 2GB for all 32-bit processes.)  If you are running out of memory space, try reducing the memory usage by shrinking the L1 or L2 cache settings in the database engine.  Or, try reducing the number of communications or IO threads, as these get 1MB stack space each, and some people link to know these numbers up to their maximum.

Second, check the OS itself -- is the service hanging, or is it just busy? You can watch the NTDBSMGR process in PerfMon for CPU usage and see if it is hanging, crashing, or is simply otherwise busy working on a big, bad query.  (You can also see this from the Pervasive Monitor tool, too, if you can watch the number of records read in the Microkernel/Active Users screen by watching the SRDE users.)

There are other things to try, such as patching to the latest 9.52 update available from Pervasive's web site, which might already contain a fix.  However, you'll want to check with Accpac tech support to verify that this is OK to use...
0
 

Author Comment

by:HouseofFara
Comment Utility
Thanks Bill.... I will give your suggestions a try and see what happens.  It will take me a while to do so.
-Bob
0
 

Author Comment

by:HouseofFara
Comment Utility
Bill,
 I checked the PerfMon and NTBSMGR is running an ave. of 668,862,534 with a max so far of 671,051,776.   So I guess this is looking OK, but I will continue to monitor it.   Is it normal for this memory usage to be almost a straight line?  I has varied but very little off the average.
The CPU usage of NTBSMGR is running an average of 2% with an occasional spike of 8-9% for a 3-4 seconds.  (This is a dual processor 3GHZ server with plenty of ram)

I think I will give the split Engine DSN a try.  I have 20+ users tying into the one ODBC connection and the issue is definitely with ODBC.  As you stated, Accpac continues to work fine as it uses Btrieve except for reports, which will not work.   This all happens on a random basis so far.  I might go weeks between hang ups and then like today, it has happened twice within an hour.  Nothing odd happening during that hour that I can detect.
0
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
If you are looking at "Virtual Bytes Peak", then yes, it will be a straight line.  If you monitor the "Virtual Bytes", then it should be close, but not exactly straight -- much depends on other memory demands of the OS and if you have L2 cache enabled.

Finding a random hang like this will likely be quite difficult.  Again, start with Accpac and find out if they support the latest patches (v9.52) from Pervasive.  If so, get them installed on the client and server ASAP.

Usually, to find a problem like this, I load up Wireshark (or another network analyzer) on the server and capture all traffic going to/from port 1583.  When the system fails, it's an easy task to stop the capture and review the last few commands sent into the server.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:HouseofFara
Comment Utility
Bill,
 You have pointed me in the right direction, now to find a solution.  I restarted the Pervasive service last night and using the PerfMon virtual bytes monitor (not peak), is showed 0 bytes.   I started a session of AccPac and it went to 300 meg.  I existd AccPac and it stayed at 300 meg.  I opened AccPac and ran a report (which is ODBC based) and it jumped to 800 meg.  Exited AccPac (no other users on the system at this time) and memory usage stayed at 800 meg.  I stopped the service and it dropped to 0 and did the same test again.   Again memory usage did not release.   This is why my earlier post stated the graph was flat lining.   Conducting another test, I went to 2 work stations, loaded AccPac and ran a report on each.  I started up a program that I wrote that simply reads data from the ODBC connection.  Memory usage jumped to 1.2 Gig and again stayed there.   I must assume that if 4 or 5 people ran reports and hit my program at the same time, the effect could bump us over the 2 Gig limit as the usage seems to be accumilitive and not releasing.

Do you know of any settings that is causing the memory not to release or do I need to get with Pervasive?    Thanks!  - Bob
0
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
What is your L1 and L2 cache set to?  For a server with lots of memory, this could simply be the normal allocation, and if the L2 cache grows and nothing else inside the server is requesting RAM, then the L2 cache has no need to release it.  In short, watching it at powerup doesn't help much.  You'll be much better off watching it long-term and simply verifying that it doesn't get too close to the 2GB mark.
0
 

Author Comment

by:HouseofFara
Comment Utility
The L1 cache in Pervasive is set to 599,999,488 (600 meg)
The L2 cache is set to 60%.  
(I have 4 gig on the server.  Taskmgr reports 2.6 gig avail. 2.4 gig System cache)

The only other programs running on this server is Veritas backup and a package that is using
the SQLExpress on occasion.  I would not expect them to be of issue.

So the memory not being released may not be of issue?  Mixed feelings on this.. Positive in
that it may not be a problem.... negative in that I thought perhaps I had found the issue!

Oh well, back to the drawing board.

-Bob
0
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
If you want "static" memory usage, go ahead & disable the L2 cache.  Having an L1 cache of 600MB should be plenty for most systems.  If your data is less than a few GB, then 600MB should be plenty.  It is usually the L2 cache that grows to the OS limit anyway, so setting this value from 60% to 0% will prevent the over-use of memory.  You may see some performance degradation on a larger, more active system, but let's see if the problem goes away...
0
 

Author Closing Comment

by:HouseofFara
Comment Utility
Whlie I have not confirmed the problem has been completely resolved, the suggestions given were very helpful and allowed me to investigate further.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

10 Experts available now in Live!

Get 1:1 Help Now