Ian Gilfillan

Last month we started looking at transactions in MySQL, in particular with InnoDB tables. This month we look at the four transaction isolation levels, again with InnoDB tables, and see how they affect the usual locking transactional behavior. Transaction Isolation Levels A transaction isolation level sets the default transactional behaviour. Our examples last month all […]

What is a transaction? If you are asking this question, you are probably used to website databases, where most often it does not matter in which order you run transactions, and if one query fails, it has no impact on others. If you are updating some sort of content, often you will not care when […]

Time for an update In 2001, I wrote an article entitled Optimizing MySQL: Hardware and the Mysqld Variables. That was almost three years ago, when MySQL 4 was but a twinkling in Monty Widenius’ eye. With MySQL 4 now stable, and MySQL 4.1 and 5.0 already out in alpha, it is time for an update […]

An introduction to replication Recently, while having the knots pounded out of my body during a particularly painful shiatsu lesson, I reflected on what put them there in the first place. Yes, ‘the database’ was once more to blame. A busy database I work with saw one of its tables jump from 3GB to 7GB […]

Looking back Just under a year ago, I wrote an article entitled MySQL–Yesterday’s Toy, Tomorrow’s Contender. This article looked at the MySQL’s roadmap as it was then, and asserted that, looking at the upcoming features, MySQL was likely to become a serious contender to the established proprietary databases. I had planned for this month’s column […]

One quiet Monday morning, I was easing into the week, when some of the staff started reporting ‘strange errors’ in their application (a content management system running on MySQL). The machine had been having problems with what looked like overheating, so I quickly checked all the tables, and finding some corruption, repaired the tables. I […]

MySQL newbies often have problems with the MySQL access control system. But once you get used to it, I think you will find it flexible and easy to use, so this month we’re going to examine how to get started with MySQL permissioning. The USER table MySQL access is controlled by the mysql database. Let’s […]

Ah, the wonderful feeling of being hauled out of the spa/candlelit dinner/Quake game to be told in panicked tones that there is a ‘database error’. You rattle off the usual suspects, determining that their keyboard is actually plugged in, monitor on, monitor’s brightness turned up (yes, it has happened to me), and that they are […]

With it being close to the end of the year, and things winding down for many of my colleagues (not me!), I thought I would have some fun, and write an article entitled PostgreSQL vs MySQL: Which is better? I am expecting the article to be read by PostgreSQL fanatics, MySQL fanatics, newbies wanting an […]

A typical scenario Boss: Our new website is crawling! How can it be, we have four state-of-the-art web servers – what’s the problem? You: Well, the web servers are fine – it’s the database server that’s struggling. Boss: What? You told me this MySQL thing was fast, that we didn’t need Oracle, and now you […]

Last month we looked at the HEAP table type, a table type which runs entirely in memory. This month we look at setting up the InnoDB table type, the type of most interest to serious users. The standard MyISAM table type is ideal for website use, where there are many reads in comparison to writes, […]

The MySQL table types We all know accessing data in RAM is a lot quicker than accessing data on disk. So, with this in mind, wouldn’t it make sense for MySQL to store as much data as possible in memory? The answer is of course yes, and MySQL can in a number of ways. Query […]