A first look at delayed replication in MySQL 5.6

If you like fresh features, you should not miss this one. MySQL 5.6.2 includes, among other improvements, the implementation of Time delayed replication, a feature that lets you tell the slave not to apply changes from the master immediately, but to wait N seconds.

The feature is documented in WL#344. (There was a manual online as well together with the binaries for MySQL 5.6.0, but they were removed after a few days for a good reason. I am confident that both the manual and some binaries will eventually show up soon).
Since as of today there are no binaries for MySQL 5.6.x, you need to get the code and compile it yourself. Just get the code from https://code.launchpad.net/mysql-server and compile it using the instructions in building MySQL 5.5 with cmake.
To get a taste of this new feature, the quickest way is to set up replication using the binaries that you have built and MySQL Sandbox.

make_replication_sandbox  mysql-5.6.2-m5-osx10.6-.tar.gz 
# the file name may change, depending on the operating system you are using

Soon you will have one master and two slaves in $HOME/sandboxes/rsandbox_5_6_2.
What you have to do is connect to one of the slaves and enter these commands:

STOP SLAVE;
change master to master_delay=60;
START SLAVE;

Let's say that you did this to slave #2.
Now whatever you do in the master will be replicated immediately in slave #1, but it will executed with 60 seconds delay in slave #2.
To be clear, the IO_THREADs of both slaves keep getting data from the master as fast as they can, same as they did until version 5.5, but slave #2 will hold the SQL_THREAD for the defined amount of seconds.
This new state is visible in the output of the SHOW SLAVE STATUS command, which lists this information after you do something in the master like creating a table or inserting data:

               SQL_Delay: 60
     SQL_Remaining_Delay: 43
 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

The main purpose of delayed replication is to protect the server against human mistakes. If I accidentally drop a table, the statement is instantly replicated to all the slaves, but it is not executed to the delayed slaves.

$ ./m -e 'drop table test.t1 '
$ ./use_all 'show tables from test'
# master  
# server: 1: 
# server: 2: 
Tables_in_test
t1

The table is gone in the master, and it is gone in the regular slave, but it is still there in the delayed slave. And if I detect the problem before the delayed statement gets executed (a delay time longer than 60 seconds would be advisable in this case, 3600=1 hour, seems healthier), then I may be able to recover the data.

I notice en passant that there is much more than delayed replication going on in MySQL 5.6. For example, the information_schema tables related to InnoDB have increased from 7 to 18:

 show tables from information_schema like 'innodb%';
+----------------------------------------+
| Tables_in_information_schema (innodb%) |
+----------------------------------------+
| INNODB_CMPMEM                          |
| INNODB_TRX                             |
| INNODB_BUFFER_PAGE                     | *
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  | *
| INNODB_CMP                             |
| INNODB_SYS_COLUMNS                     | *
| INNODB_CMPMEM_RESET                    |
| INNODB_SYS_FOREIGN_COLS                | *
| INNODB_BUFFER_PAGE_LRU                 | * 
| INNODB_BUFFER_POOL_STATS               | *
| INNODB_CMP_RESET                       |
| INNODB_SYS_FOREIGN                     | *
| INNODB_METRICS                         | *
| INNODB_SYS_INDEXES                     | *
| INNODB_LOCKS                           |
| INNODB_SYS_FIELDS                      | *
| INNODB_SYS_TABLES                      | *
+----------------------------------------+
18 rows in set (0.00 sec)
# (*) new tables marked with a star

What they do and how to play with them will be matter for some more investigation.

The MySQL Council is up and running. We want to hear from you!

The Independent Oracle User Group (IOUG) has formed the MySQL Council, with the purpose of addressing the interests and needs of MySQL users.

The current Council members are:

• Sarah Novotny, Blue Gecko, Council Chair
• Sheeri Cabral, PalominoDB
• Bradley Kuszmaul, Tokutek
• Giuseppe Maxia, Continuent
• Rob Wultsch, GoDaddy.com
• Matt Yonkovit, Percona

The IOUG is a well established and respected institution, with more than 20,000 database professionals registered as its members. In the long history of Oracle, the IOUG has always played a role in the aftermath of many acquisitions, extending a friendly hand to the newcomers in the family. I have spent much time with IOUG people, and I was impressed by their enthusiasm and professionalism. My impression of the IOUG is that it is a friendly group of people with the common goal of improving the experience of being an Oracle user.
The IOUG is not looking for assimilation. While Oracle business strategy calls for full integration, the IOUG recognizes that users come in different sizes and shapes, and they cannot be forced into the institution. Instead, the IOUG want to understand the newcomers and offer their services to help out towards the common goals. The IOUG people also know that many old Oracle users are also MySQL users. By facilitating the integration of the MySQL community they are serving the needs of a wider population than the traditional set of MySQL users.

The MySQL community has never been organized as a whole. There id nothing comparable to the massive presence of the Oracle user group. MySQL users are mostly isolated. When they convene into social entities, they identify themselves either by town boundaries or by being member of some group that has some interest in MySQL (Linux distributions, PHP developers, CMS framework users). There is no unified view of what users represent or want, and a difficult dialog between the user base and the company that produces the software.
With Oracle, this lack of unity is yet another obstacle in the path to a good understanding. Oracle is used to talk to a large entity representing its users, and it has not the patience or the skills to deal with such a distributed presence as the MySQL community. For this reason I believe that the MySQL Council is a good idea. It provides a tool for the will of the MySQL community to be conveyed to the company, using the IOUG, one of the channels that are familiar to Oracle, and that are more likely to reach the decision makers.

The main goal of the council should be to mend fences. There is much to be done. The culture of traditional Oracle users and that of MySQL users are different and sometimes hard to reconcile. Oracle strategies that were tuned towards pleasing their traditional customers may not suit the needs of the newcomers. All this needs to be addressed, and the MySQL Council could be the first step to the solution.

For this reason, the council members want to hear from the community. What are the main pain points and issues that you want addressed? What is Oracle doing right with MySQL? Where could it improve?

If you know one or more of us, please contact us by email. Or write a blog post about the issues to address. Or comment in our blogs. We want to hear from you. We feel that we must represent the larger MySQL community and bridge the gaps between the company and the user base.

The roles of the MySQL Council is not only to be a diplomatic channel of communication between users and company. It will also help with the diffusion of MySQL culture among Oracle users, with articles, conferences, meetings, and whatever the imagination provides to achieve the goal of spreading the word.

Also for this goal we welcome your input. Make your voice heard. It's time to boost the technical writing in Planet MySQL and in the rest of the net. Let MySQL be known!

Joining the Oracle ACE program

A few days ago I received an invitation to join the Oracle ACE program, which is a group of strong community enthusiasts and advocate of Oracle products. Since I have been a vocal member of the MySQL community for years, I welcome this acknowledgment as well as I appreciated being nominated MySQL Community Contributor of the Year in 2006. Unlike that award, which came from inside the MySQL company, the Oracle ACE nomination came from my peers in the community, to whom I address my thanks and appreciation. The nomination comes from the community, but the title is granted by the company, as recognition for good work. Therefore, thanks also to my former colleagues at Oracle who have approved my current status.

For the ones who are curious, being an Oracle ACE does not mean becoming an employee, or having obligations towards Oracle. There is not even an obligation to be kind towards the company, meaning that Oracle ACEs can be as outspoken as they feel they should. So, there is no restraint on what I can tell in public, and no expectations of unconditional support for Oracle policies.
What you can expect is for me to be my usual self, the guy who is enthusiast about cool technology, no matter where it comes from, and critical or appreciative depending on merit.
Now, back to hacking!

Pitfalls of monitoring MySQL table activity with stored routines

A friend of mine needed a quick method to monitor a table that was frequently updated. There were several procedures writing to the table several times per second. He needed a quick way of determining how many updates per second the table was getting. The table has only one row, which includes a counter that is increased at each operation. Therefore, the simple plan was: Let's get the counter from the table; Allow N seconds to pass; Get the counter again; The difference between the second counter and the first counter, divided by the number of seconds gives the updates per second. The plan makes sense, and if you run the above commands manually, you get what you want.

However, my friend wanted the update ratio to be a single operation, say like:

SELECT update_ratio();

He went to make a simple function, following the four steps described above.

delimiter //
drop function if exists update_ratio //
create function update_ratio()
RETURNS INT 
begin
     declare sleep_wait int default 5;
     declare start int default 1;
     declare finish int default 1;

     set start = (select counter from mytable);
     do sleep(sleep_wait);
     set finish = (select counter from mytable);
     return (finish-start)/sleep_wait;
end $$
delimiter ;

It seems OK. The function runs without errors, but it always returns zero.
Mystery! Running the statements manually gives always a sensible result. Using triggers to monitor the table shows that indeed it is updated many times per second, but the function returns always zero.
More puzzling is the fact that if we convert the function to a procedure, it gives the wanted result.

The solution to the mystery is found in the MySQL online manual

A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log.

In other words, it means that all tables referenced in a stored functions are locked when the function starts. Therefore the external procedures that were updating the table will have to wait until the function's end before updating. When the function reads from the table, it gets always the same record counter, because no updates were happening in the meantime. That's why the second read is the same as the first one, and the result is zero.

What should you do then?
One option is to convert the function into a procedure:

delimiter //
drop procedure if exists show_update_ratio //
create procedure show_update_ratio()
begin
     declare sleep_wait int default 5;
     declare start int default 1;
     declare finish int default 1;
    
     select counter into start from mytable;
     do sleep(sleep_wait);
     select counter into finish from mytable;
     SET @UPS := (finish-start)/sleep_wait;
end //

That gets the job done. If you want to get the result into a variable, you can do it with two statements.

call show_update_ratio();
select @UPS;

If you don't change the last SET into a SELECT and just display the value.

Another option is using several SQL commands from your application. Also in this case, make sure that you are NOT wrapping this code inside a transaction, or you will get the same result in both queries

# WRONG!
     set autocommit=0;
     BEGIN;
     select counter into @start from mytable;
     set @start = start;
     do sleep(5);
     select counter into @finish from mytable;
     select (@finish - @start) / 5 as UPS;

If you go for this solution (or even the stored procedure), make sure that you are either using autocommit, or commit after each query if you must use a transaction.

Continuent is hiring - Support and QA engineers wanted

Continuent is hiring. The business is growing, the opportunities are piling up nicely, and we need to beef up the team with the addition of some new professionals. The mist urgent posts to fill are a QA Engineer and a Support Engineer, both experts of their specific trades and of database clustering. We are looking at the matter without borders. Although it would be preferable to find candidates in the US, and in the West Coast in particular, we are really looking for the best people in the market, regardless of their location. Both jobs are challenging, they are both MySQL related, and both require experience with QA and support respectively, in addition to development background. If you are a super star in either QA or support, contact resumes AT continuent DOT com. Also, feel free to contact me, should you need further information.

Announcing the Open Database Camp - Sardinia, May 2011

I have been traveling to many conferences in the last 10 years, and many times I have been asked to organize an event in my native land, Sardinia. After delaying the inevitable for long time, here I can announce it. The Open Database Camp 2011 will take place in Sardinia, hosted by the Sardinia Technology Park, a local scientific and business institution with international links. Mark your calendars: the Open Database Camp will be held in Sardinia on May 6-7-8, 2011. I have already confirmed the venue, and I will have full cooperation from Sardegna Ricerche about the conference logistics. I will meet the organizers on January 27th to get in touch with nearby hotels and restaurants and negotiate rates. The place is a beautiful and modern compound, built in the middle of a forest. About 40 Km from Cagliari and its airport. There is a public bus service to reach the venue, and there will be an integrative bus during the conference. The place is a few kilometers from the sea resort of Pula, near the archeological beauty of Nora.

If you want to start booking your flights, look for connections to Cagliari Elmas.

There are cheap direct flights from several European airports with EasyJet, Ryanair, TUIFly, Air Berlin, and probably a few more.
For example, you can fly to Cagliari from Paris, Frankfurt, Berlin, Cologne, Munich, Stuttgart, London, Edinburgh, Brussels, Madrid, Barcelona, Seville, Valencia, Venice, Rome, Milan, Turin, Basel, Geneva, Krakow, and probably more by the time you come.
If you book now, you should be able to get a good price.

The weather in Sardinia is mild. May is almost summertime. If you live in cold places like the North of the USA, Canada, Scandinavia, May in Sardinia is definitely warmer.
More logistics information will come.

Why Open Database Camp, and not Open SQL Camp like before?
The Open SQL Camp tradition has evolved since its inception in 2008. It has now become a gathering of database professionals and enthusiasts, not necessarily identifiable with the SQL constraint.
So, the conference welcomes everyone who deals with open databases, regardless of the languages used to interface them.

Stay tuned for more info. In the meantime, you can discuss this matter in the opensqlcamp Google Group.