Sometimes, a bunch of decisions, each of which – considered in isolation – is perfectly sound and rational, can come together to bite you in a really nasty place.
1. Database servers are usually configured with their own local disk drives, so they can get the maximum throughput supported by the motherboard, rather than storing data on a remotely-mounted network share. Acquiring and releasing file locks, which is something that databases have to do a lot, is also faster and more reliable using local hardware to which the kernel is talking directly, rather than by sending requests and acknowledgements across the network where it contributes to the overhead.
2. You don't always want to have to think about file formats in too much depth. Sometimes, what you have to store lends itself naturally to a database. Someone else has already done the hard work, implementing the storage engine. All you need to concentrate on is getting the data in and out of the application.
(Database servers also tend to be surprisingly light on resources; because traditionally, they have had no alternative but to have been.)
Using a ready-made database server to store your data can make better sense than devising your own ad-hoc file format that may limit you in future in ways that, by defnition, you haven't thought of.
3. Desktop users shouldn't need to be intimately familiar with the under-bonnet workings of a database merely in order to use their computers. If a database server is used by an application, its configuration should be managed by the application itself. This is especially so if the application makes use of esoteric configuration options.
Starting a specific instance of the database server, just for that one user, with a configuration decided by the application and so specifically suited to it, can make better sense than trying to use an existing system-wide server. (You don't have to be root to run a server, if you listen on a non-privileged port and take care not to write anywhere you aren't allowed.)
4. A network with multiple users running a limited range of desktop applications (mainly a web browser and an e-mail client) would be really, really great if it had the ability for any user to log in at any workstation and have their own home folder. You can rotate more staff than you have workstations, through any combination of shifts; and know that, as long as there are enough workstations for every team member, everyone will be able to access their own home folder.
So, it makes sense in such a situation to use NIS to handle user logins, and mount /home on an NFS share.
Four eminently sensible decisions, taken in isolation. But put them all together and what you get is, multiple databases in users' home folders on the same NFS share.
This only became apparent when I upgraded a bunch of desktops to Debian Wheezy. (I'd resisted Squeeze for long enough already, because of the move to KDE4. KDE3 was good enough for most of what we needed – it's just that awkward "most of" that's the problem.)
Now, Wheezy uses KDE4, as did Squeeze. KDE4 includes Akonadi, which uses a MySQL database to store data. And it starts an instance per user, configured especially to use a data store under that user's home folder. The newer applications in Wheezy make heavier use of Akonadi's database functionality.
But Wheezy also has MySQL 5.5, whose default database engine is InnoDB – as opposed to Squeeze's 5.1 using MyISAM by default.
And this is where the trouble begins; because InnoDB tables don't play nicely over network shares. NFS is too busy locking and unlocking files to serve much data; and very occasionally, messages get out-of-order and a lock or unlock is missed, resulting in slowdown waiting for the lock to timeout or – worse – data corruption if a write occurred while a file was supposed to have been locked.
So when four or five users on the same network share all try to start up their Akonadi servers at exactly the same time, the result is a packet jam not quite of VoIP-degrading proportions, but certainly getting on for it. All because of an unfortunate concatenation of otherwise seemingly-innocuous circumstances.
Fortunately, there is a happy ending. I'll explain all in the next post .....