pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNew server to improve performance on our large and busy DB - advice?
9+ messages / 7 participants
[nested] [flat]
* New server to improve performance on our large and busy DB - advice?
@ 2010-01-14 19:17 Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 19:54 ` Re: New server to improve performance on our large and busy DB - advice? Joshua D. Drake <jd@commandprompt.com>
2010-01-14 21:43 ` Re: New server to improve performance on our large and busy DB - advice? Scott Marlowe <scott.marlowe@gmail.com>
2010-01-15 05:17 ` Re: New server to improve performance on our large and busy DB - advice? Greg Smith <greg@2ndquadrant.com>
0 siblings, 3 replies; 9+ messages in thread
From: Carlo Stonebanks @ 2010-01-14 19:17 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New server to improve performance on our large and busy DB - advice?
2010-01-14 19:17 New server to improve performance on our large and busy DB - advice? Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-14 19:54 ` Joshua D. Drake <jd@commandprompt.com>
2 siblings, 0 replies; 9+ messages in thread
From: Joshua D. Drake @ 2010-01-14 19:54 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New server to improve performance on our large and busy DB - advice?
2010-01-14 19:17 New server to improve performance on our large and busy DB - advice? Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-14 21:43 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2010-01-14 21:43 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
On Thu, Jan 14, 2010 at 12:17 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
RAID-10 with a battery backed hardware caching controller.
> 2) Which Windows OS would you recommend? (currently 2008 x64 Server)
That's probably the most stable choice out there for Windows.
> 3) If we were to port to a *NIX flavour, which would you recommend? (which
> support trouble-free PG builds/makes please!)
I'd parrot what Joshua Drake said here. Centos / RHEL / Debian / Ubuntu
> 4) Is this the right PG version for our needs?
8.3 is very stable. Update to the latest. 8.4 seems good, but I've
had, and still am having, problems with it crashing in production.
Not often, maybe once every couple of months, but just enough that I'm
not ready to try and use it there yet. And I can't force the same
failure in testing, at least not yet.
> The details of our use:
>
> . 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.
Can you run the ETL processes in such a way that they can do many
inserts and updates at once? That would certainly speed things up a
bit.
> . The size of the audit table makes it very difficult to manage, especially
> if we need to modify the design.
You might want to look into partitioning / inheritance if that would help.
> . Because we query the audit table different ways to audit the ETL processes
> decisions, almost every column in the audit table is indexed.
This may or may not help. If you're querying it and the part in the
where clause referencing this column isn't very selective, and index
won't be chosen anyway. If you've got multiple columns in your where
clause, the more selective ones will use and index and the rest will
get filtered out instead of using an index. Look in
pg_stat_user_indexes for indexes that don't get used and drop them
unless, of course, they're unique indexes.
> . 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
You may be well served by having two servers, one to write to, and a
slave that is used by the actual users. Our slony slaves have a much
easier time writing out their data than our master database does.
> . We find that internal stats drift, for whatever reason, causing row seq
> scans instead of index scans.
Yeah, this is a known problem on heavily updated tables and recent
entries. Cranking up autovacuum a bit can help, but often it requires
special treatment, either by adjusting the autovac analyze threshold
values for just those tables, or running manual analyzes every couple
of minutes.
> . 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
Not so much the size of the tables, as the size of the request. If
you were running aggregates across whole large tables, a seq scan
would definitely be the way to go. If you're asking for one row,
index scan should win. Somewhere between those two, when you get up
to hitting some decent percentage of the rows, the switch from index
scan to seq scan makes sense, and it's likely happening too early for
you. Look at random_page_cost and effective_cache_size for starters.
> . 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.
See previous comment I made up there ^^^ It's not about always using
indexes, it's about giving the planner the information it needs to
make the right choice.
> Current non-default conf settings are:
>
> autovacuum = on
> autovacuum_analyze_scale_factor = 0.1
You might wanna lower the analyze scale factor if you're having
problems with bad query plans on fresh data.
> 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
The default tends to be low. Run vacuum verbose to see if you're
overrunning the max_fsm_pages settings or the max_fsm_relations.
> max_locks_per_transaction = 128
> port = 5432
> shared_buffers = 500MB
> vacuum_cost_delay = 100
That's REALLY REALLY high. You might want to look at something in the
5 to 20 range.
> work_mem = 512MB
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New server to improve performance on our large and busy DB - advice?
2010-01-14 19:17 New server to improve performance on our large and busy DB - advice? Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-15 05:17 ` Greg Smith <greg@2ndquadrant.com>
2010-01-15 07:12 ` Re: New server to improve performance on our large and busy DB - advice? Carlo Stonebanks <stonec.register@sympatico.ca>
2 siblings, 1 reply; 9+ messages in thread
From: Greg Smith @ 2010-01-15 05:17 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
Carlo Stonebanks wrote:
> 1) Which RAID level would you recommend
It looks like you stepped over a critical step, which is "will the
server have a good performing RAID card?". Your whole upgrade could
underperform if you make a bad mistake on that part. It's really
important to nail that down, and to benchmark to prove you got what you
expected from your hardware vendor.
> 3) If we were to port to a *NIX flavour, which would you recommend?
> (which support trouble-free PG builds/makes please!)
The only platform I consider close to trouble free as far as the PG
builds working without issues are RHEL/CentOS, due to the maturity of
the PGDG yum repository and how up to date it's kept. Every time I
wander onto another platform I find the lag and care taken in packaging
PostgreSQL to be at least a small step down from there.
> 4) Is this the right PG version for our needs?
8.4 removes the FSM, which takes away a common source for unexpected
performance issues when you overflow max_fsm_pages one day. If you're
going to deploy 8.3, you need to be more careful to monitor the whole
VACUUM process; it's easier to ignore in 8.4 and still get by OK. As
far as general code stability goes, I think it's a wash at this point.
You might discover a bug in 8.4 that causes a regression, but I think
you're just as likely to run into a situation that 8.3 handles badly
that's improved in 8.4. Hard to say which will work out better in a
really general way.
> . 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.
Pretty much everyone thinks their requirements are exceptional. It's
funny how infrequently that's actually true. The techniques that favor
index-use aren't that unique: collect better stats, set basic
parameters correctly, adjust random_page_cost, investigate plans that
don't do what you want to figure out why. It's easy to say there's
something special about your data rather than follow fundamentals here;
I'd urge you to avoid doing that. The odds that the real issue is that
you're feeding the optimizer bad data is more likely than most people
think, which brings us to:
> Current non-default conf settings are:
I don't see effective_cache_size listed there. If that's at the
default, I wouldn't be surprised that you're seeing sequential scans
instead of indexed ones far too often.
> max_connections = 200
> work_mem = 512MB
This is a frightening combination by the way.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New server to improve performance on our large and busy DB - advice?
2010-01-14 19:17 New server to improve performance on our large and busy DB - advice? Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-15 05:17 ` Re: New server to improve performance on our large and busy DB - advice? Greg Smith <greg@2ndquadrant.com>
@ 2010-01-15 07:12 ` Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 0 replies; 9+ messages in thread
From: Carlo Stonebanks @ 2010-01-15 07:12 UTC (permalink / raw)
To: pgsql-performance
> Pretty much everyone thinks their requirements are exceptional. It's
> funny how infrequently that's actually true. The techniques that favor
> index-use aren't that unique: collect better stats, set basic parameters
> correctly, adjust random_page_cost, investigate plans that don't do what
> you want to figure out why. It's easy to say there's something special
> about your data rather than follow fundamentals here; I'd urge you to
> avoid doing that. The odds that the real issue is that you're feeding the
> optimizer bad data is more likely than most people think, which brings us
> to:
I understand that. And the answer is usually to go and do and ANALYZE
manually (if it isn't this, it will be some dependency on a set-returning
stored function we wrote before we could specify the rows and cost). My
question is really - why do I need this constant intervention? When we
rarely do aggregates, when our queries are (nearly) always single row
queries (and very rarely more than 50 rows) out of tables that have hundreds
of thousands to millions of rows, what does it take to NOT have to
intervene? WHich brings me to your next point:
> I don't see effective_cache_size listed there. If that's at the default,
> I wouldn't be surprised that you're seeing sequential scans instead of
> indexed ones far too often.
Nice to know - I suspect someone has been messing around with stuff they
don't understand. I do know that after some screwing around they got the
server to the point that it wouldn't restart and tried to back out until it
would.
>> max_connections = 200
>> work_mem = 512MB
> This is a frightening combination by the way.
Looks like it's connected to the above issue. The real max connection value
is 1/10th of that.
Thanks Greg!
Carlo
^ permalink raw reply [nested|flat] 9+ messages in thread
* New server to improve performance on our large and busy DB - advice? (v2)
@ 2010-01-14 21:25 Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 22:35 ` Re: New server to improve performance on our large and busy DB - advice? (v2) Dave Crooke <dcrooke@gmail.com>
2010-01-15 09:19 ` Re: Re: New server to improve performance on our large and busy DB - advice? (v2) Ing. Marcos L. Ortiz Valmaseda <mlortiz@uci.cu>
0 siblings, 2 replies; 9+ messages in thread
From: Carlo Stonebanks @ 2010-01-14 21:25 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New server to improve performance on our large and busy DB - advice? (v2)
2010-01-14 21:25 New server to improve performance on our large and busy DB - advice? (v2) Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-14 22:35 ` Dave Crooke <dcrooke@gmail.com>
2010-01-15 00:46 ` Re: New server to improve performance on our large and busy DB - advice? (v2) Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 1 reply; 9+ messages in thread
From: Dave Crooke @ 2010-01-14 22:35 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New server to improve performance on our large and busy DB - advice? (v2)
2010-01-14 21:25 New server to improve performance on our large and busy DB - advice? (v2) Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 22:35 ` Re: New server to improve performance on our large and busy DB - advice? (v2) Dave Crooke <dcrooke@gmail.com>
@ 2010-01-15 00:46 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 9+ messages in thread
From: Craig Ringer @ 2010-01-15 00:46 UTC (permalink / raw)
To: Dave Crooke <dcrooke@gmail.com>; +Cc: Carlo Stonebanks <stonec.register@sympatico.ca>; pgsql-performance
On 15/01/2010 6:35 AM, Dave Crooke wrote:
> 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).
>
>
> 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.
.... though that's much less important for Pg than for most other
things, as Pg uses a one-process-per-connection model and lets the OS
handle much of the caching. So long as the OS can use all that RAM for
caching, Pg will benefit, and it's unlikely you need >2GB for any given
client connection or for the postmaster.
It's nice to have the flexibility to push up shared_buffers, and it'd be
good to avoid any overheads in running 32-bit code on win64. However,
it's not that unreasonable to run a 32-bit Pg on a 64-bit OS and expect
good performance.
You can always go 64-bit once 8.5/9.0 hits and has stabilized, anyway.
--
Craig Ringer
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: New server to improve performance on our large and busy DB - advice? (v2)
2010-01-14 21:25 New server to improve performance on our large and busy DB - advice? (v2) Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-01-15 09:19 ` Ing. Marcos L. Ortiz Valmaseda <mlortiz@uci.cu>
1 sibling, 0 replies; 9+ messages in thread
From: Ing. Marcos L. Ortiz Valmaseda @ 2010-01-15 09:19 UTC (permalink / raw)
To: Ivan Voras <ivoras@freebsd.org>; +Cc: pgsql-performance
El 15/01/2010 14:43, Ivan Voras escribió:
> hi,
>
> You wrote a lot of information here so let's confirm in a nutshell
> what you have and what you are looking for:
>
> * A database that is of small to medium size (5 - 10 GB)?
> * Around 10 clients that perform constant write operations to the
> database (UPDATE/INSERT)
> * Around 10 clients that occasionally read from the database
> * Around 6000 tables in your database
> * A problem with tuning it all
> * Migration to new hardware and/or OS
>
> Is this all correct?
>
> First thing that is noticeable is that you seem to have way too few
> drives in the server - not because of disk space required but because
> of speed. You didn't say what type of drives you have and you didn't
> say what you would consider desirable performance levels, but off hand
> (because of the "10 clients perform constant writes" part) you will
> probably want at least 2x-4x more drives.
>
> > 1) Which RAID level would you recommend
>
> With only 4 drives, RAID 10 is the only thing usable here.
>
> > 2) Which Windows OS would you recommend? (currently 2008 x64 Server)
>
> Would not recommend Windows OS.
>
> > 3) If we were to port to a *NIX flavour, which would you recommend?
> (which
> > support trouble-free PG builds/makes please!)
>
> Practically any. I'm biased for FreeBSD, a nice and supported version
> of Linux will probably be fine.
>
> > 4) Is this the right PG version for our needs?
>
> If you are starting from scratch on a new server, go for the newest
> version you can get - 8.4.2 in this case.
>
> Most importantly, you didn't say what you would consider desirable
> performance. The hardware and the setup you described will work, but
> not necessarily fast enough.
>
> > . 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
>
> ... and to the small number of drives you are using.
>
> > . We believe our requirements are exceptional, and we would benefit
> > immensely from setting up the PG planner to always favour
> index-oriented decisions
>
> Have you tried decreasing random_page_cost in postgresql.conf? Or
> setting (as a last resort) enable_seqscan = off?
>
>
> Carlo Stonebanks 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
>>
>>
>>
>
>
I have a question about that, due to all of you recommend RAID-10 for
the implementatio of this system. Would you give a available
arquitecture based on all these considerations?
About the questions, I recommend FreeBSD too for a PostgreSQL production
server (and for other things too, not only Pg), but with Linux you can
obtain a strong, reliable environment that can be more efficient that
Windows.
Regards
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-01-15 09:19 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-14 19:17 New server to improve performance on our large and busy DB - advice? Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 19:54 ` Re: New server to improve performance on our large and busy DB - advice? Joshua D. Drake <jd@commandprompt.com>
2010-01-14 21:43 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-15 05:17 ` Greg Smith <greg@2ndquadrant.com>
2010-01-15 07:12 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 21:25 New server to improve performance on our large and busy DB - advice? (v2) Carlo Stonebanks <stonec.register@sympatico.ca>
2010-01-14 22:35 ` Re: New server to improve performance on our large and busy DB - advice? (v2) Dave Crooke <dcrooke@gmail.com>
2010-01-15 00:46 ` Re: New server to improve performance on our large and busy DB - advice? (v2) Craig Ringer <craig@postnewspapers.com.au>
2010-01-15 09:19 ` Re: Re: New server to improve performance on our large and busy DB - advice? (v2) Ing. Marcos L. Ortiz Valmaseda <mlortiz@uci.cu>
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