Check out how Linden Labs, creators of the popular game Second Life, upgraded their MySQL database. The MySQL they use? Straight out of Debian! Of course, now, they’re running with the Percona patchset, against MySQL 5.0.84. Definitely a good read.

Its good to see Lars post about contributing to the MySQL replication & backup codebase. It sounds like the replication & backup team have decided that mentoring is the way to go – you get a “coach developer” if the idea is accepted. I like this very much, and sincerely hope it spreads to the rest of the server; it will help decentralise …

We have a new users’s group on LinkedIn for anyone that wants to keep up to date with discussions and wants to increase their network profile on the site. Join now: http://www.linkedin.com/groups?gid=2655021

Today Google announced that instead of "carrying a USB device" you can now store your files "in the cloud" up to 1GB "for free". They claim this network service is more convenient than a USB drive you carry, which may be true, but at what cost to the environment?

A USB device uses absolutely no electricity when it isn't plugged in. When not being accessed by active programs they use almost no power at all, even when plugged in. Contrast this with spinning hard disks in the cloud. Spinning those disks in a data center is expensive, and I contend that it is an incredible waste of resources to use the cloud for convenience over a USB based device. You also don't get served ads and are at much lower risk of having your files potentially accessed by third party when using a USB device. You can reuse a USB disk over and over again, …

The explain command in MySQL is very useful. Alas, it has its limits including some related to the use of the LIMIT clause. This is even more reason to do more than EXPLAIN when testing a query.

The value of rows in EXPLAIN output ignores the impact of LIMIT. I know a few people who were mislead by this. This behavior isn't documented.

For the examples below there is a primary key on pk, the table t1 has 1000 rows, and the range for pk is from 1 to 1000. Note that the value of rows in the explain output is 99 in all cases while the queries scan the first 1, 10 and 50 rows from the PRIMARY index.

…

If you have recently downloaded or checked out a new version of Kontrollbase and find that you need to upgrade the schema from a previous version then you will probably find this post useful. If you don’t know if you need to upgrade then you probably do, so read along for a bit just to [...]

Being a MySQL DBA , one faces a common issue in replication environment -> Disk space issue on master, since the number of binary logs have increased. Now, one of the solution to this would be using expire_logs_days parameter in your mysql config file. But what if, the slave is lagging by few hours or if the slave is broken since few days and the binary logs are removed due to the parameter set. Whenever the salve comes up, it will go bonkers, knowing that the binary log where it last stopped no more exists.
I faced this issue a couple of times until I decided to automate it using a script. Herewith I am attaching the URL to my python script which can run regularly in cron. Features :

• Checks the slaves connected to the master (I have limit it to 3 for now.)
• Checks the last binary log file which is being used by the slave.
• …

One of the best kept secrets in the MySQL world are the terrific (and free) webinars that are available.  In fact we have just such a webinar coming up this Thursday.  This one is covers using MySQL embedded with Windows and is presented by Mike Frank.  You can find out more and register here. 

It’s free and I know you can spare an hour so what are you waiting for?

From Stack Overflow:

Say you want to display the latest visitors on a users profile page.

How would you structure this?

Perhaps a table called uservisitors:

And how would you select this with MySQL without any duplicates?

What I mean is if user 1 visits user 2’s profile, then 5 minutes later visits it again, I don’t want to show both entries: only the latest.

There are two approaches to this.

First one would be just aggregating the visits, finding the max time and ordering on it. Something like this:

SELECT  visitorid, MAX(time) AS lastvisit
FROM    uservisitors …

I just submitted an abstract for the MySQL Conference 2010. What do I want to talk about? Workarounds.

Domas likes to do tricks with GDB. Sheeri likes symlinking log files to /dev/null when not in use. Peter likes to do what he calls a delayed join, and I like to use IN() lists.

What's your favorite workaround? Are there any that you've seen people use in production that no longer work (or could …

I just submitted an abstract for the MySQL Conference 2010. What do I want to talk about? Workarounds.

Domas likes to do tricks with GDB. Sheeri likes symlinking log files to /dev/null when not in use. Peter likes to do what he calls a delayed join, and I like to use IN() lists.

What's your favorite workaround? Are there any that you've seen people use in production that no longer work (or could …