In case you have not already noticed, Workbench 5.2 alpha / preview release of MySQL's premier development and design tool, has been announced.

For an independent preview, you can also see here. 

In case you have not already noticed, Workbench 5.2 alpha / preview release of MySQL's premier development and design tool, has been announced.

For an independent preview, you can also see here. 

Sysbench is a open source product that enables you to perform various system benchmarks including databases. Drizzles performs regression testing of every trunk revision with a branched version of sysbench within Drizzle Automation.

A pending branch https://code.launchpad.net/~elambert/sysbench/trunk_drizzle_merge by Eric Lambert now enables side by side testing with MySQL and Drizzle. On a system running MySQL and Drizzle I was able install this sysbench branch with the following commands.

cd bzr
bzr branch lp:~elambert/sysbench/trunk_drizzle_merge
cd trunk_drizzle_merge/
./autogen.sh
./configure
make
sudo make install

Running the default lua tests supplied required me to ensure …

If your compiling a product that includes a dependency of MySQL, you can easily get the error

configure: error: mysql_config executable not found

I generally don’t see this problem, because I use MySQL binary tar files, however if you use MySQL packages, such as Ubuntu, you can easily miss the required dependency.

My currently installed MySQL packages on this Ubuntu machine are:

$ sudo dpkg -l | grep mysql
ii  libdbd-mysql                               0.8.2-1-4.1                               MySQL database server driver for libdbi
ii  libdbd-mysql-perl                          4.008-1                                   A Perl5 database interface to the MySQL data
ii  libmysqlclient15off                        5.1.30really5.0.75-0ubuntu10.2            MySQL database client library
ii  libmysqlclient16                           5.1.31-1ubuntu2                           MySQL database client library
ii  libqt4-sql-mysql …

It is important to know and understand that while indexing columns in MySQL will generally improve performance, using the appropriate type of index can make a greater impact on performance.

There are four general index types to consider when creating an appropriate index to optimize SQL queries.

• Column Index
• Concatenated Index
• Covering Index
• Partial Index

For the purpose of this discussion I am excluding other specialized index types such as fulltext, spatial and hash in memory engine.

Example Table

For the following examples, I will use this test table structure.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  external_id INT UNSIGNED NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY(id)
) …

I tried to go to mysql.com and Planet MySQL over my lunch break at OSCON 2009 to find the websites are down. Seems from conversions with fellow Drizzle colleagues this has been down for some time.

What does your site look like when your system is unavailable or down?

This is a question I ask clients. What redundancy do you have in place for DNS, for a site unavailable page, for a static copy of content?

I learned my first personal lesson several years ago when at The Planet, my server and 9,000 others were unavailable at least 40 hours due to explosion, fire at a data center. While I had copies of my site, and shared hosting options elsewhere, all DNS was also in the same unavailable data center. This was definitely a shortcoming of the Host Provider at the time.

For any commercial site, it is important that at …

Currently Drizzle offers three (3) separate query logging plugins. These plugins offer an extensible means of gathering all or selected queries and provide the foundation for a query analyser tool. Additional filtering includes selecting queries by execution time, result size, rows processed and by any given regular expression via PCRE.

During this tutorial I’ll be stepping though the various logging_query parameters which log SQL in a CSV format.

Confirm Logging Plugins

You can view the current ACTIVE plugins in Drizzle with the following SQL.

drizzle> select version();
+--------------+
| version()    |
+--------------+
| 2009.07.1097 |
+--------------+

drizzle> select * from information_schema.plugins where plugin_name like 'logging%';
+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+
| PLUGIN_NAME     | PLUGIN_VERSION | …

One thing you learn when you start to manage several servers is that life is easier if things are done the same way. I manage a few MySQL database instances on a few different hosts and here are a list of some things which I think could do with improvement. Note these views are from a UNIX point of view and for Windows MySQL DBAs many of the comments may not be appropriate. Also I’m not talking here about the administration of a single database, but the problems when you administer multiple instances.

Issues

First lets talk about the issues I notice in my day to day usage of MySQL. Then I’ll try and come up with some suggestions as to how these issues might be resolved.

• Give up root earlier and expect to run as a non-root user. While it’s true that the mysqld process normally runs as the mysql user it’s also true that to be a MySQL DBA is almost impossible at the moment if you don’t have root …

The Call for Papers for the OpenSQL Camp 2009 (European Edition) has ended yesterday — we received 27 excellent session proposals from various Open Source Database projects. I would like to say a big "Thank You" to everyone who submitted a talk! In the beginning I was a bit concerned that the conference would become too MySQL-centric, but this fortunately changed in the last few days.

Sadly we now have more than double the amount of sessions than we can actually host, which means that we will have to review and vote on the sessions to distill the final program. We've formed a small committee that will perform this task (currently consisting of …

I’ve been working on various different MySQL related issues and maintenance procedures some of which have not gone according to plan.  Here is a recipe that may help you avoid wasting a lot of time, especially if your database is large.

In order to do some of these tests make tests against a server configured identically to the one you plan to work on but instead which has no data. That is the mysql database needs to be complete but the other databases need to be dumped with the –no-data or -d options.  Don’t forget to also include any triggers or stored routines.

Now run the “procedure” on this “emtpy instance”. As it has no data most things run very quickly. So if you have issues you can repeat the procedure in no time. Restoring the instance too is easy as it’s tiny. This makes the whole procedure scriptable and you can be confident in the results.

Once you are …