Обсуждение: time sensitve: comparing performance to MySQL
Hello, I've been tasked to do some quick research on Postgres vs. MySQL that I need to present later today. I've already done a lot of Googlin' and reviewing of the Postgres websites. The project at hand will be a high-volume website with many complex queries. It will likely use replication. Performance will be most important for SELECT statements. The project will be ported form an existing codebase which uses Oracle as the backend. I understand that Postgres has been closing the speed gap with MySQL, but I'm having trouble finding hard data on this. What specific information is available in this area? Your help is appreciated! --Matt
On Thu, Oct 02, 2003 at 11:59:03AM -0500, Matt Christian wrote:
> The project will be ported form an existing codebase which uses Oracle
> as the backend.
If that's the case, then your developers will want Postgres anyway.
MySQL is just not feature-comparable. Postgres is.
> I understand that Postgres has been closing the speed gap with MySQL,
> but I'm having trouble finding hard data on this. What specific
> information is available in this area?
What specific information is there that MySQL was ever faster ;-)
The benchmarks are all lousy, as near as anyone's been able to tell,
but Great Bridge did a benchmark some time ago which purported to
prove that PostgreSQL was almost as fast as an industry-leading
database with "7i" in its name. If that's useful to you, google for
"breat bridge benchmark".
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Matt, > I understand that Postgres has been closing the speed gap with MySQL, > but I'm having trouble finding hard data on this. What specific > information is available in this area? We don't have anything hard. Currently, OSDL is working on pseudo-TPC benchmarks for us, but that's some months of testing off. Also, it's likely that both we and MySQL will soon have a second crack at getting OSDB benchmarks to actually work. But nothing's in print yet. Amusingly, though, we generally do pretty well on MySQL's "Crash Me" test -- you can see their docs for details. One thing I will tell you up front: PostgreSQL can be very fast but requires significant tuning and some knowledge of the query internals. In that way, we are very like Oracle. MySQL does not require this, but also is far more limited in SQL support, and is not ACID-compliant, and is somewhat more limited in scalability. So it depends on what you need. If ACID compliance, stored procedures, SQL-standard compliance, and correlated subselects are not on your list of needs, then going with MySQL might be the best option for you, just becuase it will save you the tuning headaches. If you need those things, though, then join the PGSQL-PERFORMANCE list and we'll help you tune your PostgreSQL database for testing. I can say that a lot of organizations have migrated from Oracle to PostgreSQL and been pleased with the result. -- Josh Berkus Aglio Database Solutions San Francisco
The information you are looking for is indeed hard to find. A lot of the information on the web is inaccurate or out of date. For truly unbiased comparisons you need to find independent entities that have worked with both databases. Unfortunately many database shops (but not all) are reluctant to publish meaningful results. FWIW, perhaps the advocacy site for either database is not the best place to be looking. Duty calls, however, so I will make my case. Here is a site comparing the relative capabilities of the architectures: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg Here is an article I recently wrote about application development with PG that may have some helpful information (no benchmarks though): http://www.zeoslib.net/modules.php?name=News&file=article&sid=19&mode=&o rder=0&thold=0 The 'speed gap' is an oversimplification that does not really exist. From my understanding, mysql is faster for: 1. insert operations 2. update operations for 1 record 3. bulk updates 4. typical 'where id=x' select statements that return 1 row pg is faster for: 1. multiple user workloads for non read-only applications (thanks to mvcc) 2. complex queries (better optimizer, statistics) 3. development 4. transactions vs. InnoDB #2 is probably the most relevant in your case. #3 is strictly my opinion, but I'm pretty well informed. Overall, PG has a much more concurrency friendly architecture from the ground up. Because you are coming from Oracle, there should be no question about your decision. PG has a feature set similar nearly as good as O's and a lot of similar constructs. Examples: 1. Sequences 2. Row level transaction locking 3. PL/SQL 4. ?? (I've never worked with O) By the way, I think mysql is a decent product with some nice features. If you are planning a web app with scripting in PHP or Perl, you could make a decent argument for mysql. I still think PG is better, tho. Regards, Merlin -----Original Message----- From: Matt Christian [mailto:matt@summersault.com] Sent: Thursday, October 02, 2003 12:59 PM To: pgsql-advocacy@postgresql.org Cc: Mark Stosberg Subject: [pgsql-advocacy] time sensitve: comparing performance to MySQL Hello, I've been tasked to do some quick research on Postgres vs. MySQL that I need to present later today. I've already done a lot of Googlin' and reviewing of the Postgres websites. The project at hand will be a high-volume website with many complex queries. It will likely use replication. Performance will be most important for SELECT statements. The project will be ported form an existing codebase which uses Oracle as the backend. I understand that Postgres has been closing the speed gap with MySQL, but I'm having trouble finding hard data on this. What specific information is available in this area? Your help is appreciated! --Matt ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
On Thu, 2 Oct 2003, Matt Christian wrote: > Hello, > > I've been tasked to do some quick research on Postgres vs. MySQL that I > need to present later today. I've already done a lot of Googlin' and > reviewing of the Postgres websites. > > The project at hand will be a high-volume website with many complex > queries. It will likely use replication. Performance will be most > important for SELECT statements. > > The project will be ported form an existing codebase which uses Oracle > as the backend. > > I understand that Postgres has been closing the speed gap with MySQL, > but I'm having trouble finding hard data on this. What specific > information is available in this area? Actually, ther "performance gap" here is more mythical than real. What MySQL is fast at is a mostly read, simple select query system, with few users, while what postgresql is fast at is a transactional high load / high number of users / complex query scenario. MySQL's lack of complex query handling, and poor performance in those few areas that have been added lately (sub selects are still quite slow, for instance) means that it is often more suited for content management, while Postgresql's ability to hand 100s of parallel updates while still delivering decent select performance make it more suited for large transactional systems, such as the .org domain, financial systems, collaboration, and data warehousing. I'd suggest taking your top 5 most complex / slowest running queries, importing the your schema into both databases, and seeing how they compare when you run those queries in parallel with 1, 5, 10, 20 etc increasing numbers to get an idea of how the performance envelope looks under load for you. note that postgresql and mysql will both need a certain amount of tuning to handle this kind of load, a default install of either is not going to be up to snuff for this.
I think your best bet is to list the features your using from oracle and then mark whether mysql / postgresql support those features, and how much work might be involved in working around missing features. heres some links in case you missed them: http://www.serverwatch.com/news/article.php/10824_1126981_Ext_2 http://faemalia.org/wiki/view/Technical/PostgreSQLvsMySQL http://www.sitepoint.com/article/529/1 http://www.phpbuilder.com/columns/tim20001112.php3?page=1 / http://www.hwaci.com/sw/sqlite/speed.html http://www.sergeant.org/sqlite_vs_pgsync.html the above two go together, the first is sqlite/mysql/postgresql, the second a tuned version of postgresql. note it's an older version of pgsql / Robert Treat On Thu, 2003-10-02 at 12:59, Matt Christian wrote: > Hello, > > I've been tasked to do some quick research on Postgres vs. MySQL that I > need to present later today. I've already done a lot of Googlin' and > reviewing of the Postgres websites. > > The project at hand will be a high-volume website with many complex > queries. It will likely use replication. Performance will be most > important for SELECT statements. > > The project will be ported form an existing codebase which uses Oracle > as the backend. > > I understand that Postgres has been closing the speed gap with MySQL, > but I'm having trouble finding hard data on this. What specific > information is available in this area? > > Your help is appreciated! > > --Matt -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> The project will be ported form an existing codebase which uses Oracle > as the backend. As your developers have Oracle experience and are thus probably used to take some things "logically granted", then just in case you end up with MySQL after all you might find the following link to be a timesaver. It's a list of MySQL's documented non-bugs. http://sql-info.de/mysql/ Kaarel