On the road again - FOSSAsia

On the road again - FOSSAsia

It has been a few busy months until now. I have moved from Italy to Thailand, and the move has been my first priority, keeping me from attending FOSDEM and interacting with social media. Now I start catching my breath, and looking around for new events to attend. But before I get into this, let’s make a few things clear:

• I am still working for Continuent. Actually, it’s because of my company flexibility that I could move to a different country (a different continent, 6 time zones away) without much trouble. Thanks, Continuent! (BTW: Continuent is hiring! )
• I am still involved with MySQL activities, events, and community matters. I just happen to be in a different time zone, where direct talk with people in Europe and US need to happen on a different schedule.

I am already committed to attend Percona Live MySQL Conference & Expo in Santa Clara, where I will present a tutorial on MySQL replication features and a regular session on multi-master topologies with Tungsten.

But in the meantime, Colin encouraged me to submit talk proposals at FOSSAsia, and both my submissions were accepted. So, at the end of February I will be talking about some of my favorite topics:

• Easy MySQL multi master replication with Tungsten
• Data in the cloud: mastering the ephemeral

The exact schedule will be announced shortly. I am eager to attend an open source event in Asia. It’s been a long time since I went to a similar event in Malaysia, which was much pleasant.

North East Linux Fest and Open Database Camp - Boston, March 16-17 2013

On Thursday, I will travel to Boston, MA, to attend the Northeast LinuxFest, which includes also an edition of the Open Database Camp. The events will be at one of my favorite places on earth: The Massachusetts Institute of Technology, a.k.a. the MIT. Every time I speak at an event there, I feel at home, and I look forward to be there once more.

The Open Database Camp is organized, as usual, with the formula of an un-conference, where the schedule is finalized on the spot.

There are a few ideas for sessions. I have proposed two of the topics I am most familiar with:

• MySQL 5.6 replication - features and usability
• Creating multi-master clusters with Tungsten replicator

In addition so seeing the MIT again, I will be also pleased to meet colleagues and friends from all over the place. If you happen to be nearby, let's get together!

MySQL Sandbox at the OTN MySQL Developers day in Paris, March 21st

On March 21st I will be in Paris, to attend the OTN MySQL Developers Day. Oracle is organizing these events all over the world, and although the majority are in the US, some of them are touching the good old European continent. Previous events were an all-Oracle show. Recently, the MySQL Community team has been asking for cooperation from the community, and in such capacity I am also presenting at the event, on the topic of testing early releases of MySQL in a sandbox. Of course, this is one of my favorite topics, but it is quite appropriate in this period, when Oracle has released a whole lot of preview features in its MySQL Labs. Which is another favorite topic of mine, since I was the one who insisted for having the Labs when I was working in the community team. It's nice to see that the labs are still in place, and being put to good use.

Speaking of sandboxes, I was making some quick tests yesterday, and I installed 15 sandboxes at once (all different versions, from 5.0.91 to 5.6.5). Installing a single sandbox, depending on the version, takes from 5 to 19 seconds. Do you know how long it takes to install 15 sandboxes, completely, from tarball to working conditions? It takes 19 seconds. How's so? It's because I have been working at a large project where we are dealing with many replicated clusters spread across three continents. Administering these clusters is a problem in itself, and so we are using tools to do our work in parallel. At the same time, using a host with a fast 16 core CPU I can install many sandboxes at once. It's a real joy to see software behaving efficiently the way it should! It works so fast, in fact, that I found a race condition bug. If you install more than one sandbox at once, the MySQL bootstrap process may try to open the same temporary file from two different servers. That's because I did not indicate a dedicated temporary directory for the bootstrap (I was using one only for the installed sandbox). When this happens, you may find that instead of 15 sandboxes you have installed only 9 or 11. So I fixed the bug, by adding --tmpdir to mysql_install_db, and now you can install more than one sandbox in parallel.

MySQL and Friends schedule at FOSDEM 2012

The MySQL DevRoom at FOSDEM is ready. The schedule has been voted. Thanks to all who have participated. Now, let's make sure that the event is successful. The schedule is juicy, and not only because I have three talks in it!

Sunday 2012-02-05
Event	Speaker	Room	When
All you need to know about migrations and you never dared to ask	Ralf Gebhardt	H.1309	09:05-09:30
Sphinx User stories	Stéphane Varoqui	H.1309	09:35-10:00
MySQL HA reloaded - old tricks and cool new tools to guarantee high availability to your MySQL Servers	Ivan Zoratti	H.1309	10:00-10:25
MariaDB 5.3's query optimizer: taking the dolphin to where he's never been before	Sergey Petrunya	H.1309	10:30-10:55
How to offload MySQL server with Sphinx	Vladimir Fedorkov	H.1309	11:00-11:25
** Build simple and complex replication clusters with Tungsten Replicator	Giuseppe Maxia	H.1309	11:30-11:55
Cluster internals	Ralf Gebhardt	H.1309	12:00-12:25
Optimising SQL applications by using client side tools	Mark Riddoch	H.1309	12:30-12:55
** MySQL Replication 101	Giuseppe Maxia	H.1309	13:00-13:25
Choosing Hardware for MySQL	Kenny Gryp	H.1309	13:30-13:55
Replication features of 2011: what they were, how to get and how to use them	Sergey Petrunya	H.1309	14:00-14:25
** MySQL creatively in a sandbox	Giuseppe Maxia	H.1309	14:30-14:55
Case Study: La Poste - Real Time, High Volume Data Warehousing Using MySQL & InfiniDB	Stéphane Varoqui	H.1309	15:00-15:25
Sphinx performance top secret	Vladimir Fedorkov	H.1309	15:30-15:55
Managing MySQL with Percona Toolkit	Frédéric Descamps	H.1309	16:00-16:25
Data Warehousing with MySQL	Ivan Zoratti	H.1309	16:30-16:55

UPDATE The schedule has changed. Speakers with more than one talk have been asked to give up one. Now I have two talks instead of three.

The Open Source Events Calendar

Kudos to Lenz, who, acting on an idea launched by Ronald Bradford some time ago, has put together a comprehensive calendar of open source events. Most of them are somehow related to the MySQL ecosystem, but there is no limitation to what the calendar contains. Here is the announcement, with the instructions to use and contribute to the calendar.

In addition to informing you about the events, this calendar does also tell you when a deadline is approaching. Using this tool, you won't miss a call for participation anymore.
You can simply subscribe to the iCal feed (it's a Google calendar) or see it online.
And of course, we want to improve the calendar. Feel free to submit new events using the event submission form.

We're looking into ways of improving the service. It would be nice to have a widget to show on your blog. Using Google APIs, it's easy to create such a widget, but the events are shown in insertion order, rather than chronological order. If anyone knows how to fix this issue, please contact me or Lenz.

Sponsoring OpenSQLCamp

The next OpenSQLCamp will be held in Portland, Oregon, USA. It is being organized by Eric Day, well known to the open source community for his active and productive participation to several projects (especially Drizzle and Gearman).

The event is public and free. Therefore, it needs public sponsoring. I don't know yet if I can attend, but I have already donated something to the organizers, and I am officially a sponsor. You can be one too. Simply go to the sponsors page and donate a minimum of $100 as in individual or $250 as an organization.
And of course, if you plan to participate, register yourselfand eventually propose a session.
OpenSQLCamp is a fun, equal level event. If you have something to say, write a proposal, and the other participants will tell you if they want to hear it or not. Either way, you will learn something.

OpenSQLCamp democracy

We have seen this before. Actually, we got the idea from Drupal, where talk proposals are public, and the most voted ones get in the schedule. Nonetheless, it's a pleasure to see that a transparent voting system is accepted and used.

The OpenSQLCamp 2009 European edition, is under scrutiny. There are 27 session proposals, from which we will need to get 12 in the schedule.
The open voting is done via Twitter or the mailing list.
I have a good feeling about it. Since I am proposing a public talk, I must be prepared for public scrutiny. It's only fair that my proposal is evaluated by the same audience that will later decide to come see my presentation. It's an incentive to write a clear and compelling proposal. I can't get away with "I am well known. I will say something about replication."
If you are attending OpenSQLCamp at FrOSCon, let us know what you would like to see. Let the best proposals win.

Giving away one copy of MySQL Administration Bible in Madrid

I will be in Madrid on June 18-19, to participate to the Sun Open Communities Forum. I will have two presentations on my own, and I will be a guest speaker during Victor Jimenez's session. There will be some ancillary activities, among which a lunch with the MySQL community and a MySQL workshop.

During one of these activities, I will give away one copy of the MySQL Administrator’s Bible. If you are a MySQL enthusiast and you are in Madrid, that's an extra reason to attend the forum. For the ones who still don't know how good this book is, you can have a look at this Review.
During the forum, there will be an announcement about the MySQL community in Spain.

Joining the PHP BBQ in Hamburg and Kiel

There must be something that gets coders and barbecue together. In Australia, I was invited to a hackers barbecue, while at FrOSCon, the Saturday evening meal is a grill fest. It seems just right that the PHP coders in Germany are getting together with a BBQ Tour, starting on Monday, June 15, and touching seven German towns in one week.

I will join the happy campers on Saturday, June 20, in Hamburg, and finish off the tour in Kiel the next day.
The event is open. However, for logistic reasons, a registration is required in some of them. Check the schedule for more details.
Being informal events, we may or may not have presentations and demos, but being inter-geek meetings, there will be some fun and mutual learning for sure. I look forward for it. Most of my "aha" technical moments came during serendipitous social gatherings. Beer and food are great science conductors!
Follow FrOSCon on Twitter and OpenSQLCamp on Twitter for more updates.

Open Communities in Madrid - June 18-19

I am going to Madrid, Spain, to participate in the Open Communities Forum on June 18-19.
As usual with Sun open events, the agenda includes several actors in the open source arena, such as MySQL, Java, Open Solaris, with topics ranging from web development to mobile integration.
It looks promising, and I look forward to this event, which is my first one in Spain.
On the first day, I will do an introductory talk about MySQL, and a more technical workshop for advanced users. The second day will be mainly dedicated to meeting users in the area, but I will also do a guest appearance in a talk about database testing.

Another usability bug bites the dust

In MySQL 5.1.33 there is a fix for an apparently innocuous bug.
Bug #36540 CREATE EVENT and ALTER EVENT statements fail with large server_id.
This is a usability bug, that makes the DBA life unnecessarily hard. The reason for having a large server_id is because a DBA might want to use the IP address as server ID, to make sure that there are unique IDs, and to have an easy way of identifying the server through the IP.
All is well until you mix the server_id assignment with event creation:

 select version();
+-----------+
| version() |
+-----------+
| 5.1.32    | 
+-----------+
1 row in set (0.00 sec)

 set global server_id =inet_aton('192.168.2.55');
Query OK, 0 rows affected (0.00 sec)

 select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  3232236087 | 
+-------------+
1 row in set (0.00 sec)

 create event e1 on schedule at now() + interval 60 second do set @a = 1;
ERROR 1538 (HY000): Failed to store event body. Error code 1 from storage engine.

Oops! the event table has a problem. The "originator" column is designed as a signed INT (2^31 -1), while the server_ID can take values up to 2^32 - 1.
In the latest version, soon to hit the market, the problem is solved.

select version();
+-----------+
| version() |
+-----------+
| 5.1.33    | 
+-----------+
1 row in set (0.01 sec)

set global server_id =inet_aton('192.168.2.55');
Query OK, 0 rows affected (0.00 sec)

select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  3232236087 | 
+-------------+
1 row in set (0.00 sec)

create event e1 on schedule at now() + interval 60 second do set @a = 1;
Query OK, 0 rows affected (0.00 sec)

Something to know about the event scheduler and replication

MySQL 5.1 has been GA for 4 months now, and I am sure that many people have been using the event scheduler. There is something that you must know if you are using the event scheduler in a replicated environment. The important thing to know is that, when you use the events in replication, by default the event is active on the master only. The event creation is replicated, but the event on the slaves is not active

The reference manual explains it in detail.
There are two things that you must remember, if you are going to promote a slave to master:

• The event_scheduler variable is not replicated. If you set it on the master, the slaves don't know anything about that.
• The event creation is replicated, but disabled. Even if you set the event_scheduler variable on the slave, the events won't start.

An example will clarify it. Let's create an event on the master:

$ ./m -e "create event e1 on schedule at now() + interval 1 minute do create table et1 (i int);"
$ ./m -e "set global event_scheduler=1";

And then request the status of the event scheduler.

$ ./m -e 'select @@event_scheduler';
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
./m -e 'show events from test\G'
*************************** 1. row ***************************
                 Db: test
               Name: e1
            Definer: msandbox@%
          Time zone: SYSTEM
               Type: ONE TIME
         Execute at: 2009-03-24 00:25:41
     Interval value: NULL
     Interval field: NULL
             Starts: NULL
               Ends: NULL
             Status: ENABLED
         Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
 Database Collation: latin1_swedish_ci

Everything seems to be in order on the master. Let's see the situation on the slave.

./s1 -e 'select @@event_scheduler';
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+

That was to be expected. We know that SET commands are not replicated. What about the event creation? This should be replicated, and indeed it is:

./s1 -e 'show events from test\G'
*************************** 1. row ***************************
                 Db: test
               Name: e1
            Definer: @
          Time zone: SYSTEM
               Type: ONE TIME
         Execute at: 2009-03-24 00:25:41
     Interval value: NULL
     Interval field: NULL
             Starts: NULL
               Ends: NULL
             Status: SLAVESIDE_DISABLED
         Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
 Database Collation: latin1_swedish_ci

It's easy to check that the event effects are replicated. After one minute, we list the tables on both master and slaves, and table "et1" was created.

./m -e 'show tables from test'
+----------------+
| Tables_in_test |
+----------------+
| et1            | 
+----------------+

./s1 -e 'show tables from test'
+----------------+
| Tables_in_test |
+----------------+
| et1            | 
+----------------+

But here's the tricky part. The event is created, but disabled. This is the desired behavior, because the effects of the events are replicated and we don't want the same operation executed twice on the slaves.
The only drawback of this situation is that you need to change "SLAVESIDE_DISABLED" into ENABLED when you promote a slave to master. You need to act with administrator powers, because a ALTER EVENT won't achieve the desired result. You need, instead, to update the mysql.event table, as explained in the manual.
It is not a big deal, but you must remember the above two issues when promoting a slave.

Q&A; on MySQL 5.1

Listening to Sheeri's presentation on MySQL 5.1, I saw that there are a few questions left unanswered. I am listing here some of the questions that I found interesting, plus a few from an early webinar on the same topic.

Q: does Partitioning physically split data?

A: No. Some engines (MyISAM, Archive) do a physical split, but this is not necessary, as you see if you apply partitioning to a InnoDB table. Partitioning is a logical split of data, for easy retrieval. It is completely transparent to the user.

Q: Can you set partitions to different servers?

A: No. Partitions are logical parts of one table within one server. Partitioning through the Federated engine is not supported.

Q: How efficient are Row-Based Replication operations compared to Statement based ones?

A: RBR is faster when the insert or update is the result of an expensive operation. Otherwise, the efficiency for insertion and deletion is roughly equivalent. Updates on multiple records are usually more expensive with Row-Based Replication.

Q: Is the event scheduler polluting the Error Log?

A: yes, unfortunately. But it has been fixed in 5.1.31 (See also Bug#38066. As you can see from the discussion in the bug report page, it was object of a long and intense discussion.

Q: Can you send email through the Event Scheduler?

A: No. But you can integrate it with a hack using MySQL Proxy through Federated tables.

Q: Is there an equivalent to SHOW FULL PROCESSLIST from the INFORMATION SCHEMA?

A: Yes. Actually, SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST is equivalent to SHOW FULL PROCESSLIST. See the manual

Q: Are partitions supported in replication?

A: Yes. Partitions are fully supported in replication. The only problem you may have is when using the "DATA DIRECTORY" and "INDEX DIRECTORY" clauses, if the slave does not have the same directory structure and OS privileges as the master.

Q: Is the event scheduler supported in replication?

A: Yes. The event definition is replicated but left inactive, and the effects of the event scheduler are replicated as any other statement. When promoting a slave to master, it is necessary to manually activate the events.

Q: What happened to RENAME DATABASE? Why is not available anymore?

A: RENAME DATABASE was a command implemented in the early stages of MySQL 5.1, for the specific purpose of helping the upgrade script to set the database name with the appropriate charset. It had a deadly side effect, though. It removed all the objects associated with the database. Thus, it was removed and renamed in such a way that nobody would use it for simply renaming a database.

Q: How does the slow query log work with microseconds?

A: To catch slow queries with duration measured in less than 1 second, you can set the long_query_time global variable to a fractional value. E.g. set global long_query_time=0.5; will enable queries taking more than 1/2 second to be logged to the slow queries log.

Using the event scheduler to purge the process list

Two of the most common tasks for database administrators are cleaning the process list from unresponsive queries and remove idle connections that are filling the connection pool. Both tasks are related to poor usage of the database. In a perfect world, users would only run queries designed, tested, and benchmarked by the DBA or the project manager, and the application servers would never allocate more connections than planned.

But users are human, and an unpredictable amount of unplanned events can happen everywhere. When I was consulting, the above cases were quite common.
Before MySQL 5.1, the only method to clean up the process list was by hand, or using a cron job to do it from time to time.
MySQL 5.1 introduces the event scheduler, and now you can run scheduled stored routines without need of external applications.
I created two stored procedures that read the processlist and kill a process if the execution time is longer than 200 seconds or if a process is idle for longer than 200 seconds. The script with the stored routines and the associated events is in MySQL Forge.

drop procedure if exists purge_slow_queries;
drop procedure if exists purge_idle_connections;
drop event if exists auto_purge_slow_queries;
drop event if exists auto_purge_idle_connections;

delimiter //

create procedure purge_idle_connections()
deterministic
begin
   declare done boolean default false;
   declare max_time int default coalesce(@max_kill_time, 200);
   declare pid bigint;
   declare c cursor for
   SELECT id
   FROM information_schema.processlist
   WHERE command in ('Sleep')
        -- add more conditions here
        AND time > max_time;
   declare continue handler for not found
       set done = true;
   open c;
   set @q_kill = 'KILL ?';
   prepare q_kill from @q_kill;
   PURGELOOP: loop
       fetch c into pid;
       if done then
           leave PURGELOOP;
       end if;
       set @pid = pid;
       execute q_kill using @pid;
   end loop;
   deallocate prepare q_kill;
end//

create procedure purge_slow_queries()
deterministic
begin
   declare done boolean default false;
   declare max_time int default coalesce(@max_kill_time, 200);
   declare pid bigint;
   declare c cursor for
   SELECT id
   FROM information_schema.processlist
   WHERE state in ('executing')
        -- add more conditions here
        AND time > max_time;
   declare continue handler for not found
       set done = true;
   open c;
   set @q_kill = 'KILL ?';
   prepare q_kill from @q_kill;
   PURGELOOP: loop
       fetch c into pid;
       if done then
           leave PURGELOOP;
       end if;
       set @pid = pid;
       execute q_kill using @pid;
   end loop;
   deallocate prepare q_kill;
end//

delimiter ;

create event auto_purge_idle_connections
   on schedule every 10 second
   do call purge_idle_connections();

create event auto_purge_slow_queries
   on schedule every 10 second
   do call purge_slow_queries();

Notice that you can disable an idle connection by setting the variable interactive_timeout, but this method allows you to be more precise. For example you can state that only idle connections to a given database should be killed, or only the ones from a given user.

UPDATE It looks like there is prior art in the same department. My colleague Matthew Montgomery beat me to it loooong time ago.

Using the event scheduler with OS commands

One of the major additions to MySQL 5.1 is the the event scheduler. It is an internal scheduler, which does not need any help from the operating system. As such, it works independently in every platform.
One drawback of this feature, though, is that it can't communicate with the operating system. i.e. the event scheduler can't read system files, can't send e-mail messages, store data into log files. It can only work within the database server. This is convenient for security, but it is quite limiting. Time for hacking!

In getting started with MySQL Proxy I showed an example of how to run shell commands from any MySQL client. Unfortunately, this method can't be used with the events, because events can't send queries to the outside. Or can they? Let's be creative, and combine Federated tables and MySQL Proxy:

drop table if exists t1, t1f;
create server fed
foreign data wrapper mysql
options (
host '127.0.0.1',
port 4040
database 'test',
user 'msandbox',
password 'msandbox'
);
create table t1 (
id int nt null, cmd varchar(250), primary key (id), key (cmd)
);
create table t1f (
id int not null, cmd varchar(250), primary key (id), key (cmd)
) engine=federated connection = 'fed/t1';

Table t1f is Federated, and it is accessed through the Proxy port (4040). Thus, every query directed to this table will be intercepted by MySQL Proxy, and then we can do what we want.
The Lua script associated with this Federated table is the following, which intercepts UPDATE statements directed to table t1, and sends the contents of the cmd column to an echo statement, using a OS call. (It's just a proof of concept)

function read_query(packet)
if packet:byte()  ~= proxy.COM_QUERY then return end
local query = packet:sub(2)
if query:match('UPDATE') then
    local tokens = proxy.tokenize(query)
    if tokens[10]['token_name'] == 'TK_INTEGER'
        and tokens[2]['text'] == 't1'
        and tokens[10]['text'] == '1' then
        print( " --- " .. query )
        os.execute('echo "executing ### ' .. tokens[6]['text'] .. ' ###"')
    end
end
end

In short, this script checks if we are updating table t1 (not t1f! The Federated engine accesses the base table) with id = 1, and if yes, uses the content of cmd as argument for an OS command.
Now, let's create and event and see what happens:

set global event_scheduler=1;
create event e1
on schedule every 10 second
do
update t1f set cmd=concat('do this ', now())
where id =1;

The result, as observed by the Proxy, is as follows:

--- UPDATE `t1` SET `cmd` = 'do this 2008-03-17 02:27:47' WHERE `id` = 1 LIMIT 1
executing ### do this 2008-03-17 02:27:47 ###
--- UPDATE `t1` SET `cmd` = 'do this 2008-03-17 02:27:57' WHERE `id` = 1 LIMIT 1
executing ### do this 2008-03-17 02:27:57 ###

QED!