One of our partners recently asked me on what platforms do we usually see MySQL installed out there...

The last 5 years I gave the answer: Typically it is 80% Linux, 10% Windows, 5% Solaris and 5% all others. But this was only the picture of my limited view and I was not sure how objective this was.

This time I really wanted to know it and so I collected the information of about 570 MySQL installations of customers.

The following numbers came out:

Operating System

This week I've been using phpMyAdmin for what feels like the first time in years. I'm happier at the command line, but needed some graphical representation of information and easy ways to export example queries for the book I'm working on. I noticed that phpMyAdmin now has a Designer tab, which shows relationships between tables and allows you to define them.

If your table types don't support foreign keys, you can still draw links in here, and phpMyAdmin will show the id fields as links to the data they represent, which is quite a nice touch (MyISAM doesn't support foreign keys). If your database does support foreign keys, then I found this post which explained that you must have an index on the column that will …

By far, the most popular way for PDI users to load data into LucidDB is to use the PDI Streaming Loader. The streaming loader is a native PDI step that:

• Enables high performance loading, directly over the network without the need for intermediate IO and shipping of data files.
• Lets users choose more interesting (from a DW perspective) loading type into tables. In particular, in addition to simple INSERTs it allows for MERGE (aka UPSERT) and also UPDATE. All done, in the same, bulk loader.
• Enables the metadata for the load to be managed, scheduled, and run in PDI.

However, we’ve had some known issues. In fact, until PDI 4.2 GA and LucidDB 0.9.4 GA it’s pretty problematic unless you run through the process of patching LucidDB outlined on this page: …

In order to optimally size the amount of RAM to allocate to a set of new machines for running MTR, I ran a few tests to check the memory usage of an MTR run for mysql-trunk and cluster-7.1. As using a RAM disk considerably speeds things up, I set the vardir to be on /ramdisk and logged the usage of that too.

The tests were performed on an 8-core E5450 @ 3.00GHz with 24GB RAM, with 8GB allocated to /ramdisk. Each branch ran the default.daily collection, which generally contains the most testing we do per-run. Between each run I rebooted the machine to clear the buffer cache and /ramdisk.

I used something like the script below, which saved the per-second usage of /ramdisk, the total RAM used, and the RAM used minus buffers.

#!/bin/bash

BRANCH="mysql-trunk"
BUILDDIR="mysql-5.6.3-m5-linux2.6-x86_64"
TESTDIR="${HOME}/mtr-test/${BRANCH}"

stats()
{
  i=1
  rm -f …

I’ve done some benchmark testing of 2 Python modules for MySQL data retrieval: MySQLdb and pyhs. MySQLdb uses MySQL’s client libraries, whereas pyhs uses HandlerSocket that bypasses MySQL’s client layer and interfaces Innodb storage engine’s files directly. In my testing, HandlerSocket results in 82% improvement over mysql client libraries based on number of rows retrieved. The tests were conducted under different conditions: right after a start when cache is cold, a warmed up cache after running SELECT * FROM customer, and alternating the execution order of those 2 Python files. The results are fairly consistent in that they all fall in the same range. …

Indexes can reduce the amount of data your query touches by orders of magnitude. This results in a proportional query speedup. So what happens when you define a nice set of indexes and you don’t get the performance pop you were expecting? Consider the following example:

mysql> show create table t;
| t     | CREATE TABLE `t` (
  `a` varchar(255) DEFAULT NULL,
  `b` bigint(20) NOT NULL DEFAULT '0',
  `c` bigint(20) NOT NULL DEFAULT '0',
  `d` bigint(20) DEFAULT NULL,
  `e` char(255) DEFAULT NULL,
  PRIMARY KEY (`b`,`c`),
  KEY `a` (`a`,`b`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now we’d like to perform the following query:

select sql_no_cache count(d) from t where a = 'this is a test' and b between 8000000 and 8100000;

Great! We have index a, which cover this query. Using a should be really fast. You’d expect to use the index to jump to the beginning of the ‘this is a test’ values for …

press release

SAN JOSÉ, CA., June 27, 2011 –  Any system administrator worth his or her salt knows that data is the oxygen of the modern age.

To keep the oxygen flowing Monitis Inc., the award-winning provider of the world’s first all-in-one systems monitoring platform, announces that users can now monitor their MySQL-operated database servers on Monitis’s free monitoring service – Monitor.Us. The new hosted MySQL monitoring service is available immediately.

“Our new MySQL monitoring service ensures high availability of database servers and performs a host of tasks to ensure MySQL is running smoothly,” said Monitis Founder and CEO Hovhannes Avoyan. “And because MySQL is the most …

We did recently a review of one of our customers systems and we found that the Query Cache was disabled even thought it had significant more read than write queries.
When we asked the customer why he has not enabled the Query Cache he mentioned a review that was done a few years ago and which stated that the Query Cache hit ratio was non optimal.
This was verified on a testing system which had the Query Cache enabled by accident.

But we all thought that the Query Cache would make sense in this situation so we investigated a bit more.

They have a Java application where they do pretty complex queries (10 to 30-way Joins) and they Connect with Connector/J to the database. We tried it out in the application on a dedicated system and verified that the Query Cache was not serving our queries but the query did a full dive to the data.

So first we were looking in the MySQL documentation if there is anything stated why …

Indexes can reduce the amount of data your query touches by orders of magnitude. This results in a proportional query speedup. So what happens when you define a nice set of indexes and you don’t get the performance pop you were expecting? Consider the following example:

mysql> SHOW CREATE TABLE t;
| t     | CREATE TABLE `t` (
  `a` VARCHAR(255) DEFAULT NULL,
  `b` BIGINT(20) NOT NULL DEFAULT '0',
  `c` BIGINT(20) NOT NULL DEFAULT '0',
  `d` BIGINT(20) DEFAULT NULL,
  `e` CHAR(255) DEFAULT NULL,
  PRIMARY KEY (`b`,`c`),
  KEY `a` (`a`,`b`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now we’d like to perform the following query:

SELECT sql_no_cache COUNT(d) FROM t WHERE a = 'this is a test' AND b BETWEEN 8000000 AND 8100000;

Great! We have index a, which cover this query. Using a should be really fast. You’d expect to use the index to jump to the beginning of the ‘this is a test’ …

Those of us used to command line editing will no doubt have been frustrated many times in Firefox when editing text in an input box and subconciously hitting ctrl-w to delete-word, only to have the tab close and your work deleted.

Thankfully there is a workaround to this. It used to be a case of adding the following to .gtkrc:

gtk-key-theme-name = "Emacs"

However these days it’s a gconf setting:

$ gconftool-2 --set /desktop/gnome/interface/gtk_key_theme Emacs --type string

This will bind ctrl-w to delete-word when in an input box, but retain the close tab binding elsewhere, a nice implementation of DWIM. See this page for more information.