[PERFORM] PostgreSQL vs Oracle
Hi, I am looking for some recent and hopefully genuine comparisons between Oracle and PostgreSQL regarding their performance in large scale applications. Tests from real world applications would be preferable but not required. Also differentiations in different areas (i.e. different data types, query structures, clusters, hardware, etc.) might be helpful as well. I don't trust the results that Google gives me. Regards, Victor Nawothnig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL vs Oracle
On Sun, Dec 21, 2008 at 1:17 AM, Victor Nawothnig wrote: > Hi, > > I am looking for some recent and hopefully genuine comparisons between > Oracle and PostgreSQL regarding their performance in large scale > applications. Tests from real world applications would be preferable > but not required. Also differentiations in different areas (i.e. > different data types, query structures, clusters, hardware, etc.) > might be helpful as well. Due to the terms of the license for Oracle, no one can publish benchmarks without their permission. Having used both Oracle 9 and pgsql from 6.5 to 8.3, I can say that PostgreSQL is competitive for most small to medium loads I've thrown at it, and given the high cost of licensing for oracle, you can throw a LOT of hardware at PostgreSQL to catch up the last 10 or 20% slowdown you might see in some apps. Most of Oracle's advantages are in the more advanced features like partitioning and reporting functions. That said, I find PostgreSQL takes a LOT less administration to keep it happy. Oracle doesn't just allow a wider range of performance tuning, it demands it. If you don't demonstrably need Oracle's advanced features then PostgreSQL is usually a better choice. Last place we worked we developed on pgsql and migrated to oracle in production (this was in the 7.4 era, when Oracle 9 was noticeably faster and better than pgsql for transactional loads.) It was very easy to write for pgsql and migrate to oracle as most SQL queries didn't need any serious changes from one db to the other. PostgreSQL generally tries to follow the SQL specs a little closer, oracle has more crufty legacy stuff in it. So, when you say large scale applications, are you talking OLTP or OLAP type workloads? My experience has been that very few OLTP apps get very large, as they get partitioned before they get over a few dozen gigabytes. OLAP, OTOH, often run into hundreds of Gigs or terabytes. I've found pgsql competitive in both really. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] dbt-2 tuning results with postgresql-8.3.5
On Dec 20, 2008, at 5:33 PM, Gregory Stark wrote: "Mark Wong" writes: To recap, dbt2 is a fair-use derivative of the TPC-C benchmark. We are using a 1000 warehouse database, which amounts to about 100GB of raw text data. Really? Do you get conforming results with 1,000 warehouses? What's the 95th percentile response time? No, the results are not conforming. You and others have pointed that out already. The 95th percentile response time are calculated on each page of the previous links. I find your questions a little odd for the input I'm asking for. Are you under the impression we are trying to publish benchmarking results? Perhaps this is a simple misunderstanding? Regards, Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL vs Oracle
One year ago a Postgres teacher pointed me there: http://it.toolbox.com/blogs/database-soup/postgresql-publishes-first-real-benchmark-17470 http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-20070606-00065.html that would be just like what you're looking for. Regards, Stefano On Sun, Dec 21, 2008 at 9:17 AM, Victor Nawothnig wrote: > Hi, > > I am looking for some recent and hopefully genuine comparisons between > Oracle and PostgreSQL regarding their performance in large scale > applications. Tests from real world applications would be preferable > but not required. Also differentiations in different areas (i.e. > different data types, query structures, clusters, hardware, etc.) > might be helpful as well. > > I don't trust the results that Google gives me. > > Regards, > Victor Nawothnig > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Query planner plus partitions equals very bad plans, again
Here is a query on a partitioned schema that produces a very bad query plan. The tables are fully vacuumed, analyzed with stats target 40, and no bloat (created with pure inserts, no updates or deletes). I already know of at least three bugs with the query planner and partitions listed at the end of this message. Below are explain analyze examples on just one partition. Expanding the date range to be multiple partitions only makes this worse. First, the query written to go against the direct partitions, which is fast and does the right plan: (although, wouldn't it use less memory if it hashed the small table, and scanned the larger one against that rather than vice-versa? Or am I reading the explain wrong?) rr=> explain analyze select pl.id, sl.terms from p_pp_logs_026_2008_12_17 pl, p_s_logs_026_2008_12_17 sl where sl.session = pl.session and sl.terms <> 'null' and sl.terms is not null; QUERY PLAN Hash Join (cost=31470.89..37199.26 rows=38597 width=27) (actual time=743.782..772.705 rows=29907 loops=1) Hash Cond: ((pl.session)::text = (sl.session)::text) -> Seq Scan on pp_logs_026_2008_12_17 pl (cost=0.00..357.49 rows=8449 width=46) (actual time=0.007..3.064 rows=8449 loops=1) -> Hash (cost=24822.35..24822.35 rows=531883 width=57) (actual time=743.597..743.597 rows=531453 loops=1) -> Seq Scan on s_logs_026_2008_12_17 sl (cost=0.00..24822.35 rows=531883 width=57) (actual time=0.011..392.242 rows=532067 loops=1) Filter: ((terms IS NOT NULL) AND ((terms)::text <> 'null'::text)) Total runtime: 779.431 ms In the above, the estimated and actual rows are about right, only a bit off after the join. We are partitioned by s_id and date, the general form of the query used for scanning more than one combination of these values fails. In the below, which restricts the data to the same tables, the planner is extremely wrong (plus the parent tables which have one irrelevant row of dummy data so that analyze works on them): explain analyze select pl.id, sl.terms from pp_logs pl, s_logs sl where pl.s_id = 26 and sl.s_id = 26 and sl.session = pl.session and sl.terms <> 'null' and sl.terms is not null and pl.date = '2008-12-17' and sl.date = '2008-12-17'; QUERY PLAN --- Merge Join (cost=79036.43..416160.16 rows=22472099 width=27) (actual time=7089.961..7839.665 rows=29907 loops=1) Merge Cond: ((pl.session)::text = (sl.session)::text) -> Sort (cost=968.98..990.10 rows=8450 width=46) (actual time=193.778..196.690 rows=8449 loops=1) Sort Key: pl.session Sort Method: quicksort Memory: 1043kB -> Append (cost=0.00..417.84 rows=8450 width=46) (actual time=36.824..133.157 rows=8449 loops=1) -> Seq Scan on pp_logs pl (cost=0.00..18.10 rows=1 width=21) (actual time=0.056..0.056 rows=0 loops=1) Filter: ((s_id = 26) AND (date = '2008-12-17'::date)) -> Seq Scan on pp_logs_026_2008_12_17 pl (cost=0.00..399.74 rows=8449 width=46) (actual time=36.766..129.317 rows=8449 loops=1) Filter: ((s_id = 26) AND (date = '2008-12-17'::date)) -> Sort (cost=78067.45..79397.16 rows=531884 width=57) (actual time=6895.648..7094.701 rows=552412 loops=1) Sort Key: sl.session Sort Method: quicksort Memory: 92276kB -> Append (cost=0.00..27483.28 rows=531884 width=57) (actual time=33.759..1146.936 rows=532067 loops=1) -> Seq Scan on s_logs sl (cost=0.00..1.19 rows=1 width=22) (actual time=0.036..0.036 rows=0 loops=1) Filter: ((terms IS NOT NULL) AND ((terms)::text <> 'null'::text) AND (s_id = 26) AND (date = '2008-12-17'::date)) -> Seq Scan on s_logs_026_2008_12_17 sl (cost=0.00..27482.09 rows=531883 width=57) (actual time=33.721..903.607 rows=532067 loops=1) Filter: ((terms IS NOT NULL) AND ((terms)::text <> 'null'::text) AND (s_id = 26) AND (date = '2008-12-17'::date)) Total runtime: 7861.723 ms Some partitions have about 10x the data as the above, resulting in ~30x longer times to do the sort, but only 10x to hash. The sort also uses far, far more memory than the hash should if it were to hash the small one and hash-join the large table against that. As you can see in the above example, this inner join, which the planner knows has two arms of row count ~530K and ~8.5K, is predicted to have 22M output rows. What? Did it loose its ability to even roughly know n_distinct? Can it use n_distinct for
Re: [PERFORM] PostgreSQL vs Oracle
Victor Nawothnig wrote: Hi, I am looking for some recent and hopefully genuine comparisons between Oracle and PostgreSQL regarding their performance in large scale applications. Tests from real world applications would be preferable but not required. Also differentiations in different areas (i.e. different data types, query structures, clusters, hardware, etc.) might be helpful as well. Victor, Oracle expressly forbids, in their license agreement, anyone from publishing performance comparisons between Oracle and any other product. So you will rarely find anyone willing to publicly provide you any performance numbers. Difference in data structures, etc, are fairly easy to determine. Anyone can read the Oracle documentation. -- Guy Rouillier -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] dbt-2 tuning results with postgresql-8.3.5
Mark Wong writes: > On Dec 20, 2008, at 5:33 PM, Gregory Stark wrote: > >> "Mark Wong" writes: >> >>> To recap, dbt2 is a fair-use derivative of the TPC-C benchmark. We >>> are using a 1000 warehouse database, which amounts to about 100GB of >>> raw text data. >> >> Really? Do you get conforming results with 1,000 warehouses? What's the 95th >> percentile response time? > > No, the results are not conforming. You and others have pointed that out > already. The 95th percentile response time are calculated on each page of > the > previous links. Where exactly? Maybe I'm blind but I don't see them. > > I find your questions a little odd for the input I'm asking for. Are you > under the impression we are trying to publish benchmarking results? Perhaps > this is a simple misunderstanding? Hm, perhaps. The "conventional" way to run TPC-C is to run it with larger and larger scale factors until you find out the largest scale factor you can get a conformant result at. In other words the scale factor is an output, not an input variable. You're using TPC-C just as an example workload and looking to see how to maximize the TPM for a given scale factor. I guess there's nothing wrong with that as long as everyone realizes it's not a TPC-C benchmark. Except that if the 95th percentile response times are well above a second I have to wonder whether the situation reflects an actual production OLTP system well. It implies there are so many concurrent sessions that any given query is being context switched out for seconds at a time. I have to imagine that a real production system would consider the system overloaded as soon as queries start taking significantly longer than they take on an unloaded system. People monitor the service wait times and queue depths for i/o systems closely and having several seconds of wait time is a highly abnormal situation. I'm not sure how bad that is for the benchmarks. The only effect that comes to mind is that it might exaggerate the effects of some i/o intensive operations that under normal conditions might not cause any noticeable impact like wal log file switches or even checkpoints. If you have a good i/o controller it might confuse your results a bit when you're comparing random and sequential i/o because the controller might be able to sort requests by physical position better than in a typical oltp environment where the wait queues are too short to effectively do that. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
