3 ms·

I'm not sure where in the article the author insults the _users_ of MySQL as stupid, but rather insults some decisions made by MySQL as ill-advised. Incidental

I'm not sure where in the article the author insults the _users_ of MySQL as stupid, but rather insults some decisions made by MySQL as ill-advised. Incidentally, I was not aware of issues around default settings with MySQL when I switched our infrastructure to Postgres - the primary reason we switched was transactional and much more efficient DDL changes. I was at a company previously that I felt was seriously hampered from innovating due to strong resistance against schema changes by the DBAs and want to do my best to prevent a similar culture where I work. Before I decided to switch I benchmarked schema changes between Postgres and MySQL and found Postgres to be quite impressive, see the gist of results here: https://gist.github.com/1620133 https://gist.github.com/1620133. If you (or anyone else) can point out why this benchmark is false or misleading, I'm all ears.

jeltz 14y ago
Your benchmarks especially demonstrate the fact that PostgreSQL tries to do as little work as possible when adding a column or dropping a column (or in recent versions of PostgreSQL when changing the type of a column). If you do not have a default value for the new column PostgreSQL only has to update the table definition which is almost instant. Another interesting part about your benchmarks are that they throw serious doubts on the popular myth that count(*) is slow in PostgreSQL. And your benchmarks were made before PostgreSQL 9.2 which will add index-only scans.
- mgkimsal 14y ago
  "... throw serious doubts on the popular myth that count() is slow in PostgreSQL." Whew! Glad that was only a myth! I'll remind myself of that whenever my code takes > 10 seconds to return the count() from a table. "Just a myth - this isn't really happening". I'll try repeating that to myself while waiting for the results - probably should only take 10-15 repeats of the phrase before the row returns, right? Whoah - just did a check on a real table with a whopping 2 million records - postgresql 9 just blazed through that in < 7 seconds to give me select count(*) from student; count --------- 2032609 (1 row) w00t! 7 seconds! I'm not even sure how that myth got started, let alone why people still believe that hogwash.
  - lusr 14y ago
    http://wiki.postgresql.org/wiki/Slow_Counting http://wiki.postgresql.org/wiki/Slow_Counting Are there DBMSs where this doesn't happen? I just did a SELECT COUNT(*) on a table here in our QA environment; 20.5 seconds to count 42385875 records from one table and 68.8 seconds to count 191906711 records from another table (Oracle 10g 64-bit).
    - mgkimsal 14y ago
      "The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense." When operating on a table, my understanding is that pg will select a version and operate on that version. If other versions are being worked on in transactions - that's a different story. Why can't metadata about the number of rows be assigned with the table version data, so after every operation, you'd know what the number of rows was at at that moment in time?
      - jeltz 14y ago
        PostgreSQL does not track any versions at the table level, instead it tracks the versions for every row. This means two queries can modify different parts of the table concurrently without any lock contention.[1] In PostgreSQL every row has two numbers. The transaction ID it was insert in and the transaction ID it was deleted in. An update is an insert plus a delete.[2] When running a select in PostgreSQL you just traverse the table and for each row check these two numbers to know if you are allowed to see the row. The details above are PostgreSQL specific but most other databases have the same problem with there being no way to know the exact count without actually counting the rows. Footnotes: 1. There is contention currently in PostgreSQL when writing the database journal (used for crash recovery and replication). 2. There are some optimization which are done here. For example HOT to avoid index updates.
  - jeltz 14y ago
    I was unclear the part that is a myth is that they are slow in _PostgreSQL_. COUNT( ) is actually slow in general on databases with high concurrency requirements. Because you have to sacrifice something to make COUNT( ) fast. If you had read his benchmark you would have seen COUNT( ) was slow in both databases (three times as slow in MySQL).
darnaut 14y ago
> If you (or anyone else) can point out why this benchmark is false or misleading, I'm all ears. You might want to check out the "Online DDL for InnoDB Tables" section of the manual, it should help with understanding the results. http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.htm...

jeltz 14y ago

Your benchmarks especially demonstrate the fact that PostgreSQL tries to do as little work as possible when adding a column or dropping a column (or in recent versions of PostgreSQL when changing the type of a column). If you do not have a default value for the new column PostgreSQL only has to update the table definition which is almost instant. Another interesting part about your benchmarks are that they throw serious doubts on the popular myth that count(*) is slow in PostgreSQL. And your benchmarks were made before PostgreSQL 9.2 which will add index-only scans.

mgkimsal 14y ago
"... throw serious doubts on the popular myth that count() is slow in PostgreSQL." Whew! Glad that was only a myth! I'll remind myself of that whenever my code takes > 10 seconds to return the count() from a table. "Just a myth - this isn't really happening". I'll try repeating that to myself while waiting for the results - probably should only take 10-15 repeats of the phrase before the row returns, right? Whoah - just did a check on a real table with a whopping 2 million records - postgresql 9 just blazed through that in < 7 seconds to give me select count(*) from student; count --------- 2032609 (1 row) w00t! 7 seconds! I'm not even sure how that myth got started, let alone why people still believe that hogwash.
- lusr 14y ago
  http://wiki.postgresql.org/wiki/Slow_Counting http://wiki.postgresql.org/wiki/Slow_Counting Are there DBMSs where this doesn't happen? I just did a SELECT COUNT(*) on a table here in our QA environment; 20.5 seconds to count 42385875 records from one table and 68.8 seconds to count 191906711 records from another table (Oracle 10g 64-bit).
  - mgkimsal 14y ago
    "The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense." When operating on a table, my understanding is that pg will select a version and operate on that version. If other versions are being worked on in transactions - that's a different story. Why can't metadata about the number of rows be assigned with the table version data, so after every operation, you'd know what the number of rows was at at that moment in time?
    - jeltz 14y ago
      PostgreSQL does not track any versions at the table level, instead it tracks the versions for every row. This means two queries can modify different parts of the table concurrently without any lock contention.[1] In PostgreSQL every row has two numbers. The transaction ID it was insert in and the transaction ID it was deleted in. An update is an insert plus a delete.[2] When running a select in PostgreSQL you just traverse the table and for each row check these two numbers to know if you are allowed to see the row. The details above are PostgreSQL specific but most other databases have the same problem with there being no way to know the exact count without actually counting the rows. Footnotes: 1. There is contention currently in PostgreSQL when writing the database journal (used for crash recovery and replication). 2. There are some optimization which are done here. For example HOT to avoid index updates.
- jeltz 14y ago
  I was unclear the part that is a myth is that they are slow in _PostgreSQL_. COUNT( ) is actually slow in general on databases with high concurrency requirements. Because you have to sacrifice something to make COUNT( ) fast. If you had read his benchmark you would have seen COUNT( ) was slow in both databases (three times as slow in MySQL).

darnaut 14y ago

> If you (or anyone else) can point out why this benchmark is false or misleading, I'm all ears. You might want to check out the "Online DDL for InnoDB Tables" section of the manual, it should help with understanding the results. http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.htm...