Re: DB Fault Tolerance - network connections
- From: "dtynan" <dtynan@xxxxxxxxx>
- Date: 9 Nov 2006 19:58:47 -0800
I should have mentioned a couple of extra things....
1. Aside from PostgreSQL we'll also support Oracle.
2. The hardware & OS are pretty set in stone....thus, maybe sigaction()
(mentioned as a way to do timeouts in the DBI docs) is a good idea? I
saw a lot of conflicting stuff about alarm(), so I'm unsure if that's
the best way or we have a better way now or...?
3. Though my test script doesn't show it, we're actually using
Experimental::Exception to catch and throw errors, if that makes any
difference.
I'm hoping there's just a simple timeout variable down in the PG stuff
somewhere...i hope!
Thanks!
Dylan
dtynan wrote:
Hi,
Perl v5.8, Redhat Ent. Linux rel3 (taroon upd5), PostgreSQL 8.1.3, DBI
1.52, DBD:Pg 1.49
We have a batch submission system where users fire off jobs handled by
a Perl server which talks to a PostgreSQL database on a different phys.
system. The jobs may run several hours, with constant communication
back and forth. We would like to code in some (better/configurable)
fault tolerance into the system to handle specific types of problems.
I am currently looking at the problem where a server begins processing,
issuing SQL to Postgress, and then the network suddenly goes down. I
am attempting to simulate this (in a small way) with a simple program
that gets a database handle, then pauses for 20 seconds to allow me to
block the port on the DB server, then attempts to issue $sth->execute.
My test seems to work fine. However, what I'm seeing is that it takes
approximately 16 minutes before the execute() times out & returns
control. I would like to lower this timeout so that I can take various
actions sooner, but I can't seem to find a setting for this timeout in
DBI or in the DBD:Pg driver docs ... I'm hoping it's obvious & I'm just
missing it..? Note, if I block the port prior to running my test
program (ie before I have a database handle), it times out in about 2
minutes .... I'm assuming this stuff can be adjusted somewhere on the
DBI/DBD:PG side (as opposed to some OS setting, etc). Also, fyi, if I
unblock the port at some point during the execute() it will finish
successfully, so I think my blocking/unblocking is working ok. Oh,
sorry, correction...I'm saying execute() but really it's
selectall_arrayref() in this example.
Note that I'm using iptables on the box that is running the Postgresql
database in order to block the incoming port. My DB is running on port
5488. Following is my test program (which is running on a separate
system from 'zing', which is the hostname of the system the DB is on),
then my iptables rule, then a couple of run outputs so you can see the
time difference. Any ideas on adjusting this timeout, alternate test
methods, etc. are most welcome. Thanks, Dylan. Here's the info...
TESTPROG (run on my app system):
#!/usr/bin/perl
use DBI;
my $user = "myuser";
my $pw = "mypw";
my $dbh, $res;
my %attr = (
PrintError => 1,
RaiseError => 0
);
$dbh = DBI->connect("dbi:Pg:dbname=mydb1;host=zing;port=5488", $user,
$pw, \%attr);
exit if ! $dbh;
print "Pausing for port block.....20secs\n";
sleep(20); print "Unpausing\n";
$res = $dbh->selectall_arrayref("select name from mytesttable");
print "We are past the selectall_arrayref command\n";
COMMAND TO ADD IP BLOCK (run on DB system):
sudo iptables -A INPUT -j DROP -p tcp --destination-port 5488
COMMAND TO UNBLOCK THE IP (assuming u only have 1 iptable rule):
sudo iptables -D INPUT 1
Sample run #1 - I just let it run, no blocked ports:
date; testprog.pl; dateThu Nov 9 21:05:13 CST 2006
Pausing for port block.....20secs
Unpausing
We are past the selectall_arrayref command
Thu Nov 9 21:05:34 CST 2006
Sample run #2 - I block the port on the other sys during the sleep 20
(dbh exists):
date; testprog.pl; dateThu Nov 9 21:06:02 CST 2006
Pausing for port block.....20secs
Unpausing
DBD::Pg::db selectall_arrayref failed: could not receive data from
server: Connection timed out
We are past the selectall_arrayref command
Thu Nov 9 21:21:57 CST 2006
Sample run #3 - I block the port on the other syste prior to running
the test prog:
date; testprog.pl; dateThu Nov 9 21:00:47 CST 2006
DBI connect('dbname=mydb;host=zing;port=5488','myuser',...) failed:
could not connect to server: Connection timed out
Is the server running on host "zing" and accepting
TCP/IP connections on port 5488?
at ./testprog.pl line 12
Thu Nov 9 21:03:57 CST 2006
.
- References:
- DB Fault Tolerance - network connections
- From: dtynan
- DB Fault Tolerance - network connections
- Prev by Date: DB Fault Tolerance - network connections
- Next by Date: Re: Bind variable question
- Previous by thread: DB Fault Tolerance - network connections
- Index(es):
Relevant Pages
|
