The League of Magnificent Scoundrels

Continuing my research on different styles of databases that I could possibly use for web developing, I have found something interesting about flat file databases. I have always heard mixed things about them, which I can usually lump into two categories: “It’s faster than SQL” and It’s slower than SQL”. The funny thing about this is the fact that both categories aren’t necessarily wrong, until someone decides to use a word like “always” when describing how a flat file database is better or worse.

Both styles of databases have their pros and cons, and what you are doing should help dictate what sort of database you are going to use (not just what you want, but what is the best/most functional). There are a few things that should be looked into before you decide on what database structure you’re going to go with. Is the data static (hardly ever changed or added to) or is it dynamic (always changing)? Is the website that is using this database going to grow and evolve, or is it a “one time” deal?

Is the data static or dynamic? Flat file databases work very well with data that is meant to be stored and not altered very much, if at all. All you have to do is read the file and pull the data that you want, right? Generally speaking, yes. More specifically, you are going to have to find or make a structure/system for declaring how you data is going to be stored (for example, CSV or by line? The great thing about using a SQL database is that the main architecture is already there – all you need to do is define what the columns and rows will hold, and then create the code (in my case, php) to insert/edit/update/remove the data from the rows/cells.

Is the website going to evolve? For many websites, it is natural progression to add more pages and features to it, either to make it more user friendly or to accomodate other forms of data. Flat file databases can be a headache when it comes to large quantities of data (not to mention that you’ll see a performance degredation due to having to read a file [files can only be read from top to bottom]). SQL can link similar tables/rows together by using “joins”, whereas you may need several flat file databases to be accessed and then the data strung together via php, asp, or whatever else you’re using. SQL allows for a more convenient and modular design of your website. SQL is great for allowing greater scalability as more complex data is needing storage.

It is easier to change the code behind a SQL based site than it is to rewrite all of your parsing code when you allow for more complex data. So, unless you are using largely static data storage, SQL is a good choice for storing data.

After some talk with the developers of AIR, I did some of my own research into the difference between the MyISAM and InnoDB transactional storage engines found in MySQL, and found some interesting things. Contrary to my first belief, MyISAM under-performs when it comes to large-scale, simultaneous transactions (you really start seeing performance issues at around 1000 simultaneous transactions).

At first, all of the results I had were pointing to MyISAM being better, but after making InnoDB’s buffer pool larger and setting autocommit to 0 (meaning that it won’t auto commit any transactions), the results still weren’t looking too good, other than the CPU consumption was considerably lower (10-15%).

One thing I really liked was the fact that it uses row locking instead of table locking, which means that simultaneous reads and writes can be done without having to wait for the table to be unlocked. MyISAM is good for small scale websites, as InnoDB performs slower if there are fewer than 100 or so simultaneous connections and if the data is small (below ~10MB per transaction).

For now I think I will stick to MyISAM for my everyday coding, but I already know a few sites that could benefit from an engine switch…

There is a new game that is entering the MMO arena, and that is AIR, the Aerial Steampunk Online RPG. This one caught my attention, thanks to a blog post by Dread Captain Robert, of Abney Park fame. Here is a small glimpse of what is to come:

AIR is a Steampunk Online Role Playing Game, where players can sail the skies, mine abandoned eyots, create mechanical constructs, concoct potent chemical compounds, join secret societies, and participate in large scale battles as a sky pirate, naval officer, or mercenary. AIR features a deep character development system where the choices your player makes affect his or her attributes and alliances. Take to the skies and unlock the secrets of AIR!

As it so happens, their forum is open to the public as of today. Their simple (as of this writing) forum style is something that I have been working on for the past few days. If you are into games, pirates, steampunk, or just plain looking for something new to check out, this would definitely be it!