GenieDB is building a database with global distribution as its core thesis. It is no secret customers demand near-instantaneous and highly reliable service, and that they are becoming more globally dispersed than ever before. We believe that data custodianship must ultimately be moved to the “edge of the web” where it can be dynamically managed in order to improve user experience, optimize network/hardware utilization and reduce TCO. A single datacenter hosted database and application stack runs afoul of this fundamental thesis in a number of ways. In this article we will focus on the issue of improving response time for users even when they are globally distributed. This is simply a matter of physics and how long it takes to transmit a packet between the two locations. No amount of application tuning can overcome this obstacle.

The obvious solution is to have multiple copies of the …

By last Friday morning the open bugs count raised above 150 mark and we managed to take it down to under 25 by the end of the day, thanks to the dedicated effort by the team. Among them, one was to make the Nodejs server run continuously. In our application we are using the Nodejs […]

I’ve been working on a data archival project over the last couple weeks and thought it would be interesting to discuss something a bit counter-intuitive. Absolutes are never true, but when getting rid of data, it’s usually more efficient to insert the data being kept into a new table rather than deleting the old data from the existing table.

Here is our example table from the IMDB database.

mysql> show create table title\G
*************************** 1. row ***************************
       Table: title
Create Table: CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `imdb_index` varchar(12) DEFAULT NULL,
  `kind_id` int(11) NOT NULL,
  `production_year` int(11) DEFAULT NULL,
  `imdb_id` int(11) DEFAULT NULL,
  `phonetic_code` varchar(5) DEFAULT NULL,
  `episode_of_id` int(11) DEFAULT NULL,
  `season_nr` int(11) …

A coworker came to me with a perplexing issue. He wanted to know why these two queries were not returning the same results:

mysql> SELECT COUNT(*) 
    -> FROM parent
    -> WHERE id NOT IN (SELECT parent_id FROM child);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (7.84 sec)

mysql> SELECT COUNT(*)
    -> FROM parent p
    -> WHERE NOT EXISTS(SELECT 1 
    ->                  FROM child c
    ->                  WHERE p.id = c.parent_id);
+----------+
| count(*) |
+----------+
|     5575 |
+----------+
1 row in set (2.95 sec)

At first (and second, and third) glance these two queries look identical. It obviously is an exclusion join and because the MySQL optimizer is what it is, I decided to rewrite it as a LEFT JOIN to see what results came back:

mysql> SELECT …

This is the second and final part of my notes from the MySQL conference. In this part I'll focus on the technical substance of talks I saw, and didn't see.

More than ever before I was a contributor rather than attendee at this conference. Looking back, this resulted in seeing less talks than I would have wanted to, since I was speaking or preparing to speak myself. Sometimes it was worse than speaking, for instance I spent half a day picking up pewter goblets from an egnravings shop... (congratulations to all the winners again :-) Luckily, I can make up for some of that by going back and browse their slides. This is especially important whenever 2 good talks are scheduled in the same slot, or in the same slot when I was to speak. So I have categorized topics here along various axes, but also along the "things I did see" versus "things I missed" axis.

My own talks

…

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]’? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2;

This query uses the INNODB_SYS_TABLES, …

Note: I’ve decided not to use Veewee due to silly compatibility issues for now.

Quoting from Vagrant’s web site:

Vagrant is a tool for building and distributing virtualized development environments. By providing automated creation and provisioning of virtual machines using Oracle’s VirtualBox, Vagrant provides the tools to create and configure lightweight, reproducible, and portable virtual environments.

A complementary technology called Veewee makes building VirtualBox VMs easier by automating away a lot of manual steps. Marius Ducea has a great blog post on how to use it.

My observations:
1. According to Vagrant’s web site, it should work on Windows. I’ve …

I was doing some research on Percona Server, and came across this great tip by Baron: if you are using Oracle’s MySQL and want to test out and learn new/improved features that are present in Percona Server, you can just stop the mysqld instance, extract Percona Server binary from its rpm/deb package or tarball file, swap the binary, and do a successful restart. You can then do a test drive, kick the tires, learn and observe to your heart’s content. Swap the original mysqld back after you are done, as necessary.

I tested it and it worked great for me. Specifically, I did the following:
1. sudo /etc/init.d/mysql(d) stop
2. sudo cp /usr/sbin/mysqld /location/mysqldFromOracleOrWhatever
3. sudo cp /perconaBinaryDirectory/mysqld /usr/sbin/mysqld
4. sudo /etc/init.d/mysql(d) start
5. Test …

Usually people do this around New Year, I will do it in February. Actually, I was inspired to do this after reviewing all the talks for this year's MySQL Conference - what a snapshot into the state of where we are! It made me realize we've made important progress in the past year, worth taking a moment to celebrate it. So here we go...

Diversification

In the past few years there was a lot of fear and doubt about MySQL due to Oracle taking over the ownership. But if you ask me, I was more worried for MySQL because of MySQL itself. I've often said that if MySQL had been a healthy open source project - like the other 3 components in the LAMP stack - then most of the NoSQL technologies we've seen come about would never have been started as their own projects, because it would have been more natural to build those needs on …

Sometimes, I make mistakes. It’s true. That can be difficult for us Systems Engineering-types to say, but I try to distance myself from my ego and embrace the mistakes because I often learn the most from them. ..Blah, blah, school of hard knocks, blah, blah…. Usually my mistakes aren’t big enough to cause any visible impact, but this one took the site out for 10 minutes during a period of peak traffic due to a confluence of events.

Doh!

Here is how it went down…

We have an issue where MySQL table statistics are occasionally getting out of whack, usually after a batch operation. This causes bad explain plans, which in turn cause impossibly slow queries. An ANALYZE TABLE (or even SHOW CREATE INDEX) resolves the issue immediately, but I prefer not get woken up at 4AM by long running query alerts when my family and I are trying to sleep. As a way to work around the issue, we decided to disable InnoDB automatic …