Re: [firebird-support] Re: Handling deadlocks with classic mode
>Thanks for the response. We are running Windows. I'm not sure how much > information about the fb_inet_server processes I can gather -- other than how > long they've been running. Not sure how to get the IP address of the > connecting client. I'm going to just start killing the oldest fb_inet_server > processes, but I don't know how I can distinguish the listener process. If > I kill that one by mistake, I guess I'll have to reboot the whole server > because I don't know how to restart just the listener. > > Is there a way to identify the listener process? If it runs as a Windows service, open SysInternals ProcessExplorer. You should see a fb_inet_server.exe process under the winit.exe/system.exe node. Restarting the listener process is simply restaring the service (if it runs as a Windows service) or by executing fb_inet_server.exe with the -a (as an application) switch. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ > vince > > --- In firebird-support@yahoogroups.com, Steve Wiser wrote: >> >> What OS are you running on the server? For Classic, if you can find the >> offending process you can just kill that particular process to resolve >> deadlocks or long running queries. We normally use linux and find the >> processes using lsof to get the open connection to the db tied back to the >> IP address of the offending computer. I am not sure how to do that on >> Windows though. >> >> -steve >> >> -- >> Steve Wiser >> President >> Specialized Business Software >> 6325 Cochran Road, Unit 1 >> Solon, OH 44139 >> >> www.specializedbusinesssoftware.com >> www.docunym.com >> (440) 542-9145 - fax (440) 542-9143 >> Toll Free: (866) 328-4936 >> >> >> >> >> On Tue, Oct 18, 2011 at 9:44 PM, vincent999x wrote: >> >>> ** >>> >>> >>> hello, >>> We recently switched from superserver mode to classic mode (still stuck on >>> Firebird 1.5 unfortunately) to take advantage of the 2nd CPU on the server >>> and to prevent intensive queries from slowing down the whole system. It has >>> worked out well except that we seem to see more deadlocks now. >>> >>> Currently, we have one user who has been unable to update a record for 3 >>> days now (keeps getting a deadlock condition), so it seems that as a last >>> resort I might have to restart Firebird to break this deadlock. >>> >>> Here's my question -- how do I do that? Do I have to kill all of the >>> fb_inet_server processes? The easiest way for me is to reboot the server, >>> but is there a better way? We have a GUI tool (Easy-IP DB manager), but it >>> only kills the Firebird listener process. >>> >>> Two other things I've noticed with classic vs. superserver: >>> 1) I'm having more trouble altering stored procedures. It seems classic >>> mode won't let you update a stored procedure if it's currently being run. I >>> don't recall this problem with superserver mode. >>> 2) In superserver mode, when I abnormally terminated from IBExpert (our SQL >>> query tool) due to the query taking too long -- the system response time >>> improved immediately. However, in classic mode, the fb_inet_server process >>> continues to run even though I abnormally terminated from IBExpert. >>> >>> Thanks in advance for any assistance. I know the long-term solution is to >>> write better code, but for the short-term I need a way to break deadlocks >>> without rebooting the database server constantly. >>> >>> vince >>> >>> >>> >>> >>> >>> This message and any files transmitted with it may contain information that >>> is privileged, confidential, and exempt from disclosure under applicable >>> law. They are intended solely for the use of the intended recipient. If >>> you are not the intended recipient, distributing, copying, disclosing, or >>> reliance on the contents of this communication is strictly prohibited. If >>> this has reached you in error, kindly destroy this message and notify the >>> sender immediately. Thank you for your assistance. >>> >>> We attempt to sweep harmful content (e.g. viruses) from e-mail and >>> attachments, however we cannot guarantee their safety and can accept no >>> liability for any resulting damage. The recipient is responsible to verify >>> the safety of this message and any attachments before accepting them. >> >> >> [Non-text portions of this message have been removed] >> > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++ > Yahoo! Groups Links > > >
Re: [firebird-support] Great variance in speed of an INSERT INTO ... SELECT
Hi Ann, > The WHERE 1 = 1 is unnecessary, but doen't do any harm. There maybe > databases that require a WHERE clause, but Firebird doesn't. But that's not > the problem. I know. But I wanted to emphasise that there are no limitations on the rows, so no index should come into play. > The normal cause of wild variations in the performance of queries is garbage > collection. For example, if you did this query twice, deleting all rows in > backuptable, then resetting the generator backupgen to 1, the second run > would have to remove all the old rows and deleted stubs and clean up the > indexes (assuming that source_key is actually a unique key). Garbage collection doesn't seem to play any significant part. I even deleted the whole backup table, performed the backup/restore cycle, and still got long times. What *did* help was limiting the SELECT from sourcetable to "significant records", some 6000 of them (a reduction of two orders of magnitude). But it still seems strange that Firebird would choke on INSERTs, even in what I perceive to be the ideal condictions to the server. Pepak ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Re: Handling deadlocks with classic mode
Steve, Thanks for the response. We are running Windows. I'm not sure how much information about the fb_inet_server processes I can gather -- other than how long they've been running. Not sure how to get the IP address of the connecting client. I'm going to just start killing the oldest fb_inet_server processes, but I don't know how I can distinguish the listener process. If I kill that one by mistake, I guess I'll have to reboot the whole server because I don't know how to restart just the listener. Is there a way to identify the listener process? vince --- In firebird-support@yahoogroups.com, Steve Wiser wrote: > > What OS are you running on the server? For Classic, if you can find the > offending process you can just kill that particular process to resolve > deadlocks or long running queries. We normally use linux and find the > processes using lsof to get the open connection to the db tied back to the > IP address of the offending computer. I am not sure how to do that on > Windows though. > > -steve > > -- > Steve Wiser > President > Specialized Business Software > 6325 Cochran Road, Unit 1 > Solon, OH 44139 > > www.specializedbusinesssoftware.com > www.docunym.com > (440) 542-9145 - fax (440) 542-9143 > Toll Free: (866) 328-4936 > > > > > On Tue, Oct 18, 2011 at 9:44 PM, vincent999x wrote: > > > ** > > > > > > hello, > > We recently switched from superserver mode to classic mode (still stuck on > > Firebird 1.5 unfortunately) to take advantage of the 2nd CPU on the server > > and to prevent intensive queries from slowing down the whole system. It has > > worked out well except that we seem to see more deadlocks now. > > > > Currently, we have one user who has been unable to update a record for 3 > > days now (keeps getting a deadlock condition), so it seems that as a last > > resort I might have to restart Firebird to break this deadlock. > > > > Here's my question -- how do I do that? Do I have to kill all of the > > fb_inet_server processes? The easiest way for me is to reboot the server, > > but is there a better way? We have a GUI tool (Easy-IP DB manager), but it > > only kills the Firebird listener process. > > > > Two other things I've noticed with classic vs. superserver: > > 1) I'm having more trouble altering stored procedures. It seems classic > > mode won't let you update a stored procedure if it's currently being run. I > > don't recall this problem with superserver mode. > > 2) In superserver mode, when I abnormally terminated from IBExpert (our SQL > > query tool) due to the query taking too long -- the system response time > > improved immediately. However, in classic mode, the fb_inet_server process > > continues to run even though I abnormally terminated from IBExpert. > > > > Thanks in advance for any assistance. I know the long-term solution is to > > write better code, but for the short-term I need a way to break deadlocks > > without rebooting the database server constantly. > > > > vince > > > > > > > > > > > > This message and any files transmitted with it may contain information that > > is privileged, confidential, and exempt from disclosure under applicable > > law. They are intended solely for the use of the intended recipient. If > > you are not the intended recipient, distributing, copying, disclosing, or > > reliance on the contents of this communication is strictly prohibited. If > > this has reached you in error, kindly destroy this message and notify the > > sender immediately. Thank you for your assistance. > > > > We attempt to sweep harmful content (e.g. viruses) from e-mail and > > attachments, however we cannot guarantee their safety and can accept no > > liability for any resulting damage. The recipient is responsible to verify > > the safety of this message and any attachments before accepting them. > > > [Non-text portions of this message have been removed] >
Re: [firebird-support] How to insert only if a matching row does not exist?
At 01:40 PM 20/10/2011, Ed Grochowski wrote:
>Using SQL only (no stored procedures), how do I construct a statement to
>insert values into a database table, if and only if, a row containing a field
>with a specific value does not already exist?
>
>In Sql Server (and others), you can do things like the following:
>
>IFNOTEXISTS(SELECT1FROMemp WHEREfruits ='mango')
>INSERTINTOemp (fruits)VALUES('mango')
>
>What is the analogous way to achieve this in Firebird (2.5) ?
INSERT INTO emp (fruits) values ('mango')
where not exists (select 1 from emp where fruits = 'mango')
Look up INSERT OR UPDATE and also MERGE in the Language Reference Update. One
or both might do what you want, more efficiently than NOT EXISTS. Not enough
info here to guess what you're really going to do with it.
./heLen
Re: [firebird-support] How to insert only if a matching row does not exist?
Try to read more about UPDATE OR INSERT dml statement (
http://www.firebirdsql.org/refdocs/langrefupd21-update-or-insert.html),
might be as you expected
On Thu, Oct 20, 2011 at 7:40 AM, Ed Grochowski wrote:
> **
>
>
> Using SQL only (no stored procedures), how do I construct a statement to
> insert values into a database table, if and only if, a row containing a
> field with a specific value does not already exist?
>
> In Sql Server (and others), you can do things like the following:
>
> IFNOTEXISTS(SELECT1FROMemp WHEREfruits ='mango')
> INSERTINTOemp (fruits)VALUES('mango')
>
> What is the analogous way to achieve this in Firebird (2.5) ?
>
> [Non-text portions of this message have been removed]
>
>
>
--
Iwan Cahyadi Sugeng
[Non-text portions of this message have been removed]
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
[firebird-support] How to insert only if a matching row does not exist?
Using SQL only (no stored procedures), how do I construct a statement to
insert values into a database table, if and only if, a row containing a field
with a specific value does not already exist?
In Sql Server (and others), you can do things like the following:
IFNOTEXISTS(SELECT1FROMemp WHEREfruits ='mango')
INSERTINTOemp (fruits)VALUES('mango')
What is the analogous way to achieve this in Firebird (2.5) ?
[Non-text portions of this message have been removed]
[firebird-support] Re: Very poor insert performance
Thanks, Ann! Increasing the page cache helped a lot! I´m using UUIDs because they are easier to work with bi-diretional replication. I think they are worth the price to pay in performance loss, at least in my case (not too many inserts daily). I used sequences and intervals before, but they still require some configuration. Regards, Fabiano --- In firebird-support@yahoogroups.com, Ann Harrison wrote: > > Fabiano, > > > > I had to insert 300.000 records in a table with no indices or triggers > > except the primary key, which is type "char(16), character set octets". > > > > With the primary key, it took 40 minutes to insert all records. > > > > Removing the primary key, it took 1m40s to insert all records. > > > > Both tests were done using a just restored database, forced-writes off, > > firebird 2.5, and the same machine. > > > > Is this difference expected? Maybe it is related to the type of the primary > > key? > > > > If your primary key is a UUID (likely, given the type) and you're using > classic, increase the page cache by a lot. The problem is that UUIDs are > created in random order, so inserts occur at random places in the index. If > the particular index page needed has been dropped from the cache, it has to > be read again. Keys generated by sequences (aka generators) compress very > well as well as avoiding random access to index pages. Of course, > generators are not unique across machines, so if you require a cross-machine > unique value you must either use a compound key id> or set the generators on different machines to different ranges. > > Good luck, > > Ann > > A second problem with using UUIDs as index keys is that they compress much > less well than sequences. > > > [Non-text portions of this message have been removed] >
Re: [firebird-support] Very poor insert performance
Fabiano, > I had to insert 300.000 records in a table with no indices or triggers > except the primary key, which is type "char(16), character set octets". > > With the primary key, it took 40 minutes to insert all records. > > Removing the primary key, it took 1m40s to insert all records. > > Both tests were done using a just restored database, forced-writes off, > firebird 2.5, and the same machine. > > Is this difference expected? Maybe it is related to the type of the primary > key? > If your primary key is a UUID (likely, given the type) and you're using classic, increase the page cache by a lot. The problem is that UUIDs are created in random order, so inserts occur at random places in the index. If the particular index page needed has been dropped from the cache, it has to be read again. Keys generated by sequences (aka generators) compress very well as well as avoiding random access to index pages. Of course, generators are not unique across machines, so if you require a cross-machine unique value you must either use a compound key or set the generators on different machines to different ranges. Good luck, Ann A second problem with using UUIDs as index keys is that they compress much less well than sequences. [Non-text portions of this message have been removed]
[firebird-support] Re: Very poor insert performance (with UUID primary keys)
Hi again, I created a test case which shows the problem, using a new database. Firebird CS 2.5.1 running on Windows 7, default firebird.conf. Time to insert 200.000 records in a single-field table: /* * Integer field, no PK */ create table t1 ( id integer not null); set term !! ; execute block as declare variable i integer; begin i = 0; while (i < 20) do begin insert into t1 values (:i); i = i + 1; end end !! set term ; !! TIME: 2s199ms /* * Integer field, with PK */ create table t2 ( id integer not null); alter table t2 add constraint pkt2 primary key (id); set term !! ; execute block as declare variable i integer; begin i = 0; while (i < 20) do begin insert into t2 values (:i); i = i + 1; end end !! set term ; !! TIME: 3s869ms /* * UUID field, no PK */ create table t3 ( id char(16) character set octets not null); set term !! ; execute block as declare variable i integer; begin i = 0; while (i < 20) do begin insert into t3 values (gen_uuid()); i = i + 1; end end !! set term ; !! TIME: 2s542ms /* * UUID field, with PK */ create table t4 ( id char(16) character set octets not null); alter table t4 add constraint pkt4 primary key (id); set term !! ; execute block as declare variable i integer; begin i = 0; while (i < 20) do begin insert into t4 values (gen_uuid()); i = i + 1; end end !! set term ; !! TIME: 1m53s587ms <--- I created a new database and ran the last test again: TIME: 4m00s881ms <--- Regards, Fabiano
Re: [firebird-support] Firebird Program loses network connection
No, I that is not the cause of the problem, but for my understanding 1 single transaction should not take so long, simply he should try to separate the transactions for self-actions and with it I believe that they avoid several types of problems. Best Regards = || ISMAEL || = - Original Message - From: Tomasz Tyrakowski To: firebird-support@yahoogroups.com Sent: Wednesday, October 19, 2011 12:26 PM Subject: Re: [firebird-support] Firebird Program loses network connection On 2011-10-19 15:22, Ismael L. Donis Garcia wrote: > For me you should check the logic of the program because that is long time for a transaction. > > try try > Transaction.StartTransaction; > Query.ExecSQL; > Transaction.Commit; > except on e:exception do > begin > . > Transaction.Rollback; > end; > end; > finally > Transaction.EndTransaction; > end; > > This should delay fractions of a second. Hmm... where do you draw this conclusion from? In my opinion, your Query.ExecSQL can take an unspecified amount of time, depending on what the actual query does. I've got a couple of apps, which execute stored procedures in FB database (which in turn end up generating some hundreds of thousands of new records) via EXECUTE PROCEDURE statements, which take a good couple of minutes to complete. Of course there are worker threads on the client side and such, but the client connection is blocked until a procedure returns. So I wouldn't be so haste to blame the client code. regards Tomasz > > Best Regards > = > || ISMAEL || > = > - Original Message - > From: Norman Dunbar > To: firebird-support@yahoogroups.com > Sent: Wednesday, October 19, 2011 3:27 AM > Subject: Re: [firebird-support] Firebird Program loses network connection > > > > Morning all, > > > I have encountered a weird network problem and have no idea what might be the cause of it. > As far as I'm concerned, all network problems are weird! ;-) > > > ... > > Client connection path to the database is using TCP/IP > > Situation: Client's program frequently shows > > Error writing data to the connection > > An existing connection was forcibly closed by the remote host > That sounds to me as if the server end is killing the connection as > opposed to the connection being "lost" due to nnetwork problems or the > client end failing for some reason. > > > Client stated it happened after using the program for some time. > > As testing, I tried saving couple of big transaction on the client computer and will encountered the same error. Note: Processing time for the transaction takes less then 3 mins. > Is the transaction doing something CPU intensive, for example a huge > sort? If so, it's possible that there is no keep-alive on the network > and some configuration option on the server is killing off "dead" > connections. > > This is something I get occasionally with my Oracle database servers at > work. They are configured (and I know not how!) to drop dead connections > after a certain time. When the clients submits a huge CPU intensive > transaction, it runs and then because there's no network traffic, the > server assumes the connection has died and drops it. > > When the application tries to talk again to the server, it gets the > message that you are seeing - force closed by remote host. > > > ... > > > Testing on network so far shows it is stable. > Probbaly because there is plenty of traffic during the test. You need to > have a connection initiate with the server and then, do nothing. > Possibly an SSH to the server could be made and then simply left alone > to see if it too dies? Of course, you need to try to get a response from > the server in order to know if it has died or not which means that you > have effectively reset the aliveness of the connection. > > > Perform test by pinging the server and then transferring file to the server while the program is running. Will get the same error above but ping and file transfer continue without hiccup. > I think, but I'm not a network guru, that ping uses UDP which is not TCP. > > > Perform another test by having two program open. Only the program running the test above will crashed with error and the second program can continue being used without any problem. > Do both programs run the same large transaction? What happens if both > do? I predict both will lose the connection. Also, when the client is > running the large transaction, what does Task Manager show for the > client app - I suspect Not Responding, but I might be wrong. > > And also, while the transaction is running, check the network traffic > (start->settings->Network and Dialup connections, double click on LAN > and see what's happening) - are you seeing sent/received clocking up > packet counts while th
[firebird-support] Very poor insert performance
Hi all, I had to insert 300.000 records in a table with no indices or triggers except the primary key, which is type "char(16), character set octets". With the primary key, it took 40 minutes to insert all records. Removing the primary key, it took 1m40s to insert all records. Both tests were done using a just restored database, forced-writes off, firebird 2.5, and the same machine. Is this difference expected? Maybe it is related to the type of the primary key? Is there something i can do to improve the performance? Regards, Fabiano
Re: [firebird-support] Debian install
I have always used the packet taken down from the official place of firebird without a hitch. Best Regards = || ISMAEL || = - Original Message - From: Rick Debay To: firebird-support@yahoogroups.com Sent: Wednesday, October 19, 2011 9:52 AM Subject: [firebird-support] Debian install I'm running Debian 6.0 (squeeze) which has a package for FB 2.5.0. FB 2.5.1, is only available for Debian sid (unstable). Is there any way to get FB 2.5.1 for a production system using Debian package management? Or must I install it using the packages from Firebird? Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference. [Non-text portions of this message have been removed]
Re: [firebird-support] Firebird Program loses network connection
On 2011-10-19 15:22, Ismael L. Donis Garcia wrote: > For me you should check the logic of the program because that is long time > for a transaction. > >try try > Transaction.StartTransaction; > Query.ExecSQL; > Transaction.Commit; >except on e:exception do > begin > . > Transaction.Rollback; > end; >end; >finally > Transaction.EndTransaction; >end; > > This should delay fractions of a second. Hmm... where do you draw this conclusion from? In my opinion, your Query.ExecSQL can take an unspecified amount of time, depending on what the actual query does. I've got a couple of apps, which execute stored procedures in FB database (which in turn end up generating some hundreds of thousands of new records) via EXECUTE PROCEDURE statements, which take a good couple of minutes to complete. Of course there are worker threads on the client side and such, but the client connection is blocked until a procedure returns. So I wouldn't be so haste to blame the client code. regards Tomasz > > Best Regards > = > || ISMAEL || > = >- Original Message - >From: Norman Dunbar >To: firebird-support@yahoogroups.com >Sent: Wednesday, October 19, 2011 3:27 AM >Subject: Re: [firebird-support] Firebird Program loses network connection > > > >Morning all, > >> I have encountered a weird network problem and have no idea what might > be the cause of it. >As far as I'm concerned, all network problems are weird! ;-) > >> ... >> Client connection path to the database is using TCP/IP >> Situation: Client's program frequently shows >> Error writing data to the connection >> An existing connection was forcibly closed by the remote host >That sounds to me as if the server end is killing the connection as >opposed to the connection being "lost" due to nnetwork problems or the >client end failing for some reason. > >> Client stated it happened after using the program for some time. >> As testing, I tried saving couple of big transaction on the client > computer and will encountered the same error. Note: Processing time for the > transaction takes less then 3 mins. >Is the transaction doing something CPU intensive, for example a huge >sort? If so, it's possible that there is no keep-alive on the network >and some configuration option on the server is killing off "dead" >connections. > >This is something I get occasionally with my Oracle database servers at >work. They are configured (and I know not how!) to drop dead connections >after a certain time. When the clients submits a huge CPU intensive >transaction, it runs and then because there's no network traffic, the >server assumes the connection has died and drops it. > >When the application tries to talk again to the server, it gets the >message that you are seeing - force closed by remote host. > >> ... > >> Testing on network so far shows it is stable. >Probbaly because there is plenty of traffic during the test. You need to >have a connection initiate with the server and then, do nothing. >Possibly an SSH to the server could be made and then simply left alone >to see if it too dies? Of course, you need to try to get a response from >the server in order to know if it has died or not which means that you >have effectively reset the aliveness of the connection. > >> Perform test by pinging the server and then transferring file to the > server while the program is running. Will get the same error above but ping > and file transfer continue without hiccup. >I think, but I'm not a network guru, that ping uses UDP which is not TCP. > >> Perform another test by having two program open. Only the program > running the test above will crashed with error and the second program can > continue being used without any problem. >Do both programs run the same large transaction? What happens if both >do? I predict both will lose the connection. Also, when the client is >running the large transaction, what does Task Manager show for the >client app - I suspect Not Responding, but I might be wrong. > >And also, while the transaction is running, check the network traffic >(start->settings->Network and Dialup connections, double click on LAN >and see what's happening) - are you seeing sent/received clocking up >packet counts while the transaction is running - at the same rate as >before the transaction? Obviously there will be background traffic - >your email, other stuff, but you might get a clue. Maybe! > >> ... > >> Also suspected something is not right with the program but running the > same test at my own office with lower end computer and much heavier > transaction will not have any problem. I pretty much given up and not sure > what so unique about this client network that
Re: [firebird-support] Great variance in speed of an INSERT INTO ... SELECT
Pepak, > I wonder - what could possibly cause a great variance in speeds of this > statement? > > INSERT INTO backuptable ( > backup_key, > original_key, original_value1, ..., original_valuen > ) > SELECT GEN_ID(backupgen,1), > source_key, source_value1, ..., source_valuen > FROM sourcetable > WHERE 1=1 The WHERE 1 = 1 is unnecessary, but doen't do any harm. There maybe databases that require a WHERE clause, but Firebird doesn't. But that's not the problem. The normal cause of wild variations in the performance of queries is garbage collection. For example, if you did this query twice, deleting all rows in backuptable, then resetting the generator backupgen to 1, the second run would have to remove all the old rows and deleted stubs and clean up the indexes (assuming that source_key is actually a unique key). Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] Programatically detect shutdown state of database? IBOAdmin?
Hi - How can I from my Delphi application detect the shutdown state (normal/multi user maintenance/single user maintenance/full) of a database? Like what the gstat command line tool can tell me. I need it for a remote database too. IBOAdmin can be used to do shut down a database and bring it back online, but can it also tell me the current state? I want to do a programmatic restore, so I want to shut down the database before using gbak. But I cant find a way to make sure that a shut down has completed sucessfully.. Thanks Jacob
Re: [firebird-support] Firebird Program loses network connection
For me you should check the logic of the program because that is long time for a transaction. try try Transaction.StartTransaction; Query.ExecSQL; Transaction.Commit; except on e:exception do begin . Transaction.Rollback; end; end; finally Transaction.EndTransaction; end; This should delay fractions of a second. Best Regards = || ISMAEL || = - Original Message - From: Norman Dunbar To: firebird-support@yahoogroups.com Sent: Wednesday, October 19, 2011 3:27 AM Subject: Re: [firebird-support] Firebird Program loses network connection Morning all, > I have encountered a weird network problem and have no idea what might be the cause of it. As far as I'm concerned, all network problems are weird! ;-) > ... > Client connection path to the database is using TCP/IP > Situation: Client's program frequently shows > Error writing data to the connection > An existing connection was forcibly closed by the remote host That sounds to me as if the server end is killing the connection as opposed to the connection being "lost" due to nnetwork problems or the client end failing for some reason. > Client stated it happened after using the program for some time. > As testing, I tried saving couple of big transaction on the client computer and will encountered the same error. Note: Processing time for the transaction takes less then 3 mins. Is the transaction doing something CPU intensive, for example a huge sort? If so, it's possible that there is no keep-alive on the network and some configuration option on the server is killing off "dead" connections. This is something I get occasionally with my Oracle database servers at work. They are configured (and I know not how!) to drop dead connections after a certain time. When the clients submits a huge CPU intensive transaction, it runs and then because there's no network traffic, the server assumes the connection has died and drops it. When the application tries to talk again to the server, it gets the message that you are seeing - force closed by remote host. > ... > Testing on network so far shows it is stable. Probbaly because there is plenty of traffic during the test. You need to have a connection initiate with the server and then, do nothing. Possibly an SSH to the server could be made and then simply left alone to see if it too dies? Of course, you need to try to get a response from the server in order to know if it has died or not which means that you have effectively reset the aliveness of the connection. > Perform test by pinging the server and then transferring file to the server while the program is running. Will get the same error above but ping and file transfer continue without hiccup. I think, but I'm not a network guru, that ping uses UDP which is not TCP. > Perform another test by having two program open. Only the program running the test above will crashed with error and the second program can continue being used without any problem. Do both programs run the same large transaction? What happens if both do? I predict both will lose the connection. Also, when the client is running the large transaction, what does Task Manager show for the client app - I suspect Not Responding, but I might be wrong. And also, while the transaction is running, check the network traffic (start->settings->Network and Dialup connections, double click on LAN and see what's happening) - are you seeing sent/received clocking up packet counts while the transaction is running - at the same rate as before the transaction? Obviously there will be background traffic - your email, other stuff, but you might get a clue. Maybe! > ... > Also suspected something is not right with the program but running the same test at my own office with lower end computer and much heavier transaction will not have any problem. I pretty much given up and not sure what so unique about this client network that keep getting this error. As I said, I'm not a network guru, but it does sound familiar. I suspect that the server has been configured, somehow, to kill off dead connections and your long running transaction is thought to be dead. When you did your test on the low spec machine, were you running exactly the same transaction or a different one? Anything that talks across the network while running is going to stay alive, anything that hits the server for a long period of CPU, for example, is possibly going to be dropped. I reapet, I'm not a network guru and I'm sure others will jump in and assist you here, but the above may be worth considering. And finally, the following sage advice comes from the firebird.conf file: # Normally, Firebird uses SO_KEEPALIVE socket option to keep track of # active connections. If you do not
[firebird-support] Debian install
I'm running Debian 6.0 (squeeze) which has a package for FB 2.5.0. FB 2.5.1, is only available for Debian sid (unstable). Is there any way to get FB 2.5.1 for a production system using Debian package management? Or must I install it using the packages from Firebird? Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.
Re: [firebird-support] Handling deadlocks with classic mode
What OS are you running on the server? For Classic, if you can find the offending process you can just kill that particular process to resolve deadlocks or long running queries. We normally use linux and find the processes using lsof to get the open connection to the db tied back to the IP address of the offending computer. I am not sure how to do that on Windows though. -steve -- Steve Wiser President Specialized Business Software 6325 Cochran Road, Unit 1 Solon, OH 44139 www.specializedbusinesssoftware.com www.docunym.com (440) 542-9145 - fax (440) 542-9143 Toll Free: (866) 328-4936 On Tue, Oct 18, 2011 at 9:44 PM, vincent999x wrote: > ** > > > hello, > We recently switched from superserver mode to classic mode (still stuck on > Firebird 1.5 unfortunately) to take advantage of the 2nd CPU on the server > and to prevent intensive queries from slowing down the whole system. It has > worked out well except that we seem to see more deadlocks now. > > Currently, we have one user who has been unable to update a record for 3 > days now (keeps getting a deadlock condition), so it seems that as a last > resort I might have to restart Firebird to break this deadlock. > > Here's my question -- how do I do that? Do I have to kill all of the > fb_inet_server processes? The easiest way for me is to reboot the server, > but is there a better way? We have a GUI tool (Easy-IP DB manager), but it > only kills the Firebird listener process. > > Two other things I've noticed with classic vs. superserver: > 1) I'm having more trouble altering stored procedures. It seems classic > mode won't let you update a stored procedure if it's currently being run. I > don't recall this problem with superserver mode. > 2) In superserver mode, when I abnormally terminated from IBExpert (our SQL > query tool) due to the query taking too long -- the system response time > improved immediately. However, in classic mode, the fb_inet_server process > continues to run even though I abnormally terminated from IBExpert. > > Thanks in advance for any assistance. I know the long-term solution is to > write better code, but for the short-term I need a way to break deadlocks > without rebooting the database server constantly. > > vince > > > > > > This message and any files transmitted with it may contain information that > is privileged, confidential, and exempt from disclosure under applicable > law. They are intended solely for the use of the intended recipient. If > you are not the intended recipient, distributing, copying, disclosing, or > reliance on the contents of this communication is strictly prohibited. If > this has reached you in error, kindly destroy this message and notify the > sender immediately. Thank you for your assistance. > > We attempt to sweep harmful content (e.g. viruses) from e-mail and > attachments, however we cannot guarantee their safety and can accept no > liability for any resulting damage. The recipient is responsible to verify > the safety of this message and any attachments before accepting them. [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Firebird Program loses network connection
Hi John, I hate to give you such imprecise hint, but I've encountered a similar problem at one of our customers' and it turned out a network switch (you know, one of the "smart" ones ;) ) kept dropping connections when there was no traffic on the wire for some time. Unfortunately I can't find the exact model or even vendor of the switch. However, there's a simple test to confirm the switch is the cause: try to leave your application for a couple of minutes (in our case it was about a quarter of an hour) without any activity and check if the connection was closed (e.g. by doing something in the client app that requires the DB connection). Reconfiguration of the switch solved our problem. To be honest, I've also heard client-side firewalls cause problems from time to time, just like Mark Rotteveel wrote. Never experienced it, though. regards Tomasz On 2011-10-19 08:06, gwc8182 wrote: > Hi everyone, > > I have encountered a weird network problem and have no idea what might be the > cause of it. > > Background info: Using Firebird 2.0.5 > Program is written on Delphi with BDE. > Server is Windows 7 Ultimate > Clients are running mixed of Windows 7 Starter, Home Premium, WinXP, Vista > Home Premium. > > Client connection path to the database is using TCP/IP > > > Situation: Client's program frequently shows > Error writing data to the connection > An existing connection was forcibly closed by the remote host > > Client stated it happened after using the program for some time. > As testing, I tried saving couple of big transaction on the client computer > and will encountered the same error. Note: Processing time for the > transaction takes less then 3 mins. > > Initially thought might be related to memory but hit the same problem with a > computer with 2 GB memory. Tried adjusting memory usage in Firebird.conf file > but makes no different. Firebird.conf was then restore back to original to > continue testing for other possibility. > > Testing on network so far shows it is stable. > Perform test by pinging the server and then transferring file to the server > while the program is running. Will get the same error above but ping and file > transfer continue without hiccup. > Perform another test by having two program open. Only the program running the > test above will crashed with error and the second program can continue being > used without any problem. > > > Server Firebird's log file only recorded 10054 error. > Tried running both SuperServer and Classic mode and > tried Firebird 2.5.1 as well but no go. > > Also suspected something is not right with the program but running the same > test at my own office with lower end computer and much heavier transaction > will not have any problem. I pretty much given up and not sure what so unique > about this client network that keep getting this error. > > Thanks and regards, > John > > > -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Re: Find out how a user has connected to the database
On Wed, 19 Oct 2011 12:43:49 +0200, Thomas Steinmaurer wrote: >> But I know from Jaybird for instance, that one needs to specify specific >> properties, see: >> http://tracker.firebirdsql.org/browse/JDBC-118 >> >> But I guess this is related to the thin driver not communicating via the >> client library. Looking at it I do think that I will create a new task for Jaybird to introduce a fallback mechanism if the user did not provide a PID and/or processname. As far as I can see it should be possible to derive the PID from ManagementFactory.getRuntimeMXBean().getName() and for Sun/Oracle JVMs there should be a property called sun.java.launcher.pid. I also know that the Oracle JDBC driver by default reports itself as 'JDBC THIN CLIENT', something similar would be a good thing for Jaybird as well. >> What access technology do you use and is your mentioned gds32.dll >> version the one on the client or do we speak about a local installation >> here anyway? > > Btw, the database parameter buffer (DPB) is called: isc_dpb_process_name And isc_dpb_process_id for the pid. Mark
Re: [firebird-support] Re: Find out how a user has connected to the database
>> Thanks for the quick response. >> >> I have a few further questions... >> >> When I try and select from this table I get a message to say that it doesn't >> exist. I am logged in as sysdba - do I need to do anything else? >> >> My db is 2.1.3.18185 and the GDS32.dll is version 6.3.3.18185 >> >> I assume that the remote process property has to be set as part of the >> connection string? >> >> And finally, I assume that this is available via an api? > > The following is from the 2.1 release notes. > > - columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains non-NULL values > only if the client library is version 2.1 or higher > > - column MON$REMOTE_PROCESS can contain a non-pathname value if an > application has specified a custom process name via DPB > > But I know from Jaybird for instance, that one needs to specify specific > properties, see: > http://tracker.firebirdsql.org/browse/JDBC-118 > > But I guess this is related to the thin driver not communicating via the > client library. > > > What access technology do you use and is your mentioned gds32.dll > version the one on the client or do we speak about a local installation > here anyway? Btw, the database parameter buffer (DPB) is called: isc_dpb_process_name Worth to check out if it is set by your access components. As you have mentioned IBQuery, I guess you are using IBX, which doesn't officially support Firebird, thus I would be surprised if IBX sets this parameter. HTH. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/
[firebird-support] Re: Table alias with AS in SELECT statement supported?
;) Thanks, Paul & Helen!
Re: [firebird-support] Re: Find out how a user has connected to the database
Hi Neil, > Thanks for the quick response. > > I have a few further questions... > > When I try and select from this table I get a message to say that it doesn't > exist. I am logged in as sysdba - do I need to do anything else? > > My db is 2.1.3.18185 and the GDS32.dll is version 6.3.3.18185 > > I assume that the remote process property has to be set as part of the > connection string? > > And finally, I assume that this is available via an api? The following is from the 2.1 release notes. - columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains non-NULL values only if the client library is version 2.1 or higher - column MON$REMOTE_PROCESS can contain a non-pathname value if an application has specified a custom process name via DPB But I know from Jaybird for instance, that one needs to specify specific properties, see: http://tracker.firebirdsql.org/browse/JDBC-118 But I guess this is related to the thin driver not communicating via the client library. What access technology do you use and is your mentioned gds32.dll version the one on the client or do we speak about a local installation here anyway? -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ > Thanks > > Neil > --- In firebird-support@yahoogroups.com, Thomas Steinmaurer wrote: >> >> Neil, >> >>> I know that it is possible to see who is currently connected to a database >>> but is it possible to see how they have connected? >>> >>> By which I mean is it possible to see whether they are connected from, say, >>> IBConsole, IBQuery or an application? >> >> MON$REMOTE_PROCESS in MON$ATTACHMENT, but AFAIK this needs at least V2.1 >> of the client libraries. >> >> >> -- >> With regards, >> Thomas Steinmaurer >> >> * Upscene Productions - Database Tools for Developers >> http://www.upscene.com/ >> >> * My Blog >> http://blog.upscene.com/thomas/index.php >> >> * Firebird Foundation Committee Member >> http://www.firebirdsql.org/en/firebird-foundation/ >> > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++ > Yahoo! Groups Links > > >
[firebird-support] Re: Find out how a user has connected to the database
Hi Thomas Thanks for the quick response. I have a few further questions... When I try and select from this table I get a message to say that it doesn't exist. I am logged in as sysdba - do I need to do anything else? My db is 2.1.3.18185 and the GDS32.dll is version 6.3.3.18185 I assume that the remote process property has to be set as part of the connection string? And finally, I assume that this is available via an api? Thanks Neil --- In firebird-support@yahoogroups.com, Thomas Steinmaurer wrote: > > Neil, > > > I know that it is possible to see who is currently connected to a database > > but is it possible to see how they have connected? > > > > By which I mean is it possible to see whether they are connected from, say, > > IBConsole, IBQuery or an application? > > MON$REMOTE_PROCESS in MON$ATTACHMENT, but AFAIK this needs at least V2.1 > of the client libraries. > > > -- > With regards, > Thomas Steinmaurer > > * Upscene Productions - Database Tools for Developers > http://www.upscene.com/ > > * My Blog > http://blog.upscene.com/thomas/index.php > > * Firebird Foundation Committee Member > http://www.firebirdsql.org/en/firebird-foundation/ >
Re: [firebird-support] Database connection delay with Firebird 2.5.0
> I am using Firebird 2.5.0 Embedded with IBObjects. > > One of my customers has a pretty extreme use case for my application. > > The EXE is invoked maybe around 1000 times per day. Every time it opens > and closes one of several Firebird databases. Several instances of the > program can run in parallel, but each will use a separate database. > > Maybe once in a few days, there is a delay opening the database. > > For example, yesterday, the command > > dmIBODatabase.IB_Connection.Connect; > > took 6 hours. The database size in this case was 386MB and the main > table contains 688654 rows. > > What could cause such a delay and how can I avoid it? A VERY wild guess is that too much page buffers are allocated as 2.5 Embedded is now based on SuperClassic and not SuperServer any more, thus allocating too much RAM getting into swap to disk scenarios, but I can't believe this results in a 6h connect time. ;-) -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/
Re: [firebird-support] Find out how a user has connected to the database
Neil, > I know that it is possible to see who is currently connected to a database > but is it possible to see how they have connected? > > By which I mean is it possible to see whether they are connected from, say, > IBConsole, IBQuery or an application? MON$REMOTE_PROCESS in MON$ATTACHMENT, but AFAIK this needs at least V2.1 of the client libraries. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/
[firebird-support] Find out how a user has connected to the database
Hi I know that it is possible to see who is currently connected to a database but is it possible to see how they have connected? By which I mean is it possible to see whether they are connected from, say, IBConsole, IBQuery or an application? Thanks Neil
[firebird-support] Database connection delay with Firebird 2.5.0
Hello, I am using Firebird 2.5.0 Embedded with IBObjects. One of my customers has a pretty extreme use case for my application. The EXE is invoked maybe around 1000 times per day. Every time it opens and closes one of several Firebird databases. Several instances of the program can run in parallel, but each will use a separate database. Maybe once in a few days, there is a delay opening the database. For example, yesterday, the command dmIBODatabase.IB_Connection.Connect; took 6 hours. The database size in this case was 386MB and the main table contains 688654 rows. What could cause such a delay and how can I avoid it? Thanks for any tips. Cheers, Tobias www.superflexible.com
[firebird-support] Re: Firebird Program loses network connection
Hi, Thanks for the reply. --- In firebird-support@yahoogroups.com, Mark Rotteveel wrote: > > On Wed, 19 Oct 2011 06:06:43 -, "gwc8182" wrote: > > Hi everyone, > > > > I have encountered a weird network problem and have no idea what might > be > > the cause of it. > > > > Background info: Using Firebird 2.0.5 > > Program is written on Delphi with BDE. > > Server is Windows 7 Ultimate > > Clients are running mixed of Windows 7 Starter, Home Premium, WinXP, > Vista > > Home Premium. > > > > Client connection path to the database is using TCP/IP > > > > > > Situation: Client's program frequently shows > > Error writing data to the connection > > An existing connection was forcibly closed by the remote host > > Also check presence and configuration of virusscanners, firewalls or other > IDS systems on both client and server. In the past I have seen > virusscanners which would forcibly close connections to not-well-known or > 'suspicious' ports if a large volume of traffic was sent in a short amount > of time (I think I have seen this with McAfee and I heard that NOD32 did as > well). This was almost always on the client-side(!). > The client is running Avira without any internet/firewall component. This was disable allow with Windows Firewall during the test. Server was not installed with any antivirus currently. Thanks
[firebird-support] Re: Firebird Program loses network connection
Hi, Thanks for the reply. --- In firebird-support@yahoogroups.com, Norman Dunbar wrote: > > Morning all, > > > I have encountered a weird network problem and have no idea what might be > > the cause of it. > As far as I'm concerned, all network problems are weird! ;-) I agreed (: > > > ... > > Client connection path to the database is using TCP/IP > > Situation: Client's program frequently shows > > Error writing data to the connection > > An existing connection was forcibly closed by the remote host > That sounds to me as if the server end is killing the connection as > opposed to the connection being "lost" due to nnetwork problems or the > client end failing for some reason. > Any way for me to check if the server is actually killing the connection other than the Firebird's log file ? > > Client stated it happened after using the program for some time. > > As testing, I tried saving couple of big transaction on the client computer > > and will encountered the same error. Note: Processing time for the > > transaction takes less then 3 mins. > Is the transaction doing something CPU intensive, for example a huge > sort? If so, it's possible that there is no keep-alive on the network > and some configuration option on the server is killing off "dead" > connections. > > This is something I get occasionally with my Oracle database servers at > work. They are configured (and I know not how!) to drop dead connections > after a certain time. When the clients submits a huge CPU intensive > transaction, it runs and then because there's no network traffic, the > server assumes the connection has died and drops it. > > When the application tries to talk again to the server, it gets the > message that you are seeing - force closed by remote host. > > > ... > > > Testing on network so far shows it is stable. > Probbaly because there is plenty of traffic during the test. You need to > have a connection initiate with the server and then, do nothing. > Possibly an SSH to the server could be made and then simply left alone > to see if it too dies? Of course, you need to try to get a response from > the server in order to know if it has died or not which means that you > have effectively reset the aliveness of the connection. > > I did a testing at my office on the program. There actually a lot of traffic when the program is running. It have two steps when saving a transactions. First, it will perform checking for the transaction and it has a lot of incoming and outgoing transfer. After that, a lot of traffic on the outgoing and then data is committed to the server. CPU processing does not take more than 30% on a dual core machine. > > Perform test by pinging the server and then transferring file to the server > > while the program is running. Will get the same error above but ping and > > file transfer continue without hiccup. > I think, but I'm not a network guru, that ping uses UDP which is not TCP. > Yes, you are right, did not realise that. I also still the test using UDP connection as well. Same problem performance much worse. > > Perform another test by having two program open. Only the program running > > the test above will crashed with error and the second program can continue > > being used without any problem. > Do both programs run the same large transaction? What happens if both > do? I predict both will lose the connection. Also, when the client is > running the large transaction, what does Task Manager show for the > client app - I suspect Not Responding, but I might be wrong. Not Responding did appeared for couple of seconds at client side. It does appeared much more frequent when I test at my office though. And yes, two program will disconnect when running the same large transactions but not at the same time. > > And also, while the transaction is running, check the network traffic > (start->settings->Network and Dialup connections, double click on LAN > and see what's happening) - are you seeing sent/received clocking up > packet counts while the transaction is running - at the same rate as > before the transaction? Obviously there will be background traffic - > your email, other stuff, but you might get a clue. Maybe! > > > ... > > > Also suspected something is not right with the program but running the same > > test at my own office with lower end computer and much heavier transaction > > will not have any problem. I pretty much given up and not sure what so > > unique about this client network that keep getting this error. > As I said, I'm not a network guru, but it does sound familiar. I suspect > that the server has been configured, somehow, to kill off dead > connections and your long running transaction is thought to be dead. > > When you did your test on the low spec machine, were you running exactly > the same transaction or a different one? Anything that talks across the > network while running is going to stay a
Re: [firebird-support] Firebird Program loses network connection
On Wed, 19 Oct 2011 06:06:43 -, "gwc8182" wrote: > Hi everyone, > > I have encountered a weird network problem and have no idea what might be > the cause of it. > > Background info: Using Firebird 2.0.5 > Program is written on Delphi with BDE. > Server is Windows 7 Ultimate > Clients are running mixed of Windows 7 Starter, Home Premium, WinXP, Vista > Home Premium. > > Client connection path to the database is using TCP/IP > > > Situation: Client's program frequently shows > Error writing data to the connection > An existing connection was forcibly closed by the remote host Also check presence and configuration of virusscanners, firewalls or other IDS systems on both client and server. In the past I have seen virusscanners which would forcibly close connections to not-well-known or 'suspicious' ports if a large volume of traffic was sent in a short amount of time (I think I have seen this with McAfee and I heard that NOD32 did as well). This was almost always on the client-side(!).
[firebird-support] Great variance in speed of an INSERT INTO ... SELECT
Hi! I wonder - what could possibly cause a great variance in speeds of this statement? INSERT INTO backuptable ( backup_key, original_key, original_value1, ..., original_valuen ) SELECT GEN_ID(backupgen,1), source_key, source_value1, ..., source_valuen FROM sourcetable WHERE 1=1 -- all records The statement takes anything between minutes and hours (in different databases, the same database usually takes approximately the same time), and I just can't figure out what could cause this variance. The SELECT itself is quite fast (seconds), traversing its resultset as well (tens of seconds). Source_value* is either a DOUBLE PRECISION or an INTEGER. The backuptable only has three indices including the primary key (the other two cover INTEGERs). The size of the sourcetable is approximately 600K records, the size of backuptable approximately 26M records. The Firebird version (1.5, 2.1, 2.5) doesn't seem to have a significant effect on this). Thanks, Pepak ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Firebird Program loses network connection
Morning all, > I have encountered a weird network problem and have no idea what might be the > cause of it. As far as I'm concerned, all network problems are weird! ;-) > ... > Client connection path to the database is using TCP/IP > Situation: Client's program frequently shows > Error writing data to the connection > An existing connection was forcibly closed by the remote host That sounds to me as if the server end is killing the connection as opposed to the connection being "lost" due to nnetwork problems or the client end failing for some reason. > Client stated it happened after using the program for some time. > As testing, I tried saving couple of big transaction on the client computer > and will encountered the same error. Note: Processing time for the > transaction takes less then 3 mins. Is the transaction doing something CPU intensive, for example a huge sort? If so, it's possible that there is no keep-alive on the network and some configuration option on the server is killing off "dead" connections. This is something I get occasionally with my Oracle database servers at work. They are configured (and I know not how!) to drop dead connections after a certain time. When the clients submits a huge CPU intensive transaction, it runs and then because there's no network traffic, the server assumes the connection has died and drops it. When the application tries to talk again to the server, it gets the message that you are seeing - force closed by remote host. > ... > Testing on network so far shows it is stable. Probbaly because there is plenty of traffic during the test. You need to have a connection initiate with the server and then, do nothing. Possibly an SSH to the server could be made and then simply left alone to see if it too dies? Of course, you need to try to get a response from the server in order to know if it has died or not which means that you have effectively reset the aliveness of the connection. > Perform test by pinging the server and then transferring file to the server > while the program is running. Will get the same error above but ping and file > transfer continue without hiccup. I think, but I'm not a network guru, that ping uses UDP which is not TCP. > Perform another test by having two program open. Only the program running the > test above will crashed with error and the second program can continue being > used without any problem. Do both programs run the same large transaction? What happens if both do? I predict both will lose the connection. Also, when the client is running the large transaction, what does Task Manager show for the client app - I suspect Not Responding, but I might be wrong. And also, while the transaction is running, check the network traffic (start->settings->Network and Dialup connections, double click on LAN and see what's happening) - are you seeing sent/received clocking up packet counts while the transaction is running - at the same rate as before the transaction? Obviously there will be background traffic - your email, other stuff, but you might get a clue. Maybe! > ... > Also suspected something is not right with the program but running the same > test at my own office with lower end computer and much heavier transaction > will not have any problem. I pretty much given up and not sure what so unique > about this client network that keep getting this error. As I said, I'm not a network guru, but it does sound familiar. I suspect that the server has been configured, somehow, to kill off dead connections and your long running transaction is thought to be dead. When you did your test on the low spec machine, were you running exactly the same transaction or a different one? Anything that talks across the network while running is going to stay alive, anything that hits the server for a long period of CPU, for example, is possibly going to be dropped. I reapet, I'm not a network guru and I'm sure others will jump in and assist you here, but the above may be worth considering. And finally, the following sage advice comes from the firebird.conf file: # Normally, Firebird uses SO_KEEPALIVE socket option to keep track of # active connections. If you do not like default 2-hour keepalive # timeout then adjust your server OS settings appropriately. On # UNIX-like OS's, modify contents of /proc/sys/net/ipv4 # /tcp_keepalive_*. On Windows, follow instrutions of this article: # http://support.microsoft.com/default.aspx?kbid=140325 As Ann would say, good luck! Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
