3

So I've been tracking a bug for a day or two now which happens out on a remote server that I have little control over. The ins and outs of my code are, I provide a jar file to our UI team, which wraps postgres and provides storage for data that users import. The import process is very slow due to multiple reasons, one of which is that the users are importing unpredictable, large amounts of data (which we can't really cut down on). This has lead to a whole plethora of time out issues.

After some preliminary investigation, I've narrowed it down to the jdbc to the postgres database is timing out. I had a lot of trouble replicating this on my local test setup, but have finally managed to by reducing the 'socketTimeout' of the connection properties to 10s (there's more than 10s between each call made on the connection).

My question now is, what is the best way to keep this alive? I've set the 'tcpKeepAlive' to true, but this doesn't seem to have an effect, do I need to poll the connection manually or something? From what I've read, I'm assuming that polling is automatic, and is controlled by the OS. If this is true, I don't really have control of the OS settings in the run environment, what would be the best way to handle this?

I was considering testing the connection each time it is used, and if it has timed out, I will just create a new one. Would this be the correct course of action or is there a better way to keep the connection alive? I've just taken a look at this post where people are suggesting that you should open and close a connection per query: When my app loses connection, how should I recover it?

In my situation, I have a series of sequential inserts which take place on a single thread, if a single one fails, they all fail. To achieve this I've used transactions:

m_Connection.setAutoCommit(false); 
m_TransactionSave = m_Connection.setSavepoint();

// Do something

m_Connection.commit(); 
m_TransactionSave = null; 
m_Connection.setAutoCommit(true);

If I do keep reconnecting, or use a connection pool like PGBouncer (like someone suggested in comments), how do I persist this transaction across them?

Community
  • 1
  • 1
Andy
  • 3,228
  • 8
  • 40
  • 65

1 Answers1

5

JDBC connections to PostGres can be configured with a keep-alive setting. An issue was raised against this functionality here: JDBC keep alive issue. Additionally, there's the parameter help page.

From the notes on that, you can add the following to your connection parameters for the JDBC connection:

tcpKeepAlive=true;

Reducing the socketTimeout should make things worse, not better. The socketTimeout is a measure of how long a connection should wait when it expects data to arrive, but it has not. Making that longer, not shorter would be my instinct.

Is it possible that you are using PGBouncer? That process will actively kill connections from the server side if there is no activity.

Finally, if you are running on Linux, you can change the TCP keep alive settings with: keep alive settings. I am sure something similar exists for Windows.

rolfl
  • 17,539
  • 7
  • 42
  • 76
  • Thanks for the response. I'll look into it. I as stated, I have used tcpKeepAlive=true; which doesn't appear to do anything (I'm assuming because I haven't changed the OS setting on my local machine, which is Windows based). As for the timeout, as stated I've intentionally made it worse on my local machines to validate it was a timeout issue. On the live server the timeout will be something reasonable. I am not currently using PGBouncer, I'm not familiar with using connection pools, but I have some worries about how it would interact with my transactions. – Andy Oct 01 '14 at 14:00