Friday, 25 November 2011

Relational databases – a quick look at flavours strengths and weaknesses

Let’s start with the master of them all Oracle.  It’s the db with all the tools, tweaks and it scales well.  And if the price was right this is the one most would or should pick, however it seldom is. Oracle never followed the development in the processor where each core get weaker but you get a lot more of them. Hence their penchant for charging per core and their customers liking of the HP Itanium processor.
 Oracle is so advanced that it’s more like an operating system in itself and you need to take your patching seriously.  Also be into your file system details. Play with the config files, there is a lot to be gained.  It’s a pity the 3 defaults of small medium and large is not more up to modern standards.  Proper bakcups are essential. 
Oracle do not like loss of any of it’s data.  And since a lot of performance can be gained from running it raw, a simple file system backup won’t do the job.  You need to learn about dumps like dd, and have it done in the correct order.  Exports is also very important. In addition to being a secondary way of doing backups, they can also give you a lot of hints on fragmentation and proper sizing.  Don’t either forget to have multiple control files in many separate locations.  
It’s the one db where you really can’t live without a support contract from the mothership.  And if you have a set of the printed manuals, they will be from a previous version but they are worth their weight in gold. And 95% of them is still applicable.  Read all about it’s system tables.  There is a lot to be gained here. For standardisation and easy admin to admin transfer have a look at the old OFA manual.

It’s nearest competitor as a multi os db is Sybase. Now owned by SAP. A brilliantly designed but more simplistic model. However you’ll have problems getting more than 1 installation (version) onto a single server. Instead it uses what they call userdatabases. Requires a strict discipline as an admin so you know which one you are in. But organizing the file storage and backups are a lot simpler
It’s penchant for “go” is not as good as Oracle’s execute command, and it’s method of dumping output to file is archaic. Like Oracle it’s very sensitive to playing with the kernel settings on unix/linux. Most of it’s performance is to be gained here. In addition to, like most relationals, a good scheduled reindexing.  A good set of Sybase’s own manuals will go a long way for your support needs.

Mssql could have done the knockout on the other db’s if it hadn’t such a scaling problem. It depends on a single server, and Windows on top of that, and can only scale upwards at the speed of the hardware development. Windows Datacenter is an option but due to its obscurity and odd Microsoft rules on deployment, Windows Enterprise is really your option. And then we are back to this processor thing again. It is a database that most admins can manage though, even without the scarce manuals.  They might not utilize all it’s potential but any Windows admin can make it run. Just give them a few hints or a small course on simple housekeeping like dumps and scheduled reindexing/reorg.
Mssql’s testing/analyzing tool is very good but it’s not as handy as Oracle’s command line “desc” for analyzing sigle sql queries.  However it does give you a nice way of presenting your findings.

Adabas owned by the German Softwareag is a story of what could have been. Popular among some German companies/developers it never reached the popularity of Sybase. If you have seen it its probably because you had a system from a German company that was based on it.  Very simple to manage, don’t even need a manual to start, stop and backup this one.  Low cost and flexible.  It’s ripe for a large multinational to take it over.  Somebody with a long reach, believe and financial muscle to push it into the limelight.

Mysql the developers  favourite due to their perception that it’s “free”.  Now owned by Oracle. There is no such thing as a “free lunch” however.  What you don’t pay for the software itself, you,  due to it’s popularity among specialists, will pay for in admins.  Recommend testing your restores frequently. Specially when it comes to getting back the last data entered.   Ripe for a organized set of admin tools. Oracle has a long way to go, and lots of opportunity for ad on profit making.

A problem with all relationals is that they are good for adding and picking/filtering small amounts of data and creating automation for repeated actions, but when you reach certain level of reads needed it’s better to forget about the indexing.  When that happens the old ways are better. The db’s  security against data loss also makes them vulnerable for slow down by locks and erroring by deadlocks. This is why if you have to read all the inputs/data it’s faster to use the file system directly for your (interim) storage, without the overhang.  Many large players do.

No comments:

Post a Comment