Here is a copy/paste example of the content from the new analytics tab that is in revision-177 of Kontrollbase (downloadable now) – it features as many or more options and analysis than pay-to-use monitoring solutions can offer at the moment and you don’t have to pay a dime. Along with the Analytics tab you also [...]

The CodePlex Foundation was announced this week by Microsoft. Most reactions have been quite positive, but there has, of course, also been skepticism and concerns from Open Source advocates. The two main worries can be summarized as "the CodePlex Foundation is a ploy" (e.g. TechNews World) and "what's in it for them?" (e.g. Open Bytes).

I will try to explain why I think that the CodePlex Foundation is a good idea, and why I agreed to be an advisor for the Foundation.

N.B. All of the following is my own interpretation based on conversations with the people behind the Foundation. Any misinterpretations of issues and all speculations are my own.

It's important …

In line with my prediction from few days ago, Larry Ellison is announcing the new Database Machine — the new version is targeting OLTP workloads and is based on Sun hardware.

Looks like I just got the date wrong. Oh well, now is the announcement, hype and demo is at the Oracle Open World and shipments are to start upon Oracle-Sun acquisition completion.

So what’s new in Exadata that I …

There have been a lot of systems I have looked at where the Created_tmp_disk_tables was very high. MySQL has good documentation on how it uses memory, specifically temp tables, here.

I would like to talk about not "why" MySQL does this but how to speed up the performance when MySQL internally or users create temporary tables to disk. A great solution is TMPFS, a quick how to is as follows:

-- Before you start
1. Make sure you allocate enough space to TMPFS
-- 2GB is usually safe but if you are using larger data sets with inefficient queries then there are far worse performance issues to deal with.

-- The safe way to implement TMPFS for MySQL
shell> mkdir /tmp/mysqltmp
shell> chown mysql:mysql /tmp/mysqltmp
shell> id mysql
##NOTE: make …

I saw Mark Callaghan’s post, and his graph showing miss rate as a function of cache size for InnoDB running MySQL. He plots miss rate against cache size and compares it to two simple models:

• A linear model where the miss rate is (1-C/D)/50, and
• A inverse-proportional model where the miss rate is D/(1000C).

He seemed happy (and maybe surprised) that that the linear model is a bad match and that inverse-proportional model is a good match. The linear model is the one that would make sense if every page were equally likely to have a hit.

I’ll argue here that it’s not so surprising. Suppose that miss rate has a heavy-tailed distribution, such as Zipf’s law. An example of a Zipf’s-law distribution would be if …

I saw Mark Callaghan’s post, and his graph showing miss rate as a function of cache size for InnoDB running MySQL.  He plots miss rate against cache size and compares it to two simple models:

• 
  
• A linear model where the miss rate is (1-C/D)/50, and
  
• A inverse-proportional model where the miss rate is D/(1000C).

He seemed happy (and maybe surprised) that that the linear model is a bad match and that inverse-proportional model is a good match.  The linear model is the one that would make sense if every page were equally likely to have a hit.

I’ll argue here that it’s not so surprising.  Suppose that miss rate has a heavy-tailed distribution, such as Zipf’s law. An …

There’s a really important difference between a unique index (MySQL’s answer to a “unique constraint”) and a primary key in MySQL. Please take a look at this:

CREATE TABLE `t` (
  `a` int,
  `b` int,
  `c` int,
  UNIQUE KEY `a` (`a`,`b`)
)

The combination of columns a, b should uniquely identify any tuple in the table, right?

select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
+------+------+------+

Wrong. Our arch-enemy NULL messes things up again:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does …

Just when I was showing off how easy is it to install a MySQL sandbox from a tarball, I was faced by an unexpected error. The tar application was not among the recognized ones. As soon as I saw the error, I immediately knew what had …

I have pushed more performance monitoring changes to the Facebook patch on Launchpad. This includes:

• Support for SHOW INNODB FILE STATUS that reports IO statistics per InnoDB tablespace. When innodb_file_per_table is used, this provides statistics per table.
• Counters in SHOW STATUS that report time processing SQL statements including Command_seconds for non-replication SQL, Command_slave_seconds for replication SQL and Innodb_record_in_range_seconds that measures time in InnoDB during query optimization.
• Changes to ignore enforcment of innodb_thread_concurrency for the replication SQL thread. This should reduce delay but more remains to be done. When a slave is busy with many more concurrent queries than CPU cores, …

In a previous post, I outlined a plan to port the Build-It-Blocks web site from MS SQL to MySQL.  I plan to run the Windows stack (Windows, .asp, and IIS) on MySQL via the MyODBC connector.  Being a Red Sox fan, this feels like getting David Ortiz to hit with A-Rod's bat, but I digress.  So far, things are going smoothly:

1. Install MySQL and MyODBC

I chose to install MySQL from the zip file, primarily because I wanted the ability to leave the Windows Server in a known good state if something went wrong; by doing a manual install I knew I would be able to "un-install" everything if necessary.  Also, all of my experience with MySQL has been on Linux, so I'm comfortable with the …