On Wed, 2006-03-22 at 05:06, Jimbo1 wrote: > Hello there, > > I'm a freelance Oracle Developer by trade (can almost hear the boos now > ;o)), and am looking into developing my own Snowboarding-related > website over the next few years. Anyway, I'm making some decisions now > about the site architecture, and the database I'm going to need is > obviously included. If my site works out, I'm expecting reasonably > heavy traffic, so want a database that I'm confident can cope with it. > > It is out of the question for me to use Oracle, although I am a > (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper > route, and to that end I'm looking at either MySQL or PostgreSQL. We use both where I work, for different things. We also use Oracle for our transactional engine. I submit bug reports for both PostgreSQL and MySQL. I've asked for help for both databases. The PostgreSQL users and developers are unbelievably knowledgeable and helpful. When I first started using PostgreSQL I found something that seemed odd, in that functional indexes couldn't accept constant arguments, only column names (this was with 6.5 or 7.0 or something like that) and within a few hours Peter Eisenstraut (I'm sure I spelled his name wrong there) had posted a work around for me. I've found a few bugs here and there. They've all resulted in changes being made anywhere from very fast for serious bugs, to being pipelined into the process for the next major release. I've never had that kind of very fast response from MySQL. If feels like those guys really have their hands full meeting all the requirements for the next version to pay much attention to the smaller bugs. For instance, this DDL in MySQL: mysql> create table a (x int primary key) engine innodb; Query OK, 0 rows affected (0.13 sec) mysql> create table b (y int references a) engine innodb; Query OK, 0 rows affected (0.07 sec) mysql> insert into b values (10); Query OK, 1 row affected (0.07 sec) Executes without a warning or an error. Now, I shouldn't be able to insert anything in b that's not referencing an entry in a. and I used innodb tables. and I used ansi SQL, and I got no errors. So how come my data's incoherent three seconds after creating the tables the way the spec says should work? Simple. MySQL only implements foreign keys if you do them this way: create table b (y int, foreign key (y) references a(x)) engine innodb; I.e. you have to declare fk constraints outside the column defs. Why? Because innodb tables and transactions in general are kind of like red-headed step-children, I guess. It's not gonna change. I files a bug report on it and the answer was basically "yep, that's how it's supposed to work". This flies in the face of MySQL's claims that they are striving towards SQL specification compliance. In closing, I found that with PostgreSQL I had to invest a fair bit of time learning how databases were supposed to work, and the more I used it, the happier and more rewarded I became. With MySQL, it's really easy to get started. It's over time you start thinking "maybe this thing isn't quite right in the head" with some of the odd behaviour like the one I listed above. And it's just one of many. that said, for content management, MySQL is a GREAT database. It has collation per column, something PostgreSQL only gets about halfway to right now with collation classes. OTOH, when that gets added to PostgreSQL, it will likely be done very well. As for the load handling, anyone who benchmarks a database with a single thread (which is what a LOT of people do, sadly) should never be allowed to declare any database fast. A database that can do exactly one thing at once fast is not really all that interesting. A database that can stay on its feet with 1000+ users hammering away updating and inserting and selecting and deleting is impressive as hell. And PostgreSQL is quickly moving in that direction. The whole "built in replication" argument smacks of intellectual laziness to me. Read the bug reports on mysql.com about it. There are scads of reports of it just stopping for no good reason. And how do you bring it back online? Simple. shut down your entire application, disconnect all users, and FILE COPY everything to the slave server. Restart replication, and then reconnect all your users. This is enterprise quality? I hope they come up with a better way than that. With Slony, I can add and remove replicated sets on the fly, on a living database system, and the user never even notices. I can use Point in time Recovery in Postgresql, a feature MySQL currently lacks, and one I consider more useful than real time replication for distaster recovery anyway. After all, replication can't save you from "delete from tablename" with no where clause. Again, I can set up PITR replication on the fly, without shutting down the master database for even a second. Lastly, try creating a test app of some kind that runs a lot of queries (read and write both) on MySQL and PostgreSQL. Turn it on, let it settle and place a heavy load on both machines. Pull the plug. Assuming PostgreSQL is running on proper hardware (SCSI drives, RAID controller with BBU, etc) it will come right back up. MySQL, if it's running on myisam files, will be trashed. You may spend quite alot of time getting it back up, and it will be missing data, and if you have related tables, you will most certainly have orphans now. Innodb tables should survive. But will they still be in MySQL in a year? who knows? I suggest someone put rubber on the road there and test both, including possibly destructive tests like the power plug being pulled several times. See how they both handle a simulation of what load you think you'll have. See how much faster each gets when you move it from a single CPU machine with 512 Meg ram and a single hard drive to an 8 way Opteron with 16 gigs ram and a 12 drive RAID 1+0 drive set. Now is the time to do it, before you've "married" your company to one or the other, and found it wanting. Waving your hands around magically declaring one better than the other gets you no where.
