3

We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive.

Our machine:

SO: CentOS 6 HW: 8 cores, 32GB RAM, 4Tb hard-drive;

We, for development and testing, are using the default PostgreSQL configuration, what I think, is somewhat wrong. I want to take advantage of the full capacities that our hardware provides, and so far we didnt got a lot of traffic. When this is release to the public, there will be a lot of users concurrently.

Our application has some queues and async strategies to handle the load, but, the main question is: how can I safely tune PostgreSQL, run some tests, and assure that the tuning was somewhat effective?

Improve this question
5
  • top-c (or htop), netstat, memstat, iostat, and pgtop during load tests Commented Jul 24, 2013 at 14:55
  • 3
    You can also check the tools developed by my colleagues at tech.zalando.com/getting-a-quick-view-of-your-postgresql-stats - of course, this is only a tool recommendation, not a tip for how to do your testing. Commented Jul 24, 2013 at 15:15
  • 1
    When you say "4TB hard drive", what exactly do you mean by that? The details of the storage make a big difference. Commented Jul 25, 2013 at 1:18
  • @dezso Excellent tool! Commented Jul 25, 2013 at 1:19
  • Hi guys, I'm not sure of the storage specs, but Ill find out. This is probably a storage - but let me check. Commented Jul 25, 2013 at 18:58

1 Answer 1

Reset to default
5

The default Pg config is almost certainly sub-optimal, it's a conservative default intended for "will run anywhere" not "will be fast".

There's lots to be said on that; see http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server, http://wiki.postgresql.org/wiki/Performance_Optimization, and Greg Smith's "PostgreSQL 9.0 High Performance". (By way of disclaimer, I now work with Greg at 2ndQuadrant, but I was recommending his book before I moved to 2ndQuadrant).

I won't repeat what's already covered in those references.

I advise that you run pg_test_fsync to see whether fsync() performance is acceptable on your I/O subsystem, as this is critical for decent transaciton-processing throughput. It doesn't matter as much for read-mostly workloads. If it's poor, consider getting a couple of high quality SSDs with proper built-in power-fail protection or a RAID controller with a battery-backed write cache. By way of comparison, my single cheap SSD laptop tends to hit about 5000 ops/sec in pg_test_fsync.

Also use sysbench to get some general idea of what disk seq and random throughput are like. This will be useful when you're adjusting random_page_size and seq_page_size.

Most importantly of all, use a connection pooler like PgBouncer instead of raising max_connections above a couple of hundred.

Improve this answer
1
  • This is good advice, especially the pgbouncer one! I'll look into the book you recommended also. Commented Jul 25, 2013 at 18:44

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.