Although MyISAM has been the default storage engine for MySQL but its soon going to change with the release of MySQL server 5.5. Not only that, more and more people are shifting over to the Innodb storage engine and the reasons for that is the tremendous benefits, not only in terms of performance, concurrency, ACID-transactions, foreign key constraints, but also because of the way it helps out the DBA with hot-backups support, automatic crash recovery and avoiding data inconsistencies which can prove to be a pain with MyISAM. In this article I try to hammer out the reasons why you should move on to using Innodb instead of MyISAM.

My editor Andy Oram recently sent me an ACM article on BASE, a technique for improving scalability by being willing to give up some other properties of traditional transactional systems.

It’s a really good read. In many ways it is the same religion everyone who’s successfully scaled a system Really Really Big has advocated. But this is different: it’s a very clear article, with a great writing style that really cuts out the fat and teaches the principles without being specific to any environment or sounding egotistical.

He mentions a lot of current thinking in the field, including the CAP principle, which Robert Hodges of Continuent first turned me onto a

  [Read more...]

Yup, the presentation slides as well as the scripts are now available online on the conference website.

The stuff you will find in there:
Information_schema.pdfDiagram of the information schema
I_S_VC_Slides.pdfSlides for the UC presentation, "Grand Tour of the Information Schema"
I_S_INDEXES2.sqlscript, returns one row for each index (rollup of information_schema.STATISTICS)
I_S_REDUNDANT_INDEXES2.sqlscript, lists all redundant indexes. Redundancy rules:

• two indexes with the same columns, type and uniqueness are interchangeable. The one with the largest index name is listed as redundant


• if there is a unique index and a non unique index with the same columns and type exists, the non-unique one is considered redundant

  [Read more...]

In the first article in this series on archiving strategies for online transaction processing (OLTP) database servers, I covered some basics: why to archive, and what to consider when gathering requirements for the archived data itself. This article is more technical. I want to help you understand how to choose which rows are archivable, and how to deal with complex data relationships and dependencies. In that context, I'll also discuss a few concrete archiving strategies, their strengths and shortcomings, and how they can satisfy your requirements, especially requirements for data consistency, which as you will see is one of the most difficult problems in archiving.