Re: Unable to get a database connection while deleting rows

Hello Poul,
I can't advise specifically regarding your problem (I'm sure some one
will chime in soon). I will offer some general advice regarding java
and jdbc connections. You are probably already aware of this ....

Typically connections between java and database take a relatively long
time to establish (not 45 seconds though!!) so it is common practice
to use a connection pool that actually maintains a set of connections
ready-to-go. This avoids the need for connection setup time and can
dramatically increase through put in many situations.

There are many free production quality libraries that may be used,
even in commercial application. We use C3PO, but I know there are
several others such as Apache's DBCP.

I suspect that this is unlikely to address your situation, but for
future googlers it may be handy ....

-Damian

On 4/9/07, Poul Møller Hansen <freebsd(at)pbnet(dot)dk> wrote:
> I have a java application receiving data from a thousand devices on
> periodic basis.
> When receiving data the application gets a database connection, inserts
> a row and closes the connection again.
> If this process takes more than 15 seconds, the device assumes the
> connection dead and makes a new one.
>
> Sometimes a device is taken out of production and the data from it is
> deleted.
> Deleting ex. 30000 rows of a total of around 30 mill. takes about 45
> seconds.
> I expect this to be a row locking process and there is also no problem
> with inserting rows while this process is running.
> The problem is that getting the database connection can take from 1 to
> the full 45 seconds.
>
> There is nothing in the log telling me what's going on except from a lot
> of "unexpected EOF on client connection"
>
> Can anyone bring a light on what resource that can be the bottleneck ?
>
> The system is "PostgreSQL 8.1.8 on x86_64-pc-linux-gnu, compiled by GCC
> gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)"
>
>
> Thanks in advance,
> Poul
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>