Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are having trouble handling the traffic during peak hours to our database server. We're looking into improving the hardware (see this question about that side of things) but we also want to work on the pooling configuration and server tuning.

The application we are working on is a turn-based multiplayer game for smartphones, where the backend consists of Rails with unicorn and PostgreSQL 9.1 as the database. We currently have 600 000 registered users and since the game state is stored in the database several thousands writes are made every couple of seconds. We have analyzed the log files from PostgreSQL using PgBadger and during the critical hours we get a lot of

FATAL: remaining connection slots are reserved for non-replication superuser connections

The naive solution to counter this problem would be to increase *max_connections* (which currently is 100) in postgresql.conf . I have read http://wiki.postgresql.org/wiki/Number_Of_Database_Connections which indicates that this might not be the right thing to do. In the aforementioned article it's referred to finding the sweet spot between max_connections and pool size.

What can be done in order to find this sweet spot? Are there any good tools to measure the I/O performance for different values of *max_connections* and pool size?

Our current setup is 4 game servers, each having 16 unicorn workers and a pool size of 5.

Here are the non-default postgres-settings we are using:

version                      | PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu,compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
checkpoint_completion_target | 0.9
checkpoint_segments          | 60
checkpoint_timeout           | 6min
client_encoding              | UTF8
effective_cache_size         | 2GB
lc_collate                   | en_US.UTF-8
lc_ctype                     | en_US.UTF-8
log_destination              | csvlog
log_directory                | pg_log
log_filename                 | postgresql-%Y-%m-%d_%H%M%S.log
log_line_prefix              | %t
log_min_duration_statement   | 200ms
log_rotation_age             | 1d
log_rotation_size            | 10MB
logging_collector            | on
max_connections              | 100
max_stack_depth              | 2MB
server_encoding              | UTF8
shared_buffers               | 1GB
ssl                          | on
TimeZone                     | localtime
wal_buffers                  | 16MB
work_mem                     | 8MB
share|improve this question
    
Are you the person that was asking about this on the mailing list in the last few weeks? If so, I'll add back-links to that discussion. Also: What is your DB server hardware and configuration? wiki.postgresql.org/wiki/Slow_Query_Questions . Include non-default settings: wiki.postgresql.org/wiki/Server_Configuration . Have you read wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ? Do you batch work into larger transactions where possible? Do you use a caching layer and if so, what? Are you using synchronous_commit = off or a commit_delay? –  Craig Ringer Nov 2 '12 at 10:07
    
So you have a total of 20 connections to the PostgreSQL server? 5 per game server? With those 5 conns from each game server being shared across 16 unicorn workers? –  Craig Ringer Nov 2 '12 at 10:09
    
Oh, are you logging slow queries? If so, what are your hot-spots? Simple INSERTs? What is your schema like - is it partitioned? What's the explain analyze of some sample queries? How frequent are your checkpoints and how long do they take? (see checkpoint logging options). And seriously, what's your PostgreSQL version? (Update: Seems you list your hardware here: dba.stackexchange.com/questions/28061/…) –  Craig Ringer Nov 2 '12 at 10:14
    
Anyway, for pool size tuning specifically, the only real answers are to set up robust measurement of load and DB server throughput, then start adjusting up and down until you find the sweet spot. –  Craig Ringer Nov 2 '12 at 10:20
    
@CraigRinger No I am not that person. But thanks for the backlinks! I have read Tuning Your PostgreSQL server and followed some of the mentioned tips. I have now included the not-default settings. We are now looking into make larger transactions and testing synchronous_commit = off –  lorgartzor Nov 2 '12 at 10:31

1 Answer 1

up vote 9 down vote accepted

The short answer here is "trial and error guided by monitoring and performance metrics".

There are some general rules of thumb that should help you find the vague area you should start in, but they're very general. The broad guidelines "number of CPUs plus number of independent has disks" is often cited, but it's only an incredibly coarse starting point.

What you really need to do is get robust performance metrics in place for your application. Start recording stats.

There isn't much in the way of integrated tooling for this. There are things like the nagios check_postgres script, Cacti system performance counter logging, the PostgreSQL statistics collector, etc ... but there isn't much that puts it all together. Sadly, you'll have to do that bit yourself. For the PostgreSQL side, see monitoring in the PostgreSQL manual. Some third party options exist, like EnterpriseDB's Postgres Enterprise Monitor.

For the application-level metrics mentioned here you will want to record them in shared data structures or in an external non-durable DB like Redis and aggregate them either as you record them or before you write them to your PostgreSQL DB. Trying to log directly to Pg will distort your measurements with the overhead created by recording the measurements and make the problem worse.

The simplest option is probably a singleton in each app server that you use to record application stats. You probably want to keep a constantly updating min, max, n, total and mean; that way you don't have to store each stat point, just the aggregates. This singleton can write its aggregate stats to Pg every x minutes, a low enough rate that the performance impact will be minimal.

Start with:

  • What's the request latency? In other words, how long does the app take from getting a request from the client until it responds to the client. Record this in aggregate over a time period, rather than as individual records. Group it by request type; say, by page.

  • What's the database access delay for each query or query type the app executes? How long does it take from asking the DB for information / storing information until it's done and can move on to the next task? Again, aggregate these stats in the application and only write the aggregate info to the DB.

  • What's your throughput like? In any given x minutes, how many queries of each major class your app executes get serviced by the DB?

  • For that same time range of x minutes, how many client request were there?

  • Sampling every few seconds and aggregating over the same x minute windows in the DB, how many DB connections were there? How many of them were idle? How many were active? In inserts? Updates? selects? deletes? How many transactions were there over that period? See the statistics collector documentation

  • Again sampling and aggregating over the same time interval, what were the host system's performance metrics like? How many read and how many write disk IOs/second? Megabytes per second of disk reads and writes? CPU utilisation? Load average? RAM use?

You can now start learning about your app's performance by correlating the data, graphing it, etc. You'll start to see patterns, start to find bottlenecks.

You might learn that your system is bottle-necked on INSERT and UPDATEs at high transaction rates, despite quite low disk I/O in megabytes per second. This would be a hint that you need to improve your disk flush performance with a battery backed write-back caching RAID controller or some high-quality power-protected SSDs. You could also use synchronous_commit = off if it's OK to lose a few transactions on server crash , and/or a commit_delay, to take some of the syncing load off.

When you graph your transactions per second against the number of concurrent connections and correct for the varying request rate the application is seeing, you'll be able to get a better idea of where your throughput sweet spot is.

If you don't have fast flushing storage (BBU RAID or fast durable SSDs) you won't want more than a fairly small number of actively writing connections, maybe at most 2x the number of disks you have, probably fewer depending on RAID arrangement, disk performance, etc. In this case it isn't even worth trial and error; just upgrade your storage subsystem to one with fast disk flushes.

See pg_test_fsync for a tool that'll help you determine if this might be a problem for you. Most PostgreSQL packages install this tool as part of contrib, so you shouldn't need to compile it. If you get less than a couple of thousand ops/second in pg_test_fsync you urgently need to upgrade your storage system. My SSD-equipped laptop gets 5000-7000. My workstation at work with a 4-disk RAID 10 array of 7200rpm SATA disks and write-through (non-write-caching) gets about 80 ops/second in f_datasync, down to 20 ops/second for fsync(); it's hundreds of times slower. Compare: laptop with ssd vs workstation with write-through (non-write-caching) RAID 10. This laptop's SSD is cheap and I don't necessarily trust it to flush its write cache on power-loss; I keep good backups and wouldn't use it for data I care about. Good quality SSDs perform just as well if not better and are write-durable.

In the case of your application, I strongly advise you to look into:

  • A good storage subsystem with fast flushes. I cannot stress this enough. Good quality power-fail-safe SSDs and/or a RAID controller with power-protected write-back cache.
  • Using UNLOGGED tables for data you can afford to lose. Periodically aggregate it into logged tables. For example, keep games-in-progress in unlogged tables, and write the scores to ordinary durable tables.
  • Using a commit_delay (less useful with fast-flushing storage - hint)
  • Turning off synchronous_commit for transactions you can afford to lose (less useful with fast-flushing storage - hint hint)
  • Partitioning tables, especially tables where data "ages out" and is cleaned up. Instead of deleting from a partitioned table, drop a partition.
  • Partial indexes
  • Reducing the number of indexes you create. Every index has a write cost.
  • Batching work into bigger transactions
  • Using read-only hot standby replicas to take the read load off the main DB
  • Using a caching layer like memcached or redis for data that changes less often or can afford to be stale. You can use LISTEN and NOTIFY to perform cache invalidation using triggers on PostgreSQL tables.

If in doubt: http://www.postgresql.org/support/professional_support/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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