From: Joshua D. Drake <jd@commandprompt.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: New server to improve performance on our large and busy DB - advice?
Date: Thu, 14 Jan 2010 11:54:09 -0800
Message-ID: <924d96f74a9902a1c5f275730b8a1fc1@commandprompt.com> (raw)
In-Reply-To: <hinqih$1o1l$1@news.hub.org>
References: <hi41os$eiv$1@news.hub.org>
<b1b9fac61001071305vf182f3ajff6827f92c943c68@mail.gmail.com>
<hi5kpr$184c$1@news.hub.org>
<dcc563d11001071740q572cdae2re410788fe790dbf6@mail.gmail.com>
<hinqih$1o1l$1@news.hub.org>
On Thu, 14 Jan 2010 14:17:13 -0500, "Carlo Stonebanks"
<stonec.register@sympatico.ca> wrote:
> My client just informed me that new hardware is available for our DB
> server.
>
> . Intel Core 2 Quads Quad
> . 48 GB RAM
> . 4 Disk RAID drive (RAID level TBD)
>
> I have put the ugly details of what we do with our DB below, as well as
> the
> postgres.conf settings. But, to summarize: we have a PostgreSQL 8.3.6 DB
> with very large tables and the server is always busy serving a constant
> stream of single-row UPDATEs and INSERTs from parallel automated
processes.
>
> There are less than 10 users, as the server is devoted to the KB
> production
> system.
>
> My questions:
>
> 1) Which RAID level would you recommend
10
> 2) Which Windows OS would you recommend? (currently 2008 x64 Server)
If you have to run Windows... that works.
> 3) If we were to port to a *NIX flavour, which would you recommend?
(which
> support trouble-free PG builds/makes please!)
Community driven:
Debian Stable
CentOS 5
Commercial:
Ubuntu LTS
RHEL 5
> 4) Is this the right PG version for our needs?
You want to run at least the latest stable 8.3 series which I believe is
8.3.9.
With the imminent release of 8.5 (6 months), it may be time to move to
8.4.2 instead.
Joshua D. Drake
>
> Thanks,
>
> Carlo
>
> The details of our use:
>
> . The DB hosts is a data warehouse and a knowledgebase (KB) tracking the
> professional information of 1.3M individuals.
> . The KB tables related to these 130M individuals are naturally also
large
> . The DB is in a perpetual state of serving TCL-scripted Extract,
> Transform
> and Load (ETL) processes
> . These ETL processes typically run 10 at-a-time (i.e. in parallel)
> . We would like to run more, but the server appears to be the bottleneck
> . The ETL write processes are 99% single row UPDATEs or INSERTs.
> . There are few, if any DELETEs
> . The ETL source data are "import tables"
> . The import tables are permanently kept in the data warehouse so that
we
> can trace the original source of any information.
> . There are 6000+ and counting
> . The import tables number from dozens to hundreds of thousands of rows.
> They rarely require more than a pkey index.
> . Linking the KB to the source import date requires an "audit table" of
> 500M
> rows, and counting.
> . The size of the audit table makes it very difficult to manage,
> especially
> if we need to modify the design.
> . Because we query the audit table different ways to audit the ETL
> processes
> decisions, almost every column in the audit table is indexed.
> . The maximum number of physical users is 10 and these users RARELY
> perform
> any kind of write
> . By contrast, the 10+ ETL processes are writing constantly
> . We find that internal stats drift, for whatever reason, causing row
seq
> scans instead of index scans.
> . So far, we have never seen a situation where a seq scan has improved
> performance, which I would attribute to the size of the tables
> . We believe our requirements are exceptional, and we would benefit
> immensely from setting up the PG planner to always favour index-oriented
> decisions - which seems to contradict everything that PG advice suggests
> as
> best practice.
>
> Current non-default conf settings are:
>
> autovacuum = on
> autovacuum_analyze_scale_factor = 0.1
> autovacuum_analyze_threshold = 250
> autovacuum_naptime = 1min
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_vacuum_threshold = 500
> bgwriter_lru_maxpages = 100
> checkpoint_segments = 64
> checkpoint_warning = 290
> datestyle = 'iso, mdy'
> default_text_search_config = 'pg_catalog.english'
> lc_messages = 'C'
> lc_monetary = 'C'
> lc_numeric = 'C'
> lc_time = 'C'
> log_destination = 'stderr'
> log_line_prefix = '%t '
> logging_collector = on
> maintenance_work_mem = 16MB
> max_connections = 200
> max_fsm_pages = 204800
> max_locks_per_transaction = 128
> port = 5432
> shared_buffers = 500MB
> vacuum_cost_delay = 100
> work_mem = 512MB
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
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: jd@commandprompt.com, stonec.register@sympatico.ca
Subject: Re: New server to improve performance on our large and busy DB - advice?
In-Reply-To: <924d96f74a9902a1c5f275730b8a1fc1@commandprompt.com>
* 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