Back when I was a teenager, Jamie Oliver was a joke to me. Naked Chef, ha ha ha. Girls love him, he probably can't even cook, gimme a break. If you mentioned him, I'd snort and go back to focussing on the playstation.

I never thought I'd say this, but Jamie is now one of my greatest heroes.

What changed in those 10 years? I got older and more mature, but I didn't find a great love for cooking. In fact, even just one year ago, my opinion wouldn't have changed at all. No, what happened was I decided I wanted to live a remarkable life, and in the process my perspective on what a great life is changed completely.

In fact, I now believe that most young people - indeed, most people - are selling themselves short, trying to obtain life security when they should be aiming far higher. And Jamie Oliver is a prime example of what young people can achieve in this world today.

Like this post? Subscribe to my RSS feed and follow me on twitter to hear about new posts early.

Want to share this post? Tweet

This is cross-posted from a forum post I made on mahara.org. I know the PostgreSQL vs. MySQL debate has been had a million times before, but don't see the harm in registering my opinion on the whole issue. I know it's already convinced some people to switch to PostgreSQL, maybe it will convert you too...

---

Rather than simply saying MySQL is terrible and Postgres is better, I'll try and outline a few good reasons based on the years I've spent (initially using MySQL, but now exclusively on Postgres if at all possible), and based on my "developer's viewpoint" - that is, looking whether the databases behave and perform the way they should, and not so much at whether they're "easy to use/newbie friendly", whatever that means.

And a disclaimer from the start.. firstly this is a long post, and secondly that we are committed to MySQL support in Mahara.

--

The most important thing you can ever have in a relational database system is data integrity - which means that under no circumstances does the database allow your data to get into an incorrect state. Many databases have lots of safeguards built in to ensure your data remains sane - foreign keys, check constraints, transactions etc... and so when we write Mahara we make want to make sure we can use these features to prevent people having issues like poor William had when he upgraded his site. [1]

The main reason for this is because we're not perfect! Sometimes we make a mistake in the upgrade process, or a bug in the software, and then other people run into our bugs. The last thing we want is for someone to hit a bug that corrupts their data, because without that data, their site is worthless.

So hopefully that gives you a background into what we care most about. We also want to use a solution that performs well (speed when returning a query or doing any database operation), scales well (is still speedy when many people are accessing it), is well documented (so we can find out how it works easily) and has a good security policy (so our users don't get stuck using something that hackers can take advantage of), among many other things. But data integrity is the most important thing.

Given our need for data integrity, the first thing we see is that we can't use MySQL's "MyISAM" storage engine for any tables. It doesn't support transactions, which clearly will allow data to become corrupt. It doesn't matter that the engine is fast - it doesn't do the most important job a database should do. So MyISAM is out. From Mahara 1.1, if your MySQL setup only allows MyISAM tables, you won't be allowed to install Mahara.

Also, MySQL's "NDB Advanced Clustering Engine" is also out. If you want to try and scale your installation this way, it's a bad idea - it requires you turn off foreign keys.

The only MySQL engine that has most of the major data integrity features is InnoDB. It supports some transactions, and it supports foreign keys. But it is still missing some important features too: CHECK constraints and transactions on database modification operations (CREATE/ALTER TABLE etc.). So we see that we can use InnoDB and it will work "most" of the time, but it's not perfect.

On the other hand, PostgreSQL doesn't have the concept of database engines. It's just got one way to store tables, that uses its MVCC model for data integrity. This model fully supports transactions (on database modification as well as INSERT/UPDATE etc.), supports CHECK constraints, foreign keys, and also supports some other useful features such as indexes on functions of columns, that Mahara sometimes uses. Here are some examples of where Mahara uses these data integrity features:

• Transactions - these are used everywhere! I won't bother to explain what they are, I'm assuming you know what they are. But I will re-iterate the point that PostgreSQL supports transactions for altering the database structure itself, something that MySQL can't do. Not even Oracle can do this! This is explained on the installation instructions page, but I'll point it out again that this means we can do upgrades without fear that if they fail, the database will be in a corrupted state. The benefits of this are obvious!
• Foreign keys - used everywhere again! Everyone knows what these are. But it amazes me that so few open source software projects don't use them. Drupal doesn't, Moodle doesn't, and so many more - yet it's a near criminal offence. Corrupted data is a nightmare to work with, it causes software bugs and "strange behaviour" that can be a mess to deal with.
• Check constraints - again, common everywhere except open source, for some reason. Mahara 1.1 ships with one in particular that will be useful. In the view and artefact tables, only one of the owner, group or institution columns can have a value. In Postgres, we have a check constraint for this, in MySQL we can't have one. So users on MySQL are more likely to suffer if there is a bug that results in more than one of these columns being set.
• Indexes on functions of columns - best demonstrated with an example. In Mahara, usernames are case insensitive, so we want to make sure that nobody can insert the user 'BOB' if there is already a user 'bob'. In postgres, we can simply create a unique index on LOWER(username), and our problem is solved. We can't create this index in MySQL, so again MySQL users miss out.

The other things that people seem to care about most are performance and scalability. As explained before, we can't use MyISAM, so we compare InnoDB against Postgres. The simple answer is that, speed wise, they're comparable - although Postgres provides all the extra integrity features. The truth is though, the database isn't the bottleneck on small sites - the main bottleneck is CPU when processing the PHP pages.

On bigger sites, the scalability of the database becomes important. And sadly, MySQL simply falls over flat. Under high concurrent user access, MySQL's performance drops through the floor. What's worse is that it has bad locking characteristics. For example, when moodle.org does its backups, the site is basically unusable. Whereas Postgres is much smarter about locking, and doesn't need full table locks for almost any operation it performs.

The last thing I'll mention is this: have you ever had to use REPAIR TABLE in MySQL? What a joke. It's sad the command even exists, let alone the fact that it's needed so often. There are clearly some deep-seated bugs in MySQL to do with integrity that they need to sort out.

--

Anyway, this has been an extremely long post, but I hope it can serve as a reference for everyone when choosing what database to use for their Mahara installation. I'll summarise with my personal opinion, and a bunch of links you can read to get some more background.

Personally, I wish we didn't have to support MySQL in Mahara. It requires us to do more testing, work around more problems and make compromises. If anyone tells me they're a "MySQL expert", I just laugh to myself, because I think that if they were a "database expert" they'd be using Postgres. But most people use MySQL, because it's the "database" with all the mindshare right now, so we support it in Mahara.

MySQL support in Mahara isn't ever going to disappear, don't get me wrong. But I think you're selling yourself short if you use it when you could use Postgres instead.

Here are some links:

• http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL - a reasonable, recent comparison. It has some facts about Postgres incorrect, such as the entire section about Insert Ignore/Replace (postgres can easily insert more than one row at a time, in 8.3 it adds support for the multiple form of INSERT, but it's also supported COPY for ever)
• http://tweakers.net/reviews/657/6 - MySQL vs. Postgres scalability
• http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 - Yes it's on the postgres wiki, but that at least means the facts about postgres are correct
• http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis - my example to the author of the previous report encouraged this page

[1]	William is a Mahara user who was affected by a bug in the MySQL upgrade path from Mahara 1.0 to 1.1. Unfortuntely, he did not have a backup of his database to restore from.

Like this post? Subscribe to my RSS feed and follow me on twitter to hear about new posts early.

Want to share this post? Tweet

Every software developer, writer, artist - almost any professional, in fact, knows about being in The Zone. It's a place you strive for when working, where you're concentrating and everything you need to know is at the front of you mind and you're making progress and work is easy and all of a sudden three hours have gone by and you're on the other side of that difficult engineering problem and you're hungry.

Up until recently, I'd made the zone twice in the last month. On the weekend between 11pm and 3am Saturday night/Sunday morning.

Not making the zone often isn't good for your productivity. Developers are often percieved as layabout slackers by other non-developer types. Always reading Slashdot, or arguing about Perl vs. PHP, or something equally unproductive. But the truth is, when it's getting near the end of the day, and a developer has been staring at their editor for a while, code starts falling out at alarming rates. And all of a sudden it's 7pm and they're still at work and they feel tired all of a sudden and it's time for the commute home to the nagging partner: "you work too late, they don't pay you for that..."

It's those periods in the zone that "pay" for all that time spent slacking about. You might be worth $20 an hour reading Slashdot, but you're worth $250 an hour in the zone implementing that critical new feature elegantly. So when you're not making the zone much, you're in trouble.

I've been looking around for reasons why I wasn't in the zone, and what I came up with boiled down to these two points:

1. Too many distractions
2. Too many jobs

Distractions are deadly. If you're trying to get in the zone, and the phone rings, *bam* you just lost 10 minutes of productivity while you context switch to the phone call, then try and switch back. Now, you can't do much about the phone, it's designed to interrupt you and you may need to be interruptable for emergencies. But if it's the phone of someone else in the office causing an interruption, that is a problem.

Unfortunately, the layout of desks at work is what I would call the "Silicon Valley" style. That means one big space, lots of desks. There's no partitions or cubicles, you can see from one end of the room to the other. There are 24 people in this space, which is about 185sqm. This is smaller than the average house. But the space isn't such an issue - it's more that with 24 people, at any given point in time, one of them is likely to be the cause of some kind of interruption that could disturb everyone around them.

These interruptions can take any form - from a phone call, to a string of text message alerts, to a group conversation, to an angry outburst by a colleague (this happens when developers code - it's a side effect of being in the zone that many people don't think about their actions).

Now I'm not saying that all interruptions should cease, because group conversations still need to happen, people still need to answer their phones and developers still need to swear at PHP from time to time. And goodness knows, I've been the cause of a few good distractions in my time too. But most of these interruptions can be easily stopped in their tracks by simply having an office per coder.

Offices mean walls that block sound and doors that close. Which means when you're busy and need to focus, you just shut the door and hang the "do not disturb" sign out, and you get an instant incubator for productivity. Cubicles, on the other hand, give you nothing. Sound still travels past them, and there's no door to keep out any pesky invaders.

The other cause for failing to get into the zone - too many jobs - comes about because there's a lot of different tasks to do, and never enough time to do them all. "Boo wah", I hear you say, "we're all busy". Yes we are, but the human mind works far better on one track than when multitasking (at least the MALE mind does ;)), so having four things to do just slows you down to a crawl as you thrash from one context to the next [1].

Let's assume that I have this task list currently:

• Import (big coding job)
• Security patches (small coding job)
• Site upgrades (a boring job similar to data entry for coders)
• Forum support

I've found that it's easy to get distracted by the small coding job and the forum support, and never get anywhere on the big coding job until it's almost too late to complete it on time.

Now there are some things you can do about this, since they're actually things you can control (unlike interruptions). If we ignore "palm some of the work off to someone else" and assume that you've been given a reasonable amount of work, then you need to recognise what your tasks are, and stick at them all day, or as long as it takes to get them done, if that is shorter than a day of work.

For example, I've found there's no point in thinking "I'll do the security patches, then move on to the site upgrades, and if there's any time left I'll start on the import." What actually happens is, I start on the patches, get distracted by e-mail, go to lunch, mostly finish the patches, go to the shop, read some more e-mails, and all of a sudden the day is gone and I haven't really got through much at all.

What would make more sense is for me to think "I'll do the security patches". Then you sit down, close your email, ignore everything else and do those. By the time you're done, it's lunchtime, and you can re-evaluate. "Email". Read read read reply read reply done, it's 15:30. "Site upgrades". Close email client, grab data from production, set up testing site, start cherry-picking, now it's 18:00 and time to go home. First task for tomorrow: "Finish upgrades".

Perhaps this seems a little contrived, but that's how things have worked in my experience. I need to be able to focus on one task, which means my one track mind can actually finish something, so I can move on to the next task.

So, given these two reasons for failing to get into the zone, there were only two solutions that were going to help:

1. Get an office
2. Change my habits

Unfortunately, there's very few offices at work, and I don't happen to have one. I couldn't just build one out of thin air, but I do have a perfectly good apartment that's only 10 minutes walk away. So there was my first solution: work from home. I'm not going to claim it's the ultimate solution, as you do miss out on being "in" on things, like the office grapevine. But right now, I have more important productivity issues to fix, so I'll stick with home.

"Change my habits" is a bit vague. I'll re-define that as "change my habits in order to make having a one track mind easier". Here are some of the things I've done to try and reduce interruptions:

• I quit most of the IRC channels I was in. I'm now in just 7 channels, three of them directly related to Mahara.
• I check my e-mail at the start of the day, do another quick check at lunch and then again at the end of the day. Between those times, I close my email client. These days, I easily receive over 100 e-mails in one 24 hour period (Mahara is an international project, I get a lot of e-mail over night). This amount of mail can overwhelm you easily - I previously found myself trying to get on top of it until the afternoon. Checking it at certain points of the day encourages me to try and get through it quicker, which gives me more incentive to pull out procmail to deal with "noise".
• I've stopped listening to music while coding. Some people say that classical music can actually help with getting into the zone, but I don't own such music. It's not like I'm bored and need something to listen to, and anyway listening to music at work is just an attempt to drown out other interruptive noise with something my brain can more easily tune out.

After that, it's a lot easier for me to begin the day thinking "Import", and actually get something done as a result.

So far, I've been at it four days, and I think the results are already beginning to speak for themselves. Looking back at my perfectly kept timesheets (it's funny how when you don't have to rush to get home at the end of the day, it's easier to fill them out), I see that I've managed a consistent 6 hours a day on my primary task: LEAP Import work for Mahara. The rest of the time has been spent on e-mail, adding more documentation to the Mahara wiki, and smaller jobs that come about day-to-day when working on a FOSS project.

I've also eaten healthier - I've cooked every night this week, and spent less money - having been able to make and eat my own lunch. The best part is, I worked less hours yet got more done. I even had time to write a decent currymail this week. Now ain't that a fine improvement to my life.

[1]	It's funny how CPUs model the human brain, I wonder if we would have come up with a different way of running programs if our minds worked differently.

Like this post? Subscribe to my RSS feed and follow me on twitter to hear about new posts early.

Want to share this post? Tweet