Postgresql – limit to the number of database connections from a single machine in PostgreSQL?

linuxperformancepostgresql-9.3postgresql-performance

We are planning on setting up multiple front ends behind a load balancer.

I think our database can handle the load because I'm currently measuring a database TPS of about 450 for approximately 42000 web requests (93000 transactions).

Our requirements are 278 messages/second average for the front end. I can only get around 92 non-idle queries running in the database, at a given time, and I was wondering if there's some limit to the number of connections from a single machine.

I have tried upping various connection pools and thread pools. I was able to up the query count from 33 to 92, but I'm unable to get higher. I am not even sure how many queries from a single machine are reasonable. I read connections should be (2 * core_count) + effective_spindle_count.

I have been getting errors from the front end stating that it failed in getting a connection ("The connection attempt failed"), but I don't know yet if this is a failure from the front end (by hitting a limit) or the database (a configuration on the database).

I believe the max # of connections for the database is 2000 last I checked.

Is there another limit to the number of connections the database will accept from one machine?

The database is running Linux 2.6.18 on x64.

I guess I'm also wondering if the number of connections is limiting. I don't think it is, but we did get the connection attempt failures. The front end is running at 300% CPU (8 processors) and 25% memory. I would think we could get more bang out of both our front end and our database, but with two test servers, we normally get 250 tests/second (maxed out at 254/s for one server and 334/sec for the other ).

Best Answer

You probably don't want more than 100 or so concurrent queries running on the DB. Use a connection pooler like pgbouncer if you need one and your app doesn't have one built-in.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Setting a high max_connections won't make things go faster. It'll make things go slower.

Related Solutions

Sql-server – Multiple schema or multiple databases for better performance

It's impossible to evaluate the options without knowing what the actual problem is.

Your first port of call should be to identify the reason the servers run slowly - it may be memory or CPU, rather than disk throughput. Perfmon will be able to tell you this; you can also run Microsoft's PAL tool to get a better insight. If it's not disk throughput that's limiting you, option 2 and 3 are pointless.

Secondly, it may well be possible to optimize the queries - this may be cheaper, quicker and less risky than changing the infrastructure. Find the query that's running slowly, work out what's constraining it, and see if you can optimize it.

If you do find out that the queries are optimally tuned, and that it is disk throughput that's the bottleneck, I'd put each of the two back-end DBs on separate drives - it doesn't sound like they are competing against the front DBs (as they run at different times), but they might be competing against each other if both DBs are used in overnight batch jobs.

Postgresql – Configuring PostgreSQL to match server configuration

This is a pretty broad topic. I suggest you pick up a copy of Greg Smith's excellent book "PostgreSQL 9.0 High Performance" and work through it. http://www.packtpub.com/postgresql-90-high-performance/book

Can you show the output of the following query:

SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

Things to test:

wal_buffers = 16MB
checkpoint_segments = 32 #(or higher)
shared_buffers = 6GB
random_page_cost = 2.0 # not the first choice of things to tune

What are your logging settings? Are there obvious slow queries? If so, you can run them through pgbadger to give you a good idea of queries to target.

Here are some settings you can test, given your current settings and what you have mentioned about your system.

log_line_prefix = '%t [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] '
shared_buffers = '6GB' # requires cluster restart
checkpoint_completion_target = 0.8
checkpoint_segments = 32
effective_io_concurrency = <lesser of cpus and disks/channels>
log_destination = stderr    # This and the log_directory are what I use,
log_directory = pg_log      # it is up to you whether or not you want to change them
logging_collector = 'on'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = '2s' # Set this as necessary, set lower per role
random_page_cost = 2.5 # If faster disks, drop to 2.0
track_activity_query_size = 4096
work_mem = '50MB'   # Adjust this as necessary.
                    # Be aware this is a per-action setting,
                    # eg. aggregation, sorts, etc can each consume up to
                    # this amount of RAM

You'll need to restart the database for some of those settings to take effect.

Best Answer

Related Solutions

Sql-server – Multiple schema or multiple databases for better performance

Postgresql – Configuring PostgreSQL to match server configuration

Related Question