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

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)
) …