Re: Tuning for a tiny database - Mailing list pgsql-general

Steve Atkins wrote:
> On Jun 20, 2011, at 10:49 PM, CSS wrote:
>
>> 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 postgres admins.
>>
>> I'm moving some DNS servers from djbdns/tinydns to PowerDNS.  While it
>> supports many backends, postgresql 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.
>> 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 three 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?
>
> I'm doing pretty much the same thing. I installed postgresql 8.4.something and
> powerdns from the ubuntu repository on a small VM and hit go. It's running just
> fine, at comparable query rates and maybe 5,000 records. At some point I'll
> tune it, just out of tidiness, but it's fine out of the box.

That's pretty much what I'm seeing.  As I mentioned in my last email in
this thread, the only other test case I need to poke around with is
hitting the powerdns server with a bunch of stuff it's not authoritative
for (forcing a lookup in the domains table) and non-existent records in
domains it is authoritative for (forcing a lookup in the records table).

> You'll also need replication, probably from a stealth master. I found bucardo
> very easy to set up for that, and it works nicely to replicate to multiple public
> servers from a single master server inside the firewall.

Already there - I have a hidden master db server and then the three
public powerdns instances all slave from that.  I'm using the
"streaming" replication option in 9.0 and so far it seems to be working
well.  I need to look more closely at the whole log-shipping thing in
case a slave somehow gets cut off from the master, but in initial
testing, it looks really good.  My only problem was I initially was
putting some of these on older hosts and I ran into the 32 vs. 64 bit
problem in copying the data directory across.

Thanks for your input!

Charles

> Cheers,
>   Steve
>
>