4 ms·

I think MySQL's advantage was that it was installed on some many hosting companies servers (along with PHP) that it became ubiquitous for many. Most DB admins

by timc3 9y ago

I think MySQL's advantage was that it was installed on some many hosting companies servers (along with PHP) that it became ubiquitous for many. Most DB admins that I knew could see no clear advantage of MySQL when we looked at the details apart from for failover and multi-host uptime has an easier deployment. Disclaimer I have been using PostgreSQL for perhaps 20 years now.

rgbrenner 9y ago
That's the ecosystem that developed because of MySQL's early performance advantage... that wasn't until later on. The performance issue is actually a core part of MySQL's history. We have to go way back to the early 90s. Back then Postgres didn't use SQL. There was a project called mSQL that created an SQL layer on top of Postgres... but it didn't perform very well on the slow computers of the early 90s. So mSQL created it's own lightweight DBMS to replace Postgres. mSQL was popular in the mid-90s, and it's performance started the creation of that early ecosystem that you're referring to. Later MySQL took mSQL and added more features, had a more liberal/cheaper license, and kept the mSQL API.. and that ecosystem that developed around mSQL continued with MySQL. So the performance advantage that gave MySQL it's lead, began pre-Postgres95 (that added SQL to postgres), that created the ecosystem that kept MySQL popular. When MySQL passed mSQL in popularity in 1997, Postgre95 was a little over a year old, and was just recently renamed to PostgreSQL (while mSQL was about 3 years old at the time). If Postgres was more performant, mSQL would have been based on it, and MySQL probably would have never existed. Instead, by the time Postgres addressed the SQL issue, they were 2 years late, and by the time they addressed the performance issue, they were a few more years late (by the early 00s, IIRC the performance was comparable).. and by then MySQL had cemented its lead in the market.
- justinclift 9y ago
  Keep in mind that the "performance advantage" MySQL supposedly "had" over PostgreSQL mostly came down to a bad default setting in PG. Back in early PG versions (7.x from memory), the default shared buffers setting value was extremely low, in order to allow PG to "just work" even on very low memory systems. (8MB?) The effect of not changing that from the default was PG then pegging the cpu at 100% (moving stuff into and out of the tiny buffer) with terrible performance. When we raised that default to something reasonable for (at the time) modern systems... our performance magically increased substantially from the perspective of many non-DBAs. Note - The exact details are in the PG email archives if anyone cares enough to look. :)
  - anarazel 9y ago
    I think that's not quite right. There's a lot of things besides default settings that lead to not so great performance. Before spread checkpoints, HOT, short varlenas, readonly transactions not needing a txid, the free space map (remember max_fsm_pages), autovacuum, custom plans for prepared statements, asynchronous commits... it was a lot harder to use PG for higher throughput systems.
    - justinclift 9y ago
      Those likely contributed, but the shared_buffers setting was the outright major culprit at the time. Go look back through the -hackers mailing list, probably ~2001-2002. Or maybe just look at the timestamps for when the default changed, in order to get the right time range. :)
      - anarazel 9y ago
        I used postgres before and after all of these changes, and I used mysql at that time too... And before you accuse me of anti postgres bias: I work on postgres all day :)
  - rgbrenner 9y ago
    NO. Nothing I wrote above has anything to do with what you've written here. (It's so irrelevant, I have to ask: did you read my post at all?) PG 7.x was 2000-2003. A full 5 years after MySQL was released.. and 7 years after mSQL. The performance of mSQL versus pre-Postgres95 has nothing to do with a setting in PG 7.x released years later. The performance advantage of mSQL was directly related to the stripped down DBMS that was developed to replace Postgres (AGAIN IN 1993) and the incomplete SQL implementation (AGAIN IN 1993)... both tradeoffs that Postgres did not make. The issue you're referring to is a separate performance issue that did not occur until years later.. after MySQL already won the marketshare race. Between the start of Postgres in 1986 and PG 7.x, there's about 14 years of history you're forgetting.

by timc3 9y ago

rgbrenner 9y ago

That's the ecosystem that developed because of MySQL's early performance advantage... that wasn't until later on. The performance issue is actually a core part of MySQL's history. We have to go way back to the early 90s. Back then Postgres didn't use SQL. There was a project called mSQL that created an SQL layer on top of Postgres... but it didn't perform very well on the slow computers of the early 90s. So mSQL created it's own lightweight DBMS to replace Postgres. mSQL was popular in the mid-90s, and it's performance started the creation of that early ecosystem that you're referring to. Later MySQL took mSQL and added more features, had a more liberal/cheaper license, and kept the mSQL API.. and that ecosystem that developed around mSQL continued with MySQL. So the performance advantage that gave MySQL it's lead, began pre-Postgres95 (that added SQL to postgres), that created the ecosystem that kept MySQL popular. When MySQL passed mSQL in popularity in 1997, Postgre95 was a little over a year old, and was just recently renamed to PostgreSQL (while mSQL was about 3 years old at the time). If Postgres was more performant, mSQL would have been based on it, and MySQL probably would have never existed. Instead, by the time Postgres addressed the SQL issue, they were 2 years late, and by the time they addressed the performance issue, they were a few more years late (by the early 00s, IIRC the performance was comparable).. and by then MySQL had cemented its lead in the market.

justinclift 9y ago
Keep in mind that the "performance advantage" MySQL supposedly "had" over PostgreSQL mostly came down to a bad default setting in PG. Back in early PG versions (7.x from memory), the default shared buffers setting value was extremely low, in order to allow PG to "just work" even on very low memory systems. (8MB?) The effect of not changing that from the default was PG then pegging the cpu at 100% (moving stuff into and out of the tiny buffer) with terrible performance. When we raised that default to something reasonable for (at the time) modern systems... our performance magically increased substantially from the perspective of many non-DBAs. Note - The exact details are in the PG email archives if anyone cares enough to look. :)
- anarazel 9y ago
  I think that's not quite right. There's a lot of things besides default settings that lead to not so great performance. Before spread checkpoints, HOT, short varlenas, readonly transactions not needing a txid, the free space map (remember max_fsm_pages), autovacuum, custom plans for prepared statements, asynchronous commits... it was a lot harder to use PG for higher throughput systems.
  - justinclift 9y ago
    Those likely contributed, but the shared_buffers setting was the outright major culprit at the time. Go look back through the -hackers mailing list, probably ~2001-2002. Or maybe just look at the timestamps for when the default changed, in order to get the right time range. :)
    - anarazel 9y ago
      I used postgres before and after all of these changes, and I used mysql at that time too... And before you accuse me of anti postgres bias: I work on postgres all day :)
- rgbrenner 9y ago
  NO. Nothing I wrote above has anything to do with what you've written here. (It's so irrelevant, I have to ask: did you read my post at all?) PG 7.x was 2000-2003. A full 5 years after MySQL was released.. and 7 years after mSQL. The performance of mSQL versus pre-Postgres95 has nothing to do with a setting in PG 7.x released years later. The performance advantage of mSQL was directly related to the stripped down DBMS that was developed to replace Postgres (AGAIN IN 1993) and the incomplete SQL implementation (AGAIN IN 1993)... both tradeoffs that Postgres did not make. The issue you're referring to is a separate performance issue that did not occur until years later.. after MySQL already won the marketshare race. Between the start of Postgres in 1986 and PG 7.x, there's about 14 years of history you're forgetting.