pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLarge tables (was: RAID 0 not as fast as expected)
10+ messages / 7 participants
[nested] [flat]
* Large tables (was: RAID 0 not as fast as expected)
@ 2006-09-18 14:37 Bucky Jordan <bjordan@lumeta.com>
2006-09-18 20:56 ` Re: Large tables (was: RAID 0 not as fast as expected) Merlin Moncure <mmoncure@gmail.com>
2006-09-19 01:10 ` Re: Large tables (was: RAID 0 not as fast as expected) Luke Lonergan <llonergan@greenplum.com>
0 siblings, 2 replies; 10+ messages in thread
From: Bucky Jordan @ 2006-09-18 14:37 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; Spiegelberg, Greg <gspiegelberg@cranel.com>; Joshua Drake <jd@commandprompt.com>; +Cc: Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
>Yes. What's pretty large? We've had to redefine large recently, now
we're
>talking about systems with between 100TB and 1,000TB.
>
>- Luke
Well, I said large, not gargantuan :) - Largest would probably be around
a few TB, but the problem I'm having to deal with at the moment is large
numbers (potentially > 1 billion) of small records (hopefully I can get
it down to a few int4's and a int2 or so) in a single table. Currently
we're testing for and targeting in the 500M records range, but the
design needs to scale to 2-3 times that at least.
I read one of your presentations on very large databases in PG, and saw
mention of some tables over a billion rows, so that was encouraging. The
new table partitioning in 8.x will be very useful. What's the largest DB
you've seen to date on PG (in terms of total disk storage, and records
in largest table(s) )?
My question is at what point do I have to get fancy with those big
tables? From your presentation, it looks like PG can handle 1.2 billion
records or so as long as you write intelligent queries. (And normal PG
should be able to handle that, correct?)
Also, does anyone know if/when any of the MPP stuff will be ported to
Postgres, or is the plan to keep that separate?
Thanks,
Bucky
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
@ 2006-09-18 20:56 ` Merlin Moncure <mmoncure@gmail.com>
2006-09-18 21:01 ` Re: Large tables (was: RAID 0 not as fast as expected) Alan Hodgson <ahodgson@simkin.ca>
2006-09-18 22:40 ` Re: Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
1 sibling, 2 replies; 10+ messages in thread
From: Merlin Moncure @ 2006-09-18 20:56 UTC (permalink / raw)
To: Bucky Jordan <bjordan@lumeta.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Spiegelberg, Greg <gspiegelberg@cranel.com>; Joshua Drake <jd@commandprompt.com>; Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
On 9/18/06, Bucky Jordan <bjordan@lumeta.com> wrote:
> My question is at what point do I have to get fancy with those big
> tables? From your presentation, it looks like PG can handle 1.2 billion
> records or so as long as you write intelligent queries. (And normal PG
> should be able to handle that, correct?)
I would rephrase that: large databses are less forgiving of
unintelligent queries, particularly of the form of your average stupid
database abstracting middleware :-). seek times on a 1gb database are
going to be zero all the time, not so on a 1tb+ database.
good normalization skills are really important for large databases,
along with materialization strategies for 'denormalized sets'.
regarding the number of rows, there is no limit to how much pg can
handle per se, just some practical limitations, especially vacuum and
reindex times. these are important because they are required to keep
a handle on mvcc bloat and its very nice to be able to vaccum bits of
your database at a time.
just another fyi, if you have a really big database, you can forget
about doing pg_dump for backups (unless you really don't care about
being x day or days behind)...you simply have to due some type of
replication/failover strategy. i would start with pitr.
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 20:56 ` Re: Large tables (was: RAID 0 not as fast as expected) Merlin Moncure <mmoncure@gmail.com>
@ 2006-09-18 21:01 ` Alan Hodgson <ahodgson@simkin.ca>
1 sibling, 0 replies; 10+ messages in thread
From: Alan Hodgson @ 2006-09-18 21:01 UTC (permalink / raw)
To: pgsql-performance
On Monday 18 September 2006 13:56, "Merlin Moncure" <mmoncure@gmail.com>
wrote:
> just another fyi, if you have a really big database, you can forget
> about doing pg_dump for backups (unless you really don't care about
> being x day or days behind)...you simply have to due some type of
> replication/failover strategy. i would start with pitr.
And, of course, the biggest problem of all; upgrades.
--
Eat right. Exercise regularly. Die anyway.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 20:56 ` Re: Large tables (was: RAID 0 not as fast as expected) Merlin Moncure <mmoncure@gmail.com>
@ 2006-09-18 22:40 ` Bucky Jordan <bjordan@lumeta.com>
2006-09-18 23:14 ` Re: Large tables (was: RAID 0 not as fast as expected) Alex Turner <armtuk@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Bucky Jordan @ 2006-09-18 22:40 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; Luke Lonergan <llonergan@greenplum.com>; +Cc: pgsql-performance
> good normalization skills are really important for large databases,
> along with materialization strategies for 'denormalized sets'.
Good points- thanks. I'm especially curious what others have done for
the materialization. The matview project on gborg appears dead, and I've
only found a smattering of references on google. My guess is, you roll
your own for optimal performance...
> regarding the number of rows, there is no limit to how much pg can
> handle per se, just some practical limitations, especially vacuum and
> reindex times. these are important because they are required to keep
> a handle on mvcc bloat and its very nice to be able to vaccum bits of
> your database at a time.
I was hoping for some actual numbers on "practical". Hardware isn't too
much of an issue (within reason- we're not talking an amazon or google
here... the SunFire X4500 looks interesting... )- if a customer wants to
store that much data, and pay for it, we'll figure out how to do it. I'd
just rather not have to re-design the database. Say the requirement is
to keep 12 months of data accessible, each "scan" produces 100M records,
and I run one per month. What happens if the customer wants to run it
once a week? I was more trying to figure out at what point (ballpark)
I'm going to have to look into archive tables and things of that nature
(or at Bizgres/MPP). It's easier for us to add more/bigger hardware, but
not so easy to redesign/add history tables...
>
> just another fyi, if you have a really big database, you can forget
> about doing pg_dump for backups (unless you really don't care about
> being x day or days behind)...you simply have to due some type of
> replication/failover strategy. i would start with pitr.
>
> merlin
I was originally thinking replication, but I did notice some nice pitr
features in 8.x - I'll have to look into that some more.
Thanks for the pointers though...
- Bucky
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 20:56 ` Re: Large tables (was: RAID 0 not as fast as expected) Merlin Moncure <mmoncure@gmail.com>
2006-09-18 22:40 ` Re: Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
@ 2006-09-18 23:14 ` Alex Turner <armtuk@gmail.com>
2006-09-18 23:58 ` Re: Large tables (was: RAID 0 not as fast as expected) Michael Stone <mstone+postgres@mathom.us>
2006-09-19 03:40 ` Re: Large tables (was: RAID 0 not as fast as expected) Alex Turner <armtuk@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Alex Turner @ 2006-09-18 23:14 UTC (permalink / raw)
To: Bucky Jordan <bjordan@lumeta.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Luke Lonergan <llonergan@greenplum.com>; pgsql-performance
Do the basic math:
If you have a table with 100million records, each of which is 200bytes long,
that gives you roughtly 20 gig of data (assuming it was all written neatly
and hasn't been updated much). If you have to do a full table scan, then
it will take roughly 400 seconds with a single 10k RPM SCSI drive with an
average read speed of 50MB/sec. If you are going to read indexes, figure
out how big your index is, and how many blocks will be returned, and figure
out how many blocks this will require transferring from the main table, make
an estimate of the seeks, add in the transfer total, and you have a time to
get your data. A big array with a good controller can pass 1000MB/sec
transfer on the right bus if you buy the write technologies. But be warned,
if you buy the wrong ones, your big array can end up being slower than a
single drive for sequential transfer. At 1000MB/sec your scan would take 20
seconds.
Be warned, the tech specs page:
http://www.sun.com/servers/x64/x4500/specs.xml#anchor3
doesn't mention RAID 10 as a possible, and this is probably what most would
recommend for fast data access if you are doing both read and write
operations. If you are doing mostly Read, then RAID 5 is passable, but it's
redundancy with large numbers of drives is not so great.
Alex.
On 9/18/06, Bucky Jordan <bjordan@lumeta.com> wrote:
>
> > good normalization skills are really important for large databases,
> > along with materialization strategies for 'denormalized sets'.
>
> Good points- thanks. I'm especially curious what others have done for
> the materialization. The matview project on gborg appears dead, and I've
> only found a smattering of references on google. My guess is, you roll
> your own for optimal performance...
>
> > regarding the number of rows, there is no limit to how much pg can
> > handle per se, just some practical limitations, especially vacuum and
> > reindex times. these are important because they are required to keep
> > a handle on mvcc bloat and its very nice to be able to vaccum bits of
> > your database at a time.
>
> I was hoping for some actual numbers on "practical". Hardware isn't too
> much of an issue (within reason- we're not talking an amazon or google
> here... the SunFire X4500 looks interesting... )- if a customer wants to
> store that much data, and pay for it, we'll figure out how to do it. I'd
> just rather not have to re-design the database. Say the requirement is
> to keep 12 months of data accessible, each "scan" produces 100M records,
> and I run one per month. What happens if the customer wants to run it
> once a week? I was more trying to figure out at what point (ballpark)
> I'm going to have to look into archive tables and things of that nature
> (or at Bizgres/MPP). It's easier for us to add more/bigger hardware, but
> not so easy to redesign/add history tables...
>
> >
> > just another fyi, if you have a really big database, you can forget
> > about doing pg_dump for backups (unless you really don't care about
> > being x day or days behind)...you simply have to due some type of
> > replication/failover strategy. i would start with pitr.
> >
> > merlin
> I was originally thinking replication, but I did notice some nice pitr
> features in 8.x - I'll have to look into that some more.
>
> Thanks for the pointers though...
>
> - Bucky
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 20:56 ` Re: Large tables (was: RAID 0 not as fast as expected) Merlin Moncure <mmoncure@gmail.com>
2006-09-18 22:40 ` Re: Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 23:14 ` Re: Large tables (was: RAID 0 not as fast as expected) Alex Turner <armtuk@gmail.com>
@ 2006-09-18 23:58 ` Michael Stone <mstone+postgres@mathom.us>
2006-09-19 13:58 ` Re: Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
1 sibling, 1 reply; 10+ messages in thread
From: Michael Stone @ 2006-09-18 23:58 UTC (permalink / raw)
To: pgsql-performance
On Mon, Sep 18, 2006 at 07:14:56PM -0400, Alex Turner wrote:
>If you have a table with 100million records, each of which is 200bytes long,
>that gives you roughtly 20 gig of data (assuming it was all written neatly
>and hasn't been updated much).
If you're in that range it doesn't even count as big or challenging--you
can keep it memory resident for not all that much money.
Mike Stone
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 20:56 ` Re: Large tables (was: RAID 0 not as fast as expected) Merlin Moncure <mmoncure@gmail.com>
2006-09-18 22:40 ` Re: Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 23:14 ` Re: Large tables (was: RAID 0 not as fast as expected) Alex Turner <armtuk@gmail.com>
2006-09-18 23:58 ` Re: Large tables (was: RAID 0 not as fast as expected) Michael Stone <mstone+postgres@mathom.us>
@ 2006-09-19 13:58 ` Bucky Jordan <bjordan@lumeta.com>
0 siblings, 0 replies; 10+ messages in thread
From: Bucky Jordan @ 2006-09-19 13:58 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; pgsql-performance
Mike,
> On Mon, Sep 18, 2006 at 07:14:56PM -0400, Alex Turner wrote:
> >If you have a table with 100million records, each of which is
200bytes
> long,
> >that gives you roughtly 20 gig of data (assuming it was all written
> neatly
> >and hasn't been updated much).
>
I'll keep that in mind (minimizing updates during loads). My plan is
updates will actually be implemented as insert to summary/history table
then delete old records. The OLTP part of this will be limited to a
particular set of tables that I anticipate will not be nearly as large.
> If you're in that range it doesn't even count as big or
challenging--you
> can keep it memory resident for not all that much money.
>
> Mike Stone
>
I'm aware of that, however, *each* scan could be 100m records, and we
need to keep a minimum of 12, and possibly 50 or more. So sure, if I
only have 100m records total, sure, but 500m, or 1b... According to
Alex's calculations, that'd be 100G for 500m records (just that one
table, not including indexes).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 20:56 ` Re: Large tables (was: RAID 0 not as fast as expected) Merlin Moncure <mmoncure@gmail.com>
2006-09-18 22:40 ` Re: Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 23:14 ` Re: Large tables (was: RAID 0 not as fast as expected) Alex Turner <armtuk@gmail.com>
@ 2006-09-19 03:40 ` Alex Turner <armtuk@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Alex Turner @ 2006-09-19 03:40 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Bucky Jordan <bjordan@lumeta.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Sweet - thats good - RAID 10 support seems like an odd thing to leave out.
Alex
On 9/18/06, Luke Lonergan <llonergan@greenplum.com> wrote:
>
> Alex,
>
> On 9/18/06 4:14 PM, "Alex Turner" <armtuk@gmail.com> wrote:
>
> > Be warned, the tech specs page:
> > http://www.sun.com/servers/x64/x4500/specs.xml#anchor3
> > doesn't mention RAID 10 as a possible, and this is probably what most
> would
> > recommend for fast data access if you are doing both read and write
> > operations. If you are doing mostly Read, then RAID 5 is passable, but
> it's
> > redundancy with large numbers of drives is not so great.
>
> RAID10 works great on the X4500 we get 1.6GB/s + per X4500 using RAID10
> in
> ZFS. We worked with the Sun Solaris kernel team to make that happen and
> the
> patches are part of Solaris 10 Update 3 due out in November.
>
> - Luke
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
@ 2006-09-19 01:10 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-19 03:45 ` Re: Large tables (was: RAID 0 not as fast as expected) mark@mark.mielke.cc
1 sibling, 1 reply; 10+ messages in thread
From: Luke Lonergan @ 2006-09-19 01:10 UTC (permalink / raw)
To: Bucky Jordan <bjordan@lumeta.com>; Spiegelberg, Greg <gspiegelberg@cranel.com>; Joshua Drake <jd@commandprompt.com>; +Cc: Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
Bucky,
On 9/18/06 7:37 AM, "Bucky Jordan" <bjordan@lumeta.com> wrote:
> My question is at what point do I have to get fancy with those big
> tables? From your presentation, it looks like PG can handle 1.2 billion
> records or so as long as you write intelligent queries. (And normal PG
> should be able to handle that, correct?)
PG has limitations that will confront you at sizes beyond about a couple
hundred GB of table size, as will Oracle and others.
You should be careful to implement very good disk hardware and leverage
Postgres 8.1 partitioning and indexes intelligently as you go beyond 100GB
per instance. Also be sure to set the random_page_cost parameter in
postgresql.conf to 100 or even higher when you use indexes, as the actual
seek rate for random access ranges between 50 and 300 for modern disk
hardware. If this parameter is left at the default of 4, indexes will often
be used inappropriately.
> Also, does anyone know if/when any of the MPP stuff will be ported to
> Postgres, or is the plan to keep that separate?
The plan is to keep that separate for now, though we're contributing
technology like partitioning, faster sorting, bitmap index, adaptive nested
loop, and hybrid hash aggregation to make big databases work better in
Postgres.
- Luke
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as expected)
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-19 01:10 ` Re: Large tables (was: RAID 0 not as fast as expected) Luke Lonergan <llonergan@greenplum.com>
@ 2006-09-19 03:45 ` mark@mark.mielke.cc
0 siblings, 0 replies; 10+ messages in thread
From: mark@mark.mielke.cc @ 2006-09-19 03:45 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Bucky Jordan <bjordan@lumeta.com>; Spiegelberg, Greg <gspiegelberg@cranel.com>; Joshua Drake <jd@commandprompt.com>; Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
On Mon, Sep 18, 2006 at 06:10:13PM -0700, Luke Lonergan wrote:
> Also be sure to set the random_page_cost parameter in
> postgresql.conf to 100 or even higher when you use indexes, as the actual
> seek rate for random access ranges between 50 and 300 for modern disk
> hardware. If this parameter is left at the default of 4, indexes will often
> be used inappropriately.
Does a tool exist yet to time this for a particular configuration?
Cheers,
mark
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...
http://mark.mielke.cc/
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-09-19 13:58 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-18 14:37 Large tables (was: RAID 0 not as fast as expected) Bucky Jordan <bjordan@lumeta.com>
2006-09-18 20:56 ` Merlin Moncure <mmoncure@gmail.com>
2006-09-18 21:01 ` Alan Hodgson <ahodgson@simkin.ca>
2006-09-18 22:40 ` Bucky Jordan <bjordan@lumeta.com>
2006-09-18 23:14 ` Alex Turner <armtuk@gmail.com>
2006-09-18 23:58 ` Michael Stone <mstone+postgres@mathom.us>
2006-09-19 13:58 ` Bucky Jordan <bjordan@lumeta.com>
2006-09-19 03:40 ` Alex Turner <armtuk@gmail.com>
2006-09-19 01:10 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-19 03:45 ` mark@mark.mielke.cc
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