Ruby on Rails Planet

MySQL 5.5 and 5.1 Support in Alpha »

Created at: 22.09.2011 22:59, source: Engine Yard Blog, tagged: Product Engine Yard Beta Program engine yard data team mysql Percona

A couple weeks ago, Engine Yard’s Data Team announced their plans to push new data solutions for our customers. We are happy to announce that MySQL 5.5 and MySQL 5.1 are available in Alpha! You can sign up for either or both versions released.

MySQL 5.1 Alpha (Percona Server v5.1.55) features

Table partitioning
Row-based replication
Plugin-API
Performance improvements
Additional information

MySQL 5.5 Alpha (Percona Server v5.5.13) adds

Semi-synchronous replication
New partitioning enhancements
Speed improvements for InnoDB recovery
Additional information

Percona is a leader in MySQL performance optimization. They deliver enhanced drop-in replacements for MySQL that allow you to run queries faster and more consistently. We are very proud to bring Percona to our stack and we think you’ll love them too. See Percona's feature comparisons for more details.

Many of you have been asking for this upgrade and we could not be more excited to put it into your hands. As we noted earlier, releasing these updated versions of MySQL will enable us to deliver improved replication, backups, and fail-over tools. We will continue to update everyone as we roll those tools out as well. To sign up for MySQL 5.x in Alpha, please request access here. Documentation can be found here. Give it a whirl and please share any feedback with the Google Group.

Note: We always recommend that you test new components in a staging environment before using them in your production application. For our customers with large databases using MySQL 5.0, please stay tuned for detailed documentation on upgrading your database to the new MySQL 5.5.

Introducing the Engine Yard Data Team »

Created at: 02.09.2011 21:08, source: Engine Yard Blog, tagged: News Technology engine yard data team mongodb mysql PostgreSQL

The Engine Yard Data Team’s mission is to formulate and iterate the Engine Yard data storage strategy, to help customers understand and choose their data solutions, to support OSS projects that match our vision, and to strengthen our leadership in the Ruby on Rails community. Our team is composed of Ines Sombra and our amazing DBAs: Erik Jones, Tyler Poland, and John Dalton.

We want to ensure that when you choose Engine Yard as your platform provider you have access to interesting data stores, features, tools, and information to build robust and scalable solutions.

Our Goals

Simplify and hide the complexities associated with data repositories.
Choose the best-in-class technologies and publish our own best practices.
Work closely with our customers and developers to understand their use cases and usage patterns.
Routinely assess how closely our data products and support match our customers needs.
Ensure Engine Yard’s data repositories are up-to-date with the rapidly evolving storage technology landscape.
Establish partnerships with key data providers to offer our customers additional services.
Design and support multi-site disaster recovery and business continuity plans for each type of data store by working with our customers to understand their scaling needs.

What we are currently working on

Upgrading our MySQL implementation

The team is currently working at a rapid pace to upgrade MySQL to version 5.1 and 5.5. We’ll also be improving MySQL replication, monitoring, backup, and fail-over tools.

Expanding the DB stack

We are working hard on expanding our stack to offer interesting NoSQL data stores, caches, and full-text search solutions. The products we are working on at the moment are:

PostgreSQL 9 PostgreSQL 9 is now in Alpha (click here for access). We are currently working towards a public Beta for all our customers to try. Postgres extensions will be available as part of the Beta release.

MongoDB We are working towards an Alpha release of MongoDB. We have been collaborating with customers that are interested in this technology to further develop our tools and finalize our supported architecture. We have established critical partnerships with hosted MongoDB providers (MongoHQ and MongoLab), and are working with 10gen to provide additional support options for our customers.

Redefining environments and data stores relationships

We are changing the way environments interact with data stores to provide greater flexibility and configuration options. We aim to support zero to multiple data stores in the same environment to facilitate the creation of polyglot systems in our platform.

Improving our data documentation

We are collaborating with the PANDAs and our Documentation team to restructure and augment our data documentation. Watch for changes in docs.engineyard.com and let us know if we are not covering something that you are interested in.

We want to hear from you!

We are actively evaluating customer requested features and we will keep you informed as our work grows from ideas to projects. Our intention is to create a strong collaborative process with our customer community, so let us know what you need.

We’d love to hear your feedback! Please drop us a note at: docs.engineyard.com/data-feedback.html

Want to work with us?

Are you passionate about data? We are hiring!

Making Migrations Faster and Safer »

Created at: 23.05.2011 20:03, source: Engine Yard Blog, tagged: Technology Tips & Tricks alter table mysql

This guest post is from our friends at Percona who are hosting Percona Live in New York on Thursday, May 26, 2011. Percona Live is a one-day intensive MySQL summit filled with four tracks and 100% technical presentations. In the NY area and want to go? We’ll be giving away some free passes this week. Keep an eye on the @engineyard twitter feed for a chance to snag one.

Are your migrations the execute-and-pray variety? If so, you are probably not (yet!) running a large application. Even small applications can benefit from a more careful migration process. It's important to establish the process now, before things get big, because it will be much more difficult later. In my study of emergency issues filed by Percona's customers, the single most valuable preventive measure I identified was change control. Migrations involve change, and the more disciplined and careful you are, the less opportunity for problems, and the more safeguards you can build in to help recover quickly if a problem ever does occur. The single best way to prevent bad changes from happening in production is to execute them in a non-production environment and observe the system. As you might know, ALTER TABLE is generally a blocking operation in MySQL. It can be very hard to predict how long it will take, and the growing sense of panic you'll feel as you watch your entire application pile up is no fun, especially because you don't know if it's about to complete or if you're only 30 seconds into a 3-hour ALTER. A non-production (staging) environment with a recent copy of your production data is your friend. The part I see a lot of folks stumble over is getting a realistic copy of the data. If you run the migration against a toy dataset, you won't understand how long it's really going to take in production. To get started, you can restore your latest backup onto the staging server. Use mysqldump or Percona XtraBackup to create a backup if you don't have one. If you’re an Engine Yard customer, their clone feature which allows creating a staging environment from a production snapshot works for this as well. When you run the migration, capture the output and review the timings carefully. Write a little script to help. Here's an example that I use sometimes for my clients. Save the output as migrated.txt, and run this Perl one-liner:

perl -ne '/(\S+): migrated \((.*)s\)/ && print "$2 $1\n"' migrated.txt | sort -rn -k1,1

The output will be execution times and migration names, sorted longest-first. That makes it easy to see which operations might take a long time in production. The next step is to review the SQL that your migration generates, looking for ALTER and CREATE INDEX statements you can combine. In case you aren't familiar with it, MySQL's ALTER statement generally works by creating a copy of the table in the background, modifying it, and then copying all the rows into it and swapping the tables. A CREATE INDEX statement is really an ALTER statement in MySQL. So if you ALTER a table, and then add an index with CREATE INDEX, you're making MySQL do all that work twice. The problem is, Rails' migration methods often generate multiple alterations to a table behind the scenes. Here's an example:

def self.up
     add_column :comments, :name, :string
     add_column :comments, :user_id, :integer, :null => false
     add_index :comments, :user_id
end

This code ends up running three separate ALTER statements: one for each added column, and one for the new index. In cases such as this, you might have to execute direct SQL instead. Here's another code snippet that does essentially the same thing:

def self.up
     execute "ALTER TABLE comments add name varchar(255), add user_id int NOT NULL, add index `index_comments_on_user_id` (`user_id`);"
end

Sometimes you might not notice repeated ALTER statements in the Ruby code, so you should review the migration's generated SQL in staging. Look for any ALTER and CREATE INDEX statements that reference the same table, and merge them. This can save a lot of work for the database, and reduce the amount of time that the migration locks the table. Do you have tips or suggestions of your own to add to mine? Post them in the comments, I look forward to your feedback!

HandlerSocket: The NoSQL MySQL & Ruby »

Created at: 14.01.2011 18:49, source: igvita.com, tagged: databases handlersocket mysql nosql

The end of an architectural era, time for a complete rewrite? Is it really the case that by attempting to be a "one size fits all", the RDBMS "systems of the past" excel at nothing? The Cambrian explosion of alternative database engines certainly lends some credibility to that view. However, amidst all the hype, it is also easy to overlook the fundamentals: normalized or not, or with or without a "structured query language" (SQL), a B-Tree is still one of the best performing data structures when it comes to indexing data.

With that in mind, Yoshinori Matsunobu and a few of his collaborators at DeNA (one of the largest social game platform providers in Japan), decided to build the literal "NoSQL" directly into MySQL! The HandlerSocket plugin, which they have released to the public can be installed into any existing MySQL server to provide an optimized protocol for reading and writing data directly from the underlying storage engine (such as InnoDB) without any SQL overhead. And the results are stunning: faster than memcached, more flexible, and no cache coherency problems.

HandlerSocket: Under the Hood

The core insight behind HandlerSocket is that for in-memory workloads where data is accessed via an index, the overhead imposed by SQL parsing, locks and concurrency controls has nothing to do with the reading or writing of data from the underlying storage engine. In other words, if all you need is direct access to the index, then you can bypass the SQL layer altogether - that is exactly what HandlerSocket provides.

The plugin is a daemon which can be loaded into any MySQL server, and which opens additional ports on the server to accept direct reads and writes to the underlying storage engine. The protocol is incredibly simple, and most importantly, HandlerSocket runs inside of your existing MySQL server, accessing the same underlying data. This means that you have the full expressive power of SQL, the persistence and error recovery of the underlying engine, and an optimized read/write protocol for where you need it.

Benchmarks are a subjective sport, but Yoshi's tests show that direct access to InnoDB via the HandlerSocket protocol yields a significant improvement even when compared to a raw memcached connection! Perhaps it is memcached we should be optimizing, not running away from our RDBMS engines?

Hands on with HandlerSocket

To get started, follow the installation instructions provided in the HandlerSocket repo, or simply pickup one of the latest builds of the Percona Server - it now ships with HandlerSocket built in! Once you're up and running, you can tweak your settings for the plugin in your my.cnf:

[mysqld]
plugin-load=handlersocket.so # or mysql> install plugin handlersocket soname 'handlersocket.so';
loose_handlersocket_port = 9998
loose_handlersocket_port_wr = 9999
loose_handlersocket_threads = 16
loose_handlersocket_threads_wr = 1
open_files_limit = 65535

If all is well, launch your mysql CLI, and do a "show processlist" to see the open HandlerSocket connections. Now, if you are curious, you can telnet directly to the read or write port and issue some queries, or pick one of the existing clients (PHP, Java, Python, Node.js, Ruby) and do it from the comfort of your favorite language.

Ruby & HandlerSocket

HandlerSocket ships with a C++ library (libhsclient) for which there are several Ruby wrappers: ruby-handlersocket and handlersocket. If native extensions are not a problem, then "gem install handlersocket":

require 'handlersocket'
 
h = HandlerSocket.new(:host => '127.0.0.1', :port => '9998')
 
# open PRIMARY index on widgets.user table, assign it ID #1
h.open_index(1, 'widgets', 'user', 'PRIMARY', 'user_name,user_email,created')
 
# fetch record from index ID 1, where PRIMARY key is equal to 1
p h.execute_single(1, '=', [1])
# > [0, [["Ilya", "ilya@igvita.com", "2010-01-01 00:00:00"]]]
 
# open 'id_created' index on widgets.user table, assign it ID #2
p h.open_index(2, 'widgets', 'user', 'id_created', 'user_name,user_email,created')
 
# fetch record from index ID 2, where id >= 2, and date >= 2010-01-03
p h.execute_single(2, '>=', [2, '2010-01-03'], 10)
# > [0, [["Bob", "bob@example.com", "2010-01-03 00:00:00"]]]

Alternatively, if you are looking for a non-blocking version, you can also use em-handlersocket which does not require any native extensions. Non-blocking reactor, combined with the fact that HandlerSocket allows pipelined execution, makes a for a very fast API! A simple example of connecting to a MySQL server and doing a range scan on a composite InnoDB index:

EM.run {
  c = EM::HandlerSocket.new
  idx = {:id => 0, :db => 'widgets', :table => 'user', :index_name => 'id_created', :columns => 'user_name'}
 
  d = c.open_index(idx)
  d.callback do |s|
 
    # Query index 0 for records where id >= 2, and created_at >= 2010-01-03
    d = c.query(:id => 0, :op => '>=', :key => ['2', '2010-01-03'])
    d.callback do |data|
      p [:received, data]
    end
  end
}

EM-HandlerSocket (EventMachine HandlerSocket client)

Downloads: 64 File Size: 0.0 KB

Stop the revolution! Hold the rewrite!

If HandlerSocket can fetch data faster than memcached, then this is a game changer. Stop the revolution! Hold the rewrite! After all, caching data in another datastore leads to data duplication, cache coherency problems and additional operational complexity. Perhaps it is too early to simply throw away the billions of dollars we have invested into developing and optimizing the underlying RDBMS engines!