Those who are familiar with me know I've a dream.

5 years ago I decided to leave a systems integrator where I was doing great. Why? I wanted to be in a company with the same growth prospects that Oracle had in the 80s. I dreamed to be in the Oracle of 30 years ago and, as time travel wasn't affordable, I decided to join MySQL AB to help expand the business in Europe, the Middle East and Africa.
A few years later my dream came true, but in a slightly different sense. Sun acquired MySQL and was later swallowed by


  [Read more...]

Standard MySQL is configurable such that a single master server can be clustered with a number of read-only slave servers. To enable this master-slave replication, master’s transaction logs are communicated to the slaves (log shipping). Log shipping is a form of asynchronous replication. Under this configuration, the data on the slave always remains behind the master, a condition referred to as slave lag or replication lag. The extent of the slave lag depends on workload, network bandwidth and network latency. Database reads can be served out of the slaves, assuming the application has been designed to tolerate the slave lag and requisite staleness of data (eventual consistency), which can at times be variable and opaque. MySQL master-slave replication offers the possibility of promoting a slave to become the new master

  [Read more...]

The next Helsinki MySQL User Group is set for Tuesday, February 19. Lari Pulkkinen from Arbitron Mobile will talk about their project adopting SSD disks for better MySQL performance. Yes, there are benchmarks included.

Note the changed location: Oracle office in Gräsantörmä 2, Espoo. We are glad to have Oracle Finland sponsoring the user group by taking turns as meetup host. Food and sauna will be available after the talk as is customary.

More details and signup at Meetup.com.

Wow! We at GenieDB have been working on a geo-distributed, multi-datacenter, relational database engine for some time. We’ve believed in this vision of providing distributed RDBMS/SQL database, but had to endure the NoSQL movement and other attempts at refuting the need for such a thing. One whitepaper and what a big difference it makes!

The Spanner whitepaper does just as good a job as any marketing speak of describing what we are after. “Even though many projects happily use Bigtable, we have also consistently received complaints from users that Bigtable can be difficult to use for some kinds of applications: those that have complex, evolving schemas, or those that want strong

  [Read more...]

Many thanks to all those who attended my talk at the Percona Live London 2012 conference!
I did put the location in the last slide, but just in case you missed the last slide (or missed my talk) you can find them here:
http://spil.com/perconalondon2012

I did receive a couple of questions afterwards (in the hallways of the conference) that made me realize that I forgot to clear up a couple of things.

First of all the essence of shifting the data ownership of a specific GID towards a specific datacenter and ensuring data consistency also means one Erlang process within that very same datacenter is the owner of that data. This does also mean this Erlang process is the only that can write to the data of this GID. Don’t worry: for every GID there should be a process that

  [Read more...]

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.

  [Read more...]

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`

  [Read more...]

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

  [Read more...]

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"

  [Read more...]

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

  [Read more...]

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

  [Read more...]

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

  [Read more...]

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

  [Read more...]

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

  [Read more...]

In early 2006 Paul Hurley (ideeli’s CEO) and I (Mark Uhrmacher, CTO) were thinking about a new business. We had the idea to create a community based around great deals for Women’s fashion products where we saw a great deal of potential for great content and product sales. Now, over five years later, we’ve realized much of that vision. Our business success has been chronicled over the years in several places (see here and here). Though we’re very proud of our achievements there, that isn’t what this blog is about.

Insatiable Demand is about a mostly untold story. Over the past five-plus years we’ve built a phenomenal technology platform and team. From two people and three servers to a

  [Read more...]

Last week I had the opportunity to attend the Surge 2011 conference in Baltimore, MD.  I thought it was a great conference, and I’m already looking forward to next year.  I’m sure there’s already a plethora of great blog posts on Surge, but here’s just some thoughts based on my experience.

In no particular order:

EC2 has changed the world, everybody hates EC2

I don’t think I heard a presentation where somebody didn’t use EC2 and the other assorted AWS products.  Amazon (as far as I know) was not represented at the conference, and it seemed awkward for them to not be there (to me, at least).  This conference was full of

  [Read more...]

Update: I won't be in Moscow after all. I was denied visa on grounds that my passport is beginning to fall apart and there wasn't time to get new passport, invitation and visa. Maybe next year - I was excited to go.

October brings 2 very interesting conferences. I will be speaking first on Oct 3rd at HighLoad++ in Moscow and a few weeks later on Oct Oct 25 at Percona Live in London. I will give a talk called Choosing a MySQL Replication / High Availability Solution which is based on my thinking developed in my recent blog post

  [Read more...]

Most analytical and BI databases have date dimension table(s). One frequently needs to generate and populate such data. I present a solution below for such data generation, written in Python. Please use different database drivers/modules to connect to your specific database server (MySQL, SQL Server, Oracle, etc.) for data population.

Notes:

1. It takes 2 parameters, start date and end date, in YYYYMMDD format, inclusive. Extensive error checking is built in, but let me know if you have comments/suggestions;

2. The script produce a Python dictionary (associated array) and print out its content;

3. The output includes dayNumber: a day’s position in a year. For example, 2011-02-01 is the 32ed day in 2011, therefore its dayNumber is 32;

4. The output includes weekNumber: a week’s position in a year. The week number in year is based on ISO

  [Read more...]

When I spoke at Percona Live (video here) on running an E-commerce database in Amazon EC2, I briefly talked about using RAID 10 for additional performance and fault tolerance when using EBS volumes. At first, this seems counter intuitive. Amazon has a robust infrastructure, EBS volumes run on RAIDed hardware, and are mirrored in multiple availability zones. So, why bother? Today, I was reminded of just how important it is. Please note that all my performance statistics are based on direct experience running a MySQL database on a m2.4xlarge instance and not on some random bonnie or orion benchmark. I have those graphs floating around on my hard drive in glorious 3D and, while interesting, they do not necessarily reflect real-life

  [Read more...]

I’ve done some benchmark testing of 2 Python modules for MySQL data retrieval: MySQLdb and pyhs. MySQLdb uses MySQL’s client libraries, whereas pyhs uses HandlerSocket that bypasses MySQL’s client layer and interfaces Innodb storage engine’s files directly. In my testing, HandlerSocket results in 82% improvement over mysql client libraries based on number of rows retrieved. The tests were conducted under different conditions: right after a start when cache is cold, a warmed up cache after running SELECT * FROM customer, and alternating the execution order of those 2 Python files.

  [Read more...]