Long before there was the official Query Analyzer (QUAN), a component of MySQL Enterprise, SQL analysis was possible using MySQL Proxy.

The following is an introduction to logging and query analysis with MySQL Proxy.

Get MySQL Proxy

You need to first download MySQL Proxy. In this example I am using the Linux RHEL5 64bit OS and Version 0.7.2

$ wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.7.2-linux-rhel5-x86-64bit.tar.gz/from/http://mirror.trouble-free.net/mysql_mirror/
$ tar xvfz mysql-proxy-0.7.2-linux-rhel5-x86-64bit.tar.gz
$ ln -s mysql-proxy-0.7.2-linux-rhel5-x86-64bit mysql-proxy
$ export PATH=`pwd`/mysql-proxy/sbin:$PATH
$ mysql-proxy --help-all

Pre-requisites

MySQL Proxy uses TCP/IP, so it is important you connect via the actual …

I have several side projects when time permits and one is that of benchmarking various MySQL technologies (e.g. MySQL 5.0,5.1,5.4), variants (e.g. MariaDB, Drizzle) and storage engines (e.g. Tokutek, Innodb plugin) and even other products like Tokyo Cabinet which is gaining large implementations.

You have two options with benchmarks, the brute force approach such as Sysbench, TPC, sysbench, Juice Benchmark, iibench, mysqlslap, skyload. I prefer the realistic approach however these are always on client’s private data. What is first needed is better access to public data for benchmarks. I have compiled this list to date and I am seeking additional sources for reference.

• Feebase – Data is in clean loadable format
• IMDB – Not clean, but Roland Bouman is working on …

I have a strong professional and personal relationship with Kosovo. Before and after the war, from 1998 to 2002, I was part of the OSCE-UN mission that helped the …

We ran a quick scalability test of Calpont join behavior across using a Star Schema Benchmark data set at a scale factor of 1000. The Star Schema Benchmark transforms a TPC-H / DBT-3 data to a more standardized data warehouse star schema data model, and the 1000 scale factor includes 6 billion rows in the primary fact table. Information on the star schema bench (SSB) can be found at http://www.cs.umb.edu/~xuedchen/research/publications/DataWarehousePerformanceDissertationProposal.pdf .

-----------------------------------------------------------------------------------------------------------------------------------------
-- Note that these queries are run without any tuning or indices created for these joins or filters.
-- Basically, this is just 1) Create tables (without index or …

Before you think I’ve posted an animal story in my MySQL category please read on. For reference, The RAT and the CAT is something I wrote back in 2006, that explains the CAT part.

I was reviewing the website performance tonight of a client. I had access to an idle system so being lazy in collecting SQL after my initial audit of the system and MySQL, I turned on the General Query log and hit the home page. NOTE: Do not do this on a running production system, especially with any volume.

What I got was 77 lines of output (after I removed newlines in the queries). 74 were queries, and 72 were from the same table, via some unnecessarily complex sub-joins. I wish I could have laughed but it wasn’t really funny.

My Friday wisdom for writing SQL is this:

• Don’t write duplicate SQL statements
• Look the entire …

I just discovered today in the MySQL 5.1 Reference Manual a handy set of additional indexes in the System Navigation section.

• Index
• Standard Index
• C Function Index
• Command Index
• Function Index
• INFORMATION_SCHEMA Index
• Transaction Isolation Level Index
• JOIN Types Index
• Operator Index
• Option Index
• Privileges Index
• SQL Modes Index
• Status Variable Index
• Statement/Syntax Index
• System Variable Index

Perhaps they have been around for some time and I’ve not noticed, but there are much better then searching when you know the content type as per the index list on what you are searching for.

As a consultant I would be rich if I made money every time when asking “Have you checked the MySQL error log?”

Today’s special found in a 13GB MySQL server error log.

090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101071 search iterations)! Consider
InnoDB: increasing the buffer pool size.
InnoDB: It is also possible that in your Unix version
InnoDB: fsync is very slow, or completely frozen inside
InnoDB: the OS kernel. Then upgrading to a newer version
InnoDB: of your operating system may help. Look at the
InnoDB: number of fsyncs in diagnostic info below.
InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs
InnoDB: Starting InnoDB Monitor to print further
InnoDB: diagnostics to the standard output.
090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101051 search iterations)! Consider
InnoDB: increasing …

1. Defragging the Stimulus -- each [recovery] site has its own silo of data, and no site is complete. What we need is a unified point of access to all sources of information: firsthand reports from Recovery.gov and state portals, commentary from StimulusWatch and MetaCarta, and more. Suggests that Recovery.gov should be the hub for this presently-decentralised pile of recovery data.
2. Memetracker -- site accompanying the research written up by the New York Times as Researchers at Cornell, using powerful computers and clever algorithms, studied the news cycle by looking for repeated phrases and tracking their appearances on 1.6 million …

Gearman is an open source generic framework for distributed processing. At OSCON 2009 I attended the Gearman: Build Your Own Distributed Platform in 3 Hours tutorial.

While it’s very easy to install Gearman, and follow the first example, if you missed the all important additional PHP steps listed on just one slide you may be left with the “‘Class ‘GearmanClient’ not found” error.

The following are detailed instructions for the installation and configuration of Gearman and PHP on Ubuntu 9.04 Jaunty.

Add the Drizzle PPA to get pre-packaged versions of Gearman.

cp /etc/apt/sources.list /etc/apt/sources.list.orig
echo "deb http://ppa.launchpad.net/drizzle-developers/ppa/ubuntu intrepid main
deb-src http://ppa.launchpad.net/drizzle-developers/ppa/ubuntu intrepid main" …

This blog post was inspired by Masood Mortazavi's comment: "OSCon to be somewhat disappointing this year -- many low quality sessions and a celebration of forks"I did not attend OSCON, even though I live in California. I did not even bother to submit a presentation proposal, even though people who attended my talks at the MySQL Conference and Expo earlier this year and the year before, all