Joyent

Using DTrace on MySQL

Even though there aren’t DTrace probes for MySQL released yet, we can still get useful information from MySQL. DTrace has a pid provider which allows us to get into any function the program is executing and see it’s arguments. The only drawback is you have to go digging around in the source code to find out what you want to see. But thanks to guys like Brendan Gregg, they have already done some of the digging for us.

Even if we want to go digging around ourselves, it’s really not that hard; you just have to get your feet wet. And because Accelerators have DTrace probes enabled, you can take advantage of using DTrace on MySQL. I will show some examples of this and how easy it is to hunt down your own functions.

First let’s start with functions that have already been dug up for us:

mysql_parse(thd, thd->query, length, & found_semicolon);

This is the function MySQL uses to parse a query. So all we have to do is trace this function through the pid provider and we get to see all the queries coming through. This shows arg1 as being the query, and we must copy it in to kernel land where DTrace works for it to see the string:

root@ferrari:~# dtrace -qn ‘pid$target:mysqld:*mysql_parse *:entry { printf(”%Y %s\n”, walltimestamp, copyinstr(arg1)) }’ -p `pgrep -x mysqld`
2007 Sep 27 10:04:35 select * from blah
2007 Sep 27 10:04:58 select * from tablenothere

Notice that this will show all queries, even if they aren’t successful. Now that we can trace queries, this can give us good information. For example we can see what queries are executed the most:

root@ferrari:~# dtrace -qn ‘pid$target:mysqld:*mysql_parse *:entry { @queries[copyinstr(arg1)] = count() }’ -p `pgrep -x mysqld`
^C
select * from blah 5
select * from tablenothere 10

You can’t get this kind of information from MySQL unless you write some kind of script to parse through the query log. If we know that there is a query being executed 1000 more times than the others, we could always try to get this one to cache. Now lets say we want to find out how long a query took to execute. The function mysql_execute_command does the actual execution of the queries so all we do here is subtract the entry and return timestamps of this function. The script shown below uses this:

root@ferrari:~# ./exactquerytimes.d -p `pgrep -x mysqld`
Tracing… Hit Ctrl-C to end.

Query: SELECT COUNT FROM joe_visitors where upper(vs_browser) not like ‘GOOGLE‘ and upper(vs_browser) not like ‘GOOGLE BOT‘ and upper(vs_browser) not like ‘BOT‘ and upper(vs_browser) not like ‘MSN‘ and upper(vs_browser) not like ‘MSNBOT‘ and upper(,
Time: 2.32

On the MySQL side, it showed this query being executed at 2.32 seconds as well:
1 row in set (2.32 sec).

This is awesome information because as of now MySQL doesn’t allow you to see a slow query that is less than 1 second (I believe this is a fix in MySQL 5.1). So with this, we can see not just slow queries, but all queries and how long they take to execute with their times.

Now let’s try this same query but I bumped my query_cache_size up to 50M:

The first try (won’t hit the cache):

root@ferrari:~# ./exactquerytimes.d -p `pgrep -x mysqld`
Tracing… Hit Ctrl-C to end.

Query: SELECT COUNT FROM joe_visitors where upper(vs_browser) not like ‘GOOGLE‘ and upper(vs_browser) not like ‘GOOGLE BOT‘ and upper(vs_browser) not like ‘BOT‘ and upper(vs_browser) not like ‘MSN‘ and upper(vs_browser) not like ‘MSNBOT‘ and upper(,
Time: 2.28

And the second try hits the cache but doesn’t show anything through DTrace. So this means a query that is served from the cache won’t show up in mysql_parse. Right now this probably doesn’t mean much, but as you learn more about how the internals of MySQL work then troubleshooting becomes much easier down the road.

So far this has all been information that was provided. Now I will show how simple it is search through MySQL’s source and look at functions.

First we need to decide what to look for. Let’s say we want to find out every time that slow query is written to the slow query log. First we download the MySQL source code from http://www.mysql.com. Now we can search through the source code for ‘slow query’:

root@ferrari:/export/home/derek/mysql-5.0.45# ggrep -ri ‘slow query’ *

This turns up only a few source code files, one of them looking most obvious called log.cc with the expression “Write to the query log”. The MySQL code is very well commented so it makes searching really easy. Looking in this file, that comment is right above the function:

/* Write to the slow query log. */
bool MYSQL_LOG::write(THD *thd,const char *query, uint query_length, time_t query_start_arg)

It’s obvious that this function is the function that writes to the slow query log. Running this script below, looking for function LOG while a slow query is being inserted shows this function being executed with some weird characters around it:

root@ferrari:~# dtrace -F -n ‘pid$1:mysqld:*LOG *:entry {} pid$1:mysqld:*LOG *:return {}’ `pgrep -x mysqld`
dtrace: description ‘pid$1:mysqld:*LOG *:entry ‘ matched 126 probes
CPU FUNCTION
0 -> _ZN9MYSQL_LOG5writeEP3THD19enum_server_commandPKcz
0 -> _ZN9MYSQL_LOG5writeEP3THDPKcjl
0 < – _ZN9MYSQL_LOG5writeEP3THDPKcjl

The only thing bad about tracing MySQL through the pid provider is that these weird characters change between MySQL versions, so we can’t always trace for the function ‘_ZN9MYSQL_LOG5writeEP3THDPKcjl’ if we want it to work on other machines. We have to trace for *MYSQ*LOG*write* which slowquerycounts.d uses:

root@ferrari:~# ./slowquerycounts.d -p `pgrep -x mysqld`
Tracing… Hit Ctrl-C to end.

SELECT COUNT FROM joe_visitors where upper(vs_browser) not like ‘GOOGLE‘ and upper(vs_browser) not like ‘GOOGLE BOT‘ and upper(vs_browser) not like ‘BOT‘ and upper(vs_browser) not like ‘MSN‘ and upper(vs_browser) not like ‘MSNBOT‘ and upper(
Count: 3

As you can see DTrace can be very powerful even if we don’t have probes released yet, we just have to do a little extra work. Some of the information shown can be obtained from MySQL, but using DTrace still provides a benefit because we don’t have to enable anything in the MySQL configuration, possibly making us restart the server. I’m providing these scripts in the MySQLDTraceKit.tar.gz. Hopefully in the near future we will have real MySQL probes.

Quick wins with MySQL

Working at Joyent I get to see many different types of MySQL setups whether it’s replicated, clustered, load balanced or just a single instance.

Each one runs a lot differently than the other so they all require different settings. Even though they each need custom tuning, there are a few performance wins you can get with almost any MySQL database:

- If you are using MyISAM tables, make sure you have a good key_buffer size. If you are using InnoDB tables, make sure you are using a good innodb_buffer_pool_size. If you don’t have either of these set they default at only 8MB. Bumping these values up can decrease disk IO and make your database run a lot smoother. So many times I’ve seen these set to such low or default values with huge DB’s.

- Use indexes. Enable the slow query log and look for queries that show up most often, then learn the explain command on them.

- Check Created_tmp_disk_tables in show status; this value usually goes in hand with huge queries that aren’t using indexes. If it keeps increasing, it means you are having to create on disk files to execute the query. Consider increasing tmp_table_size or putting your disk files on a TMPFS by setting tmpdir = /tmp.

- Use the query cache if it can help you. Check the query_cache_size. Check how well it is performing. You can do this by doing a ’show status’ and then using this formula:

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

If there are a lot of prunes, that means your query cache size isn’t big enough.

- If Opened_tables is increasing you probably have table_cache too small.

- If you have a lot of connections, check Threads_created in show status; If it keeps increasing, you need to up the thread_cache_size in my.cnf. The max connections in MySQL are default at 200. You can easily check how many connections you have by doing a prstat in Solaris and looking at the number of LWP (MySQL starts 10 LWP when it starts and then 1 LWP per connection). If you have a lot of connections it can eat up CPU having to create the threads over and over.

Introduction to the OpenSolaris Operating System

Ben did a nifty little presentation at CommunityOne recently, and while I don’t personally understand 1/2 of the techno babble in it, I am told most of you will find this rather useful.

Think Solaris = Slow-aris? Think again. Ben is here to tell you otherwise.

And I would like to personally thank Linus Torvalds and Jonathan Schwartz for their timely discussion on OpenSolaris, as it was a perfect way to help me show Sun’s commitment to the project.

I know I didn’t need to include this piece as you all believe in Ben, and Ben believes in OpenSolaris, but I thought it wouldn’t hurt to show you Jonathan believes in it too.

If you would like more information on the OpenSolaris project, check out the community site where there are some additional nuggets of information.

Getting Started with JRuby

JRuby is something we’re very excited about as it bridges the gap between the Java and Ruby worlds, bringing a wide range of new advantages to developers.

You can take advantage of the mature JDBC, utilize Java Application Servers such as Glassfish for deployment with simple WAR’s rather than Capistrano, or use existing JavaBeans and business logic written in Java to plug into a much better framework.

We’re pleased to be the first infrastructure company to stand firmly behind JRuby, offering it as part of our standard Accelerator packages.

Getting started is easy. In our Accelerators you will JRuby 1.0.0RC2 in /opt/jruby. JRuby comes complete with the jruby interpreter which is used just like ruby, including friends gem, jirb, and more. Let’s take a look at how to get started:

[z010101CA:~] admin$ export PATH=/opt/jruby/bin:$PATH 
[z010101CA:~] admin$ jirb
irb(main):001:0> string = 'JRuby Rules\!'
=> "JRuby Rules\\!"
irb(main):002:0> print string
JRuby Rules\!=> nil
irb(main):003:0> string.upcase
=> "JRUBY RULES\\!"
irb(main):004:0> quit

It’s really that simple.

Want to do some JRuby on Rails work?

[z010101CA:~] admin$ which gem
gem is /opt/jruby/bin/gem
[z010101CA:~] admin$ gem list --local
*** LOCAL GEMS ***
sources (0.0.1)
    This package provides download sources for remote gem installation
[z010101CA:~] admin$ sudo gem install rails -y
Bulk updating Gem source index for: http://gems.rubyforge.org
Successfully installed rails-1.2.3
Successfully installed rake-0.7.3
Successfully installed activesupport-1.4.2
Successfully installed activerecord-1.15.3
Successfully installed actionpack-1.13.3
Successfully installed actionmailer-1.3.3
Successfully installed actionwebservice-1.2.3
...
[z010101CA:~] admin$ sudo chmod +r /opt/jruby/bin/*
Password:
[z010101CA:~] admin$ which rails
rails is hashed (/opt/jruby/bin/rails)
[z010101CA:~] admin$ rails webstore
      create  
      create  app/controllers
      create  app/helpers
     ........
[z010101CA:~] admin$ cd webstore/
[z010101CA:~/webstore] admin$ jruby script/server 
=> Booting WEBrick...
=> Rails application started on http://0.0.0.0:3000
=> Ctrl-C to shutdown server; call with --help for options
[2007-05-27 07:00:52] INFO  WEBrick 1.3.1
[2007-05-27 07:00:52] INFO  ruby 1.8.5 (2007-05-16) [java]
[2007-05-27 07:00:52] INFO  WEBrick::HTTPServer#start: pid=33409388 port=3000
24.6.105.152 - - [27/May/2007:07:01:20 GMT] "GET / HTTP/1.1" 200 7552
- -> /
24.6.105.152 - - [27/May/2007:07:01:20 GMT] "GET /javascripts/prototype.js HTTP/1.1" 200 71260
http://8.12.34.20:3000/ -> /javascripts/prototype.js
24.6.105.152 - - [27/May/2007:07:01:20 GMT] "GET /javascripts/effects.js HTTP/1.1" 200 38200
http://8.12.34.20:3000/ -> /javascripts/effects.js
24.6.105.152 - - [27/May/2007:07:01:21 GMT] "GET /images/rails.png HTTP/1.1" 200 1787
http://8.12.34.20:3000/ -> /images/rails.png
24.6.105.152 - - [27/May/2007:07:01:21 GMT] "GET /favicon.ico HTTP/1.1" 200 0
- -> /favicon.ico
...

There you go. No tricks, no gimmicks. Just start playing.

For those who don’t have a Joyent Accelerator, download JRuby here: JRuby.org.

DTrace for Ruby 1.8.6

Earlier this week we released a patch that adds DTrace capability to Ruby 1.8.5. We chose 1.8.5 because it’s what we’ve been using in production, though 1.8.6 is the latest stable release.

It now brings me tremendous pleasure to release a patch against Ruby 1.8.6. You’ll find it in the same repository as the other patch:

• The full Ruby 1.8.6 source with DTrace probes (http://svn.joyent.com/ruby-dtrace/ruby-1.8.6)
• Diffs (http://svn.joyent.com/ruby-dtrace/patches)

The probes are the same for the 1.8.5 patch. We will be building 1.8.6 binaries shortly and you’ll find them in the same place as the 1.8.5 binaries.

The patches for 1.8.5 and 1.8.6 are released under the same license as Ruby.

Enjoy!

Solaris, DTrace and Rails

We committed ourselves to Solaris as our base operating system two years ago as Solaris was becoming OpenSolaris. We needed a solid operating system that was 32/64bit, can manage lots of CPUs and RAM, one that we could contribute to, and we realized that three features would be a competitive advantage if we became experts in using them in production: ZFS, Zones and DTrace (the pre-existing observability tools in Solaris are quite excellent).

With time, it’s been surprising to me how that’s not clear to a lot more people, and we at Joyent have found ourselves spending a considerable amount of time simply being Solaris advocates.

So this last Sunday we (Ben and I) had a DTrace jam session at the Obvious offices with Bryan Cantrill and Jeremy over at Twitter, and were just running through an expanded way of looking at what ruby processes are doing when in production (the luxury of the processes being an issue is what great load balancers gives you).

(Jeremy, Bryan, Ian; the MacBook Pro in front of Bryan happens to be Ben’s, Bryan only uses Solaris on an Acer Ferrari ;-) )

Bryan happens to be a great Solaris advocate, and his biggest hammer is DTrace. So we try and get him in front of as many customers as we can, so that he can make an experiential case for why everyone with applications in production should be using Solaris. James Governor seems to agree.

We use DTrace all the time in identifying performance issues in our customer’s and in our own applications (remember we have some of the largest and oldest rails apps around), but it helps when its creator makes an appearance and a case for it as well. It was a bonus that Ian Murdock was there as well. Ben covered his impressions (or as he said, his “verdict”) of Ian after being able to spend a Sunday afternoon with him talking about Solaris.

From just a little bit of time, and out of the box it was clear that a lot of CPU was spent in an odd place: raised exceptions would generate backtraces that were going through hundreds of frames.

The result was a ticket filed at dev.rubyonrails.org 16 hours ago, and David committed the changes to Rails itself 5 hours ago. Everyone benefits from something pointed to by DTrace on Solaris.

And I think this is much in line with David’s main points about communities around open source projects, and a nice example of an open source operating system and tool giving you specific insights into a open source language and web framework, and then submitting and applying fixes to the right places. Blaine, the guys at Twitter, and DHH should be proud of this example.

But this introspection into what the Ruby processes are doing is still not deep enough. DTrace tells us from the outside of an interpreted language what it’s doing in regard to tens of thousands of different “probes” throughout the operating system.

You can see the number of places DTrace is hooked in with

$ dtrace -l | wc -l                                                                    

   51942

$ dtrace -n fbt:::entry'{@[execname] = count()}'                                       

dtrace: description 'fbt:::entry' matched 24798 probes

That’s more probes then I often know what to do with, but it’s great that they’re there.

Nearly a year ago, we worked with Bryan and the DTrace team to get as complete a set as possible of is-enabled probes into Ruby and to maintain that going forward (there’s only been patches against 1.8.2 up to this point). We’ve done that internally, and now with the increased use of Solaris by our own customers, and with DTrace showing up in the what is likely the most common development platform for Rails, Mac OS X, we renewed our collaboration today.

So we’re polishing up the patches for Ruby 1.8.5 (the version we and our Accelerator customers still run in production, and we’ll do 1.8.6 as well once that’s done in QA), and we’ll be sending those off to Bryan and his crew by Thursday for some vesting, and we’re aiming to release those next week.

The result is going to be a tremendous amount of insight into production Rails (and other Ruby) processes, and we hope that lots of improvements (even application-specific) can come from that.

Joyent Slingshot

Introducing Joyent Slingshot

Joyent Slingshot allows developers to deploy Rails applications that work the same online and offline (with synchronization) and with drag into and out of the application just like a standard desktop application. We have Joyent Connector and a select group of third party applications working under Joyent Slingshot. Joyent plans to have Slingshot available for general release on both Windows and Macintosh OS X in late April, 2007. If you would like to be considered for a spot an early release tester, please send email to slingshot [at] joyent.com You must be a Rails developer with an application we believe would help us put the final polish on Slingshot.

Joyent Slingshot is a game changer.

The framework is lightweight and customizable and allows Ruby on Rails applications to run offline with a simple and transparent data synchronization. You, the developer, make the decisions about exactly how their apps runs, what synchronizes – not Joyent.

Joyent Slingshot enables Rails to break free of the browser. It breaks down the wall between a Web application and a desktop application without losing what makes a Web application great: the ability to rapidly develop, deploy and update, now for desktop applications.

Joyent Slingshot allows Rails developers to easily create a hybrid Web/desktop application with the experience a user expects from a normal desktop application. Drag in and out of the Rails application for files, email, iCal events, vCards, bookmarks (html files) to begin with. Before final release, Joyent will be extending this to native filesystem integration and native datastore for additional data-types.

How does Joyent Slingshot work?

It provides a consistent and stable environment for a Rails application to run off Windows and Macintosh OS X. We remove all dependencies and conflicts with system binaries. Additionally, Joyent Slingshot allows developers to customize their environment as they please. Install any gems, plugins, binaries, whatever. We can handle it. Joyent Slingshot is like a virtual machine for a Rails application to run on. Sweet.

Simple and lightweight

Joyent Slingshot is a simple and lightweight mechanism to cleanly synchronize online and offline data. You, as a developer, can provide an ActiveRecord transport layer allowing easy customization of the data that gets synced to your application’s who, and when, and how. With the addition of about thirty lines of code, your Rails application can sync data from client to server. With another thirty lines of code you synchronize have file-based data.

The framework provides an extensible drag and drop abstraction layer. Hook into an application’s existing data import/export mechanisms without necessarily modifying any application code.

Finally, it allows the developer to easily deploy code updates and migrations, no matter how long a user has been offline.

We are evaluating applications to use Joyent Slingshot before the final release date. Please send mail to slingshot [at] joyent [dot] com to be considered. A very limited number will be chosen.

Joyent Slingshot Pricing Model

While we continue to kick around different business models for Joyent Slingshot, we can safely say that Slingshot will be available for free to developers that host their Rails application on Joyent Accelerators. Other uses of Slingshot will be allowed, but we haven’t finalized the pricing for those uses. Everyone using Joyent Connector will get a version of Joyent Connector on Slingshot for free.

Joyent Slingshot Demonstration

Here’s a quick tour of Slingshot. We’ll be putting up more/better screencasts in the coming days and weeks.

Update: here’s some further technical insight into the project from our partners at Magnetk.

Broader Cloth

Congrats and thanks to Alex Shiels for releasing an updated and much-fixed standalone version of Textile (the Humane Web Text Generator), which, in the PHP version that was circulating around in the past couple of years, was certainly starting to show its age. The new code remains tight and compact, and there’s a nice sprinkling of new features.

Textile: born out of a weekend’s groaning and fretting at a copy of Jeffrey Friedl’s excellent book on regular expressions, used by millions, and now in capable programming hands.

EyeSpot: Video Editor in JavaScript

EyeSpot is a new web site (“beta” of course) for posting and sharing video clips. There are a slew of sites offering similar services—presumably all in a race to become for video what Flickr is for still photography—but one of EyeSpot’s hooks is that they allow you to edit your video clips after you upload them, and the editor is written in JavaScript.

It’s a far cry from iMovie, but my hat is off to EyeSpot for the pure gumption of attempting this, let alone pulling it off. (Via Nat Torkington at O’Reilly Radar.)

Joyful JavaScript

One of the real benefits of using a framework like Ruby on Rails for development is that there are new, agile features being added all the time. One of the challenges in using a framework with such an accelerated curve of improvement is… well, keeping up. Luckily it's fun to do, and the benefits are real. The next update to the Joyent platform has been on edge Rails for a while and we've been able to simplify + improve our code a lot using '1.1' features like polymorphic associations, with_scope, integration tests and .rjs templates (my personal favorite).

Another big, constantly improving 'feature' of Rails is Sam Stephenson's incredible Prototype JavaScript library (live large with version 1.5.0_rc0!), which has become a vital part of the framework. It almost single-handedly changed my entire mindset about JavaScript. There was a time (4-5 years ago…) when I would have done absolutely anything to avoid using any JavaScript whatsoever. Not exactly one of those people who turned JavaScript off in my browser or anything, but close.

But JavaScript's always been the same, versatile language it is today; it even shares a lot in common with Ruby. They're both object-oriented and interpreted. Both allow objects to be modified extensively at runtime. JavaScript supports closures. And it's the only language that runs client-side within the browser across platforms, so we're kind of stuck with it too.

And that's okay, as the language seems to have finally recovered from the one-two punch of browser incompatibilities and the lack of a standard library. Time has (mostly) solved the former, and prototype.js has done much to take care of the latter. And while the library is small enough to be easily digestible, here are some favorite must-use features and some related must-read articles that can help you modernize your JavaScript skills.

Event Handling

Sometimes you want an event to occur when the document has finished loading. In the past you might have added an onload attribute with the corresponding code to the document's body tag. One problem with this approach was that you (or another library whose internals you are unaware of) could inadvertently overwrite this code elsewhere. So Prototype provides a simple way to register n callbacks whose declarations won't interfere with each other.

Event.observe(window, 'load', function(event){ Calendar.setupEdit(); });

Any event for any element can be observed, too, such as blur, mousemove, or keypress. Useful for cleaning up your code or writing more complex behavior that just doesn't work with inline event handlers. If you're running Safari and aren't using WebKit ('golden Safari') read up on an easy workaround for Event.stop, which is kind of the new-school way of returning false. For a more advanced example see the always excellent Justin Palmer's article about events in Prototype.

The Selector

Much like John Resig's jQuery, Prototype now has a DOM-inspecting selector that queries for elements based on the criteria you specify. This is exciting stuff. You want to toggle all the checkboxes in a fieldset with the class of 'toggleable' inside your form with the id of 'itemList'? No problem.

$$('form#itemList fieldset.toggleable input[type=checkbox]').each(function(input){
  input.checked = !input.checked;
});


The $$() function performs a document query based on the HTML tag types, JavaScript ids, CSS classes, and HTML attribute keys + values and returns the matching DOM elements. It's not always fast on the client-side, but there's no easier way to get a handle on document elements that match arbitrarily complex criteria. You even get =, ~=, |= and != operators for matching HTML attribute values.

Ruby?

You may have noticed in that last example the use of the very Ruby-like, and very un-Javascript-like each method. Well, Prototype adds the ability to treat arrays and 'hash' objects as enumerables, just like Ruby.

To convert an existing array to the Prototype extended Array class, wrap it like so: $A(myArray);. Or declare your arrays from the get-go like this: var myArray = $A();. The newly 'mixed in' methods you'll recognize as being from Ruby, or at least Ruby-inspired, include:

all, any, collect, detect, each, entries, find, findAll, grep, include, inject, invoke, map, max, member, min, partition, pluck, reject, select, sortBy, toArray, zip

You can get your Ruby on in JavaScript like so:

// show a nice, friendly, sorted, comma-delimited string of all the events tagged 'awesome'

var Events = $A([
  {name: 'Yesterday', tags: $A(['not so awesome', 'snow'])},
  {name: 'Tomorrow', tags: $A(['awesome', 'warm'])},
  {name: 'Today', tags: $A(['awesome', 'sunny'])}
]);

var awesomeEvents = Events.findAll(function(event){
  return event.tags.detect(function(tag){
    return tag == 'awesome';
    });
}).sortBy(function(event){
  return event.name;
}).collect(function(event){
  return event.name;
}).join(', ');

alert(awesomeEvents);


Whew! That was awesome, non? A little messy with all the anonymous functions all over the place, but awesome nonetheless. Thank goodness ugly for loops are a thing of the past. The best write-up on these Enumerable features lives over at Encytemedia (where you can also find the swanky Vibrant Ink TextMate theme).

In Conclusion

Of course we can't forget the AJAX support that put Prototype on the map, the ever-handy $() function, bootstrap exception handling, and the foundation laid for the script.aculo.us effects + UI library. For a comprehensive reference (currently covering only 1.4.0) see Sergio Pereira's excellent developer notes. And enjoy writing joyful JavaScript code, something that wasn't possible but is now.