About a month ago I blogged about the debug console I was adding to drizzled. I finished this work up and it’s now in the trunk and latest release. This is implemented using the Client and Listen plugin points (which are heavily modified versions of MySQL’s Protocol class), and can be enabled using the ‘–console-enable’. For example:

hades> drizzled --datadir=/Users/eday/drizzle.data --console-enable
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
090928 15:22:07  InnoDB: highest supported file format is Barracuda.
090928 15:22:07 InnoDB Plugin 1.0.3 started; log sequence number 46409
Listening on :::4427
Listening on 0.0.0.0:4427
./drizzled/drizzled: Forcing close of thread 0  user: '(null)'
./drizzled/drizzled: ready for connections.
Version: '2009.09.1144' Source distribution (trunk)
drizzled> show tables in information_schema; …

One of the first questions to answer when running databases on SSDs is what B-tree block size to use. There are a number of factors that affect this decision:

• The type of workload
• I/O time to read and write the block size
• The size of the cache

That’s a lot of variables to consider. For this blog post we assume a fairly common OLTP scenario – a database that’s dominated by random point queries. We will also sidestep some of the more subtle caching effects by treating the caching algorithm as perfectly optimal, and assuming the cost of lookup in RAM is insignificant.

Even with these restrictions it isn’t immediately obvious what is the optimal block size. Before discussing SSDs, let’s quickly address this problem on rotational drives. If we benchmark the number of IOPS for different block sizes on a typical rotation drive we get the following graph:

There are two …

Sometimes problems manifest inside of MySQL but the MySQL server is really not the problem. A good example is how MySQL uses reverse DNS lookups for authentication.

The Problem

You are alerted to a problem with a MySQL server either by Nagios or whatever… You log into the server and everything seems to be working correctly until you run “show full processlist”. The majority of your connections are in an “Unauthenticated” state and the rest are in some other state, probably “Sleeping”. You speak with the lead developer and they state that there have not been any code changes for 1 month. It’s an odd problem, when you see it; however, the solution is not that obscure when you think about how MySQL uses DNS.

The Solution

When you restart MySQL with “skip-name-resolve” enabled you …

Thanks to everyone who came out to the San Francisco PHP and MySQL meetup! Also, thanks to Michael for organizing such a great event, and Percona for sponsoring the food. I put the slides from the talk up on my wiki for reference or in case you missed it. I believe that there will be a video up at some point as well. While down there I also had a chance to stop by Digg and talked to them about Gearman (they’ve been using it for a while). It was interesting to see how they were using it in a large scale deployment. I was able to get some valuable feedback to future development, and a cool t-shirt. :) Thanks Digg!

I am speaking at Enterprise LAMP Summit 2009 (and also should be speaking at the Camp the next day, but I see the schedule isn’t quite updated yet). My talk at the Summit will not be on the future landscape of MySQL — that’s a mistake. My talk’s title isn’t finalized yet.

This conference is the first weekend in November, in the Nashville area. I hope to see you there!

Related posts:

1. Speaking at CPOSC 2009

   I

2. …

Why a New Utility?

A couple of months back, Tim Procter, Sheeri Cabral and I were discussing about how best to diagnose a MySQL server and/or tune its performance, automating the process as much as possible. The Performance Advisors from MySQL Enterprise do this, but most of our customers don’t have a subscription and Pythian’s collective experience is not necessary reflected by its rules.

In our daily work, we have used Major Heyden’s MySQL Tuner, Mark Leith’s Statpack and our own tools to review a MySQL server configuration parameters. However, all of these tools had limitations in regards of what we wanted to achieve. Our major …

Installing Lighttpd With PHP5 And MySQL Support On Fedora 11

Lighttpd is a secure, fast, standards-compliant web server designed for speed-critical environments. This tutorial shows how you can install Lighttpd on a Fedora 11 server with PHP5 support (through FastCGI) and MySQL support.

I want to highlight the importance of reviewing mysql’s initial set of accounts.
Say you have a mysql on abc.def.ghi.jkl running on port 3306 anonymous account with privileges without a password, then:
1. mysql (if issued on localhost)
2. mysql -h abc.def.ghi.jkl
3. mysql -u ” -h abc.def.ghi.jkl
4. mysql -u ” -h abc.def.ghi.jkl -P 3306
5. mysql -u user_which_does_not_exist -h abc.def.ghi.jkl

will all manage to get into mysql given the way mysql authenticates users is against your username and client host from where you are connecting.

This verification is done versus the following columns in the mysql.user table, i.e., User,Host and Password columns.
An entry in the mysql.user table with the following values User=”, Host=’%’ will accept ANY user connecting from ANYWHERE in the world, thus disabling ANY security. Hence the reason for this blog post highlighting the importance …

1. Brown Cloud Marketing -- advertorial "interviewing" GM of a company offering "DNS in the cloud". This might be a worthwhile service, but the way he markets it (by saying open source is "freeware" and the market leader is "legacy") reveals a rich vein of bozo. Freeware legacy DNS is the internet's dirty little secret (actually, it's the reason we have a functioning DNS), Nominum software was written 100 percent from the ground up, and by having software with source code that is not open for everybody to look at, it is inherently more secure. (security through obscurity is equating clothing with being naked yet blind). The Internet kindly did the poor man's homework: screenshot of a cross-site scripting …

On Thursday it became public that Carlo Piana has joined the Oracle team as co-counsel in the EU investigation on Oracle acquiring Sun. The short introduction of Mr Piana is that he has been (still is?) legal counsel for the Free Software Foundation Europe, in particular representing the Samba project in the Microsoft anti-trust case (which we/he won!).

read more