Page 1 of 1

Discussion: DBMS's.....

Posted: Fri Dec 09, 2005 10:40 am
by Chris Corbyn
Before I start, I'm looking for a discussion, not a flame-war, although I'm not sure this lends itself to flaming in any case ;)

After spending the past 2 years working with just MySQL I've decided it's time to play around with some other SQL-based DBMS's and perhaps change what I'm using by default, but at the very least gain the experience.

I'm learning PgSQL (Postgres) at the moment which seems nice and easy enough to switch too if you're used to MySQL (I find the way you add users and create databases/set privileges a bit odd though) and then I'm going to have a play with Oracle 8 (Free Developer License).

I think it'd be interesting to read what your opinions on the pros and cons of various DBMS's are in the real world.

One thing I'm particularly interested in is the server load. Postgres is drastically lower on memory usage from what I've seen so far in comparison to MySQL which guzzles at my RAM. That's a big plus when I'm running a VDS server where costs go up if I require more RAM allocation.

Without further ado.... discuss!

Re: Discussion: DBMS's.....

Posted: Fri Dec 09, 2005 12:55 pm
by Roja
d11wtq wrote:Before I start, I'm looking for a discussion, not a flame-war, although I'm not sure this lends itself to flaming in any case ;)
Anything is capable of flames.. but hopefully this one won't be too bad. For my part, I will try to focus on light, not heat. :)
d11wtq wrote:I think it'd be interesting to read what your opinions on the pros and cons of various DBMS's are in the real world.
I have solid, extensive experience in PGSQL, MySQL, and MSSQL. I've dabbled in others, but those are my focal areas.

I find PGSQL to be the best balance. It is much more compliant to the SQL standard than others. It is easy to code for, and by default it has support for the things I need (transactions, atomic commits, row-locking, utf-8 support). The speed is (imho) not quite as good as MySQL (at least in my testing it isn't for several key areas I looked at), although it gets *dramatically* better every release. Its also under a sexy opensource license, developed openly, well-documented, and - as if that wasnt enough - its free.

MySQL is easily my second choice. It uses some fugly SQL-hacks, but it does so to help speed the engine up. Its the most common db used, so its easy to copy/learn code for it. The speed is industry-leading, although it seems to be decreasing with each new major release (again, at least in my testing, for several key areas I looked at). Its license caused some arguing and difficulties for PHP (which has its own license issues), but that has been settled for the most part. It is reasonably well-documented (although I find the documentation often glosses over MAJOR issues, and is poorly written), and its free.

MS-SQL is a blight upon humanity. It requires the sacrifice of small children to accomplish simple tasks, and it eats kittens and puppies just to keep running. It is only available under the black moon, in exchange for your first born. Avoid it at all costs, lest ye be judged.
d11wtq wrote: One thing I'm particularly interested in is the server load. Postgres is drastically lower on memory usage from what I've seen so far in comparison to MySQL which guzzles at my RAM. That's a big plus when I'm running a VDS server where costs go up if I require more RAM allocation.

Without further ado.... discuss!
I suspect you are getting skewed results from a small sample size. Try increasing the number of simultaneous attempts to 30-40. Then you should see the two use fairly similar amounts of cpu and ram (at least I did). MySQL is simply more aggressive by default about its cache, memory use, and pre-emptive access patterns. PGSQL tries harder to be "per-use", and as a result does (far) better on memory use at the low-end, while being slower to return results. In my experience, they change places at higher levels.. PGSQL (in my experience!) does extremely well with heavy loads, while mysql needs considerable tuning to keep up.

Posted: Sat Dec 10, 2005 8:46 am
by Chris Corbyn
Thanks for the insight. Admittedly I hadn't looked closely enough into the really heavy stuff when comparing the two (MySQL, PgSQL) so I'll set up some almighty queries later today (ermm... tomorrow cos I'm off to the pub) and put them through their paces.

MSSQL wasn't something I was considering trying anyway since I'll almost always be using cross-platform systems although it wouldn't hurt to have on your CV that you've developed with it.

Has anybody used Oracle? -- I know that's what the big insurance companies I used to provide support for were using.

Posted: Mon Dec 12, 2005 2:27 am
by n00b Saibot
Roja wrote:For my part, I will try to focus on light, not heat. :)
Hey, I have heard that before somwhere :P
d11wtq wrote:Has anybody used Oracle? -- I know that's what the big insurance companies I used to provide support for were using.
I have...It feels good to have learnt an industry level DBMS 8)

Posted: Mon Dec 12, 2005 8:33 am
by Chris Corbyn
n00b Saibot wrote:
d11wtq wrote:Has anybody used Oracle? -- I know that's what the big insurance companies I used to provide support for were using.
I have...It feels good to have learnt an industry level DBMS 8)
Another reason I want it under my belt. If I apply for a job at a large company that have been using Oracle for ages it's gonna be a bit off-putting if they know I'll need training up for it :D

Here's something I'm gonna do. Write an OOP webmail type app which stores data in an SQL based DBMS. A key component of that app of course is the "Database" class. Technically I should be able to rewrite the DB class to use any one of MySQL, PgSQL, Oracle and keep the rest of the system working just fine without knowledge of the actual DBMS behind it :)