Posted on 3 Comments

Importing a file dumped from MySQL with mysqldump into drizzle

As a big fan of new technology, we try to keep up to date with what’s happening in the industry. As such, I decided to start using drizzle on my development machine since they announced GA this week.
First exercise: import a file dumped from a MySQL server I don’t have access to into drizzle. Normally, you can use drizzledump on the mysql server and make it dump a drizzle compatible file. Not in this case, so I decided to sed my way through the various errors. Not pretty, and I hope that at some point we’ll have a tool that can convert a mysqldump into a drizzle compatible file, but it works for now.
Here’s what I had to do. Note that this is by no means complete or comes with any guarantees, it’s just a starting point.
# This file started by setting a SQL_MODE. That doesn't exist in 
# drizzle, so we comment it out
sed -i "s/^SET SQL_MODE/#SET SQL_MODE/g" mysqldump.sql 

# The create database statement set a default character set. 
# Everything in drizzle is UTF8, so let's lose it!
sed -i "s/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci//g" mysqldump.sql 

# The table definitions mentioned a default character set. 
# Everything in drizzle is UTF8, so let's lose it!
sed -i 's/DEFAULT CHARSET=utf8//g' mysqldump.sql 

# No MyISAM except for temporary tables, so away with it.
sed -i 's/ENGINE=MyISAM//g' mysqldump.sql 

# Invalid timestamps are not accepted in drizzle, so this should be a null 
# value. Since some of the columns in this file are actually NOT NULL defined, 
# for now I just set those dates to 1970. UGLY, but works for me. Don't do this 
# on anything that will ever go anywhere near production though!
sed -i "s/'0000-00-00/'1970-01-01/g" mysqldump.sql 

# tinyint doesn't exist anymore, so just replace with integer. Note that you'll 
# have to do this for all data types that no longer exist in drizzle
sed -i "s/tinyint(.*)/integer/g" mysqldump.sql
Hope this helps others!
Posted on 3 Comments
Posted on

A day in the life of Datacenter Disasters

Open Query currently hosts a large part of our infrastructure at Linode. We are extremely happy with their performance, stability and support. Unfortunately any chain is only as strong as it’s weakest link. This week, there was a major thunderstorm near the Hurricane Electric datacenter (anyone else think that name is funny in combination with the event in case?) in fremont and through a massive powersurge, most of HE’s datacenter lost power. Among the Linodes affected in our infrastructure were all of the machines involved in our MMM setup.

The masters came back up before the monitor, which is around the time I was alerted. Logging in, I noticed replication was broken on one of the masters, but the other master seemed healthy. Since the monitor was not up and it seemed like it could potentially be hours before it would, I decided it was time for manual action. Since our MMM setup doesn’t have slaves currently, I decided a good option would be to mimic MMM and move the virtual IP to the healthy server.

I executed the following manual commands to make the desired changes:

$ ip addr add <virtip> dev eth0
$ /usr/sbin/arping -I eth0 -c 5 <virtip>

That brought all our applications back online, which was the desired effect. I manually fixed replication by repositioning the masters.ย A while later, the monitor came up and automatically took over, bringing everything back to normal.

Everything went well, but it wasn’t until the next morning I realised there was a possible flaw in my logic (that din’t effect us, but I wanted to blog about it to make others realise): When replication stopped, master A was active. My commands above made master B the active master. Now, in theory it is possible that writes were sent to master A after replication broke, and commands that were sent to master B would presume those writes were executed there which they were not as replication didn’t execute them. This is one of those niche occasions where data-drift can occur without noticing it.

My recommendation is to not do what I did unless you are very certain your setup doesn’t suffer from this potential problem. If you do decide to use this trick however, make sure to use the maatkit mk-tablecheck and mk-tablesynch when all is well again to check for (and correct!) data drift.

Posted on
Posted on

Report from Barcamp Johor Bahru

This weekend, I decided to attend BarcampJB pretty last minute. Lucky for me, barcamps are made for chaotics like me, so it was no problem at all. I found some friends that live here in Kuala Lumpur who I drove down to JB with (JB is around a 5 hour drive from KL, we did it in 3.5 ๐Ÿ™‚ ).

The camp was very interesting. Because JB is on the border with Singapore, there’s a good crossover between Malaysian and Singaporean techies.

I decided to go all out and give three talks on Saturday: First up was the MMM talk I’ve given at a few conferences before. All went well, and later on in the day some people approached me for more in-depth questions. It still seems that people have this idea in their head that they somehow need MySQL Cluster when there is more then one machine involved. When I explain them that that is very rarely the case and they can achieve what they want with MMM as well, they are often happy to hear that.

My next talk was more of a personal development one. People keep asking me here where I am from. When I explain to them that I’ve been location independent for the last 3 years, they are usually very eager to find out how I pull that off. I decided to summarise my experiences and put them in a talk. This talk was very well attended and I loved giving it. Most of the attendants were young techies, they are usually in a perfect position to do something very similar to what I’m doing.

The last talk was a lightning talk on Zabbix, the Open Source monitoring system we use at Open Query. Quick, and dirty, but effective.

Other interesting talks I attended were on breeze, an online banking application made for Standard Chartered bank that looks very slick and usable (If anyone from my bank is reading this: get with the program and fix our banking application to enter the 21st century please ๐Ÿ˜‰ ).

Conary and Foresight Linux were interesting as well. Conary (the package management system in Foresight Linux) is not quite mature yet, but definitely a very interesting technology. I was interested to hear about it and hope to see it become more mainstream in the future.

Daniel Cerventus gave a good lightning talk on what not to do as a startup. The main message was to just do it, and not wait for grant money or VC’s. Some solid tips as well, one of them being to run your potential name through Namechk, a handy potential username checker for many services.

There was obviously also a lot of networking and we went for a foot massage at the end of the day. Funny fact: I was the only one to stay awake through the massage (Even though I am narcoleptic), while two of my ย friends (who I won’t name here ๐Ÿ˜‰ ) snored all the way through it ๐Ÿ™‚

All in all another succesful tech event in Malaysia. Definitely one of the many reasons I love living here!

Posted on
Posted on 5 Comments

Mmm, what an interesting week

I have been very busy here in Malaysia this week.ย On thursday, I was asked to do a MySQL University session on MMM. The preparation was very stressful. There was no good wifi to be found until literally a few hours before the session (Big thank you to Gurdip at APIIT for providing a space and exceptional help!). On top of that, dimdim, the software used by MySQL for their sessions doesn’t seem to want to work on Linux (particularly the speaker part). I ended up using a laptop borrowed from APIIT with Vista and IE. Feels kind of counter-intuitive for a company in the FOSS business.

The session went very well and here is the resulting recording of the MMM talk on the mysqlforge page.

But that wasn’t the end of the MMM-promotion week:ย I happened to be allowed to present at the foss.my conference in Kuala Lumpur pretty last minute. At first I was going to do an updated version of the talk I gave at Froscon in August, but I was asked to do a tutorial session of 3 hours. I had never done anything like that, but I am always up for a challenge ๐Ÿ™‚

Again, preparation was a bit stressful. I didn’t know how many people to expect and it wasn’t clear if I would achieve getting running MMM clusters up in 3 hours. Well, I was underestimating my own capabilities apparently. Almost 100 people showed up, most of them without a laptop. I was surprised at that and explained them that it was probably not going to be so interesting for them. Again, I was wrong. While the laptop-owners prepared their laptops, I used my time to explain to everyone what MMM is, and how it works. Then we set up the laptops, solving all the problems we met on the beamer that we had a user connected to.

In the end we managed to set up 2 clusters within exactly 3 hours. Only 6 (almost 7) ‘servers’ participated in that end-result, for various reasons the rest was not possible. Still, it was a very good result and the attendees were visibly very happy.

If you hadn’t noticed yet, I’m a big fanboy for MMM and thinks this project needs/deserves a lot more visibility. It really solves a bunch of problems many MySQL admins struggle with, while providing a simple, cheap HA solution. This week has been very good for the promotion of MMM.

Along the way I also discovered that I really love doing this workshop and I hope to do many more like this. On that note: if you know of any conferences or meetings in the Asia Pacific area in the upcoming months, let me know and I’ll try to be there with either a presentation or a workshop!

Posted on 5 Comments
Posted on

MySQL University session Oct 22: Dual Master Setups With MMM

This Thursday (October 22nd, 13:00 UTC), Walter Heck (of Open Query) will present Dual Master Setups With MMM. MMM (Multi-Master Replication Manager for MySQL) is a set of flexible scripts to perform monitoring/failover and management of MySQL master-master replication configurations (with only one node writable at any time). Session slides (PDF).

The toolset also has the ability to read balance standard master/slave configurations with any number of slaves, so you can use it to move virtual IP addresses around a group of servers depending on whether they are behind in replication. For more
information, see mysql-mmm.org.

For MySQL University sessions you point your browser here. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don’t have to.

Posted on
Posted on 2 Comments

Looking for MySQL-ish things around Thailand/SE Asia

I recently relocated to Khon Kaen, Thailand for 3 months. Since I can do my Open Query work from anywhere (as long as there is a decent internet connection with not too much latency) that is entirely no problem. It is a nice city, far from the business/busyness of Bangkok. Almost noone speaks english here, which makes even the simplest task an adventure (imagine miming an umbrella in front of 10 giggling store employees)

Now that I have gotten settled in Khon Kaen, I am looking for fun work-related things to do around here: conferences, user groups, interesting projects etc. Anything goes really, even if it is not so much work-related. I also have free time on my hands ๐Ÿ™‚

I am quite likely going to do a tutorial on MMM at FOSS.my in Kuala Lumpur. It’s still waiting on a few things since I announced my availability super-late, but I have good hope. I am turning to our readers to ask for suggestions of places/people that I can visit while here. Suggestions anyone?

Posted on 2 Comments
Posted on

FrOSCon 2009: 1 down, 1 to go

So, I’m in my hotel room and I got some sleep. Now, it is 2:15 am and Icouldn’t sleep for some reason. PRobably has something to do with me being narcoleptic, as weird as it sounds ๐Ÿ™‚ Since I can’t sleep for a bit anyway, I figured writing a blog summarising the first day of FrOSCon would be a good idea.

Well, in one word: GREAT! In a few more: The first day was a big success. I started it out with a shift at the registration desk (I’m volunteering for FrOSCon as well). Since it was very early, most people that came in were actually exhibitors and speakers, and they all had to be at the VIP desk. At first, the door wouldn’t open. Later it turned out that the sensors thought we were to close to it. Claustrophobic doors are interesting.

Then, there was breakfast for the people with VIP access. Basically that was anyone who was not just a visitor. I helped out for a bit and then went back to my hotel room to catch up on some sleep and practice my talk a bit. The practice run was done in a staggering 57 minutes, which was exactly enough since I had 1 hour to finish. I went back to the conference and saw some talks. I saw the tail-end of Sheeri’s talk on ‘a better mysqltuner’ and then the start of Jan Kneschke’s talk on MySQL proxy. It was interesting, but my nerves made me exit that prematurely so I could be sure to be in time for my own talk.

When I walked into the conference room I was giving my talk in a half hour in advance, it was already empty because the previous talk was finished early. This gave me a decent amount of time to finish setting up.

I decided to use the wiimote that the CentOS guys were offering as a presentation remote. That turned out to be a very good decision, as it gives you something to hold in your hands so you hve soemthing to do with them. It also gives you force feedback and led information on how much time you have used for your talkso far. Pretty awesome!

The talk itself went pretty well, and people asked good questions afterwards. One guy came up to me an I thought he was asking me a question, only have hearing him sya he wrote this stuff. It took me a while to realise that it was Pascal Hoffman, the guy that wrote all of MMM 2. I hung out with him and my friend Nicolas from Rotterdam at the social event all night, speaking about many things related to MMM.

That is actually what these conferences are so great for: the human interaction aspect. You get to meet the humans behind an irc-name or blog, and talk to them directly, (almost) always a pleasant experience.

Before the social event I went to the PBXT talk in the OpenSQLCamp room, but i was too tired to focus on it with the attention it deserved. Kind of ashame, because i think PBXT is an interesting project.

Looking forward to tomorrow, in particular to the shootout in the OpenSQLCamp room between experts from various open source databases. I guess I should go back to sleep now, so I am full of energy tomorrow. Walter signing off!

PS. I’ll put the slides to my talk online as soon as possible.

Posted on
Posted on

Getting ready for FrOScon 2009

I arrived yesterday in St. Augustin, near Bonn in Germany. After a good day of hitchhiking (weather is beautiful here) I stayed with my Pakistani Couchsurfing host and we had an extremely interesting evening talking about the gigantic cultural differences between western civilization and Pakistani civilization. It beats staying in a hotel by about a million points ๐Ÿ™‚

This morning I headed to the FrOScon HQ at the fachhochschule to help out with whatever was needed. Turns out that was a bit premature (misunderstanding on my part), so I have had some time to catch up on mail and give some more attention to my talk on Saturday. I’ll be helping out throughout the and the whole day tomorrow with things though.

I’ll be talking about MySQL MMM, a project that I have invested quite a bit of time in getting to know. My talk will outline what MMM is, what it’s not and an example of our setup at Open Query. It’s a full hour long, so it should be very interesting to be able to go into that much detail.

If you are near St. Augustin, make sure to come by for Froscon, as it’s schedule has some very interesting talks and you’ll also have a good chance to meet fellow MySQL-geeks in the OpenSQLCamp dev-room.

Posted on
Posted on 1 Comment

On partial indexes for string columns

After reading Fernando Ipar’s interesting post on partial indexes for string columns, there were two things I wanted to note:

First, this trick works quite well, but only if your like clauses only ever use the wildcard on the right hand side (or not at all). MySQL will not be able to use the index if the like contains a wildcard on the left.

Consider the following table definition:

mysql> show create table people\G
*************************** 1. row ***************************
Table: people
Create Table: CREATE TABLE `people` (
`person_id` int(15) NOT NULL default '0',
`username` varchar(255) default NULL,
`email` varchar(255) default NULL,
PRIMARY KEY (`person_id`),
KEY `people_username` (`username`(5))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Now, see the following queries:


mysql> explain select username from people where username like 'jo%';
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+-------------+
| 1 | SIMPLE | people | range | people_username | people_username | 8 | NULL | 394 | Using where |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> explain select username from people where username like '%jo';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | people | ALL | NULL | NULL | NULL | NULL | 128928 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)


mysql> explain select username from people where username like '%jo%';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | people | ALL | NULL | NULL | NULL | NULL | 128928 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

As you can see, the first one nicely uses the index. The second and third though, cannot use it at all.
Now, we add an index on the full username field:


mysql> create index people_username_full on people(username);
Query OK, 128928 rows affected (6.72 sec)
Records: 128928 Duplicates: 0 Warnings: 0

Then, rerunning the queries we see the result changing:


mysql> explain select username from people where username like 'jo%';
+----+-------------+--------+-------+--------------------------------------+----------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+--------------------------------------+----------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | people | range | people_username,people_username_full | people_username_full | 258 | NULL | 143 | Using where; Using index |
+----+-------------+--------+-------+--------------------------------------+----------------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)


mysql> explain select username from people where username like '%jo';
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | people | index | NULL | people_username_full | 258 | NULL | 128928 | Using where; Using index |
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)


mysql> explain select username from people where username like '%jo%';
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | people | index | NULL | people_username_full | 258 | NULL | 128928 | Using where; Using index |
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

Notice how all queries are now using the index. Even the first query now uses the longer but more detailed index.

The second suggestion I wanted to make is one that is exploiting this index option for right-oriented ‘like’ searches. A common use is for email addresses. Often, they are being searched by e.g. domain name. If you add an extra field to your table that stores the reverse of the email field, you can then use a partial index to index the email field from the right. You will need the MySQL function REVERSE() for this:


mysql> alter table people add email_reverse varchar(255) null;
Query OK, 128928 rows affected (4.63 sec)
Records: 128928 Duplicates: 0 Warnings: 0

mysql> desc people;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| person_id | int(15) | NO | PRI | 0 | |
| username | varchar(255) | YES | MUL | NULL | |
| email | varchar(255) | YES | | NULL | |
| email_reverse | varchar(255) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> update people set email_reverse = REVERSE(email);
Query OK, 128928 rows affected (1.36 sec)
Rows matched: 128928 Changed: 128928 Warnings: 0


mysql> create index people_email_rev on people(email_rev(10));
Query OK, 128928 rows affected (6.72 sec)
Records: 128928 Duplicates: 0 Warnings: 0

Now, you can execute queries with right handed ‘like’ clauses using the partial index:


mysql> explain select username from people where email_reverse like 'moc.oohay%';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | people | range | people_email_rev | people_email_rev | 13 | NULL | 1 | Using where |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select username from people where email_reverse like REVERSE('%yahoo.com');
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | people | range | people_email_rev | people_email_rev | 13 | NULL | 1 | Using where |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Posted on 1 Comment
Posted on

The king of traffic spikes?

Unless you have been hiding under a rock, you have one way or the other heard about the king of pop passing away. This is in my opinion a great loss to the world, as this man was like a god to me when I was a little boy.
Besides it being really sad that he died, it is also a good test for the systems us techies take care of: the web has seen some of it’s biggest spikes in traffic on june 25th.
That is not over yet though. Next tuesday there will be a memorial service in LA, which will also be live broadcasted on the web. For any system administrator and/or DBA responsible for a site that is news or social media related, this will be yet another good real-life load test.
Rest assured that many people will find their favorite news site or social media application to get the latest on this event.
Now might be a good time to make sure you are ready for a spike like that, and if you were planning a day off for tuesday, you might want to inform your replacement for the day about your phone number. Then again: if your design is dodgy, you are in trouble anyway.

Posted on