6

I really have no idea what the problem is.

The logs read

FATAL: sorry, too many clients already

Over and over again. At first I thought that sometimes the connections lingered or were not closed properly so I tested that by connecting to the database and checking how many opened connections there were at any given time, and the answer has always been 1.

I tried to connect to the website that's using the DB and I managed to glimpse at 2 or 3 opened connections that were promptly closed when the page was done loading.

My remaining guess is that sometimes there are spikes in concurrent connections to the website and that causes the database to stop accepting new connections and somehow doesn't allow the current connections to be dropped.

I DID NOT WRITE ANY CODE THAT CONNECTS TO THE DATABASE, I'm using a pretty vanilla Django (1.7) backend that handles all of the connections.

I couldn't find anything while searching google, has anyone experienced any problems?

EDIT:

Database configuration is here(PasteBin)

Essential part:

port = 26445                # (change requires restart)
max_connections = 500           # (change requires restart)
unix_socket_directory = '/home/clearintent/webapps/norr2_db/run'        # (change requires restart)

shared_buffers = 32MB           # min 128kB
                    # (change requires restart)
log_destination = 'stderr'      # Valid values are combinations of
logging_collector = on          # Enable capturing of stderr and csvlog
log_directory = 'pg_log'        # directory where log files are written,
log_filename = 'postgresql-%a.log'  # log file name pattern,
log_truncate_on_rotation = on       # If on, an existing log file with the
log_rotation_age = 1d           # Automatic rotation of logfiles will
log_rotation_size = 0           # Automatic rotation of logfiles will
datestyle = 'iso, mdy'
lc_messages = 'C'           # locale for system error message
lc_monetary = 'C'           # locale for monetary formatting
lc_numeric = 'C'            # locale for number formatting
lc_time = 'C'               # locale for time formatting
default_text_search_config = 'pg_catalog.english'
Tomasz Jakub Rup
  • 10,502
  • 7
  • 48
  • 49
XelharK
  • 598
  • 8
  • 21
  • That is not a crash, FYI. – Craig Ringer Nov 26 '15 at 02:44
  • Please include database configuration, maybe the problem is there. – noamk Nov 26 '15 at 15:04
  • @CraigRinger I think it is, I cannot even kill the process, I need to "kill -9" it – XelharK Nov 26 '15 at 15:42
  • Check out `SELECT * FROM pg_stat_activity;` for clues. – dan-klasson Dec 01 '15 at 09:23
  • It only shows my current connection, or the website 2 or 3 connections if it's currently visited. I should probably set up a cron job to output that table into a file every minute or so so I know what's going on before the crash but I'm not sure how to do it – XelharK Dec 01 '15 at 09:33
  • Maybe the following two problems are somewhat related and would help: http://stackoverflow.com/a/2758432/3628578 and http://stackoverflow.com/a/14191857/3628578. You could also check how many idle connections are there in the background. – m_____z Dec 01 '15 at 16:22
  • Have you checked your django and web server configuration? As explained [here](https://docs.djangoproject.com/en/1.7/ref/databases/) it will maintain a connection per thread used to process a request. I could believe a heavy spike would start a lot of threads and so max out your connections. This would be even worse if you are using persistent connections. – Peter Brittain Dec 05 '15 at 23:58
  • Is your Django app running in a cluster? Is it possible that that any deadlock is being created, which keeps the connections open for ever? – Santanu Dey Dec 08 '15 at 04:04

3 Answers3

3

Sounds like something is bashing your PostgreSQL but that error alone does not crash the DB.
It simply means the latest connection attempt exceeded the amount of allowed parallel connections to the DB and it was rejected.

But, if you want to dump the amount of connections every minute, you can use this script

#!/bin/bash

function spew_connections() {
# Run psql on local if trust or auth is set.
# Change dbadmin and dbname accordingly.
/usr/bin/psql -d dbname -U dbadmin -w -t -c "SELECT localtimestamp(2), count(*) FROM pg_stat_activity;"
}

echo -n `spew_connections` >> /tmp/connections
echo >> /tmp/connections

And then, to execute it using crontab every minute

crontab -e
*/1 * * * * /path/to/executable/script
edd
  • 1,307
  • 10
  • 10
1

Try to install tools like pgBouncer between Your app and database.

PgBouncer is a lightweight connection pooler for PostgreSQL.

Most important points:

[databases]
pgbase = host=localhost dbname=bazdb

[pgbouncer]
listen_addr = *
listen_port = 6432

pool_mode = transaction

And in client connect to pgbase on localhost:6432

Tomasz Jakub Rup
  • 10,502
  • 7
  • 48
  • 49
  • Wouldn't this increase the number of connections I need? – XelharK Dec 08 '15 at 18:55
  • No. pgBoucer manage connections to DB. I had the same problem on my system. pgBoucer resolved this problem definitly. When I catch my admin, then I paste this pgBoucer config. – Tomasz Jakub Rup Dec 08 '15 at 19:00
0

Django doesn't close connections automatically.

Add

'OPTIONS': {
    'autocommit': True,
}

to Your database configuration.

Tomasz Jakub Rup
  • 10,502
  • 7
  • 48
  • 49
  • 2
    Django [**does** close connections automatically](https://docs.djangoproject.com/en/1.7/ref/settings/#conn-max-age). Auto-commit is [turned on by default since Django 1.6](https://docs.djangoproject.com/en/1.7/ref/databases/#autocommit-mode) – Steven Dec 07 '15 at 23:16
  • Yeah I would have been pretty shocked. I'm using Django 1.7. And also I would have seen the opened lingering connections after my tests. – XelharK Dec 08 '15 at 08:56
  • @Steven Yes, You have right. I did not notice information about Django version. – Tomasz Jakub Rup Dec 08 '15 at 08:59