debugging server connection issue

From: Stephen Constable <sjconsta(at)scisat(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: debugging server connection issue
Date: 2016-03-29 20:10:52
Message-ID: CACfdzQns4E1NemZ0reGwYsDOHZVdYjO-0HA=XMc3MivLFnEGjg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All,

I'm a new-ish sysadmin working on porting legacy scientific code from a
local server/client to new supercomputer environment. My work is mostly
done, except that my postgres database doesn't seem to be able to keep up
with the new environment. The application is written in-house in a mixture
of FORTAN 77 and C, and uses postgres BLOBS as its main data store. This
application in particular only reads from the database, it never writes,
which *should* make it easy to scale.

My main problem is that this client application is unable to connect to the
database under a modest load (32 simultaneous jobs). The client error logs
print out messages like "could not connect to server: Cannot assign
requested address" and "Cannot connect to database [runlog]!!!" (an
important database of ours). The "cannot assign requested address" message
makes me think it's a configuration issue. The logs are flooded with
hundreds of connection and disconnection notices per second. This same
code and configuration runs fine on our mid-2000's Solaris 10 box with
postgres 8.4 (albeit very slowly) but totally fails with these connection
errors on a modern Dell system running CentOS 7 or FreeBSD 10 (I tested
both) with postgres 9.4.

While the database is under load (and jobs are actively failing), select
count(*) from pg_stat_activity returns 30-34 ish connections, show
max_connections returns 100, and show superuser_reserved_connections shows
3. My only other hint is that right after a fresh install of CentOS 7 my
job success rate was around 50%, and now it has approached approximately
5%, so something is changing over time.

Does anyone have any advice or experience with similar issues?

Thanks,
Steve

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-03-29 20:21:15 Re: debugging server connection issue
Previous Message Brian Fehrle 2016-03-29 19:54:18 Re: Partitioning and ORM tools