Re: PostgreSQL vs MySQL - Mailing list pgsql-performance

David Griffiths wrote:

> This is a timely thread for myself, as I'm in the middle of testing
> both databases as an Oracle replacement.
>
> As of this moment, I know more about MySQL (tuning, setup, features)
> than I do about Postgres. Not because I like MySQL more, but because
>
> 1) the MySQL docs are better (sorry - I found them easier to read, and
> more comprehensive; I had an easier time finding the answers I needed)
> 2) there are more web pages devoted to MySQL (probably because it has
> a bit more market share)
> 3) there are more books on MySQL at the bookstore (I haven't had a
> chance to pick up Bruce's book yet; it might be all the book I'd ever
> need)
> 4) we looked at MySQL first (we needed replication, and eRServer had
> not been open-sourced when we started looking)
>
> With regards to #1, I'd like to specifically mention tuning - the docs
> at http://www.postgresql.org/docs/7.3/static/runtime-config.html give
> a basic explanation of the different options, but much more is needed
> for tuning. I'm running into a problem with an update statement (that
> uses a select in a sub-query) in Postgres - it's taking  hours to run
> (the equiv, using a multi-table update statement in MySQL instead of a
> sub-query, takes all of 2 seconds). I'll be posting it later once I do
> more reading to make sure I've done as much as I can to solve it myself.


David,

I think you have valid observations.  And the issue regarding
replication has been quite a hot topic on occasion in the developer
lists.   I'm hoping at some point it would become part of the standard
PostgreSQL package; but point in time recovery, PITR, is needed as a
stepping stone to providing that functionality.

Have you attempted the multi table update inside of a transaction for
PostgreSQL yet and thus assuring the all of your updates are only
visible after the commit?   Depending on the design and the nature of
the updates, their could be a race condition if the updates on one table
are utilized by another process before the rest of the updates have
completed.

Sets of updates in a single transaction can improve performance as well.

>
> I really agree with this post:
>
> "I guess my point is simply this: instead of saying: "okay we use
> default settings that will run on _old_ hardware too" we should go for
> a little script that creates a "still save but much better" config
> file. There's just no point in setting SHARED_BUFFERS to something
> like 16 (what's the current default?) if the PC has >= 1 GB of RAM.
> Setting it to 8192 would still be save, but 512 times better...  ;-)
> (IIRC 8192 would take 64 MB of RAM, which should be save if you leave
> the default MAX_CONNECTIONS.)" It provides examples, and some real
> numbers to help someone new to the database take an initial crack at
> tuning. Remember, you're trying to compete with the big-guys (Oracle,
> etc), so providing info that an Oracle DBA needs is pretty critical.
> I'm currently at a complete loss for tuning Postgres (it seems to do
> things very differently than both Oracle and MySQL).
>
>
> I also have to admit a bit of irritation reading this thread; there is
> a fair number of incorrect statements on this thread that, while not
> wrong, definately aren't right:
>
> "Speed depends on the nature of use and the complexity of queries.  If
> you are doing updates of related tables, ACID is of vital importance
> and MySQL doesn't provide it."
> MySQL has ACID in InnoDB. I've found that MySQL is actually very fast
> on complex queries w/InnoDB (six tables, 1 million rows, two of the
> joins are outer-joins. In fact, I can get InnoDB to be almost as fast
> as MyISAM. Complex updates are also very very fast. We have not tried
> flooding either database with dozens of complex statements from
> multiple clients; that's coming soon, and from what I've read, MySQL
> won't do too well.
>
> "using InnoDB tables (the only way to have foreign keys, transactions,
> and row level locking for MySQL) makes MySQL slower and adds
> complexity to tuning the database"
> Adding this: "innodb_flush_method=O_DSYNC" to the my.cnf made InnoDB
> as fast as MyISAM in our tests. It doesn't turn off disk flushing;
> it's just a flush method that might work better with different kernels
> and drives; it's one of those "play with this and see if it helps"
> parameters; there are lots of those in Postgres, it seems. There are
> 10 variables for tuning InnoDB (and you don't have to tune for MyISAM,
> so it's actually a six-of-one, half-dozen-of-the-other). Setup between
> the two seems to be about the same.
>
> "PostgreSQL supports constraints. MySQL doesn't; programmers need to
> take care of that from the client side"
> Again, InnoDB supports constraints.
>
> "Transactions: We've been here before. Suffice to say, MySQL+InnoDB is
> almost there. Plain ol' MySQL doesn't have it, which tells you
> something about their philosophy towards database design."
> InnoDB supports transactions very nicely, has the equivalent of WAL,
> and one thing I really like: a tablespace (comprised of data files
> that can be spread around multiple hard drives), and in a month or so,
> InnoDB will support multiple tablespaces.
>
>
> To be fair, here are a few MySQL "bad-things" that weren't mentioned:
>
> 1) InnoDB can't do a hot-backup with the basic backup tools. To
> hot-backup an InnoDB database, you need to pay $450 US per database
> per year ($1150 per database perpetual) for a proprietary hot-backup tool
> 2) InnoDB can't do full-text searching.
> 3) I see alot more corrupt-database bugs on the MySQL lists (most are
> MyISAM, but a few InnoDB bugs pop up from time to time) - way more
> than I see on the Postgres lists.
> 4) There are some really cranky people on the MySQL lists; the
> Postgres lists seem to be much more effective (esp. with people like
> Tom Lane). Maybe it's because they get alot of dumb questions, as
> people unfamiliar with databases turn to MySQL first?
>
> Maybe the Postgres community needs an anti-FUD individual or two;
> people that know both databases, and can provide the proper
> information for answering questions like this. A section in the docs
> would help as well. Yes, I know many of the people advocating Postgres
> do not want to compare themselves to MySQL (but rather to Oracle,
> Sybase, DB2, etc) , but the volume of responses on a thread like this
> indicates that the comparison is going to happen regardless. Better to
> nip it in the bud quickly than let it go on over 3-4 days.
>
> One last observation: someone looking at both databases, reading those
> posts, might get a bad impression of Postgres based on the
> inconsistency and incorrectness of some of the statements made about
> MySQL. If a salesperson provides misinformation about a competitors
> product and you find out about it, that salesperson has most likely
> lost a customer.
>
> Anyway, I hope I haven't offended anyone - I'm not trying to troll or
> flame, but rather just give some constructive criticism from someone
> outside both the MySQL and Postgres camps.
>
> David
>