I've been making some improvements to our use of Hudson recently that have been really helpful.

The first was starting to use a set of parameterized builds. These use the Parameterized Trigger plugin, which allows you to pass parameters to triggered additional jobs when a job finishes. So using these we make a job which checks out the latest source (which should just about always succeed) and then fire off a whole host of jobs running on all of our build hosts. It has a single "build now" submit form which takes a bzr branch location as the branch to build. With this all of our developers can check their tree against the build farm before submitting the branch for merge.

That's great, but as we got more and more build hosts, we had to set up a job on each of them - and then having 4 machines which were all amd64 running Ubuntu 9.10 …

I came across a performance issue and the underlining query was a subselect. MySQL was spending a lot of time in 'preparing' state - meaning the optimiser is working out how to execute the query:
(snip)| 54937 | root | localhost | rea | Query | 2 | preparing | select listingeve0_.LISTING_UID as col_0_0_, count(distinct listingeve0_.VISITOR_UID) as col_1_0_ fr | | 54938 | root | localhost | rea | Query | 2 | preparing | select listingeve0_.LISTING_UID as col_0_0_, count(distinct listingeve0_.VISITOR_UID) as col_1_0_ fr | | 54939 | root | localhost | rea | Query | 2 | preparing | select listingeve0_.LISTING_UID as col_0_0_, count(distinct listingeve0_.VISITOR_UID) as col_1_0_ fr | (snip)

I re-wrote the query as an outer join and I want to show the results to you.
Heres the subselect query and results:

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 ;insert into …

My boss vented about the lack of a Com_ping SHOW GLOBAL STATUS variable earlier, and I figured it would be dead easy to hack in.

A few minutes later:

Cerberus:mysql-next-mr mark$ bzr diff ./sql
=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc       revid:alik@sun.com-20100114090008-3rsdmlp1w2mqgrhg
+++ sql/mysqld.cc       2010-03-02 22:58:45 +0000
@@ -3174,6 +3174,7 @@
   {"load",                 (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_LOAD]), SHOW_LONG_STATUS},
   {"lock_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_LOCK_TABLES]), SHOW_LONG_STATUS},
   {"optimize",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_OPTIMIZE]), SHOW_LONG_STATUS},
+  {"ping",                 (char*) offsetof(STATUS_VAR, com_ping), SHOW_LONG_STATUS},
   {"preload_keys",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PRELOAD_KEYS]), SHOW_LONG_STATUS},
   {"prepare_sql",          (char*) offsetof(STATUS_VAR, …

Both grids in "Data" and "Query" tabs now support a variable row height. Very useful if you like to see more than the first line of text in table cells at once. Just increase the option "Lines of text in grid rows" in "Tools" > "Preferences" > "Data" to a higher value.



Above the data grid, you will notice two new buttons for "Next" and "Show all". The grid loads table data in chunks of 1,000 rows. So, "Next" loads the next chunk of 1,000 rows, and "Show all" loads the entire table, up to a maximum of 100,000 rows. Both values are customizable via "Tools" > "Preferences" > "Data".

The good thing is: normally you won't need to click any of these two buttons, because:
A) "Next" is automatically executed when you have the last row focused and the press the PageDown key. So, if you hold down the PgDn key, you will see Heidi loads data on demand in a larger table.
B) Both buttons …

One of my colleagues, Ryan Lowe, has just heard that his session on PCI compliance with MySQL has been accepted at the upcoming MySQL conference. Ryan is highly qualified to present this topic, and not many people can say that; I certainly can’t claim that title myself. If you’re looking to learn how to make your MySQL installation PCI-compliant, there’s also not a lot of trustworthy information online. Personally – and really, no bias just because he’s my colleague – I think this is a great session for the MySQL conference, which I sometimes thought didn’t have enough diversity of topics in past years.

Today I ran into my first MySQL binlog race condition: The initial problem was quite simple: A typical MySQL master->slave setup with heavy load on the master and nearly no load on the slave, which only serves as a hot … Weiterlesen →

I have recently been trying out semisynchronous replication. This is a new feature added in MySQL 5.5 based on the original google patch.

Installing was very simple and has been covered elsewhere in detail. Suffice to say it was relatively simple to do.

While test I was a bit surprised by some behavior I saw that turned out correct. What I wanted to examine what was semi-synchronous actually does and use cases for it.

The manual defines this feature correctly in very careful language:
If semisynchronous replication is enabled on the master side and there is at least one semisynchronous slave, a thread …

MySQL system variables and status variables have two scopes. These are GLOBAL and SESSION which are self explanatory.
This is important to realize when altering system variables dynamically. The following example does not produce the expected results.

mysql> USE test;
Database changed
mysql> CREATE TABLE example1(
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> col1 VARCHAR(10) NOT NULL,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> SHOW CREATE TABLE example1\G
*************************** 1. row ***************************
       Table: example1
Create Table: CREATE TABLE `example1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

We see that the table has a default CHARACTER SET of latin1. If you wanted to ensure all tables are …

How often do people get indexing wrong? All the time. This is a sample table in Drizzle, I'll show how you can view indexes with the new data dictionary code.

drizzle> show create table f;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| f     | CREATE TABLE `f` (
   `a` int NOT NULL,
   `b` int NOT NULL,
   `c` int NOT NULL,
   PRIMARY KEY (`a`,`b`,`c`),
   KEY `c` (`c`),
   KEY `b` (`b`,`c`)
)  | …

As part of my MySQLCamp for the Oracle DBA series of talks to help the Oracle DBA understand, use and appreciate MySQL I have also developed a series of short interesting posts I have termed “Don’t Assume”. Many of these are re-occurring points during my consulting experiences as I observe Oracle DBA’s using MySQL. I am putting the finishing touches to my MySQL for the Oracle DBA series of talks and I’m excited to highlight some of the subtle differences and unique characteristics of MySQL RDBMS in comparison to Oracle and some extent other products including SQL Server.

Stay tuned for more soon.

I will be presenting at the MySQL Users Conference 2010 in Santa Clara, April 2010 two presentations from this series, …