The latest from the MySQL community

A few days ago the mysql proxy tool was released (link) and it does exactly what the name would suggest. But it does boast a few features that have changed the development of the test creation tool.

• It has a Lua interpreter that allows user written scripts or modules to be run by the proxy. These scripts can intercept queries and responses between the client and the MySQL server, and also manipulate them in whatever way necessary. There are examples on the wiki of how to use the scripts.
• Instead of writing an entirely new command line client, we can use the standard mysql client and focus on the scripts that will intercept the client-server communication and translate that into test cases.

For these reasons the test creation tool will not be written in C/C++ but instead as Lua modules that the proxy will run. This will hopefully …

Ever wondered what your PHP application and MySQL actually do? An experimental mysqlnd branch will give you full access to the network communication stream. Using a custom PHP stream filter you can then intercept the communication ... but let's start at the beginning:

When talking about mysqlnd - the mysql native driver for PHP - we always mention the fact it's native in a way that we're, when possible, using PHP infrastructure. The most common example here is the memory management. By directly using PHP's memory we can avoid unnecessary copies of data from the MySQL Client Library's memory into PHP memory.

<?php
$mysqli = mysqli_connect("localhost", "root", "", "test");
$stream = mysqli_conn_to_stream($mysqli);
…

From a performance standpoint, sometimes even tightly tuned queries can cause a performance drag. The common problem here is not one of actual query performance, rather it is a function of:

- the velocity and frequency that a query is submiited for execution
- the total execution time of the aggregated executions

This could be symptomatic of an application not properly configured for caching (see Darren Oldag's blog on this!), or just overall poor design. Regardless of why, when or how we all know it happens. The trouble with this particular problem is that when a query is tuned, or very simple, it is usually not suspect for being a resource hog. Pulling aggregates for number of execs and total exec time for specific queries is a little tricky and labor intensive with the Slow Query Log, and not really a good …

One of the biggest problems I faced as a field DBA was defending my production databases against poorly performing SQL code. Talking to folks at the MySQL UC, during customer visits and trolling the MySQL forums/blogs confirms that this is a common problem and even more challenging when a performance problem pops up during times when no code promotions have taken place.

Traditional/popular way of finding bad SQL code
For MySQL DBAs the challenge to identify and isolate resource intensive SQL code is really two-fold; job #1 is to find the offending code (after that the chore becomes fixing it so it performs, but that is a topic for another post). Most MySQL DBAs have used the MySQL Slow Query Log ("SQ Log") at some point to help with job #1. For those not familiar, the SQ Log is enabled by starting MySQL with --log-slow-queries[=file_name] option. Once enabled the SQ Log captures queries that took longer than X seconds (user …

I’ve been accepted to speak at the upcoming 2008 OSCON Conference in Portland Oregon July 21-25 2008, where I’ll be speaking with Giuseppe Maxia on MySQL Proxy. I haven’t seen a schedule yet, so I’m interested if there are any other presenters on MySQL topics!

In my opinion, the last MySQL Meetup group in London was a really good event. We met Marten Mickos, former CEO of MySQL and now Senior Vice President of Sun, David Axmark, founder of MySQL and Simon Phipps, Chief Open Source Officer at Sun.

The main questions to the guests were pretty expected: is it going to change anything in the licensing, the deployment and the platforms of MySQL? The single answer is a "No, nothing is going to change". No change in the licensing (Simon Phipps has clearly stated it), no changes in the Community and Enterprise editions (at least not in the near future) and no changes in the development for Linux, Windows, Solaris and other OSes. There will be more opportunities, instead, of spreading the database in environments that are not the common target for MySQL at the moment. We are definitely not going to inflate the server with a large set of barely …

There is a big event for the London Meetup Group coming up next week. We will all meet on Wednesday 5 March @ 7pm
The location: The Lamb
4 Lambs Conduit St
Bloomsbury
London
WC1N 3LZ

We will have a very special guest: Marten Mickos, CEO of MySQL (now in his new role, due to the Sun acquisition). Marten will join us and he will be happy to answer to questions, to share opinions and spend some good time with MySQL Brits users in front of a nice drink (no surprise which one will be the best seller :) ).

From a technical point of you, I have prepared a short presentation on MySQL Proxy, with a basic intro on the project and with some ideas on how to use it and what to expect from a production version. The slides will be uploaded after the venue.

As usual, I am open to suggestions and topics to …

With 130 entries in the “MySQL” category and no MySQL-related subcategories, my blog had become impossible to search and navigate easily.

And thus I created a number of new categories for the MySQL entries within my blog. They’re listed in the left navigation bar, below the months, as well as below:

• MySQL Server, MySQL Cluster, Falcon
• Connectors: PHP, Ruby on Rails
…

Giuseppe Maxia teased me last week to see whether I could decipher the notice on the Italian web site Punto Informatico about “MySQL University, lezione gratuita su Lua“.

It’s about Giuseppe teaching MySQL University attendees (for free as in both beer and speech) on the Lua scripting language. The MySQL reason to learn Lua is to do scripting in MySQL Proxy. Don’t worry, the MySQL University session is in English, not Italian, and Giuseppe has deciphered the Italian himself in his blog.

You can attend MySQL University virtually with slides in PDF and Wiki, audio …

A while ago, I had a discussion with Stewart Smith, Vinay Joosery, Monty Taylor and a number of other MySQLers who know much more about MySQL Cluster than I do. The result is a model for using MySQL Proxy to offload MySQL Cluster from doing Table Scans, without touching the application.

The discussion started from me asking Stewart about the largest road block for expanding the number of use cases for MySQL Cluster. “Oh, that would probably be doing JOINs and other SELECTs requiring the scanning of large parts of the database”, he replied. “There, other storage engines are faster, such as MyISAM and InnoDB.”

In a very simple view, the application talks SQL with MySQL Cluster, and gets responses.

Stewart’s insight can be refined into the first simplistic diagram by adding the recognition that “SQL” can consist of

1. UPDATE, INSERT, DELETE statements (very light, usually …