pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance - moving from oracle to postgresql
4+ messages / 4 participants
[nested] [flat]
* Performance - moving from oracle to postgresql
@ 2005-06-24 19:49 Greg Maples <gregm@nimblefish.com>
0 siblings, 2 replies; 4+ messages in thread
From: Greg Maples @ 2005-06-24 19:49 UTC (permalink / raw)
To: pgsql-performance
Hi:
I'm beginning the push at our company to look at running
postgreSQL in production here. We have a dual CPU 2.8 GHZ Xeon
Box running oracle. Typical CPU load runs between 20% and 90%.
Raw DB size is about 200GB. We hit the disk at roughly 15MB/s
read volume and 3MB/s write.
At any given time we have from 2 to 70 sessions running
on the instance. Sessions often persist for 24 hours or more.
Total Free Free
Mb Mb %
IDXS_EXT10 2000 290 14.5
DATA_EXT100 10000 3200 32
SYSTEM 220 95.2 43.3
IDXS_EXT100 20000 9600 48
DATA_EXT10 6000 2990 49.8
UNDOB 4000 2561.1 64
TEMP 8000 5802.9 72.5
DATA_LOB_EXT20 2000 1560 78
IDXS_EXT1 500 401 80.2
DATA_EXT1 4000 3758 94
Total Instance 56720 30258.2 53.3
There are some immediate questions from our engineers about performance
"- Oracle has one particular performance enhancement that Postgres is
missing. If you do a select that returns 100,000 rows in a given order,
and all you want are rows 99101 to 99200, then Oracle can do that very
efficiently. With Postgres, it has to read the first 99200 rows and
then discard the first 99100. But... If we really want to look at
performance, then we ought to put together a set of benchmarks of some
typical tasks."
Is this accurate:
accoring to
http://www.postgresql.org/docs/8.0/interactive/queries-limit.html
-- " The rows skipped by an OFFSET clause still have to be computed
inside the server; therefore a large OFFSET can be inefficient."
What are the key performance areas I should be looking at?
Where is psql not appropriate to replace Oracle?
Thanks in advance, apologies if this occurs as spam, please send
Replies to me off-list.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance - moving from oracle to postgresql
@ 2005-06-24 22:00 Rod Taylor <pg@rbt.ca>
parent: Greg Maples <gregm@nimblefish.com>
1 sibling, 0 replies; 4+ messages in thread
From: Rod Taylor @ 2005-06-24 22:00 UTC (permalink / raw)
To: Greg Maples <gregm@nimblefish.com>; +Cc: pgsql-performance
> There are some immediate questions from our engineers about performance
>
> "- Oracle has one particular performance enhancement that Postgres is
> missing. If you do a select that returns 100,000 rows in a given order,
> and all you want are rows 99101 to 99200, then Oracle can do that very
> efficiently. With Postgres, it has to read the first 99200 rows and
> then discard the first 99100. But... If we really want to look at
> performance, then we ought to put together a set of benchmarks of some
> typical tasks."
>
> Is this accurate:
> accoring to
> http://www.postgresql.org/docs/8.0/interactive/queries-limit.html
> -- " The rows skipped by an OFFSET clause still have to be computed
> inside the server; therefore a large OFFSET can be inefficient."
Yes. That's accurate. First you need to determine whether PostgreSQLs
method is fast enough for that specific query, and if the performance
gains for other queries (inserts, updates, delete) from reduced index
management evens out your concern. All performance gains through design
changes either increase complexity dramatically or have a performance
trade-off elsewhere.
I find it rather odd that anyone would issue a single one-off select for
0.1% of the data about 99.1% of the way through, without doing anything
with the rest. Perhaps you want to take a look at using a CURSOR?
> Where is psql not appropriate to replace Oracle?
Anything involving reporting using complex aggregates or very long
running selects which Oracle can divide amongst multiple CPUs.
Well, PostgreSQL can do it if you give it enough time to run the query,
but a CUBE in PostgreSQL on a TB sized table would likely take
significantly longer to complete. It's mostly just that the Pg
developers haven't implemented those features optimally, or at all, yet.
--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance - moving from oracle to postgresql
@ 2005-06-25 10:45 Radu-Adrian Popescu <radu.popescu@aldratech.com>
parent: Greg Maples <gregm@nimblefish.com>
1 sibling, 0 replies; 4+ messages in thread
From: Radu-Adrian Popescu @ 2005-06-25 10:45 UTC (permalink / raw)
To: Greg Maples <gregm@nimblefish.com>; +Cc: pgsql-performance
> "- Oracle has one particular performance enhancement that Postgres is
> missing. If you do a select that returns 100,000 rows in a given order,
> and all you want are rows 99101 to 99200, then Oracle can do that very
> efficiently. With Postgres, it has to read the first 99200 rows and
> then discard the first 99100.
When I was reading up on resultset pagination on AskTom I got a clear
impression that the same happens in Oracle as well.
Resultset is like:
0....START...STOP...END
0............STOP
START...END
You first select all the rows from 0 to STOP and then from that select the
rows from START to end (which is now STOP). This is done using ROWNUM
twice and subselects.
It was discussed over there that this obviously produces higher response
times as you move towards the end of a very large resultset. Tom even
pointed out the same effect when using google search, as you move forward
through a very large (millions) search result.
Regards,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance - moving from oracle to postgresql
@ 2005-06-27 12:40 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 4+ messages in thread
From: Merlin Moncure @ 2005-06-27 12:40 UTC (permalink / raw)
To: Greg Maples <gregm@nimblefish.com>; +Cc: pgsql-performance; Rod Taylor <pg@rbt.ca>
> There are some immediate questions from our engineers about
performance
>
> "- Oracle has one particular performance enhancement that Postgres is
> missing. If you do a select that returns 100,000 rows in a given
order,
> and all you want are rows 99101 to 99200, then Oracle can do that very
> efficiently. With Postgres, it has to read the first 99200 rows and
> then discard the first 99100. But... If we really want to look at
> performance, then we ought to put together a set of benchmarks of some
> typical tasks."
I agree with Rod: you are correct but this is a very odd objection. You
are declaring a set but are only interested in a tiny subset of that
based on arbitrary critera. You can do this with cursors or with clever
querying (not without materializing the full set however), but why?
Merlin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-27 12:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-24 19:49 Performance - moving from oracle to postgresql Greg Maples <gregm@nimblefish.com>
2005-06-24 22:00 ` Rod Taylor <pg@rbt.ca>
2005-06-25 10:45 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2005-06-27 12:40 Re: Performance - moving from oracle to postgresql Merlin Moncure <merlin.moncure@rcsonline.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