Re: Unexpected transaction behaviour

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 6 December 2016 at 21:40, Rodrigo Garcia <rjsdg(at)sapo(dot)pt> wrote:

> Hello,
>
> I have a OLTP system supported by a Java standalone app using JDBC 42
> Driver postgresql-jdbc-9.4-1212 and a backend DB running in "PostgreSQL
> 9.6.1, compiled by Visual C++ build 1800, 64-bit”. The Os is Windows 7 64
> bit.
>
> The system normal throughput is about 110 transactions/min constantly
> without degradation. Recently a new Java module of the application was
> installed and when the app startups the system throughput starts to degrade
> to the point that reaches 50 transactions/min, it’s not an immediate
> downfall in the throughput but it looses like 5 to 10 transactions per
> minute until it reaches 50 transactions per minute. At this point if we
> terminate the only existing backend pid of the new Java module (that is in
> “idle in transaction” with a backend_xmin value) immediatly in the next
> minute the throughput rises again to 110 transactions/min.
>
> We’re not being able to identify the root cause for this behaviour, but we
> found out that this occurs only when we use JDBC Driver via Java and is
> reproducible whenever we use JDBC driver with a small piece of Java code
> (simulating the new Java module). At POINT 4 below we stay with a “idle in
> transaction” session and a backend_xmin value corresponding to the snapshot
> of the moment when the “select * from x” query was fired. This doesn’t
> happen when we connect via psql and reproduce the similar actions the issue
> is not occuring, this is, with psql we stay at POINT 4 with a “idle in
> transaction” session but without a backend_xmin value.
>

Why are you not closing this transaction and connection ?

>
> Reading PG documentation this behaviour around backend_xmin depends on the
> transaction isolation level, the snapshot collection is diferent. We
> suspect that the loss of throughput resides in the fact that the “idle in
> transaction” with a backend_xmin avoids or causes delays in the MVCC
> versioning core system of Postgres. Nevertheless we don’t understang why it
> happens only when using JDBC.
>

See my coomment above.

>
> The new Java module does the following code:
>
> Class.forName("org.postgresql.Driver");
> String url = "jdbc:postgresql://127.0.0.1:5433/postgres";
> Properties props = new Properties();
> props.setProperty("user”,"postgres");
> props.setProperty("password”,”**********");
> Connection conn = DriverManager.getConnection(url,props);
> conn.setAutoCommit(true);
>
use setAutoCommit(false) instead of the line below

> PreparedStatement st = conn.prepareStatement("BEGIN");
>
st.execute();
> st = conn.prepareStatement("select * from x");
> st.execute();
> //POINT 4 => At this point pg_stat_activity shows "idle in transaction”
> and a backend_xmin value filled
> Thread.sleep(300000);
> conn.close();
>

>
> The same actions via PSQL (after connect) are:
>
> BEGIN;
> select * from x;
> => At this point pg_stat_activity shows "idle in transaction” and
> backend_xmin is null
>
> Why the differente behaviour? We understand that PSQL and JDBC use
> different protocols but in terms of transaction behaviour should be the
> same, the Java code and PSQL are equivalent in terms of transaction
> behaviour. Can someone help?
>

JDBC uses server prepared statements for everything. psql does not.

Dave