pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRecommendations for configuring a 200 GB database
4+ messages / 3 participants
[nested] [flat]
* Recommendations for configuring a 200 GB database
@ 2005-06-08 21:04 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2005-06-08 21:04 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Recommendations for configuring a 200 GB database
@ 2005-06-09 08:06 Richard Huxton <dev@archonet.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2005-06-09 08:06 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
Kevin Grittner wrote:
>
> 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.
Well, there's a lot more experience running PG on various *nix systems
and a lot more help available. Also, I don't think performance on
Windows is as good as on Linux/*BSD yet.
Against switching OS is the fact that you presumably don't have the
skills in-house for it, and the hardware was chosen for Windows
compatibility/performance.
Speaking of which, what sort of hardware are we talking about?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Recommendations for configuring a 200 GB
@ 2005-06-09 14:55 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2005-06-09 14:55 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-performance
Thanks for your reply. Besides your post regarding *nix vs. Windows I
got a few which didn't go to the group. Words like "bold move" and
"disaster waiting to happen" tended to feature prominently in these
messages (regarding putting something this big on PostgreSQL under
Windows), and management is considering deploying one under Windows and
one under Linux, or possibly even both under Linux -- so please pass
along advice for either environment.
The four web servers are not all identical -- we have two "large" and
two "small". They are split between sites, and even one of the small
ones is capable of keeping our apps running, although with significantly
impaired performance. The initial PostgreSQL implementation will be on
one large and one small, unless we decide to do one each of Windows and
Linux; in that case we'd want identical hardware to better compare the
OS issues, so it would probably be the two small servers.
The small servers are IBM 8686-9RX servers with 4 xeon processors at 2
ghz, 6 gig of ram. The internal drives are set as a 67 gig raid 5 array
with three drives. We have an external storage arry attached. This has
a 490 gig raid 5 array on it. The drives are 15K drives.
http://www-307.ibm.com/pc/support/site.wss/quickPath.do?quickPathEntry=86869rx
for more info.
The large servers are also IBM, although I don't have a model number
handy. I know the xeons are 3 ghz and the bus is faster; otherwise they
are similar. I know the large servers can go to 64 GB RAM, and
management has said they are willing to add a lot more RAM if it will
get used. (Our current, commercial database product can't use it under
Windows.) There is also the possibility of adding additional CPUs.
Like I said, with the current hardware and Sybase 12.5.1, one small
machine can keep the applications limping along, although data
replication falls behind during the day and catches up at night, and we
get complaints from web users about slow response and some requests
timing out. One large machine handles the load with little degradation,
and using any two machines keeps everyone happy. We have four so that
we can have two each at two different sites, and so we can take one out
for maintenance and still tolerate a singe machine failure.
We're hoping PostgreSQL can match or beat Sybase performance, and
preliminary tests look good. We should be able to get some load testing
going within a week, and we're shooting for slipping these machines into
the mix around the end of this month. (We've gone to some lengths to
keep our code portable.)
-Kevin
>>> Richard Huxton <dev@archonet.com> 06/09/05 3:06 AM >>>
Kevin Grittner wrote:
>
> 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.
Well, there's a lot more experience running PG on various *nix systems
and a lot more help available. Also, I don't think performance on
Windows is as good as on Linux/*BSD yet.
Against switching OS is the fact that you presumably don't have the
skills in-house for it, and the hardware was chosen for Windows
compatibility/performance.
Speaking of which, what sort of hardware are we talking about?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Recommendations for configuring a 200 GB
@ 2005-06-09 15:01 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Christopher Kings-Lynne @ 2005-06-09 15:01 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: dev@archonet.com; pgsql-performance
> We're hoping PostgreSQL can match or beat Sybase performance, and
> preliminary tests look good. We should be able to get some load testing
> going within a week, and we're shooting for slipping these machines into
> the mix around the end of this month. (We've gone to some lengths to
> keep our code portable.)
Just make sure to set up and run the contrib/pg_autovacuum daemon, or
make sure you fully read 'regular database maintenance' in the manual.
Chris
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-09 15:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-08 21:04 Recommendations for configuring a 200 GB database Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-06-09 08:06 ` Richard Huxton <dev@archonet.com>
2005-06-09 14:55 Re: Recommendations for configuring a 200 GB Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-06-09 15:01 ` Re: Recommendations for configuring a 200 GB Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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