MySQL

Next on the list is an application that shouldn't be a stranger to most - MySQL.

Before diving into it, I should not that the real power house here is SQL and there are other options such as Postgres that almost certainly could do as well or better.

The reason I wanted to bring this up is that having a powerhouse database server sitting behind you when you are trying to put applications together is amazing. There are a ton of data management problems that you solve in one feel swooop by just choosing to use MySQL as a back end to store your data.

If you have an application that needs to have hundreds of thousands of entries added per day, and manage that insane quantity of information so that you can use it in a productive way - then MySQL is a great tool.

A thing to note here - if you just have an application that doesn't need any persistent data and doesn't ever need to interact with anything than of course you don't need a relational database added into the mix. The really interesting projects tend to be really complicated and so I tend to lean on MySQL a lot for it. In my last blog I talked about postfix - a key lesson I took away from postfix is that it's great to write small app's that each do targeted jobs. MySQL is a great enabler for this approach. Say you have an application that requires real time threat analysis and triggers actions to stop hostile attackers. Using MySQL as the point of IPC (Inter Process Communication) it let's you split the job up into relatively easy to manage chunks. You have agents that collect your data and feed it back, and then you have a series of analysis scripts that look for trends in that data, and finally you have scripts designed to process that analysis into actions.

One of the big weaknesses of relying too much on MySQL is that it creates a central point of failure for your application. I hate designing applications with this kind of a dependency in them - fortunately there are ways to deal with this to. First I like to use queue's for data that needs to be written to a SQL server. Rather than add a dependincy on a mission critical service (like email delivery) to the MySQL server, I instead make my application dump it's logs into a queue. I then have a secondary program that processes that queue of log data and loads them into the SQL server. In this way if the sql server is down, service still progresses just fine, and when it comes back up it get's all the logs sent over to it, so it doesn't miss anything.

If you are using your SQL server to impact the configuration of applications there are other tricks that work to get around that. The most basic one is to use SQL for all of your configuration, then write scripts that generate application specific configuration files that are then distributed to your remote servers. In this way you get all the wonderful advantages of SQL for managing your data, and you keep all of your services from depending on the SQL server being up in order to function.

The preceding is all underscoring an important reality for many applications - the application must have 99.99% uptime, but the ability to modify settings for that application has a whole lot more breathing room. To illustrate - how mad are you if you can't get your email? how mad are you if you can't change your password?

I'm trying to communicate a lot of best practices in these blogs - you can solve any problem a whole lot of different ways, but I would encourage you to look over these ideas and at least think of them when you are diving in to make your own "useful things".