pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedData Warehouse Reevaluation - MySQL vs Postgres
52+ messages / 26 participants
[nested] [flat]
* Data Warehouse Reevaluation - MySQL vs Postgres
@ 2004-09-12 04:24 Mark Cotner <mcotner@yahoo.com>
0 siblings, 3 replies; 52+ messages in thread
From: Mark Cotner @ 2004-09-12 04:24 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I had a difficult time deciding which list to post
this to, so please forgive me if this list doesn't
perfectly match my questions. My decision will not
solely be based on performance, but it is the primary
concern. I would be very appreciative if you all
could comment on my test plan. Real world examples of
a Postgres implementation of >=600G with a web
front-end would be great, or any data warehouse with
some size to it.
The dilemma:
The time has come to reevaluate/rearchitect an
application which I built about 3 years ago. There
are no performance concerns with MySQL, but it would
benefit greatly from stored procedures, views, etc.
It is a very large rolling data warehouse that inserts
about 4.5 million rows every 2 hours and subsequently
rolls this data off the back end of a 90 day window.
A web interface has been designed for querying the
data warehouse.
Migration planning is much easier with views and
stored procedures and this is my primary reason for
evaluating Postgres once again. As the application
grows I want to have the ability to provide backward
compatible views for those who are accustomed to the
current structure. This is not possible in MySQL.
Some of the mining that we do could benefit from
stored procedures as well. MySQL may have these in
the works, but we won't be able to move to a version
of MySQL that supports stored procs for another year
or two.
Requirements:
Merge table definition equivalent. We use these
extensively.
Merge table equivalent with all tables containing over
100M rows(and about 40 columns, some quite wide) will
need to do index scans in at least 5 seconds(MySQL
currently does 2, but we can live with 5) and return
~200 rows.
Um, gonna sound silly, but the web interface has to
remain "snappy" under load. I don't see this as a
major concern since you don't require table locking.
If business logic is moved to the database(likely with
Postgres) performance for inserting with light logic
on each insert has to keep up with the 4.5M inserts
per 2 hours(which MySQL completes in ~35min
currently). Acceptable numbers for this aggregation
would be 45-55min using stored procedures.
About 3 years ago I did some performance
characterizations of Postgres vs. MySQL and didn't
feel Postgres was the best solution. 3 years later
we've won runner-up for MySQL application of the
year(behind Saabre). Oddly enough this reevaluting
database strategy is right on the coattails of this
award. I'll begin writing my business logic within
the next week and start migrating test data shortly
thereafter. Case studies would be very beneficial as
I put together my analysis.
Also, this is for a Fortune 500 company that uses this
data warehouse extensively. It is an internal
application that is widely used and gets about 4 hits
per employee per day. Much of customer care, data
engineering, plant engineering(it's a cable company),
and marketing use the interface. I've done a great
deal of press for MySQL and would be equally willing
to tout the benefits of Postgres to trade rags,
magazines, etc provided the results are favorable.
Here's our case study if you're interested . . .
http://www.mysql.com/customers/customer.php?id=16
Thoughts, suggestions?
'njoy,
Mark
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres
@ 2004-09-12 05:22 Dennis Bjorklund <db@zigo.dhs.org>
parent: Mark Cotner <mcotner@yahoo.com>
2 siblings, 0 replies; 52+ messages in thread
From: Dennis Bjorklund @ 2004-09-12 05:22 UTC (permalink / raw)
To: Mark Cotner <mcotner@yahoo.com>; +Cc: pgsql-performance
On Sat, 11 Sep 2004, Mark Cotner wrote:
> There are no performance concerns with MySQL, but it would benefit
> greatly from stored procedures, views, etc. It is a very large rolling
> data warehouse that inserts about 4.5 million rows every 2 hours and
> subsequently rolls this data off the back end of a 90 day window.
While it is impossible to know without testing, postgresql has the benefit
of readers and writers that does not block each other. So in situations
where you do lots of concurrent inserts and selects postgresql should
behave well.
> Merge table definition equivalent. We use these extensively.
As far as I can tell a merge table in mysql is the same as a view over a
number of unions of other tables. And possibly a rule that defines how
inserts will be done if you do inserts in the merged table.
> Merge table equivalent with all tables containing over 100M rows(and
> about 40 columns, some quite wide) will need to do index scans in at
> least 5 seconds(MySQL currently does 2, but we can live with 5) and
> return ~200 rows.
Since each table that are merged will have it's own index the speed should
be proportional to the number of tables. Index scans in them self are very
fast, and of you have 30 tables you need 30 index scans.
Also, are you sure you really need merge tables? With pg having row locks
and mvcc, maybe you could go for a simpler model with just one big table.
Often you can also combine that with partial indexes to get a smaller
index to use for lots of your queries.
> Thoughts, suggestions?
I see nothing in what you have written that indicates that pg can not do
the job, and do it well. It's however very hard to know exactly what is
the bottleneck before one tries. There are lots of cases where people have
converted mysql applications to postgresql and have gotten a massive
speedup. You could be lucky and have such a case, who knows..
I spend some time each day supporting people using postgresql in the
#postgresql irc channel (on the freenode.net network). There I talk to
people doing both small and big conversions and the majority is very happy
with the postgresql performance. Postgresql have gotten faster and faster
with each release and while speed was a fair argument a number of years
ago it's not like that today.
That said, in the end it depends on the application.
We are all interested in how it goes (well, at least me :-), so feel free
to send more mails keeping us posted. Good luck.
--
/Dennis Björklund
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres
@ 2004-09-12 09:07 Gaetano Mendola <mendola@bigfoot.com>
parent: Mark Cotner <mcotner@yahoo.com>
2 siblings, 0 replies; 52+ messages in thread
From: Gaetano Mendola @ 2004-09-12 09:07 UTC (permalink / raw)
To: Mark Cotner <mcotner@yahoo.com>
Mark Cotner wrote:
> Requirements:
> Merge table definition equivalent. We use these
> extensively.
What do you mean with "merge table definition equivalent"?
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-12 20:47 Mischa Sandberg <ischamay.andbergsay@activestateway.com>
parent: Mark Cotner <mcotner@yahoo.com>
2 siblings, 3 replies; 52+ messages in thread
From: Mischa Sandberg @ 2004-09-12 20:47 UTC (permalink / raw)
To: pgsql-performance
Mark Cotner wrote:
> Hi all,
> I had a difficult time deciding which list to post
> this to, so please forgive me if this list doesn't
> perfectly match my questions. My decision will not
> solely be based on performance, but it is the primary
> concern. I would be very appreciative if you all
> could comment on my test plan. Real world examples of
> a Postgres implementation of >=600G with a web
> front-end would be great, or any data warehouse with
> some size to it.
I'm only in the 30GB range of database, in case that's a consideration
for my comments that follow.
At this time, I'm working out the best ROLAP options for our PG
transaction store. The transaction store is highly volatile -- longest a
txn stays in it is 15 days ... so you imagine the need for historic
summaries :-)
I've also implemented multiple data servers, including
a federated server that had to make the best of existing engines
(like MySQL, PG and everything from MSJet to RedBrick in the commercial
world).
> The time has come to reevaluate/rearchitect an
> application which I built about 3 years ago. There
> are no performance concerns with MySQL, but it would
> benefit greatly from stored procedures, views, etc.
If your company is currently happy with MySQL, there probably are other
(nontechnical) reasons to stick with it. I'm impressed that you'd
consider reconsidering PG.
> Some of the mining that we do could benefit from
> stored procedures as well. MySQL may have these in
> the works, but we won't be able to move to a version
> of MySQL that supports stored procs for another year
> or two.
And PG lets you back-end with some powerful pattern- and
aggregate-handling languages, like Perl. This was definitely a plus for
data mining of web traffic, for example. The power of server-side
extensibility for bailing you out of a design dead-end is not
inconsequential.
PG doesn't have PIVOT operators (qv Oracle and MSSQL), but it makes the
translation from data to column fairly painless otherwise.
> Requirements:
> Merge table definition equivalent. We use these
> extensively.
Looked all over mysql.com etc, and afaics merge table
is indeed exactly a view of a union-all. Is that right?
PG supports views, of course, as well (now) as tablespaces, allowing you
to split tables/tablesets across multiple disk systems.
PG is also pretty efficient in query plans on such views, where (say)
you make one column a constant (identifier, sort of) per input table.
> Merge table equivalent with all tables containing over
> 100M rows(and about 40 columns, some quite wide) will
> need to do index scans in at least 5 seconds(MySQL
> currently does 2, but we can live with 5) and return
> ~200 rows.
PG has TOAST for handling REALLY BIG columns, and the generic TEXT type
is as efficient as any size-specific VARCHAR() type ... should make
things easier for you.
> Um, gonna sound silly, but the web interface has to
> remain "snappy" under load. I don't see this as a
> major concern since you don't require table locking.
Agreed. It's more in your warehouse design, and intelligent bounding of
queries. I'd say PG's query analyzer is a few years ahead of MySQL for
large and complex queries.
> If business logic is moved to the database(likely with
> Postgres) performance for inserting with light logic
> on each insert has to keep up with the 4.5M inserts
> per 2 hours(which MySQL completes in ~35min
> currently). Acceptable numbers for this aggregation
> would be 45-55min using stored procedures.
Again, it's a matter of pipeline design. The tools for creating an
efficient pipeline are at least as good in PG as MySQL.
If you try to insert and postprocess information one row at a time,
procedures or no, there's no offhand way to guarantee your performance
without a test/prototype.
On the other hand, if you do warehouse-style loading (Insert, or PG
COPY, into a temp table; and then 'upsert' into the perm table), I can
guarantee 2500 inserts/sec is no problem.
> Here's our case study if you're interested . . .
> http://www.mysql.com/customers/customer.php?id=16
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-13 02:29 Christopher Browne <cbbrowne@acm.org>
parent: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
2 siblings, 2 replies; 52+ messages in thread
From: Christopher Browne @ 2004-09-13 02:29 UTC (permalink / raw)
To: pgsql-performance
The world rejoiced as Mischa Sandberg <ischamay.andbergsay@activestateway.com> wrote:
> Mark Cotner wrote:
>> Requirements:
>> Merge table definition equivalent. We use these
>> extensively.
> Looked all over mysql.com etc, and afaics merge table is indeed
> exactly a view of a union-all. Is that right?
> PG supports views, of course, as well (now) as tablespaces, allowing
> you to split tables/tablesets across multiple disk systems. PG is
> also pretty efficient in query plans on such views, where (say) you
> make one column a constant (identifier, sort of) per input table.
The thing that _doesn't_ work well with these sorts of UNION views are
when you do self-joins. Supposing you have 10 members, a self-join
leads to a 100-way join, which is not particularly pretty.
I'm quite curious as to how MySQL(tm) copes with this, although it may
not be able to take place; they may not support that...
>> Um, gonna sound silly, but the web interface has to remain "snappy"
>> under load. I don't see this as a major concern since you don't
>> require table locking.
> Agreed. It's more in your warehouse design, and intelligent bounding
> of queries. I'd say PG's query analyzer is a few years ahead of
> MySQL for large and complex queries.
The challenge comes in if the application has had enormous amounts of
effort put into it to attune it exactly to MySQL(tm)'s feature set.
The guys working on RT/3 have found this a challenge; they had rather
a lot of dependancies on its case-insensitive string comparisons,
causing considerable grief.
> On the other hand, if you do warehouse-style loading (Insert, or PG
> COPY, into a temp table; and then 'upsert' into the perm table), I
> can guarantee 2500 inserts/sec is no problem.
The big wins are thus:
1. Group plenty of INSERTs into a single transaction.
2. Better still, use COPY to cut parsing costs plenty more.
3. Adding indexes _after_ the COPY are a further win.
Another possibility is to do clever things with stored procs; load
incoming data using the above optimizations, and then run stored
procedures to use some more or less fancy logic to put the data where
it's ultimately supposed to be. Having the logic running inside the
engine is the big optimization.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','linuxfinances.info').
http://linuxfinances.info/info/spreadsheets.html
Rules of the Evil Overlord #198. "I will remember that any
vulnerabilities I have are to be revealed strictly on a need-to-know
basis. I will also remember that no one needs to know."
<http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-13 07:57 Mark Cotner <mcotner@yahoo.com>
parent: Christopher Browne <cbbrowne@acm.org>
1 sibling, 1 reply; 52+ messages in thread
From: Mark Cotner @ 2004-09-13 07:57 UTC (permalink / raw)
To: pgsql-performance
See comments . . . thanks for the feedback.
'njoy,
Mark
--- Christopher Browne <cbbrowne@acm.org> wrote:
> The world rejoiced as Mischa Sandberg
> <ischamay.andbergsay@activestateway.com> wrote:
> > Mark Cotner wrote:
> >> Requirements:
> >> Merge table definition equivalent. We use these
> >> extensively.
>
> > Looked all over mysql.com etc, and afaics merge
> table is indeed
> > exactly a view of a union-all. Is that right?
>
> > PG supports views, of course, as well (now) as
> tablespaces, allowing
> > you to split tables/tablesets across multiple disk
> systems. PG is
> > also pretty efficient in query plans on such
> views, where (say) you
> > make one column a constant (identifier, sort of)
> per input table.
>
> The thing that _doesn't_ work well with these sorts
> of UNION views are
> when you do self-joins. Supposing you have 10
> members, a self-join
> leads to a 100-way join, which is not particularly
> pretty.
>
> I'm quite curious as to how MySQL(tm) copes with
> this, although it may
> not be able to take place; they may not support
> that...
>
> >> Um, gonna sound silly, but the web interface has
> to remain "snappy"
> >> under load. I don't see this as a major concern
> since you don't
> >> require table locking.
>
> > Agreed. It's more in your warehouse design, and
> intelligent bounding
> > of queries. I'd say PG's query analyzer is a few
> years ahead of
> > MySQL for large and complex queries.
>
> The challenge comes in if the application has had
> enormous amounts of
> effort put into it to attune it exactly to
> MySQL(tm)'s feature set.
>
> The guys working on RT/3 have found this a
> challenge; they had rather
> a lot of dependancies on its case-insensitive string
> comparisons,
> causing considerable grief.
>
Not so much, I've tried to be as agnostic as possible.
Much of the more advanced mining that I've written is
kinda MySQL specific, but needs to be rewritten as
stored procedures anyway.
> > On the other hand, if you do warehouse-style
> loading (Insert, or PG
> > COPY, into a temp table; and then 'upsert' into
> the perm table), I
> > can guarantee 2500 inserts/sec is no problem.
>
> The big wins are thus:
>
> 1. Group plenty of INSERTs into a single
> transaction.
>
> 2. Better still, use COPY to cut parsing costs
> plenty more.
>
> 3. Adding indexes _after_ the COPY are a further
> win.
>
> Another possibility is to do clever things with
> stored procs; load
> incoming data using the above optimizations, and
> then run stored
> procedures to use some more or less fancy logic to
> put the data where
> it's ultimately supposed to be. Having the logic
> running inside the
> engine is the big optimization.
Agreed, I did some preliminary testing today and am
very impressed. I wasn't used to running analyze
after a data load, but once I did that everything was
snappy.
My best results from MySQL bulk inserts was around 36k
rows per second on a fairly wide table. Today I got
42k using the COPY command, but with the analyze post
insert the results were similar. These are excellent
numbers. It basically means we could have our
cake(great features) and eat it too(performance that's
good enough to run the app).
Queries from my test views were equally pleasing. I
won't bore you with the details just yet, but
PostgreSQL is doing great. Not that you all are
surprised. ;)
> --
> wm(X,Y):-write(X),write('@'),write(Y).
> wm('cbbrowne','linuxfinances.info').
> http://linuxfinances.info/info/spreadsheets.html
> Rules of the Evil Overlord #198. "I will
> remember that any
> vulnerabilities I have are to be revealed strictly
> on a need-to-know
> basis. I will also remember that no one needs to
> know."
> <http://www.eviloverlord.com/;
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-13 09:21 Christopher Browne <cbbrowne@acm.org>
parent: Mark Cotner <mcotner@yahoo.com>
0 siblings, 1 reply; 52+ messages in thread
From: Christopher Browne @ 2004-09-13 09:21 UTC (permalink / raw)
To: pgsql-performance
A long time ago, in a galaxy far, far away, mcotner@yahoo.com (Mark Cotner) wrote:
> Agreed, I did some preliminary testing today and am very impressed.
> I wasn't used to running analyze after a data load, but once I did
> that everything was snappy.
Something worth observing is that this is true for _any_ of the
database systems supporting a "cost-based" optimization system,
including Oracle and DB2.
When working with SAP R/3 Payroll, on one project, we found that when
the system was empty of data, the first few employee creates were
quick enough, but it almost immediately got excruciatingly slow. One
of the DBAs told the Oracle instance underneath to collect statistics
on the main table, and things _immediately_ got snappy again. But it
didn't get snappy until the conversion folk had run the conversion
process for several minutes, to the point to which it would get
painfully slow :-(. There, with MILLIONS of dollars worth of license
fees being paid, across the various vendors, it still took a fair bit
of manual fiddling.
MySQL(tm) is just starting to get into cost-based optimization; in
that area, they're moving from where the "big DBs" were about 10 years
ago. It was either version 7 or 8 where Oracle started moving to
cost-based optimization, and (as with the anecdote above) it took a
release or two for people to get accustomed to the need to 'feed' the
optimizer with statistics. This is a "growing pain" that bites users
with any database where this optimization gets introduced. It's
worthwhile, but is certainly not costless.
I expect some forseeable surprises will be forthcoming for MySQL AB's
customers in this regard...
> My best results from MySQL bulk inserts was around 36k rows per
> second on a fairly wide table. Today I got 42k using the COPY
> command, but with the analyze post insert the results were similar.
> These are excellent numbers. It basically means we could have our
> cake(great features) and eat it too(performance that's good enough
> to run the app).
In the end, performance for inserts is always fundamentally based on
how much disk I/O there is, and so it should come as no shock that
when roughly the same amount of data is getting laid down on disk,
performance won't differ much on these sorts of essentials.
There are a few places where there's some need for cleverness; if you
see particular queries running unusually slowly, it's worth doing an
EXPLAIN or EXPLAIN ANALYZE on them, to see how the query plans are
being generated. There's some collected wisdom out here on how to
encourage the right plans.
There are also unexpected results that are OK. We did a system
upgrade a few days ago that led to one of the tables starting out
totally empty. A summary report that looks at that table wound up
with a pretty wacky looking query plan (compared to what's usual)
because the postmaster knew that the query would be reading in
essentially the entire table. You'd normally expect an index scan,
looking for data for particular dates. In this case, it did a "scan
the whole table; filter out a few irrelevant entries" plan.
It looked wacky, compared to what's usual, but it ran in about 2
seconds, which was way FASTER than what's usual. So the plan was
exactly the right one.
Telling the difference between the right plan and a poor one is a bit
of an art; we quite regularly take a look at query plans on this list
to figure out what might be not quite right. If you find slow ones,
make sure you have run ANALYZE on the tables recently, to be sure that
the plans are sane, and you may want to consider posting some of them
to see if others can point to improvements that can be made.
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://linuxfinances.info/info/linuxdistributions.html
"I can't believe my room doesn't have Ethernet! Why wasn't it wired
when the house was built?"
"The house was built in 1576."
-- Alex Kamilewicz on the Oxford breed of `conference American.'
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-13 22:07 Simon Riggs <simon@2ndquadrant.com>
parent: Christopher Browne <cbbrowne@acm.org>
1 sibling, 1 reply; 52+ messages in thread
From: Simon Riggs @ 2004-09-13 22:07 UTC (permalink / raw)
To: pgsql-performance; Mark Cotner <mcotner@yahoo.com>; +Cc: Christopher Browne <cbbrowne@acm.org>
Mark,
I thought some additional comments on top of Christopher's excellent notes
might help you.
> Christopher Browne
> The world rejoiced as Mischa Sandberg
> <ischamay.andbergsay@activestateway.com> wrote:
> > Mark Cotner wrote:
> >> Requirements:
> >> Merge table definition equivalent. We use these
> >> extensively.
>
> > Looked all over mysql.com etc, and afaics merge table is indeed
> > exactly a view of a union-all. Is that right?
>
PostgreSQL's functionality is in many ways similar to Oracle Partitioning.
Loading up your data in many similar tables, then creating a view like:
CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
SELECT 200409130800, col1, col2, col3... FROM table200409130800
UNION ALL
SELECT 200409131000, col1, col2, col3... FROM table200409131000
UNION ALL
SELECT 200409131200, col1, col2, col3... FROM table200409131200
...etc...
will allow the PostgreSQL optimizer to eliminate partitions from the query
when you run queries which include a predicate on the partitioning_col, e.g.
select count(*) from bigtable where idate >= 200409131000
will scan the last two partitions only...
There are a few other ways of creating the view that return the same answer,
but only using constants in that way will allow the partitions to be
eliminated from the query, and so run for much longer.
So you can give different VIEWS to different user groups, have different
indexes on different tables etc.
However, I haven't managed to get this technique to work when performing a
star join to a TIME dimension table, since the parition elimination relies
on comparison of constant expressions. You'll need to check out each main
join type to make sure it works for you in your environment.
> > PG supports views, of course, as well (now) as tablespaces, allowing
> > you to split tables/tablesets across multiple disk systems. PG is
> > also pretty efficient in query plans on such views, where (say) you
> > make one column a constant (identifier, sort of) per input table.
>
> The thing that _doesn't_ work well with these sorts of UNION views are
> when you do self-joins. Supposing you have 10 members, a self-join
> leads to a 100-way join, which is not particularly pretty.
>
Well, that only happens when you forget to include the partitioning constant
in the self join.
e.g. select count(*) from bigtable a, bigtable b where a.idate =
.idate; --works just fine
The optimizer really is smart enough to handle that too, but I'm sure such
large self-joins aren't common for you anyhow.
> I'm quite curious as to how MySQL(tm) copes with this, although it may
> not be able to take place; they may not support that...
>
It doesn't, AFAIK.
> Christopher Browne wrote
> A long time ago, in a galaxy far, far away, mcotner@yahoo.com
> (Mark Cotner) wrote:
> > Agreed, I did some preliminary testing today and am very impressed.
> > I wasn't used to running analyze after a data load, but once I did
> > that everything was snappy.
>
> Something worth observing is that this is true for _any_ of the
> database systems supporting a "cost-based" optimization system,
> including Oracle and DB2.
Agreed. You can reduce the time for the ANALYZE by ignoring some of the
(measures) columns not used in WHERE clauses.
Also, if you're sure that each load is very similar to the last, you might
even consider directly updating pg_statistic rows with the statistical
values produced from an earlier ANALYZE...scary, but it can work.
To create a set of tables of > 600Gb, you will benefit from creating each
table WITHOUT OIDS.
Hope some of that helps you...
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 07:39 Mark Cotner <mcotner@yahoo.com>
parent: Christopher Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 52+ messages in thread
From: Mark Cotner @ 2004-09-14 07:39 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; pgsql-performance
You all have been so very helpful so far and I really
appreciate it.
The data in these tables is thankfully static since
they are logging tables and an analyze only takes
about 4 minutes for the largest of them.
I've finished porting the schema and am importing the
data now. My estimates for just two-thirds(60 of the
90 days) of one of our 30 cable systems(MySQL dbs) is
estimated to take about 16 hours. This may seem like
a lot, but I'm satisfied with the performance. I've
created a slightly normalized version and some stored
procedures to help me normalize the data. When this
finishes I'm going to query the data as is with the
views as you suggested, and I'm going to create views
for the normalized version to test that as well. This
will then be contrasted to the MySQL query results and
I plan to write a white paper of my findings.
I don't have any concerns that Postgres will do fine,
but if I run into any performance problems I'll be
sure and post them here first.
It should be noted that our development life cycle is
currently severely hindered by lack of features in
MySQL like views and stored procedures. Frankly I've
implemented some pretty ugly SQL using as many as 5
temp tables to generate a result set with MySQL.
Having stored procedures and views is going to help us
tremendously. This performance evaluation is to
verify that Postgres can handle what we're going to
throw at it, not to find out if it's faster in
milliseconds than MySQL. We love the speed and ease
of maintenance with MySQL, but have simply outgrown
it. This will be reflected in the white paper.
I have already imported our customer tables, which
aren't too small(2.4M rows x 3 tables), and stuck a
view in front of it. The view queried faster than
MySQL would query a pre-joined flat table.
Getting carried away . . . needless to say I'm really
excited about the possiblity of Postgres, but I won't
bore you with the details just yet. I'll send the
link out to the white paper so you all can review it
before I send it anywhere else. If anything could
have been optimized more please let me know and I'll
see that it gets updated before it's widely published.
Thanks again for all the great feedback!
'njoy,
Mark
--- Christopher Browne <cbbrowne@acm.org> wrote:
> A long time ago, in a galaxy far, far away,
> mcotner@yahoo.com (Mark Cotner) wrote:
> > Agreed, I did some preliminary testing today and
> am very impressed.
> > I wasn't used to running analyze after a data
> load, but once I did
> > that everything was snappy.
>
> Something worth observing is that this is true for
> _any_ of the
> database systems supporting a "cost-based"
> optimization system,
> including Oracle and DB2.
>
> When working with SAP R/3 Payroll, on one project,
> we found that when
> the system was empty of data, the first few employee
> creates were
> quick enough, but it almost immediately got
> excruciatingly slow. One
> of the DBAs told the Oracle instance underneath to
> collect statistics
> on the main table, and things _immediately_ got
> snappy again. But it
> didn't get snappy until the conversion folk had run
> the conversion
> process for several minutes, to the point to which
> it would get
> painfully slow :-(. There, with MILLIONS of dollars
> worth of license
> fees being paid, across the various vendors, it
> still took a fair bit
> of manual fiddling.
>
> MySQL(tm) is just starting to get into cost-based
> optimization; in
> that area, they're moving from where the "big DBs"
> were about 10 years
> ago. It was either version 7 or 8 where Oracle
> started moving to
> cost-based optimization, and (as with the anecdote
> above) it took a
> release or two for people to get accustomed to the
> need to 'feed' the
> optimizer with statistics. This is a "growing pain"
> that bites users
> with any database where this optimization gets
> introduced. It's
> worthwhile, but is certainly not costless.
>
> I expect some forseeable surprises will be
> forthcoming for MySQL AB's
> customers in this regard...
>
> > My best results from MySQL bulk inserts was around
> 36k rows per
> > second on a fairly wide table. Today I got 42k
> using the COPY
> > command, but with the analyze post insert the
> results were similar.
> > These are excellent numbers. It basically means
> we could have our
> > cake(great features) and eat it too(performance
> that's good enough
> > to run the app).
>
> In the end, performance for inserts is always
> fundamentally based on
> how much disk I/O there is, and so it should come as
> no shock that
> when roughly the same amount of data is getting laid
> down on disk,
> performance won't differ much on these sorts of
> essentials.
>
> There are a few places where there's some need for
> cleverness; if you
> see particular queries running unusually slowly,
> it's worth doing an
> EXPLAIN or EXPLAIN ANALYZE on them, to see how the
> query plans are
> being generated. There's some collected wisdom out
> here on how to
> encourage the right plans.
>
> There are also unexpected results that are OK. We
> did a system
> upgrade a few days ago that led to one of the tables
> starting out
> totally empty. A summary report that looks at that
> table wound up
> with a pretty wacky looking query plan (compared to
> what's usual)
> because the postmaster knew that the query would be
> reading in
> essentially the entire table. You'd normally expect
> an index scan,
> looking for data for particular dates. In this
> case, it did a "scan
> the whole table; filter out a few irrelevant
> entries" plan.
>
> It looked wacky, compared to what's usual, but it
> ran in about 2
> seconds, which was way FASTER than what's usual. So
> the plan was
> exactly the right one.
>
> Telling the difference between the right plan and a
> poor one is a bit
> of an art; we quite regularly take a look at query
> plans on this list
> to figure out what might be not quite right. If you
> find slow ones,
> make sure you have run ANALYZE on the tables
> recently, to be sure that
> the plans are sane, and you may want to consider
> posting some of them
> to see if others can point to improvements that can
> be made.
> --
> If this was helpful,
> <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
>
http://linuxfinances.info/info/linuxdistributions.html
> "I can't believe my room doesn't have Ethernet! Why
> wasn't it wired
> when the house was built?"
> "The house was built in 1576."
> -- Alex Kamilewicz on the Oxford breed of
> `conference American.'
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
> settings
>
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 09:07 Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
parent: Mark Cotner <mcotner@yahoo.com>
1 sibling, 0 replies; 52+ messages in thread
From: Pierre-Frédéric Caillaud @ 2004-09-14 09:07 UTC (permalink / raw)
To: pgsql-performance
Performance hint :
For static data, do not normalize too much.
For instance if you have a row which can be linked to several other rows,
you can do this :
create table parents (
id serial primary key,
values... )
create table children (
id serial primary key,
parent_id references parents(id),
integer slave_value )
Or you can do this, using an array :
create table everything (
id serial primary key,
integer[] children_values,
values... )
Pros :
No Joins. Getting the list of chilndren_values from table everything is
just a select.
On an application with several million rows, a query lasting 150 ms with
a Join takes 30 ms with an array.
You can build the arrays from normalized tables by using an aggregate
function.
You can index the array elements with a GIST index...
Cons :
No joins, thus your queries are a little bit limited ; problems if the
array is too long ;
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 12:14 Markus Schaber <schabios@logi-track.com>
parent: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
2 siblings, 1 reply; 52+ messages in thread
From: Markus Schaber @ 2004-09-14 12:14 UTC (permalink / raw)
To: pgsql-performance
Hi, Mischa,
On Sun, 12 Sep 2004 20:47:17 GMT
Mischa Sandberg <ischamay.andbergsay@activestateway.com> wrote:
> On the other hand, if you do warehouse-style loading (Insert, or PG
> COPY, into a temp table; and then 'upsert' into the perm table), I can
> guarantee 2500 inserts/sec is no problem.
As we can forsee that we'll have similar insert rates to cope with in
the not-so-far future, what do you mean with 'upsert'? Do you mean a
stored procedure that iterates over the temp table?
Generally, what is the fastest way for doing bulk processing of
update-if-primary-key-matches-and-insert-otherwise operations?
Thanks,
Markus Schaber
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 12:42 Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
0 siblings, 2 replies; 52+ messages in thread
From: Leeuw van der, Tim @ 2004-09-14 12:42 UTC (permalink / raw)
To: Markus Schaber <schabios@logi-track.com>; pgsql-performance
Hi,
I found bulk-insert to perform very slow, when compared to MySQL / Oracle. All inserts were done in 1 transaction. However, mitigating factors here were:
- Application was a .Net application using ODBC drivers
- PostgreSQL 7.3 running on CYGWIN with cygipc daemon
- Probably very bad tuning in the config file, if any tuning done at all
- The application was issuing 'generic' SQL since it was generally used with Oracle and MySQL databases. So no tricks like using COPY or multiple rows with 1 INSERT statement. No stored procedures either.
- When doing queries, most of the time the results were comparable to or better than MySQL (the only other database that I tested with myself).
So what I can say is, that if you want fast INSERT performance from PostgreSQL then you'll probably have to do some trickery that you wouldn't have to do with a default MySQL installation.
regards,
--Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Markus Schaber
Sent: Tuesday, September 14, 2004 2:15 PM
To: PostgreSQL Performance List
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Hi, Mischa,
On Sun, 12 Sep 2004 20:47:17 GMT
Mischa Sandberg <ischamay.andbergsay@activestateway.com> wrote:
> On the other hand, if you do warehouse-style loading (Insert, or PG
> COPY, into a temp table; and then 'upsert' into the perm table), I can
> guarantee 2500 inserts/sec is no problem.
As we can forsee that we'll have similar insert rates to cope with in
the not-so-far future, what do you mean with 'upsert'? Do you mean a
stored procedure that iterates over the temp table?
Generally, what is the fastest way for doing bulk processing of
update-if-primary-key-matches-and-insert-otherwise operations?
Thanks,
Markus Schaber
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 13:24 Aaron Werman <awerman2@hotmail.com>
parent: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
2 siblings, 1 reply; 52+ messages in thread
From: Aaron Werman @ 2004-09-14 13:24 UTC (permalink / raw)
To: ischamay.andbergsay@activestateway.com; pgsql-performance
> Mark Cotner wrote:
> > The time has come to reevaluate/rearchitect an
> > application which I built about 3 years ago. There
> > are no performance concerns with MySQL, but it would
> > benefit greatly from stored procedures, views, etc.
>
From: "Mischa Sandberg" <ischamay.andbergsay@activestateway.com>
> If your company is currently happy with MySQL, there probably are other
> (nontechnical) reasons to stick with it. I'm impressed that you'd
> consider reconsidering PG.
I'd like to second Mischa on that issue. In general, if you migrate an
*existing* application from one RDBMS to another, you should expect
performance to decrease significantly. This is always true in a well
performing system even if the replacement technology is more sophisticated.
This is because of several factors.
Even if you try to develop in totally agnostic generic SQL, you are always
customizing to a feature set, namely the ones in the current system. Any
existing application has had substantial tuning and tweaking, and the new
one is at a disadvantage. Moreover, an existing system is a Skinnerian
reward/punishment system to the developers and DBAs, rewarding or punishing
them for very environment specific choices - resulting in an application,
dbms, OS, and platform that are both explicitly and unconsciously customized
to work together in a particular manner.
The net effect is a rule of thumb that I use:
NEVER reimplement an existing system unless the project includes substantial
functional imporovement.
Every time I've broken that rule, I've found that users expectations, based
on the application they are used to, are locked in. Any place where the new
system is slower, the users are dissatisfied; where it exceeds expectations
it isn't appreciated: the users are used to the old system quirks, and the
improvements only leave them uncomforable since the system "acts
differently". (I've broken the rule on occation for standardization
conversions.)
My expectation is that pg will not get a fair shake here. If you do it - I'd
like to see the results anyway.
/Aaron
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 13:33 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
1 sibling, 0 replies; 52+ messages in thread
From: Steinar H. Gunderson @ 2004-09-14 13:33 UTC (permalink / raw)
To: pgsql-performance
On Tue, Sep 14, 2004 at 02:42:20PM +0200, Leeuw van der, Tim wrote:
> - PostgreSQL 7.3 running on CYGWIN with cygipc daemon
Isn't this doomed to kill your performance anyhow?
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 14:00 Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
0 siblings, 1 reply; 52+ messages in thread
From: Leeuw van der, Tim @ 2004-09-14 14:00 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Steinar H. Gunderson
Sent: Tuesday, September 14, 2004 3:33 PM
To: PostgreSQL Performance List
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
> On Tue, Sep 14, 2004 at 02:42:20PM +0200, Leeuw van der, Tim wrote:
> > - PostgreSQL 7.3 running on CYGWIN with cygipc daemon
>
> Isn't this doomed to kill your performance anyhow?
Yes and no, therefore I mentioned it explicitly as one of the caveats. When doing selects I could get performance very comparable to MySQL, so I don't want to blame poor insert-performance on cygwin/cygipc per se.
I'm not working on this app. anymore and don't have a working test-environment for it anymore so I cannot retest now with more recent versions.
regards,
--Tim
>
> /* Steinar */
> --
> Homepage: http://www.sesse.net/
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 14:15 Harald Fuchs <hf0722x@protecting.net>
parent: Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
1 sibling, 0 replies; 52+ messages in thread
From: Harald Fuchs @ 2004-09-14 14:15 UTC (permalink / raw)
To: pgsql-performance
In article <BF88DF69D9E2884B9BE5160DB2B97A85010D6D5F@nlshl-exch1.eu.uis.unisys.com>,
"Leeuw van der, Tim" <tim.leeuwvander@nl.unisys.com> writes:
> So what I can say is, that if you want fast INSERT performance from
> PostgreSQL then you'll probably have to do some trickery that you
> wouldn't have to do with a default MySQL installation.
I think the word "INSERT" is superfluous in the above sentence ;-)
Contrary to MySQL, you can't expect decent PostgreSQL performance on
decent hardware without some tuning.
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 14:23 Michael Kleiser <mkl@webde-ag.de>
parent: Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
0 siblings, 0 replies; 52+ messages in thread
From: Michael Kleiser @ 2004-09-14 14:23 UTC (permalink / raw)
To: Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>; +Cc: Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
What MySQl-table-type did you use?
Was it "MyISAM" which don't supports transactions ?
Yes I read about that bulk-inserts with this table-type are very fast.
In Data Warehouse one often don't need transactions.
Leeuw van der, Tim schrieb:
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Steinar H.
> Gunderson
> Sent: Tuesday, September 14, 2004 3:33 PM
> To: PostgreSQL Performance List
> Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
>
>
> > On Tue, Sep 14, 2004 at 02:42:20PM +0200, Leeuw van der, Tim wrote:
> > > - PostgreSQL 7.3 running on CYGWIN with cygipc daemon
> >
> > Isn't this doomed to kill your performance anyhow?
>
> Yes and no, therefore I mentioned it explicitly as one of the caveats.
> When doing selects I could get performance very comparable to MySQL, so
> I don't want to blame poor insert-performance on cygwin/cygipc per se.
> I'm not working on this app. anymore and don't have a working
> test-environment for it anymore so I cannot retest now with more recent
> versions.
>
> regards,
>
> --Tim
>
> >
> > /* Steinar */
> > --
> > Homepage: http://www.sesse.net/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 14:33 Stephen Frost <sfrost@snowman.net>
parent: Markus Schaber <schabios@logi-track.com>
0 siblings, 2 replies; 52+ messages in thread
From: Stephen Frost @ 2004-09-14 14:33 UTC (permalink / raw)
To: Markus Schaber <schabios@logi-track.com>; +Cc: pgsql-performance
* Markus Schaber (schabios@logi-track.com) wrote:
> Generally, what is the fastest way for doing bulk processing of
> update-if-primary-key-matches-and-insert-otherwise operations?
This is a very good question, and I havn't seen much of an answer to it
yet. I'm curious about the answer myself, actually. In the more recent
SQL specs, from what I understand, this is essentially what the 'MERGE'
command is for. This was recently added and unfortunately is not yet
supported in Postgres. Hopefully it will be added soon.
Otherwise, what I've done is basically an update followed by an insert
using outer joins. If there's something better, I'd love to hear about
it. The statements looks something like:
update X
set colA = a.colA,
colB = a.colB
from Y a
where keyA = a.keyA and
keyB = a.keyB;
insert into X
select a.keyA,
a.keyB,
a.colA,
a.colB
from Y a left join X b
using (keyA, keyB)
where b.keyA is NULL and
b.keyB is NULL;
With the appropriate indexes, this is pretty fast but I think a merge
would be much faster.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 15:20 Harald Lau (Sector-X) <harald@sector-x.de>
parent: Aaron Werman <awerman2@hotmail.com>
0 siblings, 0 replies; 52+ messages in thread
From: Harald Lau (Sector-X) @ 2004-09-14 15:20 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> From: "Mischa Sandberg" <ischamay.andbergsay@activestateway.com>
>
> > If your company is currently happy with MySQL, there probably are
> > other (nontechnical) reasons to stick with it. I'm impressed that
> > you'd consider reconsidering PG.
>
> I'd like to second Mischa on that issue.
Though both of you are right from my point of view, I don't think
it's very useful to discuss this item here.
Having once migrated a MySQL-DB to PG I can confirm, that in fact
chances are good you will be unhappy if you adopt the MySQL
data-model and the SQL 1:1.
As well as PG has to be much more configured and optimized than
MySQL.
As well as the client-application is supposed to be modified to a
certain extend, particularly if you want to take over some -or some
more- business-logic from client to database.
But, from what Mark stated so far I'm sure he is not going to migrate
his app just for fun, resp. without having considered this.
> NEVER reimplement an existing system unless the project includes
> substantial functional imporovement.
or monetary issues
I know one big database that was migrated from Oracle to PG and
another from SQLServer to PG because of licence-costs. Definitely
there are some more.
That applies to MySQL, too; licence policy is somewhat obscure to me,
but under certain circumstances you have to pay
regards Harald
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com;
iQA/AwUBQUb+O8JpD/drhCuMEQJCZACgqdJsrWjOwdP779PFaFMjxdgvqkwAoIPc
jPONy6urLRLf3vylVjVlEyci
=/1Ka
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 18:20 aaron werman <awerman2@hotmail.com>
0 siblings, 0 replies; 52+ messages in thread
From: aaron werman @ 2004-09-14 18:20 UTC (permalink / raw)
To: harald@sector-x.de; pgsql-performance
>From: "Harald Lau (Sector-X)" <harald@sector-x.de>
...
> > From: "Mischa Sandberg" <ischamay.andbergsay@activestateway.com>
> >
> > > If your company is currently happy with MySQL, there probably are
> > > other (nontechnical) reasons to stick with it. I'm impressed that
> > > you'd consider reconsidering PG.
> >
> > I'd like to second Mischa on that issue.
>
>Though both of you are right from my point of view, I don't think
>it's very useful to discuss this item here.
>
It is kinda windy for the list, but the point is that a big part of
performance is developer expectation and user expectation. I'd hope to lower
expectations before we see an article in eWeek. Perhaps this thread should
move to the advocacy list until the migration needs specific advice.
_________________________________________________________________
Get ready for school! Find articles, homework help and more in the Back to
School Guide! http://special.msn.com/network/04backtoschool.armx
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 18:27 Vivek Khera <khera@kcilink.com>
parent: Mark Cotner <mcotner@yahoo.com>
1 sibling, 0 replies; 52+ messages in thread
From: Vivek Khera @ 2004-09-14 18:27 UTC (permalink / raw)
To: pgsql-performance
>>>>> "MC" == Mark Cotner <mcotner@yahoo.com> writes:
MC> I've finished porting the schema and am importing the
MC> data now. My estimates for just two-thirds(60 of the
MC> 90 days) of one of our 30 cable systems(MySQL dbs) is
MC> estimated to take about 16 hours. This may seem like
MC> a lot, but I'm satisfied with the performance. I've
be sure to load your data without indexes defined for your initial
import.
check your logs to see if increasing checkpoint_segments is
recommended. I found that bumping it up to 50 helped speed up my
data loads (restore from dump) significantly.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 20:38 Simon Riggs <simon@2ndquadrant.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 52+ messages in thread
From: Simon Riggs @ 2004-09-14 20:38 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; Markus Schaber <schabios@logi-track.com>; +Cc: pgsql-performance
> Stephen Frost
> * Markus Schaber (schabios@logi-track.com) wrote:
> > Generally, what is the fastest way for doing bulk processing of
> > update-if-primary-key-matches-and-insert-otherwise operations?
>
> This is a very good question, and I havn't seen much of an answer to it
> yet. I'm curious about the answer myself, actually. In the more recent
> SQL specs, from what I understand, this is essentially what the 'MERGE'
> command is for. This was recently added and unfortunately is not yet
> supported in Postgres. Hopefully it will be added soon.
>
Yes, I think it is an important feature for both Data Warehousing (used in
set-operation mode for bulk processing) and OLTP (saves a round-trip to the
database, so faster on single rows also). It's in my top 10 for 2005.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 22:33 Jim C. Nasby <decibel@decibel.org>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 52+ messages in thread
From: Jim C. Nasby @ 2004-09-14 22:33 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-performance@postgresql.org, Mark Cotner <mcotner@yahoo.com>; Christopher Browne <cbbrowne@acm.org>
On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
> PostgreSQL's functionality is in many ways similar to Oracle Partitioning.
>
> Loading up your data in many similar tables, then creating a view like:
>
> CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
> SELECT 200409130800, col1, col2, col3... FROM table200409130800
> UNION ALL
> SELECT 200409131000, col1, col2, col3... FROM table200409131000
> UNION ALL
> SELECT 200409131200, col1, col2, col3... FROM table200409131200
> ...etc...
>
> will allow the PostgreSQL optimizer to eliminate partitions from the query
> when you run queries which include a predicate on the partitioning_col, e.g.
>
> select count(*) from bigtable where idate >= 200409131000
>
> will scan the last two partitions only...
>
> There are a few other ways of creating the view that return the same answer,
> but only using constants in that way will allow the partitions to be
> eliminated from the query, and so run for much longer.
Is there by any chance a set of functions to manage adding and removing
partitions? Certainly this can be done by hand, but having a set of
tools would make life much easier. I just looked but didn't see anything
on GBorg.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-14 22:58 Mischa Sandberg <ischamay.andbergsay@activestateway.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 2 replies; 52+ messages in thread
From: Mischa Sandberg @ 2004-09-14 22:58 UTC (permalink / raw)
To: pgsql-performance
Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
and DB2's implementation of MERGE, which does what AMOUNTS to what is
described below (one mass UPDATE...FROM, one mass INSERT...WHERE NOT
EXISTS).
No, you shouldn't iterate row-by-row through the temp table.
Whenever possible, try to do updates in one single (mass) operation.
Doing it that way gives the optimizer the best chance at amortizing
fixed costs, and batching operations.
---------
In any database other than Postgres, I would recommend doing the
INSERT /followed by/ the UPDATE. That order looks wonky --- your update
ends up pointlessly operating on the rows just INSERTED. The trick is,
UPDATE acquires and holds write locks (the rows were previously visible
to other processes), while INSERT's write locks refer to rows that no
other process could try to lock.
Stephen Frost wrote:
> * Markus Schaber (schabios@logi-track.com) wrote:
>
>>Generally, what is the fastest way for doing bulk processing of
>>update-if-primary-key-matches-and-insert-otherwise operations?
>
>
> This is a very good question, and I havn't seen much of an answer to it
> yet. I'm curious about the answer myself, actually. In the more recent
> SQL specs, from what I understand, this is essentially what the 'MERGE'
> command is for. This was recently added and unfortunately is not yet
> supported in Postgres. Hopefully it will be added soon.
>
> Otherwise, what I've done is basically an update followed by an insert
> using outer joins. If there's something better, I'd love to hear about
> it. The statements looks something like:
>
> update X
> set colA = a.colA,
> colB = a.colB
> from Y a
> where keyA = a.keyA and
> keyB = a.keyB;
>
> insert into X
> select a.keyA,
> a.keyB,
> a.colA,
> a.colB
> from Y a left join X b
> using (keyA, keyB)
> where b.keyA is NULL and
> b.keyB is NULL;
>
> With the appropriate indexes, this is pretty fast but I think a merge
> would be much faster.
>
> Thanks,
>
> Stephen
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 23:32 Simon Riggs <simon@2ndquadrant.com>
parent: Jim C. Nasby <decibel@decibel.org>
1 sibling, 4 replies; 52+ messages in thread
From: Simon Riggs @ 2004-09-14 23:32 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-performance; Mark Cotner <mcotner@yahoo.com>; Christopher Browne <cbbrowne@acm.org>
> Jim C. Nasby
> On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
> > PostgreSQL's functionality is in many ways similar to Oracle
> Partitioning.
> >
> > Loading up your data in many similar tables, then creating a view like:
> >
> > CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
> > SELECT 200409130800, col1, col2, col3... FROM table200409130800
> > UNION ALL
> > SELECT 200409131000, col1, col2, col3... FROM table200409131000
> > UNION ALL
> > SELECT 200409131200, col1, col2, col3... FROM table200409131200
> > ...etc...
> >
> > will allow the PostgreSQL optimizer to eliminate partitions
> from the query
> > when you run queries which include a predicate on the
> partitioning_col, e.g.
> >
> > select count(*) from bigtable where idate >= 200409131000
> >
> > will scan the last two partitions only...
> >
> > There are a few other ways of creating the view that return the
> same answer,
> > but only using constants in that way will allow the partitions to be
> > eliminated from the query, and so run for much longer.
>
> Is there by any chance a set of functions to manage adding and removing
> partitions? Certainly this can be done by hand, but having a set of
> tools would make life much easier. I just looked but didn't see anything
> on GBorg.
Well, its fairly straightforward to auto-generate the UNION ALL view, and
important as well, since it needs to be re-specified each time a new
partition is loaded or an old one is cleared down. The main point is that
the constant placed in front of each table must in some way relate to the
data, to make it useful in querying. If it is just a unique constant, chosen
at random, it won't do much for partition elimination. So, that tends to
make the creation of the UNION ALL view an application/data specific thing.
The "partitions" are just tables, so no need for other management tools.
Oracle treats the partitions as sub-tables, so you need a range of commands
to add, swap etc the partitions of the main table.
I guess a set of tools that emulates that functionality would be generically
a good thing, if you can see a way to do that.
Oracle partitions were restricted in only allowing a single load statement
into a single partition at any time, whereas multiple COPY statements can
access a single partition table on PostgreSQL.
BTW, multi-dimensional partitioning is also possible using the same general
scheme....
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-14 23:32 Mischa Sandberg <ischamay.andbergsay@activestateway.com>
parent: Simon Riggs <simon@2ndquadrant.com>
3 siblings, 0 replies; 52+ messages in thread
From: Mischa Sandberg @ 2004-09-14 23:32 UTC (permalink / raw)
To: pgsql-performance
Simon Riggs wrote:
>>Jim C. Nasby
>>On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
>>
>>>PostgreSQL's functionality is in many ways similar to Oracle
>>Partitioning.
>>
>>>Loading up your data in many similar tables, then creating a view like:
>>>
>>>CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
>>>SELECT 200409130800, col1, col2, col3... FROM table200409130800
>>>UNION ALL
>>>SELECT 200409131000, col1, col2, col3... FROM table200409131000
>>>UNION ALL
>>>SELECT 200409131200, col1, col2, col3... FROM table200409131200
>>>...etc...
>>>
>>>will allow the PostgreSQL optimizer to eliminate partitions
>>from the query
>>>when you run queries which include a predicate on the
>>partitioning_col, e.g.
>>
>>>select count(*) from bigtable where idate >= 200409131000
>
> The "partitions" are just tables, so no need for other management tools.
> Oracle treats the partitions as sub-tables, so you need a range of commands
> to add, swap etc the partitions of the main table.
A few years ago I wrote a federated query engine (wrapped as an ODBC
driver) that had to handle thousands of contributors (partitions) to a
pseudotable / VIEWofUNIONs. Joins did require some special handling in
the optimizer, because of the huge number of crossproducts between
different tables. It was definitely worth the effort at the time,
because you need different strategies for: joining a partition to
another partition on the same subserver; joining two large partitions on
different servers; and joining a large partition on one server to a
small one on another.
The differences may not be so great for a solitary server;
but they're still there, because of disparity in subtable sizes. The
simplistic query plans tend to let you down, when you're dealing with
honking warehouses.
I'm guessing that Oracle keeps per-subtable AND cross-all-subtables
statistics, rather than building the latter from scratch in the course
of evaluating the query plan. That's the one limitation I see in
emulating their partitioned tables with Views.
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-15 01:01 Michael Glaesemann <grzm@myrealbox.com>
parent: Simon Riggs <simon@2ndquadrant.com>
3 siblings, 0 replies; 52+ messages in thread
From: Michael Glaesemann @ 2004-09-15 01:01 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Mark Cotner <mcotner@yahoo.com>; Jim C. Nasby <decibel@decibel.org>; pgsql-performance; Christopher Browne <cbbrowne@acm.org>
On Sep 15, 2004, at 8:32 AM, Simon Riggs wrote:
> The "partitions" are just tables, so no need for other management
> tools.
> Oracle treats the partitions as sub-tables, so you need a range of
> commands
> to add, swap etc the partitions of the main table.
>
> I guess a set of tools that emulates that functionality would be
> generically
> a good thing, if you can see a way to do that.
>
> Oracle partitions were restricted in only allowing a single load
> statement
> into a single partition at any time, whereas multiple COPY statements
> can
> access a single partition table on PostgreSQL.
How does this compare to DB2 partitioning?
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-15 02:34 Chris Browne <cbbrowne@acm.org>
parent: Simon Riggs <simon@2ndquadrant.com>
3 siblings, 1 reply; 52+ messages in thread
From: Chris Browne @ 2004-09-15 02:34 UTC (permalink / raw)
To: pgsql-performance
simon@2ndquadrant.com ("Simon Riggs") writes:
> Well, its fairly straightforward to auto-generate the UNION ALL view, and
> important as well, since it needs to be re-specified each time a new
> partition is loaded or an old one is cleared down. The main point is that
> the constant placed in front of each table must in some way relate to the
> data, to make it useful in querying. If it is just a unique constant, chosen
> at random, it won't do much for partition elimination. So, that tends to
> make the creation of the UNION ALL view an application/data specific thing.
Ah, that's probably a good thought.
When we used big "UNION ALL" views, it was with logging tables, where
there wasn't really any meaningful distinction between partitions.
So you say that if the VIEW contains, within it, meaningful constraint
information, that can get applied to chop out irrelevant bits?
That suggests a way of resurrecting the idea...
Might we set up the view as:
create view combination_of_logs as
select * from table_1 where txn_date between 'this' and 'that'
union all
select * from table_2 where txn_date between 'this2' and 'that2'
union all
select * from table_3 where txn_date between 'this3' and 'that3'
union all
select * from table_4 where txn_date between 'this4' and 'that4'
union all
... ad infinitum
union all
select * from table_n where txn_date > 'start_of_partition_n';
and expect that to help, as long as the query that hooks up to this
has date constraints?
We'd have to regenerate the view with new fixed constants each time we
set up the tables, but that sounds like it could work...
--
"cbbrowne","@","acm.org"
http://www3.sympatico.ca/cbbrowne/x.html
But what can you do with it? -- ubiquitous cry from Linux-user
partner. -- Andy Pearce, <ajp@hpopd.pwd.hp.com>
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-15 03:33 Christopher Browne <cbbrowne@acm.org>
parent: Simon Riggs <simon@2ndquadrant.com>
3 siblings, 0 replies; 52+ messages in thread
From: Christopher Browne @ 2004-09-15 03:33 UTC (permalink / raw)
To: pgsql-performance
simon@2ndquadrant.com ("Simon Riggs") wrote:
> The main point is that the constant placed in front of each table
> must in some way relate to the data, to make it useful in
> querying. If it is just a unique constant, chosen at random, it
> won't do much for partition elimination.
It just struck me - this is much the same notion as that of "cutting
planes" used in Integer Programming.
The approach, there, is that you take a linear program, which can give
fractional results, and throw on as many additional constraints as you
need in order to force the likelihood of particular variable falling
on integer values. The constraints may appear redundant, but
declaring them allows the answers to be pushed in the right
directions.
In this particular case, the (arguably redundant) constraints let the
query optimizer have criteria for throwing out unnecessary tables.
Thanks for pointing this out; it may turn a fowl into a feature, when
I can get some "round tuits" :-). That should allow me to turn an
81-way evil join into something that's 4-way at the worst.
Cheers!
--
"cbbrowne","@","linuxfinances.info"
http://linuxfinances.info/info/nonrdbms.html
Implementing systems is 95% boredom and 5% sheer terror.
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 04:30 Joe Conway <mail@joeconway.com>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 52+ messages in thread
From: Joe Conway @ 2004-09-15 04:30 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Chris Browne wrote:
> Might we set up the view as:
>
> create view combination_of_logs as
> select * from table_1 where txn_date between 'this' and 'that'
> union all
> select * from table_2 where txn_date between 'this2' and 'that2'
> union all
> select * from table_3 where txn_date between 'this3' and 'that3'
> union all
> select * from table_4 where txn_date between 'this4' and 'that4'
> union all
> ... ad infinitum
> union all
> select * from table_n where txn_date > 'start_of_partition_n';
>
> and expect that to help, as long as the query that hooks up to this
> has date constraints?
>
> We'd have to regenerate the view with new fixed constants each time we
> set up the tables, but that sounds like it could work...
That's exactly what we're doing, but using inherited tables instead of a
union view. With inheritance, there is no need to rebuild the view each
time a table is added or removed. Basically, in our application, tables
are partitioned by either month or week, depending on the type of data
involved, and queries are normally date qualified.
We're not completely done with our data conversion (from a commercial
RDBMSi), but so far the results have been excellent. Similar to what
others have said in this thread, the conversion involved restructuring
the data to better suit Postgres, and the application (data
analysis/mining vs. the source system which is operational). As a result
we've compressed a > 1TB database down to ~0.4TB, and seen at least one
typical query reduced from ~9 minutes down to ~40 seconds.
Joe
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 04:54 Iain <iain@mst.co.jp>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 52+ messages in thread
From: Iain @ 2004-09-15 04:54 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-performance
Hi Joe,
> That's exactly what we're doing, but using inherited tables instead of a
> union view. With inheritance, there is no need to rebuild the view each
> time a table is added or removed. Basically, in our application, tables
> are partitioned by either month or week, depending on the type of data
> involved, and queries are normally date qualified.
That sounds interesting. I have to admit that I havn't touched iheritance in
pg at all yet so I find it hard to imagine how this would work. If you have
a chance, would you mind elaborating on it just a little?
Regards
Iain
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
@ 2004-09-15 05:10 Steve Atkins <steve@blighty.com>
parent: Jim C. Nasby <decibel@decibel.org>
1 sibling, 1 reply; 52+ messages in thread
From: Steve Atkins @ 2004-09-15 05:10 UTC (permalink / raw)
To: pgsql-performance
On Tue, Sep 14, 2004 at 05:33:33PM -0500, Jim C. Nasby wrote:
> On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
> > PostgreSQL's functionality is in many ways similar to Oracle Partitioning.
> >
> > Loading up your data in many similar tables, then creating a view like:
> >
> > CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
> > SELECT 200409130800, col1, col2, col3... FROM table200409130800
> > UNION ALL
> > SELECT 200409131000, col1, col2, col3... FROM table200409131000
> > UNION ALL
> > SELECT 200409131200, col1, col2, col3... FROM table200409131200
> > ...etc...
[...]
>
> Is there by any chance a set of functions to manage adding and removing
> partitions? Certainly this can be done by hand, but having a set of
> tools would make life much easier. I just looked but didn't see anything
> on GBorg.
I've done a similar thing with time-segregated data by inheriting
all the partition tables from an (empty) parent table.
Adding a new partition is just a "create table tablefoo () inherits(bigtable)"
and removing a partition just "drop table tablefoo".
Cheers,
Steve
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 06:15 Christopher Browne <cbbrowne@acm.org>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 52+ messages in thread
From: Christopher Browne @ 2004-09-15 06:15 UTC (permalink / raw)
To: pgsql-performance
In the last exciting episode, mail@joeconway.com (Joe Conway) wrote:
> That's exactly what we're doing, but using inherited tables instead of
> a union view. With inheritance, there is no need to rebuild the view
> each time a table is added or removed. Basically, in our application,
> tables are partitioned by either month or week, depending on the type
> of data involved, and queries are normally date qualified.
Sounds interesting, and possibly usable.
Where does the constraint come in that'll allow most of the data to be
excluded?
Or is this just that the entries are all part of "bigtable" so that
the self join is only 2-way?
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/advocacy.html
"Be humble. A lot happened before you were born." - Life's Little
Instruction Book
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 06:51 Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
0 siblings, 0 replies; 52+ messages in thread
From: Leeuw van der, Tim @ 2004-09-15 06:51 UTC (permalink / raw)
To: mkl@webde-ag.de; +Cc: Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
Hi,
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Michael Kleiser
Sent: Tuesday, September 14, 2004 4:23 PM
To: Leeuw van der, Tim
Cc: Steinar H. Gunderson; PostgreSQL Performance List
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
> What MySQl-table-type did you use?
> Was it "MyISAM" which don't supports transactions ?
> Yes I read about that bulk-inserts with this table-type are very fast.
> In Data Warehouse one often don't need transactions.
Although totally beyond the scope of this thread, we used InnoDB tables with MySQL because of the transaction-support.
regards,
--Tim
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 09:10 simon@2ndquadrant.com
0 siblings, 1 reply; 52+ messages in thread
From: simon@2ndquadrant.com @ 2004-09-15 09:10 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-performance
Joe Conway <mail@joeconway.com> wrote on 15.09.2004, 06:30:24:
> Chris Browne wrote:
> > Might we set up the view as:
> >
> > create view combination_of_logs as
> > select * from table_1 where txn_date between 'this' and 'that'
> > union all
> > select * from table_2 where txn_date between 'this2' and 'that2'
> > union all
> > select * from table_3 where txn_date between 'this3' and 'that3'
> > union all
> > select * from table_4 where txn_date between 'this4' and 'that4'
> > union all
> > ... ad infinitum
> > union all
> > select * from table_n where txn_date > 'start_of_partition_n';
> >
> > and expect that to help, as long as the query that hooks up to this
> > has date constraints?
> >
> > We'd have to regenerate the view with new fixed constants each time we
> > set up the tables, but that sounds like it could work...
>
> That's exactly what we're doing, but using inherited tables instead of a
> union view. With inheritance, there is no need to rebuild the view each
> time a table is added or removed. Basically, in our application, tables
> are partitioned by either month or week, depending on the type of data
> involved, and queries are normally date qualified.
>
> We're not completely done with our data conversion (from a commercial
> RDBMSi), but so far the results have been excellent. Similar to what
> others have said in this thread, the conversion involved restructuring
> the data to better suit Postgres, and the application (data
> analysis/mining vs. the source system which is operational). As a result
> we've compressed a > 1TB database down to ~0.4TB, and seen at least one
> typical query reduced from ~9 minutes down to ~40 seconds.
Sounds interesting.
The performance gain comes from partition elimination of the inherited
tables under the root?
I take it the compression comes from use of arrays, avoiding the need
for additional rows and key overhead?
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 09:16 Markus Schaber <schabios@logi-track.com>
parent: Steve Atkins <steve@blighty.com>
0 siblings, 1 reply; 52+ messages in thread
From: Markus Schaber @ 2004-09-15 09:16 UTC (permalink / raw)
To: pgsql-performance
Hi,
On Tue, 14 Sep 2004 22:10:04 -0700
Steve Atkins <steve@blighty.com> wrote:
> > Is there by any chance a set of functions to manage adding and removing
> > partitions? Certainly this can be done by hand, but having a set of
> > tools would make life much easier. I just looked but didn't see anything
> > on GBorg.
>
> I've done a similar thing with time-segregated data by inheriting
> all the partition tables from an (empty) parent table.
>
> Adding a new partition is just a "create table tablefoo () inherits(bigtable)"
> and removing a partition just "drop table tablefoo".
But you have to add table constraints restricting the time after adding
the partition?
Thanks,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 15:15 Joe Conway <mail@joeconway.com>
parent: simon@2ndquadrant.com
0 siblings, 2 replies; 52+ messages in thread
From: Joe Conway @ 2004-09-15 15:15 UTC (permalink / raw)
To: simon@2ndquadrant.com; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-performance
simon@2ndquadrant.com wrote:
> Joe Conway <mail@joeconway.com> wrote on 15.09.2004, 06:30:24:
>>We're not completely done with our data conversion (from a commercial
>>RDBMSi), but so far the results have been excellent. Similar to what
>>others have said in this thread, the conversion involved restructuring
>>the data to better suit Postgres, and the application (data
>>analysis/mining vs. the source system which is operational). As a result
>>we've compressed a > 1TB database down to ~0.4TB, and seen at least one
>>typical query reduced from ~9 minutes down to ~40 seconds.
>
> Sounds interesting.
>
> The performance gain comes from partition elimination of the inherited
> tables under the root?
>
> I take it the compression comes from use of arrays, avoiding the need
> for additional rows and key overhead?
Sorry, in trying to be concise I was not very clear. I'm using the term
compression very generally here. I'll try to give a bit more background,
The original data source is a database schema designed for use by an
operational application that my company sells to provide enhanced
management of equipment that we also sell. The application needs to be
very flexible in exactly what data it stores in order to be useful
across a wide variety of equipment models and versions. In order to do
that there is a very large central "transaction" table that stores
name->value pairs in varchar columns. The name->value pairs come from
parsed output of the equipment, and as such there is a fair amount of
redundancy and unneeded data that ends up getting stored. At each
installation in the field this table can get very large (> billion
rows). Additionally the application prematerializes a variety of
summaries for use by the operators using the GUI.
We collect the data exported from each of the systems in the field and
accumulate it in a single central database for data mining and analysis.
This is the database that is actually being converted. By compression I
really mean that unneeded and redundant data is being stripped out, and
data known to be of a certain datatype is stored in that type instead of
varchar (e.g. values known to be int are stored as int). Also the
summaries are not being converted (although we do some post processing
to create new materialized summaries).
My points in telling this were:
- the use of inherited tables to partition this huge number of rows and
yet allow simple query access to it seems to work well, at least in
early validation tests
- had we simply taken the original database and "slammed" it into
Postgres with no further thought, we would not have seen the big
improvements, and thus the project might have been seen as a failure
(even though it saves substantial $)
Hope that's a bit more clear. I'm hoping to write up a more detailed
case study once we've cut the Postgres system into production and the
dust settles a bit.
Joe
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 17:28 Josh Berkus <josh@agliodbs.com>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 52+ messages in thread
From: Josh Berkus @ 2004-09-15 17:28 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: simon@2ndquadrant.com, Chris Browne <cbbrowne@acm.org>; pgsql-performance
Joe,
> - the use of inherited tables to partition this huge number of rows and
> yet allow simple query access to it seems to work well, at least in
> early validation tests
> - had we simply taken the original database and "slammed" it into
> Postgres with no further thought, we would not have seen the big
> improvements, and thus the project might have been seen as a failure
> (even though it saves substantial $)
Any further thoughts on developing this into true table partitioning?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 17:34 Joe Conway <mail@joeconway.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 52+ messages in thread
From: Joe Conway @ 2004-09-15 17:34 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: simon@2ndquadrant.com, Chris Browne <cbbrowne@acm.org>; pgsql-performance
Josh Berkus wrote:
>> - the use of inherited tables to partition this huge number of rows and
>> yet allow simple query access to it seems to work well, at least in
>> early validation tests
>> - had we simply taken the original database and "slammed" it into
>> Postgres with no further thought, we would not have seen the big
>> improvements, and thus the project might have been seen as a failure
>> (even though it saves substantial $)
>
>
> Any further thoughts on developing this into true table partitioning?
>
Just that I'd love to see it happen ;-)
Maybe someday I'll be able to find the time to work on it myself, but
for the moment I'm satisfied with the workarounds we've made.
Joe
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables?
@ 2004-09-15 20:54 Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 52+ messages in thread
From: Simon Riggs @ 2004-09-15 20:54 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Chris Browne <cbbrowne@acm.org> wrote on 15.09.2004, 04:34:53:
> simon@2ndquadrant.com ("Simon Riggs") writes:
> > Well, its fairly straightforward to auto-generate the UNION ALL view,
and
> > important as well, since it needs to be re-specified each time a new
> > partition is loaded or an old one is cleared down. The main point is
that
> > the constant placed in front of each table must in some way relate to
the
> > data, to make it useful in querying. If it is just a unique constant,
chosen
> > at random, it won't do much for partition elimination. So, that tends to
> > make the creation of the UNION ALL view an application/data specific
thing.
>
> Ah, that's probably a good thought.
>
> When we used big "UNION ALL" views, it was with logging tables, where
> there wasn't really any meaningful distinction between partitions.
>
> So you say that if the VIEW contains, within it, meaningful constraint
> information, that can get applied to chop out irrelevant bits?
>
> That suggests a way of resurrecting the idea...
>
> Might we set up the view as:
>
> create view combination_of_logs as
> select * from table_1 where txn_date between 'this' and 'that'
> union all
> select * from table_2 where txn_date between 'this2' and 'that2'
> union all
> select * from table_3 where txn_date between 'this3' and 'that3'
> union all
> select * from table_4 where txn_date between 'this4' and 'that4'
> union all
> ... ad infinitum
> union all
> select * from table_n where txn_date > 'start_of_partition_n';
>
> and expect that to help, as long as the query that hooks up to this
> has date constraints?
>
That way of phrasing the view can give you the right answer to the
query, but does not exclude partitions.
With the above way of doing things, you end up with predicate phrases of
the form ((PARTLIMITLO < partcol) AND (PARTLIMITHI > partcol) AND
(partcol > QUERYLIMITLO) AND (partcol < QUERYLIMITHI))
...if the values in capitals are constants, then this should evaluate to
a true or false value for each partition table. The optimizer doesn't
yet do this....
If you specify the view the way I showed, then the predicate query
becomes a comparison of constants, which DOES get evaluated prior to
full execution....you will see this as a "one time test: false" in the
EXPLAIN.
The way you've phrased the view is the more obvious way to phrase it,
and I'd spent a few days trying to work out how to solve the algebra
above in code....but that was wasted effort.
Anyway, if you use constants you can still specify ranges and betweens
and have them work... hence my example showed date-like integers - but
I don't think it just applies to one datatype.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 20:56 Joe Conway <mail@joeconway.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 52+ messages in thread
From: Joe Conway @ 2004-09-15 20:56 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-performance
Simon Riggs wrote:
> Joe,
>
> Your application is very interesting. I've just read your OSCON paper. I'd
> like to talk more about that. Very similar to Kalido.
>
> ...but back to partitioning momentarily: Does the performance gain come from
> partition elimination of the inherited tables under the root?
I think the major part of the peformance gain comes from the fact that
the source database has different needs in terms of partitioning
criteria because of it's different purpose. The data is basically
partitioned by customer installation instead of by date. Our converted
scheme partitions by date, which is in line with the analytical queries
run at the corporate office. Again, this is an argument in favor of not
simply porting what you're handed.
We might get similar query performance with a single large table and
multiple partial indexes (e.g. one per month), but there would be one
tradeoff and one disadvantage to that:
1) The indexes would need to be generated periodically -- this is a
tradeoff since we currently need to create inherited tables at the same
periodicity
2) It would be much more difficult to "roll off" a month's worth of data
when needed. The general idea is that each month we create a new monthly
table, then archive and drop the oldest monthly table. If all the data
were in one big table we would have to delete many millions of rows from
a (possibly) multibillion row table, and then vacuum that table -- no
thanks ;-)
Joe
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-15 20:59 Simon Riggs <simon@2ndquadrant.com>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 52+ messages in thread
From: Simon Riggs @ 2004-09-15 20:59 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-performance
Joe,
Your application is very interesting. I've just read your OSCON paper. I'd
like to talk more about that. Very similar to Kalido.
...but back to partitioning momentarily: Does the performance gain come from
partition elimination of the inherited tables under the root?
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 04:07 Joe Conway <mail@joeconway.com>
parent: Iain <iain@mst.co.jp>
0 siblings, 1 reply; 52+ messages in thread
From: Joe Conway @ 2004-09-16 04:07 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: pgsql-performance
Iain wrote:
>>That's exactly what we're doing, but using inherited tables instead of a
>>union view. With inheritance, there is no need to rebuild the view each
>>time a table is added or removed. Basically, in our application, tables
>>are partitioned by either month or week, depending on the type of data
>>involved, and queries are normally date qualified.
>
> That sounds interesting. I have to admit that I havn't touched iheritance in
> pg at all yet so I find it hard to imagine how this would work. If you have
> a chance, would you mind elaborating on it just a little?
OK, see below:
=====================
create table foo(f1 int, f2 date, f3 float8);
create table foo_2004_01() inherits (foo);
create table foo_2004_02() inherits (foo);
create table foo_2004_03() inherits (foo);
create index foo_2004_01_idx1 on foo_2004_01(f2);
create index foo_2004_02_idx1 on foo_2004_02(f2);
create index foo_2004_03_idx1 on foo_2004_03(f2);
insert into foo_2004_02 values(1,'2004-feb-15',3.14);
-- needed just for illustration since these are toy tables
set enable_seqscan to false;
explain analyze select * from foo where f2 = '2004-feb-15';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=100000000.00..100000061.32 rows=16 width=16) (actual
time=0.224..0.310 rows=1 loops=1)
-> Append (cost=100000000.00..100000061.32 rows=16 width=16)
(actual time=0.214..0.294 rows=1 loops=1)
-> Seq Scan on foo (cost=100000000.00..100000022.50 rows=5
width=16) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_01_idx1 on foo_2004_01 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.101..0.101 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_02_idx1 on foo_2004_02 foo
(cost=0.00..4.68 rows=1 width=16) (actual time=0.095..0.101 rows=1 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_03_idx1 on foo_2004_03 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.066..0.066 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
Total runtime: 0.582 ms
(11 rows)
create table foo_2004_04() inherits (foo);
create index foo_2004_04_idx1 on foo_2004_04(f2);
explain analyze select * from foo where f2 = '2004-feb-15';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=100000000.00..100000078.38 rows=21 width=16) (actual
time=0.052..0.176 rows=1 loops=1)
-> Append (cost=100000000.00..100000078.38 rows=21 width=16)
(actual time=0.041..0.159 rows=1 loops=1)
-> Seq Scan on foo (cost=100000000.00..100000022.50 rows=5
width=16) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_01_idx1 on foo_2004_01 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_02_idx1 on foo_2004_02 foo
(cost=0.00..4.68 rows=1 width=16) (actual time=0.016..0.022 rows=1 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_03_idx1 on foo_2004_03 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_04_idx1 on foo_2004_04 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.095..0.095 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
Total runtime: 0.443 ms
(13 rows)
For loading data, we COPY into foo, and have a trigger that redirects
the rows to the appropriate partition.
Notice that the partitions which do not contain any data of interest are
still probed for data, but since they have none it is very quick. In a
real life example I got the following results just this afternoon:
- aggregate row count = 471,849,665
- total number inherited tables = 216
(many are future dated and therefore contain no data)
- select one month's worth of data for one piece of equipment by serial
number (49,257 rows) = 526.015 ms
Not too bad -- quick enough for my needs. BTW, this is using NFS mounted
storage (NetApp NAS).
Joe
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 04:17 Steve Atkins <steve@blighty.com>
parent: Markus Schaber <schabios@logi-track.com>
0 siblings, 1 reply; 52+ messages in thread
From: Steve Atkins @ 2004-09-16 04:17 UTC (permalink / raw)
To: pgsql-performance
On Wed, Sep 15, 2004 at 11:16:44AM +0200, Markus Schaber wrote:
> Hi,
>
> On Tue, 14 Sep 2004 22:10:04 -0700
> Steve Atkins <steve@blighty.com> wrote:
>
> > > Is there by any chance a set of functions to manage adding and removing
> > > partitions? Certainly this can be done by hand, but having a set of
> > > tools would make life much easier. I just looked but didn't see anything
> > > on GBorg.
> >
> > I've done a similar thing with time-segregated data by inheriting
> > all the partition tables from an (empty) parent table.
> >
> > Adding a new partition is just a "create table tablefoo () inherits(bigtable)"
> > and removing a partition just "drop table tablefoo".
>
> But you have to add table constraints restricting the time after adding
> the partition?
Uhm... unless I'm confused that's not a meaningful thing in this context.
There's no rule that's putting insertions into an inherited table - the
decision of which inherited table to insert into is made at application
level.
As I was using it to segregate data based on creation timestamp the
application just inserts into the 'newest' inherited table until it's
full, then creates a new inherited table.
I've no doubt you could set up rules to scatter inserted data across
a number of tables, but that's not something that's been applicaable
for the problems I tend to work with, so I've not looked at it.
Cheers,
Steve
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 04:29 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
1 sibling, 0 replies; 52+ messages in thread
From: Christopher Kings-Lynne @ 2004-09-16 04:29 UTC (permalink / raw)
To: ischamay.andbergsay@activestateway.com; +Cc: pgsql-performance
>> insert into X
>> select a.keyA,
>> a.keyB,
>> a.colA,
>> a.colB
>> from Y a left join X b
>> using (keyA, keyB)
>> where b.keyA is NULL and
>> b.keyB is NULL;
>>
>> With the appropriate indexes, this is pretty fast but I think a merge
>> would be much faster.
Problem is it's subject to race conditions if another process is
inserting stuff at the same time...
Chris
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 05:08 Iain <iain@mst.co.jp>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 52+ messages in thread
From: Iain @ 2004-09-16 05:08 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-performance
Hi Joe,
You went to quite a bit of effort, thanks I have the picture now.
Using inheritence seems to be a useful refinement on top of the earlier
outlined aproach using the UNION ALL view with appropriate predicates on the
condition used to do the partitioning. Having the individual partitions
derived from a parent table makes a lot of sense.
regards
Iain
----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "Iain" <iain@mst.co.jp>
Cc: <pgsql-performance@postgresql.org>
Sent: Thursday, September 16, 2004 1:07 PM
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
> Iain wrote:
> >>That's exactly what we're doing, but using inherited tables instead of a
> >>union view. With inheritance, there is no need to rebuild the view each
> >>time a table is added or removed. Basically, in our application, tables
> >>are partitioned by either month or week, depending on the type of data
> >>involved, and queries are normally date qualified.
> >
> > That sounds interesting. I have to admit that I havn't touched
iheritance in
> > pg at all yet so I find it hard to imagine how this would work. If you
have
> > a chance, would you mind elaborating on it just a little?
>
> OK, see below:
> =====================
>
> create table foo(f1 int, f2 date, f3 float8);
>
> create table foo_2004_01() inherits (foo);
> create table foo_2004_02() inherits (foo);
> create table foo_2004_03() inherits (foo);
>
> create index foo_2004_01_idx1 on foo_2004_01(f2);
> create index foo_2004_02_idx1 on foo_2004_02(f2);
> create index foo_2004_03_idx1 on foo_2004_03(f2);
>
> insert into foo_2004_02 values(1,'2004-feb-15',3.14);
>
>
> -- needed just for illustration since these are toy tables
> set enable_seqscan to false;
> explain analyze select * from foo where f2 = '2004-feb-15';
>
> QUERY PLAN
> --------------------------------------------------------------------------
----------------------------------------------------------------------
> Result (cost=100000000.00..100000061.32 rows=16 width=16) (actual
> time=0.224..0.310 rows=1 loops=1)
> -> Append (cost=100000000.00..100000061.32 rows=16 width=16)
> (actual time=0.214..0.294 rows=1 loops=1)
> -> Seq Scan on foo (cost=100000000.00..100000022.50 rows=5
> width=16) (actual time=0.004..0.004 rows=0 loops=1)
> Filter: (f2 = '2004-02-15'::date)
> -> Index Scan using foo_2004_01_idx1 on foo_2004_01 foo
> (cost=0.00..17.07 rows=5 width=16) (actual time=0.101..0.101 rows=0
loops=1)
> Index Cond: (f2 = '2004-02-15'::date)
> -> Index Scan using foo_2004_02_idx1 on foo_2004_02 foo
> (cost=0.00..4.68 rows=1 width=16) (actual time=0.095..0.101 rows=1
loops=1)
> Index Cond: (f2 = '2004-02-15'::date)
> -> Index Scan using foo_2004_03_idx1 on foo_2004_03 foo
> (cost=0.00..17.07 rows=5 width=16) (actual time=0.066..0.066 rows=0
loops=1)
> Index Cond: (f2 = '2004-02-15'::date)
> Total runtime: 0.582 ms
> (11 rows)
>
> create table foo_2004_04() inherits (foo);
> create index foo_2004_04_idx1 on foo_2004_04(f2);
>
> explain analyze select * from foo where f2 = '2004-feb-15';
>
> QUERY PLAN
> --------------------------------------------------------------------------
----------------------------------------------------------------------
> Result (cost=100000000.00..100000078.38 rows=21 width=16) (actual
> time=0.052..0.176 rows=1 loops=1)
> -> Append (cost=100000000.00..100000078.38 rows=21 width=16)
> (actual time=0.041..0.159 rows=1 loops=1)
> -> Seq Scan on foo (cost=100000000.00..100000022.50 rows=5
> width=16) (actual time=0.004..0.004 rows=0 loops=1)
> Filter: (f2 = '2004-02-15'::date)
> -> Index Scan using foo_2004_01_idx1 on foo_2004_01 foo
> (cost=0.00..17.07 rows=5 width=16) (actual time=0.012..0.012 rows=0
loops=1)
> Index Cond: (f2 = '2004-02-15'::date)
> -> Index Scan using foo_2004_02_idx1 on foo_2004_02 foo
> (cost=0.00..4.68 rows=1 width=16) (actual time=0.016..0.022 rows=1
loops=1)
> Index Cond: (f2 = '2004-02-15'::date)
> -> Index Scan using foo_2004_03_idx1 on foo_2004_03 foo
> (cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.008 rows=0
loops=1)
> Index Cond: (f2 = '2004-02-15'::date)
> -> Index Scan using foo_2004_04_idx1 on foo_2004_04 foo
> (cost=0.00..17.07 rows=5 width=16) (actual time=0.095..0.095 rows=0
loops=1)
> Index Cond: (f2 = '2004-02-15'::date)
> Total runtime: 0.443 ms
> (13 rows)
>
> For loading data, we COPY into foo, and have a trigger that redirects
> the rows to the appropriate partition.
>
> Notice that the partitions which do not contain any data of interest are
> still probed for data, but since they have none it is very quick. In a
> real life example I got the following results just this afternoon:
>
> - aggregate row count = 471,849,665
> - total number inherited tables = 216
> (many are future dated and therefore contain no data)
> - select one month's worth of data for one piece of equipment by serial
> number (49,257 rows) = 526.015 ms
>
> Not too bad -- quick enough for my needs. BTW, this is using NFS mounted
> storage (NetApp NAS).
>
> Joe
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 05:17 Joe Conway <mail@joeconway.com>
parent: Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 52+ messages in thread
From: Joe Conway @ 2004-09-16 05:17 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Christopher Browne wrote:
> In the last exciting episode, mail@joeconway.com (Joe Conway) wrote:
>>That's exactly what we're doing, but using inherited tables instead of
>>a union view. With inheritance, there is no need to rebuild the view
>>each time a table is added or removed. Basically, in our application,
>>tables are partitioned by either month or week, depending on the type
>>of data involved, and queries are normally date qualified.
> Where does the constraint come in that'll allow most of the data to be
> excluded?
Not sure I follow this.
> Or is this just that the entries are all part of "bigtable" so that
> the self join is only 2-way?
We don't have a need for self-joins in our application. We do use a
crosstab function to materialize some transposed views of the data,
however. That allows us to avoid self-joins in the cases where we might
otherwise need them.
Joe
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 09:08 Iain <iain@mst.co.jp>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 52+ messages in thread
From: Iain @ 2004-09-16 09:08 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Joe, Christopher,
Joe's example wasn't excluding partions, as he didn't use a predicated UNION
ALL view to select from. His queries use an indexed column that allow the
various partitions to be probed at low cost, and he was satisfied wth that.
My point in my previous post was that you could still do all that that if
you wanted to, by building the predicated view with UNION ALL of each of the
child tables.
regards
Iain
----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "Christopher Browne" <cbbrowne@acm.org>
Cc: <pgsql-performance@postgresql.org>
Sent: Thursday, September 16, 2004 2:17 PM
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
> Christopher Browne wrote:
> > In the last exciting episode, mail@joeconway.com (Joe Conway) wrote:
> >>That's exactly what we're doing, but using inherited tables instead of
> >>a union view. With inheritance, there is no need to rebuild the view
> >>each time a table is added or removed. Basically, in our application,
> >>tables are partitioned by either month or week, depending on the type
> >>of data involved, and queries are normally date qualified.
>
> > Where does the constraint come in that'll allow most of the data to be
> > excluded?
>
> Not sure I follow this.
>
> > Or is this just that the entries are all part of "bigtable" so that
> > the self join is only 2-way?
>
> We don't have a need for self-joins in our application. We do use a
> crosstab function to materialize some transposed views of the data,
> however. That allows us to avoid self-joins in the cases where we might
> otherwise need them.
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 10:39 Markus Schaber <schabios@logi-track.com>
parent: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
1 sibling, 0 replies; 52+ messages in thread
From: Markus Schaber @ 2004-09-16 10:39 UTC (permalink / raw)
To: ischamay.andbergsay@activestateway.com; +Cc: pgsql-performance
Hi, Mischa,
On Tue, 14 Sep 2004 22:58:20 GMT
Mischa Sandberg <ischamay.andbergsay@activestateway.com> wrote:
> Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
> and DB2's implementation of MERGE, which does what AMOUNTS to what is
> described below (one mass UPDATE...FROM, one mass INSERT...WHERE NOT
> EXISTS).
>
> No, you shouldn't iterate row-by-row through the temp table.
> Whenever possible, try to do updates in one single (mass) operation.
> Doing it that way gives the optimizer the best chance at amortizing
> fixed costs, and batching operations.
But when every updated row has a different value for the column(s) to be
updated, then I still have to use one update statement per row, which I
expect to be faster when done via a stored procedure than having the
whole client-server roundtrip including parsing every time. Or did I
miss some nice SQL statement?
Have a nice day,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 13:38 Markus Schaber <schabios@logi-track.com>
parent: Steve Atkins <steve@blighty.com>
0 siblings, 0 replies; 52+ messages in thread
From: Markus Schaber @ 2004-09-16 13:38 UTC (permalink / raw)
To: pgsql-performance
Hi, Steve,
On Wed, 15 Sep 2004 21:17:03 -0700
Steve Atkins <steve@blighty.com> wrote:
> On Wed, Sep 15, 2004 at 11:16:44AM +0200, Markus Schaber wrote:
> > But you have to add table constraints restricting the time after adding
> > the partition?
>
> Uhm... unless I'm confused that's not a meaningful thing in this context.
> There's no rule that's putting insertions into an inherited table - the
> decision of which inherited table to insert into is made at application
> level.
I thought of the query optimizer. I thought it could use the table
constraints to drop tables when creating the union. But now I think that
an index gives enough win, because the tree-based indices are rather
quick at returning zero rows when the queried value is out of the
indexed range.
Greetings,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-16 15:36 Joe Conway <mail@joeconway.com>
parent: Iain <iain@mst.co.jp>
1 sibling, 0 replies; 52+ messages in thread
From: Joe Conway @ 2004-09-16 15:36 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance
Iain wrote:
> Joe's example wasn't excluding partions, as he didn't use a predicated UNION
> ALL view to select from. His queries use an indexed column that allow the
> various partitions to be probed at low cost, and he was satisfied wth that.
Right.
> My point in my previous post was that you could still do all that that if
> you wanted to, by building the predicated view with UNION ALL of each of the
> child tables.
Right. It doesn't look that much different:
create or replace view foo_vw as
select * from foo_2004_01 where f2 >= '2004-jan-01' and f2 <= '2004-jan-31'
union all
select * from foo_2004_02 where f2 >= '2004-feb-01' and f2 <= '2004-feb-29'
union all
select * from foo_2004_03 where f2 >= '2004-mar-01' and f2 <= '2004-mar-31'
;
-- needed just for illustration since these are toy tables
set enable_seqscan to false;
explain analyze select * from foo_vw where f2 = '2004-feb-15';
QUERY PLAN
----------------------------------------------------------------------------------
Subquery Scan foo_vw (cost=0.00..14.54 rows=3 width=16) (actual
time=0.022..0.027 rows=1 loops=1)
-> Append (cost=0.00..14.51 rows=3 width=16) (actual
time=0.019..0.022 rows=1 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..4.84 rows=1
width=16) (actual time=0.004..0.004 rows=0 loops=1)
-> Index Scan using foo_2004_01_idx2 on foo_2004_01
(cost=0.00..4.83 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((f2 >= '2004-01-01'::date) AND (f2 <=
'2004-01-31'::date) AND (f2 = '2004-02-15'::date))
-> Subquery Scan "*SELECT* 2" (cost=0.00..4.84 rows=1
width=16) (actual time=0.013..0.015 rows=1 loops=1)
-> Index Scan using foo_2004_02_idx2 on foo_2004_02
(cost=0.00..4.83 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: ((f2 >= '2004-02-01'::date) AND (f2 <=
'2004-02-29'::date) AND (f2 = '2004-02-15'::date))
-> Subquery Scan "*SELECT* 3" (cost=0.00..4.84 rows=1
width=16) (actual time=0.001..0.001 rows=0 loops=1)
-> Index Scan using foo_2004_03_idx2 on foo_2004_03
(cost=0.00..4.83 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((f2 >= '2004-03-01'::date) AND (f2 <=
'2004-03-31'::date) AND (f2 = '2004-02-15'::date))
Total runtime: 0.188 ms
(12 rows)
regression=# explain analyze select * from foo where f2 = '2004-feb-15';
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=100000000.00..100000073.70 rows=20 width=16) (actual
time=0.059..0.091 rows=1 loops=1)
-> Append (cost=100000000.00..100000073.70 rows=20 width=16)
(actual time=0.055..0.086 rows=1 loops=1)
-> Seq Scan on foo (cost=100000000.00..100000022.50 rows=5
width=16) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_01_idx2 on foo_2004_01 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_02_idx2 on foo_2004_02 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_03_idx2 on foo_2004_03 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
Total runtime: 0.191 ms
(11 rows)
The main difference being that the view needs to be recreated every time
a table is added or dropped, whereas with the inherited tables method
that isn't needed.
Joe
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Data Warehouse Reevaluation - MySQL vs Postgres --
@ 2004-09-17 07:39 Simon Riggs <simon@2ndquadrant.com>
parent: Iain <iain@mst.co.jp>
1 sibling, 0 replies; 52+ messages in thread
From: Simon Riggs @ 2004-09-17 07:39 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; Joe Conway <mail@joeconway.com>; Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
> Iain
> Joe's example wasn't excluding partions, as he didn't use a
> predicated UNION
> ALL view to select from. His queries use an indexed column that allow the
> various partitions to be probed at low cost, and he was satisfied
> wth that.
Agreed - very very interesting design though.
> My point in my previous post was that you could still do all that that if
> you wanted to, by building the predicated view with UNION ALL of
> each of the
> child tables.
>
AFAICS of all the designs proposed there is still only one design *using
current PostgreSQL* that allows partitions to be excluded from queries as a
way of speeding up queries against very large tables: UNION ALL with
appended constants.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 52+ messages in thread
end of thread, other threads:[~2004-09-17 07:39 UTC | newest]
Thread overview: 52+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-12 04:24 Data Warehouse Reevaluation - MySQL vs Postgres Mark Cotner <mcotner@yahoo.com>
2004-09-12 05:22 ` Dennis Bjorklund <db@zigo.dhs.org>
2004-09-12 09:07 ` Gaetano Mendola <mendola@bigfoot.com>
2004-09-12 20:47 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Mischa Sandberg <ischamay.andbergsay@activestateway.com>
2004-09-13 02:29 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Christopher Browne <cbbrowne@acm.org>
2004-09-13 07:57 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Mark Cotner <mcotner@yahoo.com>
2004-09-13 09:21 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Christopher Browne <cbbrowne@acm.org>
2004-09-14 07:39 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Mark Cotner <mcotner@yahoo.com>
2004-09-14 09:07 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-09-14 18:27 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Vivek Khera <khera@kcilink.com>
2004-09-13 22:07 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Simon Riggs <simon@2ndquadrant.com>
2004-09-14 22:33 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Jim C. Nasby <decibel@decibel.org>
2004-09-14 23:32 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Simon Riggs <simon@2ndquadrant.com>
2004-09-14 23:32 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Mischa Sandberg <ischamay.andbergsay@activestateway.com>
2004-09-15 01:01 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Michael Glaesemann <grzm@myrealbox.com>
2004-09-15 02:34 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Chris Browne <cbbrowne@acm.org>
2004-09-15 04:30 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Joe Conway <mail@joeconway.com>
2004-09-15 04:54 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Iain <iain@mst.co.jp>
2004-09-16 04:07 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Joe Conway <mail@joeconway.com>
2004-09-16 05:08 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Iain <iain@mst.co.jp>
2004-09-15 06:15 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Christopher Browne <cbbrowne@acm.org>
2004-09-16 05:17 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Joe Conway <mail@joeconway.com>
2004-09-16 09:08 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Iain <iain@mst.co.jp>
2004-09-16 15:36 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Joe Conway <mail@joeconway.com>
2004-09-17 07:39 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Simon Riggs <simon@2ndquadrant.com>
2004-09-15 03:33 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Christopher Browne <cbbrowne@acm.org>
2004-09-15 05:10 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Steve Atkins <steve@blighty.com>
2004-09-15 09:16 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Markus Schaber <schabios@logi-track.com>
2004-09-16 04:17 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Steve Atkins <steve@blighty.com>
2004-09-16 13:38 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Markus Schaber <schabios@logi-track.com>
2004-09-14 12:14 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Markus Schaber <schabios@logi-track.com>
2004-09-14 14:33 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Stephen Frost <sfrost@snowman.net>
2004-09-14 20:38 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Simon Riggs <simon@2ndquadrant.com>
2004-09-14 22:58 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Mischa Sandberg <ischamay.andbergsay@activestateway.com>
2004-09-16 04:29 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-09-16 10:39 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Markus Schaber <schabios@logi-track.com>
2004-09-14 13:24 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Aaron Werman <awerman2@hotmail.com>
2004-09-14 15:20 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Harald Lau (Sector-X) <harald@sector-x.de>
2004-09-14 12:42 Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
2004-09-14 13:33 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-09-14 14:15 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Harald Fuchs <hf0722x@protecting.net>
2004-09-14 14:00 Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
2004-09-14 14:23 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Michael Kleiser <mkl@webde-ag.de>
2004-09-14 18:20 Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables aaron werman <awerman2@hotmail.com>
2004-09-15 06:51 Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
2004-09-15 09:10 Re: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- simon@2ndquadrant.com
2004-09-15 15:15 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Joe Conway <mail@joeconway.com>
2004-09-15 17:28 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Josh Berkus <josh@agliodbs.com>
2004-09-15 17:34 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Joe Conway <mail@joeconway.com>
2004-09-15 20:59 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Simon Riggs <simon@2ndquadrant.com>
2004-09-15 20:56 ` Re: Data Warehouse Reevaluation - MySQL vs Postgres -- Joe Conway <mail@joeconway.com>
2004-09-15 20:54 Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables? Simon Riggs <simon@2ndquadrant.com>
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