Impressions from MySQL conf 2011, part IV

MySQL

This post concludes my impressions from some of the talks I’ve been to.

Thursday

I opened this day’s sessions with a smile.

Antony Curtis and Arjen Lentz have authored the OQGraph storage engine a while back. I had every intention to try it out, but never got round to it. Which is why I was happy to find this session in the conference. OQGraph is a specialized engine, and comes to solve hierarchal or otherwise graph-related queries, such as: “who are the descendants of a given node”, “find a route from a to b”, etc. MySQL does not support the RECURSIVE syntax as Oracle does, and does not provide out-of-the-box solution for that.

The syntax is just excellent: you just write something like SELECT * FROM my_graph WHERE from_node = ’empusa fasciata’ AND to_node = ‘homo sapiens’ to find a route. Otherwise just use WHERE from_node = ‘Heathrow Central’ to find all outgoing links. So this is just plain old SQL, no new syntax involved.

I rounded corners. It is also possible (and required) to specify an algorithm. Do you want Djekstra? BFS? You specify it in the query. The result of a route query is a rowset, where each row is a step in the route, along with its sequence within the route. So you can do your ORDER BY, LIMIT etc. I find that syntax-wise, OQGraph is very intuitive! Continue reading » “Impressions from MySQL conf 2011, part IV”

Impressions from MySQL conf 2011, part III: BoF Replication

MySQL

This post continues my impressions from some of the talks I’ve been to. I’ll dedicate this post to a single session.

Wednesday

  • BoF: Replication, Lars Thalmann, Mat Keep (Oracle)

Lars Thallman presented the MySQL 5.5 replication features, as well as the expected 5.6 features. Among other features, one could notice parallel replication, binlog checksums, sub-second resolution and more. There was an open discussion about these features, asking for comments; looking for new ideas and suggestion from the audience.

I can’t possibly cover it all. I’ll note two discussion I participated in, and which have interested me. This also serves for noting down to myself my ideas and thoughts. Continue reading » “Impressions from MySQL conf 2011, part III: BoF Replication”

Impressions from MySQL conf 2011, part II

MySQL

This post continues my impressions from some of the talks I’ve been to.

Wednesday

Grant McAlister described the Amazon RDS offer, which provides with a pre-installed MySQL servers, and supports auto management of replication and high availability. He desribed asynchronous vs. synchronous replication, logical (i.e log shipping & replaying) vs. physical replication.

Amazon implement physical replication by shipping data pages to a secondary, standby server, located at a different availability zone. A transaction does not complete before pages are shipped to, and acknowledged by standby machine. The standby machine writes data pages in parallel. This is similar in concept to DRBD. RDS uses InnoDB, which promises data integrity in case of power/network failure.

The fail over process, in case active master has crashed, involves blocking access to the active master, starting MySQL on standby master (promoted to be active), while changing elastic IP for master to point to promoted master. McAlister said this process takes a few minutes. Live demo resulted at about 4 minutes. Continue reading » “Impressions from MySQL conf 2011, part II”

Impressions from MySQL conf 2011, Part I

MySQL

Having the conference behind now, I’m reviewing some of my impressions and of sessions I attended.

The people

To begin with, this conference was a big success for me, in many respects. The sessions were great (more on that later), but of course, meeting with new people and with familiar people, was the more important part.

I live in Israel, which makes travel to the US very long and expensive. Apparently not many MySQL community members in my neighborhood, so I don’t ever get to meet the faces. The conference makes that possible. I did not participate in all community events, as I had scheduled calls with little girls who miss their father. And I was very much under jet lag. And I have more excuses on demand.

But I did get to meet known faces; people I only knew by name; unfamiliar people who were familiar with my work (fun!); and otherwise just (ex-)strangers.

The sessions

There was a variety of sessions to choose from. Many times, I had to pick one out of two or three sessions I was interested in, running at the same time. Not all sessions appeal to one in the same way, but looking back, I find there were a lot of GOOD sessions I attended. I mostly like sessions that are very technical; preferably drilling into details of algorithms & implementation. Continue reading » “Impressions from MySQL conf 2011, Part I”

Slides for “openark-kit: MySQL utilities for everyday use”

MySQL

Today I have delivered my talk, openark-kit: MySQL utilities for everyday use, at the O’REILLY  MySQL Conference 2011.

The slides are uploaded to the O’Reilly site, and I’m attaching them here as well. Feel free to download the PDF: openark-kit-mysqlconf11.pdf

I wish to thank all who attended my talk!

 

Checking for AUTO_INCREMENT capacity with single query

MySQL

Darn! This means oak-show-limits becomes redundant. Am I not supposed to speak about it on my coming presentation? Bad timing!

You have AUTO_INCREMENT columns. How far are you pushing the limits? Are you going to run out of AUTO_INCREMENT values soon? Perhaps you wonder whether you should ALTER from INT to BIGINT?

The answer is all there in INFORMATION_SCHEMA. The TABLES table shows the current AUTO_INCREMENT value per table, and the COLUMNS table tells us all about a column’s data type.

It takes some ugly code to deduce the maximum value per column type, what with signed/unsigned and data type, but then its very simple. Here is the query: Continue reading » “Checking for AUTO_INCREMENT capacity with single query”

Recovering a MySQL `root` password: the fourth solution

MySQL

Have just read Darren Cassar’s Recovering a MySQL `root` password – Three solutions. There’s a fourth solution: using an init-file, which leads to just one restart of the database instead of two. It also avoids the security issue involved with using skip-grant-tables.

I’ve written all about it before on Dangers of skip-grant-tables.

Darren’s 1st advice (look for password ini files, scripts, etc.) is a very good one. One password that can always be looked up in files is the replication’s password.

Replication’s password is easily forgotten: you only set it once and never use it again; never script it nor manually login with. When setting up new slaves, though, you suddenly need it.

Apparently not many realize that the replication password is written in plaintext in the master.info file. This file tells the slave all about it’s master connection: host, port, user & password are all there for you to read.

Reasons to use AUTO_INCREMENT columns on InnoDB

MySQL

An InnoDB table must have a primary key (one is created if you don’t do it yourself). You may have a natural key at hand. Stop! Allow me to suggest an AUTO_INCREMENT may be better.

Why should one add an AUTO_INCREMENT PRIMARY KEY on a table on which there’s a natural key? Isn’t an AUTO_INCREMENT a pseudo key, meaning, it doesn’t have any explicit relation to the row data, other than it is a number and unique?

Yes, indeed so. Nevertheless, consider:

  • Natural keys are many times multi-columned.
  • Multi column PRIMARY KEYs make for larger keys, and make for bloated secondary keys as well. You may be wasting space for storing the additional AUTO_INCREMENT column, but you may gain space back on secondary keys.
  • Multi column PRIMARY KEYs make for more locks. See also this post.
  • InnoDB INSERTs work considerably faster when worked in ascending PRIMARY KEY order. Can you ensure your natural key is in such order?
  • Even though an AUTO_INCREMENT makes for an INSERT bottleneck (values must be given serially), it is in particular helpful to InnoDB by ensuring PRIMARY KEY values are in ascending order.
  • AUTO_INCEMENT makes for chronological resolution. You know what came first, and what came next.
  • In many datasets, more recent entries are often being accessed more, and are therefore “hotter”. By using AUTO_INCREMENT, you’re ensuring that recent entries are grouped together within the B+ Tree. This means less random I/O when looking for recent data.
  • A numerical key is in particular helpful in splitting your table (and tasks on your table) into smaller chunks. I write tools which can work out with any PRIMARY KEY combination, but it’s easier to work with numbers.

Would you be my friend on mysqlconf? (tempting offer inside)

MySQL

I’m still throwing papers to the trash and starting all over, fixing, rewriting and improving my talk at mysqlconf 2011, where I will be presenting openark-kit: MySQL utilities for everyday use.

However I’ve got something up my sleeve: a benefit many can enjoy, that’ll make me a respectful, popular and sought after speaker. While others may try and lure you with such earthly temptations as a 20% off discount, I am in a position to offer you a more spiritual gift: my friendship!

See, if you become my friend, I can offer you a 25% discount on the MySQL conference. Yes, that’s 5% more than my competitors! The only thing I ask in return is that you be my friend (hey, it’s called “friends of speaker”). Not like a FB virtual friend, but a real friendship! One where you can buy me beer or dinner!

If you agree to such humane terms, I will be in the position to let you know that all you have to do is fill in mys11fsd in your registration form.

No, wait! I let it slip! Rewrite: You should fill in mys11fsd [will only tell you this password after your commitment to a beer] in your registration form.

Oh no, not again!

Don’t use mys11fsd without talking to me first… You’re not supposed to… Oh, my beer!

Argghhh!