I want to answer a simple question - If a query takes X milliseconds, how much of it is spent in the storage engine, and how much in the MySQL server? Why do I think is important? Well, since I am working on MySQL performance, I want to be able to place my bets on where to spend my time optimizing MySQL. Lets take an example. If a query takes 50ms, and I am able to figure out that 40ms is in the mysql server and the remaining 10ms is in the storage engine, the first place I would want to take a look at optimizing the MySQL server. This also tells me that it does not matter what the storage engine is, I am being limited by the server. Similarly if I find the storage engine taking up most of the time, I can explore alternate storage engines. I know all you mysql experts will tell me that much of the attribution (of time) has got to do with the capability of the storage engine, what features it supports, etc. and you cannot really do the …

I want to answer a simple question - If a query takes X milliseconds, how much of it is spent in the storage engine, and how much in the MySQL server? Why do I think is important? Well, since I am working on MySQL performance, I want to be able to place my bets on where to spend my time optimizing MySQL. Lets take an example. If a query takes 50ms, and I am able to figure out that 40ms is in the mysql server and the remaining 10ms is in the storage engine, the first place I would want to take a look at optimizing the MySQL server. This also tells me that it does not matter what the storage engine is, I am being limited by the server. Similarly if I find the storage engine taking up most of the time, I can explore alternate storage engines. I know all you mysql experts will tell me that much of the attribution (of time) has got to do with the capability of the storage engine, what features it supports, etc. and you cannot really do the …

The next generation of MySQL DBA exams are going to be performance based tests (PBT) or 'Hands on'. A candidate will have a series of tasks to be performed, a virtual server, and a time limit. The tasks are going to be based on the usual tasks performed by a Database Administrator. For instance, a candidate will get a list of user accounts that need to be added, updated, or deleted and will be graded on how well they meet the objectives.

I have already asked my fellow MySQL employees to review the tasks but I would like to have some input from the public. Following the Open Source dictum of the more eyeballs looking at a problem will provide better products, I would like to get some of you to look at the proposed tasks and give me your comments.

In the past there have been complaints on questions being too syntax specific, too picky, and asking for too obscure knowledge. This is a chance to help make sure that the exam …

I was running an import script today taken from a mysqldump from another user, when I saw an error that looked like this:

ERROR 1005 (HY000) at line 123: Cant create table
 (errno: 150)

This is caused by a mysqldump or export process exporting tables in alphabetical order and not in the order in which they rely on one another. My tables had foreign keys which fail on import if the other table doesn't exist when you create the table with the key. In this case I was only importing six or eight tables so I simply opened the script in a text editor and re-ordered the import blocks. On a bigger scale a more technical solution might be required!

Lorna is an independent web development consultant, writer and trainer, open source project lead and community evangelist. This post was originally published at LornaJane

export MYISAM_BACKUP_NO_INDEX=1

backup database employees to 'emp2.bkp';
+-----------+
| backup_id | …

Things have been quiet from MySQL over the past month or so, but today's Wall Street Journal has an awesome interview with Sun's Marten Mickos, perhaps the most quotable technology executive on the planet.

I really like how he talks through community (how to provide incentives, what to ...

I'm back from my week of vacation and am ready to pick up where I left off with the interviews I conducted at the Red Hat Summit/FUDCon last month.  After today's, I will have three left: Dennis Gilmore, Max Spevack and Chander Kant of Zmanda.

The Honourable James Zemlin

On the first day of the Summit I was able to grab my buddy Jim Zemlin, the Executive Director of the Linux Foundation for a spirited discussion about the Foundation, Linux and a bunch more. 

My interview with Jim (15:55)  Listen ( …

My previous post on calculating percentiles with MySQL generated some comments and good discussion. In particular, I got some very interesting comments from Vladimir.

Basically, Vladimir was doubtful whether the GROUP_CONCAT() solution would be optimal in comparison to a JOIN. His proposal is to solve it like this:

SELECT   SUM(g1.r) sr
,        g2.length l
,        SUM(g1.r)/(SELECT COUNT(*) FROM film) p
FROM    (SELECT COUNT(*) r, length FROM film GROUP BY length) g1
JOIN    (SELECT COUNT(*) r, length FROM film GROUP BY length) g2
ON       g1.length < g2.length
GROUP BY g2.length
HAVING p > 0.9
ORDER BY p
LIMIT 1

First, this query sets up two identical subqueries in …

Remember the first test of online backup? I tested the new feature, which was performing quite well, compared to mysqldump. OK. Get ready for a surprise.
I tested the native MyISAM driver from the mysql-6.0-backup tree, and I compared the results with the normal backup.

It's finished. After the usual two-week beta period, we can announce the new "final" XAMPP version available for public download.

In this version we updated: Apache (2.2.9), MySQL (5.0.51b), PHP (5.2.6), phpMyAdmin (2.11.7), mod_perl (2.0.4), OpenSSL (0.9.8h), and eAccelerator (0.9.5.3).

Get the downloads and more details on the specific platforms XAMPP project page.