The last point I'll make is about scalability on large systems. PostgreSQL scales much better on multi-CPU systems that MySQL currently does.

Take a look at these benchmarks from tweakers.net:
Sun T1 versus Opteron
8 way opteron
Cloverton
Dual Xeon 5160

And look at what happens with lots of CPUs and concurrent users.

Note that almost all of these tests are on 64 hardware with 64 bit OSes.

Their results reflect my own. I find that people who think of MySQL as fast haven't tried it on really large servers compared to Oracle and PostgreSQL. On workstations and smaller servers it does just fine, but on big machines, it doesn't seem to scale well.

    Another much more useful feature, in my opinion, is CREATE INDEX CONCURRENTLY which lets you create indexes without locking readers OR writers from your databas while the index is being created. That's a feature that's VERY handy on large, heavily accessed databases in a production environment.

    Sounds very useful.

    We have a number of largish tables in a medium sized, but very busy database which is highly critical and cannot be blocked for any reason under normal circumstances.

    This makes stuff like CREATE INDEX on a table which is big and frequently used basically impossible without downtime - it will block readers and writers until it's done.

    Another problem we've had is making backups - obviously if you have MVCC (multi versioning) this is possible with no locking - but in MySQL that is only relevant if all your tables are transactional.

    Like many people, we use a mixture of MyISAM and Innodb in production - the snag being that a transactional backup will not snapshot a consistent state of the MyISAM tables.

    Therefore the only solution (as is well documented) is to set up one or more replication slaves - but MySQL (< 5.1) has statement-level replication which is not completely reliable - therefore I'm currently engineering some tools to check the replication and alert any problems.

    The replication slaves can be taken offline / locked to do a consistent backup. However, there is another problem: In MySQL, replication serialises all writes to the database.

    This means that you can only replicate a database if your slaves have enough grunt to process the same queries as the master in a single thread. Fortunately however, this is the case with our system.

    As a master could have many threads doing updates, it might be able to outperform the slaves in terms of rate of change.

    However, in our system this is also unlikely as the master also spends a lot of its time doing SELECTs, which the slaves don't need to worry about (yet).

    Mark

      Wow! That's a lot of work just to do what I do in pgsql with "pg_dump dbname"...

      I just found this blog entry while reading the mysql performance tuning blog, which I found at the tweakers.net site, where they had thanked the guys there for helping to check their configuration:

      Mysql 4.0 to 5.0 performance regressions

      wherein the author talks about a two year old bug that has still not been fixed which means you either have good performance, or a reliable innodb store, but not both. ouch.

      So, to say you can have transactions too, just use innodb is kind of short sited. In pgsql I have transactions, all the time, everytime (hell, you can't turn the dang things off, everything you do is a transaction except create database) plus reliable data store plus good performance.

      I always feel like I'm making one compromise too many when I try to use MySQL for anything other than content management. OTOH, as a target for snort, or a wiki page, MySQL works well, and if you paint it green it won't rust. 🙂

        Sxooter wrote:

        Wow! That's a lot of work just to do what I do in pgsql with "pg_dump dbname"...

        Quite. Although of course, we do have other reasons for setting up replication.

        I can't see us changing all our tables' storage engine soon, it's just too big a change.

        Unfortunately we also need to upgrade to mysql 5 and 64-bit, these projects are going to be tricky.

        Mark

          MarkR wrote:

          Quite. Although of course, we do have other reasons for setting up replication.

          I can't see us changing all our tables' storage engine soon, it's just too big a change.

          Unfortunately we also need to upgrade to mysql 5 and 64-bit, these projects are going to be tricky.

          Mark

          I know, I was being a bit tongue in cheeky there. We run replication with pgsql as well, and actually, having a replicant for backups is nice because you can run them without fear of placing additional load on your primary server.

          Major version upgrades are always tricky. 4.1 to 5.0 seems to be more so than usual, especially if you get bitten by the places where 5.0 became stricter about following the SQL standard, like join order and such.

          We've been updating our internal db server from pgsql 7.4 to 8.2 (that's skipping 8.0 and 8.1) and it's pretty much been pg_dump (with 8.2's pg_dump) and import with psql and point the app at the new server. Let's hope it stays that easy.

            Do you use pgsql on 64-bit? Does it work well?

            I am assuming here that pgsql uses a multithreaded server, if so, 32-bit is very limiting in terms of address space limitations.

            Mark

              MarkR wrote:

              Do you use pgsql on 64-bit? Does it work well?

              I am assuming here that pgsql uses a multithreaded server, if so, 32-bit is very limiting in terms of address space limitations.

              Mark

              Wish I did. We have 64 bit machines but they're running 32 bit RHEL. OTOH, we've only got 4 to 8 gigs ram and we let the OS do the caching (when we were running 7.4 it was the preferred method)

              I'm now working on getting 64 bit OS on newer hardware for a few internal reporting servers, so they'll be the first ones running 64bit pgsql. 64 bit pgsql runs fine

              Pgsql does not use a multi-threaded design, so much as a multi-process design. It uses Unix SysV Shared mem for shared buffer memory, which allows plenty of memory in 64 bit, but limits you to about 1.8 gig under a 32 bit OS. OTOH, sorts and such run in their own memory, so you can use a huge amount of memory for sorts.

              So, postgresql on 64 bit is REAL common because of the inability to run a large buffer pool on 32 bit.

              Last tidbit. This post by Hekki Tuuri on the pgsql mailing lists is fascinating. It points up the huge difference between how pgsql does MVCC and locking and how innodb does it. They are VERY different engines indeed:

              http://archives.postgresql.org/pgsql-hackers/2003-09/msg01221.php

              In pgsql, btw, indexes are updated outside of transactions, and there is no transactional visibility in them. i.e. if you hit the index, you gotta go to the table itself to confirm the visibility of the data you're getting. In InnoDB you never actually hit the table for reads on keys.

                Sxooter wrote:

                Pgsql does not use a multi-threaded design, so much as a multi-process design. It uses Unix SysV Shared mem for shared buffer memory, which allows plenty of memory in 64 bit, but limits you to about 1.8 gig under a 32 bit OS. OTOH, sorts and such run in their own memory, so you can use a huge amount of memory for sorts.

                We use MySQL, which is multithreaded. Therefore there is a limit on the total amount of memory that you can use for the server based on address space, to something along the lines of 2Gig.

                This creates problems for supporting a large number of clients as even a relatively small amount of buffer per client quickly fills the address space (with, say 600+ client threads).

                This is why we really need the 64bit.

                Sadly upgrading will not be easy as we will need to validate our entire huge application.

                We need this on several different servers in our architecture - in fact almost all of our servers run MySQL, but some are more critical than others. Ultimately we'll probably upgrade the lot to 64bit, but it won't happen soon because of the huge workload validating our application (the roll out will probably take months AFTER we've validated it).

                My advice to anyone making a new application NOW (or doing a major upgrade, e.g. mysql 4 to 5) is to go 64 bit as soon as possible, because you may need it one day and you'll save LOADS of testing / validation work.

                Mark

                  Fascinating. I never really apreciated how using multi-process instead of multi-thread allowed postgresql to use more memory space before. While each process consumes a tiny amount of shared memory (about 4k I believe) most of the memory structure required to start a child is allocated independently of the others, and of course, like I mentioned, sort mem is also independently allocated.

                  Because of numa arc, there's been work on having multiple shared mem segments open at once, and that would solve even the limit on shared memory on 32 bit systems for postgresql.

                  As Spock would say, this has been a truly fascinating thread.

                    Note that using multiprocess vs multithread probably increases the overall memory consumption, but in this case, that's good as it allows you to use more of the memory available.

                    The multithreaded approach is probably better on 64-bit systems where there is enough address space. It is also probably quicker to start a new (e.g. MySQL) thread than a new (e.g. pgsql) process.

                    But I haven't measured it.

                    Mark

                      The difference in memory usage is probably so small as to not really make a difference, but the startup speed of PostgreSQL is well known for being much slower than MySQL. Part of this is the process versus thread creation speed, but most of it is the difference in architecture. PostgreSQL has a lot more to do to start up a new backend than MySQL, so it takes much longer (like 5ms for mysql and 30-40 for postgresql on the same machine). Connection pooling makes that less of an issue, as do longer running pages. I.e. if your average page takes 500 ms to execute, the difference between 5 and 30 ms isn't so great.

                      The real difference between thread and process is one of robustness.

                      When a pgsql process crashes, it can't affect the other processes directly, it simply doesn't have the access. The postmaster detects the crash and flushes the shared_buffer and all the other backends just keep on going.

                      A crashing MySQL thread can kill the whole server, because threads have access to each other's memory space. There are some threading models that reduce this risk, but you can't really completely eliminate it without basically emulating processes, including having the overhead that slows down process creation.

                        Sxooter wrote:

                        A crashing MySQL thread can kill the whole server, because threads have access to each other's memory space. There are some threading models that reduce this risk, but you can't really completely eliminate it without basically emulating processes, including having the overhead that slows down process creation.

                        Faulty software can cause badness, whether it crashes the whole server or not. Not that I'm claiming pgsql is faulty, but nor, it seems, is MySQL particularly.

                        We're using MySQL 4.1, our main production server has been up for 6 months or so now since the last reboot without crashing, it serves thousands of queries per minute, 24/7, typically with > 200 clients connected most of the time (and more connecting and disconnecting constantly).

                        Our database isn't particularly large, but it is VERY busy.

                          I would agree that crashes in either database are pretty uncommon.

                          In postgresql, since it's extensible, it's more common for people to run with extensions installed, like slony, or user types, or user defined functions written in C, etc... each of which might cause a backend to crash occasionally, especially if you're an early adopter.

                          Of course, one should NOT be running untried / unproven backend code in production. 😃

                          I too have uptimes measured in months and years on my dbs. With no backend crashes in that time.

                          May neither of us ever run an off the wall query that causes the db to hit that one bit that makes it crash, eh?

                            I bet the guy that started this thread never expect it to explode like that.

                            I learned quite allot reading this thread - very detailed and interesting.

                            Thanks.

                            Slightly off the threads point... but I'll ask anyway, besides the non-open source and not being free. Do you have any views on SQL Server in comparison to mysql/postgre?

                              I have only dealt with MSSQL server a little bit. It's one fatal flaw is that there's not a version to run on a real server class* OS.

                              • Server class = package management among other things, as well as not requiring me to run a GUI or Internet Exploder on it. 🙂

                                MSSQL is not that bad really, the main problems I have found are mostly with the way people use it.

                                One problem is that there are some types of table alteration you can't do with ALTER TABLE - some alterations are simply impossible. In any case, the standard recommended way is to rename the table, create a new one and transfer all the data (this can of course be very slow and blocks clients, but internally that's probably all an ALTER TABLE would have done anyway).

                                Another issue is the difficulty of dropping object which have dependent objects with a system-generated name. Whenever you assign a column a default value in MSSQL (A common thing, you might think), it will create a constraint object with a system-generated name (Which won't be the same in different installations).

                                If you subsequently want to drop a column with a default value, it's necessary to drop this system-generated constraint first. This is tricky because it doesn't have a fixed name from one installation to another and they must be dropped by name.

                                I eventually gave up and wrote a rather complicated nasty stored procedure to work out the name of the constraint, drop it, then drop the column.

                                The only reasonable way of keeping schemas in synch between dev / staging, production etc, is to script all changes. MSSQL makes scripting some schema changes VERY hard.

                                The GUI generates scripts for you to script a given schema change, but this has one major flaw - these scripts contain the hard-coded names of dependent objects, which have system-generated names, so these scripts often won't work on another server.

                                Mark

                                  I'm pretty sure Sxooter is a paid PostgreSQL lobbiest. ;P I mean...Jeremy Zawodny and the dude who runs Tailrank CAN'T be wrong about MySQL being the most amazing database and the only one you'll ever need!!!

                                    csn wrote:

                                    I'm pretty sure Sxooter is a paid PostgreSQL lobbiest. ;P I mean...Jeremy Zawodny and the dude who runs Tailrank CAN'T be wrong about MySQL being the most amazing database and the only one you'll ever need!!!

                                    In a way, that's true. My job (or at least part of it) is keeping PostgreSQL servers happy. But I also keep Oracle servers happy. I prefer keeping PostgreSQL servers happy. They require less regular feeding and care than Oracle.

                                    The funny thing about MySQL is that in terms of what people have to do to make it happy it seems closer to Oracle than PostgreSQL. Like working around the limit on rows in a myisam table, having full text searching on an innodb table by exporting it daily / hourly to a myisam table. Fixing corrupted isam tables after a db crash, and so on. Dealing with a bloated innodb store. And on and on. Again, that db just feels like one compromise after another.

                                    Oracle claims to be unbreakable, which we all know is crap. The closest I've come to an unbreakable db so far is postgresql. The times where things just get lost are far greater in Oracle or MySQL than in PostgreSQL.

                                    Which databases, out of the three, will silently delete data here and which one won't?

                                    begin;
                                    delete from tablea ...
                                    alter tableb ...
                                    insert into tablec ...
                                    rollback;

                                    Three guesses and the first two don't count.

                                      Sxooter wrote:

                                      Which databases, out of the three, will silently delete data here and which one won't?

                                      begin;
                                      delete from tablea ...
                                      alter tableb ...
                                      insert into tablec ...
                                      rollback;

                                      Three guesses and the first two don't count.

                                      Yikes. Hopefully it's not Oracle too.

                                      I was thinking about taking some database classes for resume filler, but all they offer is Oracle or MSSQL. But I guess either is good to know. Do you have any insights on PostgreSQL's CertCamp or other classes (Intensive DB, etc.)? Think a PostgreSQL certification is worth much?

                                      As for PostgreSQL, it's been going and going for me for years, even through several crashes (PG not to blame). Makes me think of the Maytag repair man. As for the MySQL issues you brought up--BEEN THERE, DONE THAT! Shudder...

                                        csn wrote:

                                        Yikes. Hopefully it's not Oracle too.

                                        I was thinking about taking some database classes for resume filler, but all they offer is Oracle or MSSQL. But I guess either is good to know. Do you have any insights on PostgreSQL's CertCamp or other classes (Intensive DB, etc.)? Think a PostgreSQL certification is worth much?

                                        As for PostgreSQL, it's been going and going for me for years, even through several crashes (PG not to blame). Makes me think of the Maytag repair man. As for the MySQL issues you brought up--BEEN THERE, DONE THAT! Shudder...

                                        Yep, Oracle too. I think they were saying that 11G might have transactable DDL, but I'm not holding my breath, and I'm sure there's a compromise or two in there.

                                        The postgresql cert that you posted a question on elsewhere (don't remember where now) seems thorough. I'd say it covers all the basics that I learned over several years teaching myself.

                                        I'd guess that if you don't have the money to spend, you could scrape the pgsql news groups on those subjects, set up a couple of machines to test it on, and learn it all by yourself. But if you've got the time and cash it would be a good course to take.

                                        As for MySQL, I've always felt that the relationship between OS proponents and MySQL was kinda like a disfunctional marriage, where one partner contantly makes excuses for the shortcoming of the other abusive partner, and even paints them in a favorable light. Which might be understandable if it were the only partner, I mean database, available that was open source.