Our latest changes have been pushed to public mysql@facebook branch, allowing this post to happen \o/

Recently we started rolling out InnoDB compression to our main database tier, and that has been a huge undertaking for multiple teams and a major test for MySQL. Nizam was sure the hero of all this work, and make sure you don’t miss his talk about it at MySQL conference.

Though MySQL manuals have quite some introduction about benefits of compression, we agree that benefits are good – in theory we can do less reads from disk, keep more data in buffer pool or flashcache and take less …

At ideeli, there is an asynchronous process that allows internal users to import SKUs into our MySQL database. As ideeli has grown, more people are doing more of the same thing at the same time and the SKU import process had become a pain point. At its core, it simply inserts records into a handful of tables from an uploaded file within a transaction. Subsequently, a cleanup process deletes some data from the same tables. Each import ordinarily takes a few minutes, but when multiple SKU imports are running simultaneously, everything grinds to a near-halt. If an internal user didn’t complain first, Technical Operations would get paged for multiple long running MySQL transactions in the several hour(!!) range. We would kill off the longest running SKU import and the other imports would eventually complete as the locks it held were released. This annoyed people at many levels of the business, but it was a reasonably manageable problem…until it …

There are four SQL transaction isolation levels supported by InnoDB: REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, and SERIALIZABLE. Because READ UNCOMMITTED and SERIALIZABLE are rarely used, I am going to outline the distinction between READ COMMITTED and REPEATABLE READ. Perhaps I will follow up with SERIALIZABLE and READ UNCOMMITTED if there is interest.

REPEATABLE READ:
The state of the database is maintained from the start of the transaction. If you retrieve a value in session1, then update that value in session2, retrieving it again in session1 will return the same results. Reads are repeatable. Repeatable Read.

session1> BEGIN;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron

session2> BEGIN;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE …

It seems that our friends at Oracle have been pretty busy with the GA release of MySQL Cluster 7.2.

This is not just old wine in new bottles.

While it may be a dot release, it does appear to be a more significant step forward than a dot release would imply.

First off, we are very excited to announce that the Severalnines Cluster Configurator now includes support for 7.2 GA.

As the title of this blog suggests, it is possible, as we have experienced, to deploy 7.2 GA in 288 seconds, i.e. just a few minutes. This was done on m1.large instances on Amazon. We have published a quick how-to deployment guide for Amazon here: http://support.severalnines.com/entries/20916823-installing-on-amazon-ec2.

…

I recently received a question asking about InnoDB and the ability to autoextend the ibdata files.

The question was basically this:
If the database has 1 ibdata file that is using autoextend but also has a max value set, will it start a new file once the max value is reached.

The answer is no it will not create a new file.You are able to use autoextend and have more than 1 ibdate file though.

For example I made a simple example via a virtual box.

Started using:
innodb_data_file_path            = ibdata1:20M:autoextend:max:100M
innodb_autoextend_increment        = 50M

 I downloaded the full employees dataset from launchpad.

Started the server …

“Legacy MySQL does not scale well on a single node, which forces granular sharding and explicit application code changes to make them sharding-aware and results in low utilization of severs”– Dr. John Busch, Schooner Information Technology A super-set of MySQL suitable for Big Data? On this subject, I have interviewed Dr. John Busch, Founder, Chairman, [...]

This is a time-honored topic, and there’s no shortage of articles on the topic on this blog. I wanted to write a post trying to condense and clarify those posts, as it has taken me a while to really understand this relationship.

Some basic facts

• Most of …

While it might not have been New England’s weekend on the Big Gridiron, it was certainly New England’s day for Big Data at the New England Database Summit on Friday at MIT.

The summit was well attended, with 350 registrants and keynotes from prominent MySQL users such as Mark Callaghan. The coverage was quite broad, with presentations running the gamut from grad students (complete with bodyguards and intimidating academic advisors) to established companies such as StreamBase. The sponsor list was an …

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 …

iiBench measures the rate at which a database can insert new rows while maintaining several secondary indexes. We ran this for 1 billion rows with TokuDB and InnoDB starting last week, right after we launched TokuDB v5.2. While TokuDB completed it in 15 hours, InnoDB took 7 days.

The results are shown below. At the end of the test, TokuDB’s insertion rate remained at 17,028 inserts/second whereas InnoDB had dropped to 1,050 inserts/second. That is a difference of over 16x. Our complete set of benchmarks for TokuDB v5.2 can be found here.

…