Tuning for a tiny database

From: CSS <css(at)morefoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Tuning for a tiny database
Date: 2011-06-04 03:38:38
Message-ID: 4DE9A8BE.9030701@morefoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I couldn't find much information in the archives on this -- perhaps this
is a bit of a specialized need, but I was hoping for some input from
some experienced pgsql admins.

I'm moving some DNS servers from djbdns/tinydns to PowerDNS. While it
supports many backends, postgres seems like the best choice for us since
it's what is used elsewhere (in larger configurations). As a bit of
background, PowerDNS does not query the db for every incoming DNS query,
it caches at various levels (both a "packet cache" and a db cache), so
it's database needs are quite modest.

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 at
most. 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. There
will be one "master" pgsql db and two slaves using streaming replication.

Now given the number of records and the frequency of queries, how should
I be tuning for such a small setup? Ideally PowerDNS with it's huge
amount of caching should get as much RAM and CPU as I can give it, but I
do want to ensure the tiny bit of data postgres has is stuck in physical
memory as well.

What would you suggest for this scenario?

Thanks,

Charles

Browse pgsql-general by date

  From Date Subject
Next Message Esmin Gracic 2011-06-04 10:18:35 Re: Need suggestion
Previous Message Alvaro Herrera 2011-06-04 01:32:56 Re: Postmaster holding unlinked files for pg_largeobject table