Re: Tuning for a tiny database

On 06/21/2011 01:49 AM, CSS wrote:
> Some raw numbers: We're only looking at a total of about six tables in
> one db. In total there are going to be well under 10,000 records in ALL
> tables. That might increase to at most 100,000 in the next few years.
> Our raw DNS queries/second tops out around 50 qps over three distinct
> servers. Keeping in mind that PowerDNS is doing heavy caching, we
> should never really see more than a few db queries per second.

I doubt you really need to do any tuning for this scenario. I would set
shared_buffers to a modest value--maybe 256MB--and stop further tuning
until there's some evidence it's necessary.

If presented with the same problem but with the much harder twist "I
need to support >10,000 queries/second", I would recommend:

-Populate a prototype with a representative amount of data
-Measure the database size
-Set shared_buffers to that
-Whenever the database is restarted, construct a series of queries that
forces all the data used regularly into the database's cache
-Use pg_buffercache to confirm what's in there is what you expect

Getting all the data into cache is sometimes harder than expected. Some
optimizations in PostgreSQL keep it from caching large amount of tables
when you do a sequential scan of the contents, as one example that
complicates things. But if you get to where this is necessary, building
such a tool isn't difficult, and there are some projects out there that
address this particular need: filling the cache back up with relevant
data after restart. This is the main one:

http://pgfoundry.org/projects/pgfincore/
http://www.pgcon.org/2010/schedule/events/261.en.html

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books