4

I have a java scraper that spawns around 1,500-2,100 threads, each of which connects to the same database via jdbc and makes inserts. These threads insert quite frequently.

It seems like the db crashes when there are too many connections. I always get to spawning 210 threads or connections when all threads lose connection. The following is the log msgs that get generated on the server side.

2015-07-15 20:18:37 UTC [10825-21] LOG:  checkpointer process (PID 13435) was terminated by signal 9: Killed
2015-07-15 20:18:37 UTC [10825-22] LOG:  terminating any other active server processes
2015-07-15 20:18:37 UTC [16836-1] user@db WARNING:  terminating connection because of crash of another server process
2015-07-15 20:18:37 UTC [16836-2] user@db DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-07-15 20:18:37 UTC [16836-3] user@db HINT:  In a moment you should be able to reconnect to the database and repeat your command.

The following is the error msg displayed on the client(scraper) side.

SEVERE: An I/O error occurred while sending to the backend.
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:366)
    at SocketBot.run(SocketBot.java:167)
Caused by: java.io.EOFException
    at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:284)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1803)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    ... 4 more

In the postgresql.conf file, I set max connections = 2500. But I'm thinking there are more things to adjust here. My server has 512MB RAM if anyone would like to know.

  • 1
    seems Linux out-of-memory (OOM) killer killed the process – Federico Sierra Jul 15 '15 at 21:11
  • Is 512MB RAM not enough for what I hope to do? Or is there a setting I need to adjust? – jeebface Jul 15 '15 at 21:13
  • 1
    Maybe, I don't know, take a look serverfault.com/questions/384686/… – Federico Sierra Jul 15 '15 at 21:20
  • Thanks for pointing me in the right direction. I found out each connection to db is consuming about 3% of memory and this is definitely not how I should handle things. I'll work on reducing the number of concurrent connections by streaming all inserts commands to something like 20 threads and see how it goes from there. – jeebface Jul 15 '15 at 21:36
5

Linux's out-of-memory killer is probably terminating processes. This means your server is misconfigured. It's strongly advised that you not let Linux overcommit memory, so it responds with a proper out-of-memory error to the application instead of killing processes almost at random. PostgreSQL is designed to handle out-of-memory conditions, but cannot do so if Linux kills processes instead of reporting out-of-memory.

To confirm that's what is happening here, check the kernel message log with the dmesg command.

See http://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.