mySQL DBA

Looks to be a really nice blog on using MySQL in real world applications.

One of very interesting techniques Innodb uses is technique called "doublewrite" It means Innodb will write data twice when it performs table space writes - writes to log files are done only once.

So why doublewrite is needed ? It is needed to archive data safety in case of partial page writes. Innodb does not log full pages to the log files, but uses what is called "physiological" logging which means log records contain page number for the operation as well as operation data (ie update the row) and log sequence information. Such logging structure is geat as it require less data to be written to the log, however it requires pages to be internally consistent. It does not matter which page version it is - it could be "current" version in which case Innodb will skip page upate operation or "former" in which case Innodb will perform update. If page is inconsistent recovery can't proceed.

Now …

The first beta version of XAMPP for Linux is available for public download in our XAMPP BETA area. New in this beta version are: Apache 2.2.3, MySQL 5.0.22, PHP 4.4.3 and phpMyAdmin 2.8.2.

Beta releases are only for testing purposes. We would very much appreciate it if you could test this release and let us know if you run into any problems.

Follow me to the XAMPP BETA area

I'm working on an article with Peter G. about MySQL partitioning that should be on dev.mysql.com next week sometime (assuming that I pull my finger out and get my bits finished soon). I started working on an example that partitions based on years and quarters and very quickly realised that if you use QUARTER() as part of of the partitioning expression, then that table is going to have all the high-speed performance of the average garden slug.I won't give away everything here (otherwise you'd have no reason to go read the article), but I will say that using TO_DAYS() instead of QUARTER() is part of the solution, and that I came up with a utility of sorts to help in creating such tables. Here's the stored procedure: DELIMITER |CREATE PROCEDURE qdays ( IN y1 INT, IN q1 INT, IN y2 INT, IN q2 INT ) BEGIN DECLARE y INT DEFAULT 2005; DECLARE q INT DEFAULT 0; DECLARE mn INT DEFAULT 1; DECLARE ms CHAR(2) DEFAULT ''; DECLARE s VARCHAR(50) DEFAULT ''; DROP …

I'm working on an article with Peter G. about MySQL partitioning that should be on dev.mysql.com next week sometime (assuming that I pull my finger out and get my bits finished soon). I started working on an example that partitions based on years and quarters and very quickly realised that if you use QUARTER() as part of of the partitioning expression, then that table is going to have all the high-speed performance of the average garden slug.I won't give away everything here (otherwise you'd have no reason to go read the article), but I will say that using TO_DAYS() instead of QUARTER() is part of the solution, and that I came up with a utility of sorts to help in creating such tables. Here's the stored procedure: DELIMITER |CREATE PROCEDURE qdays ( IN y1 INT, IN q1 INT, IN y2 INT, IN q2 INT ) BEGIN DECLARE y INT DEFAULT 2005; DECLARE q INT DEFAULT 0; DECLARE mn INT DEFAULT 1; DECLARE ms CHAR(2) DEFAULT ''; DECLARE s VARCHAR(50) DEFAULT ''; DROP …

Flickr: macplusg3’s photos tagged with beijing

There’s some photos I’ve taken around Beijing up there. Will be posting more over the next few days (and until I leave - on the 16th). Enjoy.

A "little-known way," I claim, and yet it happens all the time -- precisely because it's little-known. Experts will quickly recognize where I'm going to go with this article, but I hope many others in my audience will understand deadlocks more deeply after reading it. I'll use MySQL and InnoDB for illustration purposes, but the scenario this article describes (dramatic music, please!) could happen to you, too! And probably will someday, unless you're one of the elite few (ok, enough drama) who know how to avoid it.

In this article I'll briefly introduce deadlocks, give an example of one that happened at my employer recently, analyze and explain it, and then disclose the secret way to avoid cause such deadlocks. Then I'll show you how to reproduce the deadlock and dive into the gory details of what goes on internally with InnoDB. I'll also demonstrate how …

... and most important - good use of SQL!

That doesn't sound surprising, I guess.

Recently, Giuseppe Maxia provided a good hint (An ugly query should be a warning bell) how to get an indicator that something is wrong with your query. I would like to add a hint to help you find out that something is wrong with the way you use your database inside your application. Just ask yourself the following question:

How much of my client side code is there just to get the data that I got from the database into the form that I need in my application?

Your answer should be: very little.

In most cases, SQL provides the capabilities to get out of the database exactly what you need. Needless to say that it's most …

Stephan Uhrenbacher is the Managing Director of Qype GmbH, the company behind the Qype.com web site, which allows users to recommend places in Germany and has developed into one of Germany?s most interesting web 2.0 applications.