Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8E7B063502F for ; Thu, 14 Jan 2010 18:36:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87695-01 for ; Thu, 14 Jan 2010 22:35:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f213.google.com (mail-ew0-f213.google.com [209.85.219.213]) by mail.postgresql.org (Postfix) with ESMTP id 3AD92634215 for ; Thu, 14 Jan 2010 18:35:56 -0400 (AST) Received: by ewy5 with SMTP id 5so64609ewy.34 for ; Thu, 14 Jan 2010 14:35:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=f/NdW9euh2/w18Mpioa6RjsUZYAoJ6O7kbkv5vR7Zfc=; b=eNz9+dkxiFLcZ8W4GAEPNBjzthoAqeaf2ZYJHsL89WofHC6nNKRWiDXJLnBD2Tn8FX OcX8FE6862ilDO7jFCweFw9PwJYQ5OJuyOH628rPRVW4K/2alVNIZuRKuMZQ18YhKYD6 CZT3NSyePwq9i02J/PRULEGsAXkZPQe8YZMb0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=HHw8rnx0m0diRRdrJkhwQTzcCuqDJXl1yhXtQtI00gkLE5RDyZ5HZuE5cXDTsZlbV6 zaUHcojqrd5BB4bvhvdqMK1GZzB+5h0+U/yvVuVmKnkOrk3n7z6Xe3RxbizBohcMnHJo q2ZN7PRuRuyIerQvsVWZU7+vUQng+fp5W8wK4= MIME-Version: 1.0 Received: by 10.216.89.202 with SMTP id c52mr485530wef.215.1263508553411; Thu, 14 Jan 2010 14:35:53 -0800 (PST) In-Reply-To: References: Date: Thu, 14 Jan 2010 16:35:53 -0600 Message-ID: Subject: Re: New server to improve performance on our large and busy DB - advice? (v2) From: Dave Crooke To: Carlo Stonebanks Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d64b187f2d80047d278243 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.004 tagged_above=-10 required=5 tests=BAYES_40=-0.185, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: *** X-Archive-Number: 201001/319 X-Sequence-Number: 37199 --0016e6d64b187f2d80047d278243 Content-Type: text/plain; charset=ISO-8859-1 I'll bite .... 1. In general, RAID-10 is the only suitable RAID configuration for a database. The decision making comes in how many drives, and splitting stuff up into LUNs (like putting pg_xlog on its own LUN). 2. None of the above - you're asking the wrong question really. PostgreSQL is open source, and is developed on Unix. The Windows version is a pretty good port, as Windows posrt of OSS stuff go, but it's just that, a port. Your server is being dedicated to running Postgres, so the right question to ask is "What is the best OS for running Postgres?". For any given database engine, regardless of the marketing and support stance, there is only one true "primary" enterprise OS platform that most big mission critical sites use, and is the best supported and most stable platform for that RDBMS. For Oracle, that's HP-UX (but 10 years ago, it was Solaris). For PostgreSQL, it's Linux. The biggest problem with Postgres on Windows is that it only comes in 32-bit. RAM is the ultimate performance tweak for an RDBMS, and to make proper use of modern amounts of RAM, you need a 64-bit executable. 3. The two choices I'd consider are both Linux: - for the conservative / supported approach, get Red Hat and buy support from them and (e.g.) Enterprise DB - if you plan to keep pretty current and are happy actively managing versions and running locally compiled builds, go with Ubuntu 4. The general wisdom is that there are a lot of improvements from 8.3 to 8.4, but how much benefit you'll see in your environment is another question. If you're building a new system and have to migrate anyway, it seems like a good opportunity to upgrade. Cheers Dave On Thu, Jan 14, 2010 at 3:25 PM, 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 > 2) Which Windows OS would you recommend? (currently 2008 x64 Server) > 3) If we were to port to a *NIX flavour, which would you recommend? (which > support trouble-free PG builds/makes please!) > 4) Is this the right PG version for our needs? > > 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 > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --0016e6d64b187f2d80047d278243 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I'll bite ....


1. In general, RAID-10 is the only suitable R= AID configuration for a database. The decision making comes in how many dri= ves, and splitting stuff up into LUNs (like putting pg_xlog on its own LUN)= .


2. None of the above - you're asking the wrong question really.= PostgreSQL is open source, and is developed on Unix. The Windows version i= s a pretty good port, as Windows posrt of OSS stuff go, but it's just t= hat, a port. Your server is being dedicated to running Postgres, so the rig= ht question to ask is "What is the best OS for running Postgres?"= .

For any given database engine, regardless of the marketing and support stance, there is only one true "primary" enterprise OS platform t= hat most big mission critical sites use, and is the best supported and most stable platform for that RDBMS. For Oracle, that's HP-UX (but 10 years ago, it was Solaris). For PostgreSQL, it's Linux.

The biggest problem with Postgres on Windows is that it only comes in 3= 2-bit. RAM is the ultimate performance tweak for an RDBMS, and to make prop= er use of modern amounts of RAM, you need a 64-bit executable.
=A0


3. The two choices I'd consider are both Linux:

- for th= e conservative / supported approach, get Red Hat and buy support from them = and (e.g.) Enterprise DB
- if you plan to keep pretty current and are ha= ppy actively managing versions and running locally compiled builds, go with= Ubuntu


4. The general wisdom is that there are a lot of improvements from = 8.3 to 8.4, but how much benefit you'll see in your environment is anot= her question. If you're building a new system and have to migrate anywa= y, it seems like a good opportunity to upgrade.


Cheers
Dave

On Thu, Jan 14, 20= 10 at 3:25 PM, Carlo Stonebanks <stonec.register@sympatico.ca> wrot= e:
My client just in= formed 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
2) Which Windows OS would you recommend? (currently 2008 x64 Server)
3) If we were to port to a *NIX flavour, which would you recommend? (which<= br> support trouble-free PG builds/makes please!)
4) Is this the right PG version for our needs?

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<= br> . 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&qu= ot; 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 processe= s
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 =3D on
autovacuum_analyze_scale_factor =3D 0.1
autovacuum_analyze_threshold =3D 250
autovacuum_naptime =3D 1min
autovacuum_vacuum_scale_factor =3D 0.2
autovacuum_vacuum_threshold =3D 500
bgwriter_lru_maxpages =3D 100
checkpoint_segments =3D 64
checkpoint_warning =3D 290
datestyle =3D 'iso, mdy'
default_text_search_config =3D 'pg_catalog.english'
lc_messages =3D 'C'
lc_monetary =3D 'C'
lc_numeric =3D 'C'
lc_time =3D 'C'
log_destination =3D 'stderr'
log_line_prefix =3D '%t '
logging_collector =3D on
maintenance_work_mem =3D 16MB
max_connections =3D 200
max_fsm_pages =3D 204800
max_locks_per_transaction =3D 128
port =3D 5432
shared_buffers =3D 500MB
vacuum_cost_delay =3D 100
work_mem =3D 512MB



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--0016e6d64b187f2d80047d278243--