Well, there are days in an admin's life that hurt but are necessary: I'm currently playing around with a spam filter (DSPAM) that stores its statistics in an SQL database. The spam filter supports a variety of database drivers, including PostgreSQL and MySQL, and a few other non-client-server databases (SQLite, etc.). So, out of habit, I first reached for PostgreSQL - it was already running on the machine.
Well, it was a bit slow at the beginning and the machine was a bit overloaded, but I found a few tips on the net with which one could make PostgreSQL run faster for DSPAM. After that, the computer didn't run particularly fast, but significantly faster than before. So, let it run through the night.
Well, the next day the rude awakening: tons of blocked processes, pig-slow updates against the database, deadly performance when learning a mail: 12 minutes runtime is no exception. Ouch. The database dump is already 100 MB in size at this point. The whole thing is not particularly exciting when the system load is always between 3 and 6 ... Ok, so bite the bullet and install and configure the index card box MySQL. Then bring Exim back up and sort the waiting mails. Effect: total load explosion. Loads above 30 and then at some point the watchdog struck and booted. Oh shit. All clear, let's see what's actually in the box: yeah, only 256 MB of memory and the MySQL server got massively into paging. He can't help it if I just don't have enough memory. PostgreSQL had fewer problems with that because PostgreSQL's memory management is much more static and the server doesn't grab so much memory in the basic configuration.
Ok, Jutta swapped the memory with her Linux box and now the server has 512 MB of memory, which is enough for the purpose. And the system load with MySQL is significantly better than anything before. Ok, I could certainly also bring PostgreSQL to better performance with a larger configuration, but the problem was, according to the symptoms, the massive number of parallel updates and the multi-version transaction technique of the server - that was definitely in the way in this concrete case.
Note: MySQL is still only a glorified index card box and MyISAM is definitely the dumbest table format you can choose, but no technology is so stupid that you can't need it from time to time. If the data is completely transaction-free - because the SQL server is simply being misused as a data storage without a real business data model with great referential integrity - then you should simply not use a database whose focus is exactly the opposite. In this case, MySQL and MyISAM are simply the better choice.
It's definitely better than Berkley-DB or other in-process databases, because they can only work reliably via file locking and with the massive parallel updates that DSPAM makes (it learns - depending on the setting - with every mail and updates its statistical basis) a database on a file system basis is extremely unfavorable.
Now I'll wait for the next night and see how DSPAM struggles with the nightly mail pile and how the system looks tomorrow, when several thousand mails have been processed (yes, with only two users we consume gigantic amounts of mail traffic - primarily due to mountains of spam, mountains of administrative mails from various systems and mountains of mailing lists). Let's see if the system is still as fast tomorrow as it is today. I'm afraid that with the amount of mail I will also push the MySQL base for DSPAM to the limit of the possible ...
Update: so far, things look very good with the load, so the index card box actually has the nose ahead