Pythian Group

First off, let me wish you all a Happy 2009, and my apologies for being a bit slack with blogging in the last two months of 2008. It’s been a bunch of busy weeks, but I’m fully expecting to remedy that in this year.

Second, let me welcome you to this series on refactoring MySQL applications. I will cover best practices and useful tips, as well as show working examples of potential loopholes and solutions.

So, you are a DBA or a developer, and you’re faced with a problem. Your years-old application (perhaps you inherited it from a former co-worker) is now starting to keel over, and your choice of responses is limited.

Let’s start by saying that there are other ways to reach performance, but in some cases refactoring may be the only way that is possible to pursue, for one reason or another. Let’s take the refactoring way, focusing on SQL rather than applications, as there’s a lot that can be done on this side.

But first, why refactoring? Refactoring normally sits between parameter-tuning and throwing more hardware at the problem, and can be a very cost-effective way of solving performance or scalability issues.

So let’s start with a good point about composite indexes that seems to be forgotten sometimes. Given the following data . . . 

Read the rest of this entry . . .

I began to write a post on InnoDB transactions, but there was so much background material that I decided first to write a post introducing transactions, and then one on how InnoDB implements them.  If there is good response from these two posts, I will continue with other posts on the major storage engines and their transactional characteristics.

When reading through literature about RDBMSs (Relational Database Management Systems), you will see the terms transaction(s), and ACID-compliance.

A transaction is a sequence of statements that are executed on a RDBMS. For a transaction to be completed and database changes made permanent, a transaction has to be completed in its entirety. If a transaction is not completed for any reason, it is rolled back to the original state of  the database before the transaction began execution. The characteristics that define a transaction are called ACID.  ACID stands for: Atomicity, Consistency, Isolation, and Durability.

According to Wikipedia’s article on ACID, it is “a set of properties that guarantee that database transactions are processed reliably.” It is useful to understand the different parts of ACID, so here they are briefly:

Atomicity

The ability of the database to guarantee that either every part of the transaction is performed or none is performed.

Consistency

The database remaining in a consistent state before the start of a transaction and after the transaction is finished.

Isolation

The ability of the database to make operations in a transaction appear isolated from other concurrent operations.

Durability

The guarantee that once the user has been notified of success, the transaction will persist through all conditions, including system failure.  Durability is frequently accomplished through writing all transactions to a log file.  This log file can be “replayed” in the event of a system failure.  It is only after the transaction is written to this log file that the transaction can be considered safe.

So now you have some background.  Next time, we will take a look at how the InnoDB storage engine implements transactions.