Hello all.
We have a problem that we've been working on with a client for several months that we cannot resolve. The environment is as follows.
Server (not sure of server details, but an adequate box I'm told) is running Interbase 6.0.1.6 with 1 user and 1 database (ours) which is used to track timesheets.
On the same machine we have a Delphi 5 application running as a service which we call a 'Transaction Server' which is the layer that interacts with the Interbase database using the BDE (version 5.2) and the clients which are ActiveX controls running in IE.
The problem we see from our logs is that the connection from our 'Transaction Server' to the Interbase Server seems to fail during heavy periods of use with messages like "General SQL error.'#13#10'Error reading data from the connection." or our own generated errors such as 'Unable to Process Request' which we display when we get an unexpected response from the database server. During the week when there is less activity, things seem to go along without interuption and the service is not restarted, but on Friday or Monday when timesheets are due and the volume is up it's as if the IB Server cannot handle the requests fast enough.
We have taken their GDB file and run several tests here with it and it always behaves properly although we cannot really simulate the traffic this client has, but we have literally hundreds of other clients who have literally hundreds more users than this client does and the other clients do not display the same problems AT ALL. Note that 'most' other clients are using either SQL Server as their back-end or Oracle with the same software and version, but we do have other Interbase database clients who do not encounter these issues.
All that to say we think it's environmental but don't know where else to look.
We've checked and optimized the BDE, we've made sure we're all using the same version of the application, we've run their database and heavy processes here. All without issue.
We've looked at the Interbase log, but it doesn't really tell us anything.
Are there better tools to examine the Interbase database?
Does Interbase keep any better statistics about it's failures, or being overloaded or anything liek that that might help us?
Any suggestions are VERY welcome.
Thanks
HMSTechSupport
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The sweep interval on the database is set to 20,000 and the database has 6433 allocated pages, and a page size of 4096.
We use the free version of Interbase so we can bundle it with our software. I have looked at Firebird, but we had issues with reserved words we use throughout our application so we made the choice to stick to IB 6.0.1.6 and not have to rewrite our application.
The database dialect is set to 1.
I am currently reading a bunch of articles that seem to point to this being a network or NIC problem (URL below). Have you seen this before? The articles appear to be several years old, so I am not sure if they are accurate or not.
http://groups.google.ca/gr
Thanks Kacor. I appreciate the assistance.
I do not think though that the client has the skill (sorry to say that) to perform a sweep or a backup of the database. Our bundling of the free version database was partly an effort to hide the complexities of a database from the client.
Here are the highlights that I've come across.
--------------------------
OGCAL-MWTS01 (Client) Mon Jul 18 07:13:04 2005
INET/inet_error: send errno = 10054
OGCAL-MWTS01 (Client) Mon Jul 18 07:13:04 2005
INET/inet_error: send errno = 10054
OGCAL-MWTS01 (Client) Mon Jul 18 07:13:04 2005
REMOTE INTERFACE/gds__detach: Unsuccesful detach from database.
Uncommitted work may have been lost
OGCAL-MWTS01 (Client) Mon Jul 18 07:13:04 2005
INET/inet_error: send errno = 10054
OGCAL-MWTS01 (Client) Mon Jul 18 07:13:04 2005
INET/inet_error: send errno = 10054
OGCAL-MWTS01 (Client) Mon Jul 18 07:13:04 2005
REMOTE INTERFACE/gds__detach: Unsuccesful detach from database.
Uncommitted work may have been lost
OGCAL-MWTS01 (Client) Mon Jul 18 07:13:04 2005
INET/inet_error: send errno = 10054
----
---- From newsgroups
The interbase log is getting a few 10054 errors. I cannot reproduce them.
the error code is ISC335544726 Error Reading data from the connection:
Does anybody know what could be causing this?
Jeff Overcash (TeamB) Oct 23 2001, 9:17 am show options
Newsgroups: mers.interbase.list
From: "Jeff Overcash (TeamB)" <overc...@onramp.net> - Find messages by this author
Date: Mon, 22 Oct 2001 22:25:23 -0400
Local: Mon, Oct 22 2001 10:25 pm
Subject: Re: 10054 errors in interbase log
Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse
10054 tend to be a physical problem with the network. Usually a bad NIC.
----
> server using BDE (in 1-5 threads).
> From time to time "Error reading data from the connection" occurs.
> I don't know why.
My first guess would be one of the following:
1) Network problem
2) Threading problem
In general, when multithreading using IB you must have a separate
connection to the DB for each thread, and the connection must be
"remote" (use "localhost" as the host name if the server is running on
the same machine as the client -- using "localhost" is considered a
"remote" connection).
The BDE in particular seems to have problems with multithreading on IB,
especially if three or more threads access the server concurrently. The
BDE requires a separate TSession for each thread, but even when this is
done you sometimes see problems. The IBX components do not have this
problem.
----
---- More
This usually means one of two things:
o Your network is having serious problems
o The IB server has crashed.
You can figure out which one is the problem by looking at interbase.log
on the server. If you see messages about IBGuardian restarting the
server, then the server has crashed. If you don't see any messages at
the time you saw this message, then you're having network problems.
If the server is crashing on a simple SELECT, then your DB is probably
corrupt. You should do a database validation in IBConsole and then
figure out what made your DB corrupt in the first place. In particular,
make sure you have IB 6.0.1.6 and that Forced Writes are ON.
----
All connections lost at once with error 10054 - by Borland Developer Support Staff
Rating: \l "rating" \l "rating"Ratings: 6 Rate it
Abstract: Some sort of network error occured
Problem:
All connections lost at once with error 10054 in interbase.log. It can
happen with both Service pack 2 & 3 for NT 4.0. InterBase Server is
still running on the server.
Solution:
Error 10054 is a winsock error for connection reset. You can find the
list of winsock errors in the winsock.h file.
Since the InterBase Server is up and running at the time of the problem,
it's an indication that it's not an InterBase issue but rather a network
issue. Check the following:
* At the time of the problem, what's the traffic load? Check for
traffic loads that can flood the network. Is there anything scheduled
to flood the network at certain time?
* Check for network hardware problems such as bad switch, router,
hub, etc.
* Another possible scenario involves having more than one protocol
bound to the same network card. In one case a customer had TCP/IP,
IPX/SPX, and RIP for IPX bound to the same ethernet card. When he
unbound IPX/SPX and RIP for IPX from the card, the errors disappeared.
----
You must connect as "remote" (use localhost as the host name if
the IB server and client are running on the same machine) and you must
not attempt to concurrently access the same connection to IB from two
different threads, except in the case of asynchronous query cancellation
----
Thanks Kacor.
I appreciate the time you took to examine the case for me. I have convinced the client to change the network card about 10 minutes ago, so now we watch and see.
Do you know of better logs than the interbase.log or a tool to get better logs from Interbase?
Are there ways to tweak the server to use more RAM, or less RAM or any configuration capabilities within the Server part of Interbase?
Thanks
From the IB6 Operation Manual
Sizing memory
It is important to equip your server with a sufficient amount of physical memory to
ensure good performance.
While InterBase can function in a low-profile hardware configuration, with as little as
32MB of RAM on most operating systems, it is recommended to have at least 64MB of
RAM on a server system. Database servers that experience a high load can benefit from
more RAM.
The base RAM requirement of the ibserver executable and for each connected user is low:
approximately 1500KB, plus 28KB for each client connection. ibserver caches metadata
and data for each database to which it connects. User operations such as sorting
temporarily consume additional memory. A heavily loaded server with dozens of clients
performing concurrent queries requires up to 256MB of RAM.
On Windows NT, you can use the Task Manager, Performance Monitor, and other tools
to monitor the resource use of ibserver. UNIX and Linux servers have similar resource
consumption reporting tools. Add RAM to a system that shows too many page faults.
From the IB6 Operation Manual
Network cables develop flaws surprisingly frequently. The result can be sporadic lost
packets, for which operating systems compensate by automatically resending packets.
This translates into mysterious network performance degradation. You should test
network cables regularly. Replacing flawed cables is a low-cost way to keep your
network running at peak efficiency.
In addition to "Comment from hmstechsupport Date: 09/14/2005 10:32PM EEST" we indentifyed another cause of this type of gds_db broker crash - an overflow caused by external function library. Are you using some UDF's ? If yes, please take a look of them and see if they corectly free the memory on exit. If not, UDF's must be re-written or at lest recompiled with correct compiler options. The problem has the same "appearance" on Firebird/Interbase on Windows ,Linux and FreeBSD.
hi,
I am sure that this is network problem - we have encountered similar ones with 10054 errors and various network errors. The problem was in bad network cables - some packets were lost and this causes connection to be dropped.
The problem is that you say they are in same machine - database client and server, so I cannot imagine how this can happen.
If you use connection by IP address it still goes thru IP stack and there can be some failures in network drivers...try update draivers also (you said that NIC is changed).
Why not try using local connection - directly path to database in bde configuration... (BDE is not supported, also IB 6 and there are recommendations how to recover from such errors but rewriting of application is not a case)
Could not get it to work locally. When I try to change the BDE setting from "SERVERNAME:C:\path\file.g
We're still working with the client (long distance) to try to help, but so far have been unsuccessful.
Business Accounts
Answer for Membership
by: NickUpsonPosted on 2005-09-14 at 09:55:02ID: 14882430
what is the sweep interval set to? It could be the sweep is starting in the heavy load periods and it's that extra load that causes the failure.
The interbase log should say something in the relevant time period.
ib6 was known to have several bugs, can you upgrade to either ib7 (purchase from borland) or firebird (free download from www.ibphoenix.com)