I have been working on large databases for the last few years. This includes areas such as story archives, data warehouses, high-end OLTP databases, all with quite a large amount of data. The smaller ones are usuall in the range of 10GB or more.

Well, I came across a situation this week that was a bit of a surprise. A business had somehow lost all the data out of one table in the database. The first surprise was that it appeared to happen at 04:00am according to the binlogs. However, nobody logs on then, there was no evidence of intrusion (behind a secure firewall as well), and their intranet showed no activity for that time either? So where the DELETE FROM tablename; command came from is still a bit of a mystery.

The second surprise was trying to fix up the problem. I was looking at recovering from backup and running a point-in-time-recovery process with the binlogs to get it back to the updated state. Now, there were only 2 tables …

Brian recently posted an article comparing UUID and auto_increment primary keys, basically advertising to use UUID instead of primary keys. I wanted to clarify this a bit as I've seen it being problems in so many cases.

First lets look at the benchmark - we do not have full schema specified in the article itself so it results are not absolutely clear but we already can have certain conclusions.

Data size is very small. What is the biggest problem with UUID ? It is the fact inserts are going in random locations in index tree which will require a lot of IO in case of index tree not fitting into memory. This is not simply the case of 32 bytes vs 4 bytes for key value - if you would use integer key and insert data in random order you would have the same problems.
In fact if you store UUID in binary form you can bring it down to 16 bytes so …

With all the great sessions that Jay and crew have lined up at the upcoming MySQL Users Conference, you might have a hard time creating a personal schedule from all that’s available. So I just wrote a new dev zone article that is a DBA’s guide to the MySQL User’s Conference to help. In the article, I try and highlight the things DBA’s care about - performance management and key database administration duties - and then link each area to the sessions you’ll want to make sure and attend.

Anyone interested in writing a Developer’s Guide to the UC…?

I get a lot of mail and I prefer to store it for long time if not forever. With modern hard disk sizes it should not be problem at all, but because of how mailing programs are written it causes a lot of problems.

I’ve tried a lot of programs - Kmail, Evolution, Thunderbird on Linux, Outlook and The Bat! on Windows and they all seems to have the same problem - it is some kind of assumed mail messages, or at least some portion of them will fit in memory.

At this point for example I got tired of Thunderbird handling my 1GB inbox (In fact my Inbox holds less than 1000 of emails rests are “Deleted” but Thunderbird still keeps it in the same file) so I decided to move some 70.000 of messages to specially created “archive”
Folder. This makes Thunderbird to consume about 2GB of memory and I’m not sure if it will be able to complete operation at all as it is already running low on virtual memory.

This is not only …

As an exercise I wrote a proof of concept of a proxy for MySQL. It isn't using any code from the MySQL Server, only some headers files. I only used the internals document and implemented the server-side protocol of it.

After having the basic stuff running:

• socket handling
• mysql wire-protocol
• len-encoding of some fields
• field-types
• the life-cycle of a connection

... I could finally use the mysql command line client to connect to my dummy-server. The mysql-clients sends a few statements at startup:

select @@version_comment LIMIT 1;
select USER();

Detecting slow queries

Proxying requests is pretty simple, just speak the server protocol one side and the client on the other just forward the packets as the come in. Pretty straight-forward.

As extension of the initial task we want to find out which queries are causing a …

A little less than a week ago, I opened a zoomerang survey about database stored procedures. In this post, I'm presenting the results.

In just a few days, the survey was visited 232 times, and no less than 155 people completed the survey! (There was a very small (2) number of people that answered some, but not all the items, and these are excluded from the results proper.)

I didn't really know what to expect, but I did not expect as much as this! So, I'm quite pleased, and I want to thank everybody that took the time to complete the survey. Thank You very, very much, I appreciate your efforts a lot!

Before we Begin
Before I present the results, I want to explain a few things about the survey.

First of all, I want to stress that the results I am about to present are not to be generalized. There is no way to discover to what …

Every database that houses user generated content is a master in at least 1 pair within my environment. To make sure that id's don't collide we engineered around the auto-increment functionality or lack there of in mySQL-4.1. For the most part the auto-increment is externally generated.



mySQL 5.0 introduces a cool feature that increments auto increments different per mysql instance.

In my my.cnf file I define this:

auto_increment_increment = 5
auto_increment_offset = 1


This means that increments will be produced by 5 so for example starting from 1
1,6,11,16, etc.

On another server I define

auto_increment_increment = 5
auto_increment_offset = 2

This functionality is cool, but there are limitations:

I can only have 5 masters to generate this sequence before I need to do some …

It is possible to use an active - active shared-disk cluster in MySQL in some cases. For doing this you have to fulfill the following requirements:

<ul> <li>Works with MyISAM tables only.</li> <li>POSIX-locking compliant cluster file system on the device (such as OCFS2 or GFS).</li> <li>External locking must be enabled.</li> <li>The MySQL query cache must be turned off.</li> <li>The MySQL delay key write must be turned off.</li> <li>OS where file locking is supported in MySQL.</li> </ul>

It is possible to use an active - active shared-disk cluster in MySQL in some cases. For doing this you have to fulfil the following requirements:

• Works with MyISAM tables only.
• POSIX-locking compliant cluster file system on the device (such as OCFS2 or GFS).
• External locking must be enabled.
• The MySQL query cache must be turned off.
• The MySQL delay key write must be turned off.
• OS where file locking is supported in MySQL.

Interested? To read more, follow the link: MySQL active - active Cluster (PDF 157 kbyte).

Related pages

• A very interesting comment from Peter Zaitsev you MUST read. He comments, why this set up you should NOT use: …

Jonas just switched me on to Gnome SSH Tunnel Manager - a simple GNOME app that stores a list of SSH tunnels you want and can automatically start and stop them.

Totally useful for those who travel (hrrm.. fair few MySQLers there) and/or always have SSH tunnels to places (hrrm… MySQLers there too).

There’s a debian package up there (and you can build one easily) but it’s not yet in the Ubuntu archive… maybe for the next release. But works fine on edgy for me!