From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
Subject: Recommendations for configuring a 200 GB database
Date: Wed, 08 Jun 2005 16:04:36 -0500
Message-ID: <s2a71722.022@gwmta.wicourts.gov> (raw)
We have had four databases serving our web site, but due to licensing
issues, we have had to take two out of production, and we are looking to
bring those two onto PostgreSQL very quickly, with an eye toward moving
everything in the longer term. The central web DBs are all copies of
the same data, drawn from 72 servers at remote locations. We replicate
modifications made at these 72 remote sites real-time to all central
servers.
On each central server, there are 352 tables and 412 indexes holding
about 700 million rows, taking almost 200 GB of disk space. The largest
table has about 125 million of those rows, with several indexes. There
are about 3 million database transactions modifying each central
database every day, with each transaction typically containing many
inserts and/or updates -- deletes are sparse. During idle time the
replication process compares tables in the source databases to the
central databases to log any differences and correct the central copies.
To support the 2 million browser and SOAP hits per day, the web sites
spread about 6 million SELECT statements across available central
servers, using load balancing. Many of these queries involve a 10 or
more tables with many subqueries; some involve unions.
The manager of the DBA team is reluctant to change both the OS and the
DBMS at the same time, so unless I can make a strong case for why it is
important to run postgresql under Linux, we will be running this on
Windows. Currently, there are two Java-based middle tier processes
running on each central database server, one for the replication and one
for the web. We expect to keep it that way, so the database needs to
play well with these processes.
I've been reading everything I can find on postgresql configuration, but
would welcome any specific suggestions for this environment. I'd also
be really happy to hear that we're not the first to use postgresql with
this much data and load.
Thanks for any info you can provide.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov
Subject: Re: Recommendations for configuring a 200 GB database
In-Reply-To: <s2a71722.022@gwmta.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox