ODBC driver locks up, will using multiple drivers help?

Posted on 2008-10-21
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
Question by:HouseofFara
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 28

Accepted Solution

Bill Bach earned 500 total points
ID: 22769220
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...

Author Comment

ID: 22769891
Thanks Bill.... I will give your suggestions a try and see what happens.  It will take me a while to do so.

Author Comment

ID: 22770629
 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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 28

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

LVL 28

Expert Comment

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

Author Closing Comment

ID: 31508372
Whlie I have not confirmed the problem has been completely resolved, the suggestions given were very helpful and allowed me to investigate further.

Featured Post

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…

717 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