Ishmael – a UI for mk-query-digest

Ever since Peter Z from Percona showed us the awesome that is the mk-query-digest tool, we’ve been using it quite a bit to help identify costly queries. It analyzes the MySQL slow query log and compiles a detailed report which includes tons of useful data – how many times was a particular query called? how much time did MySQL spend executing a particular query during the given timeframe? how long did the query take to execute on average? what about the worst case?

The report is output as a long long text file that you can page through and look at all the queries. All the data you need is there, but it’s a bit difficult to get to, especially if you want to compare multiple reports or if you want to aggregate over a period of time longer than the interval at which you run the reports (we’ve been running it every 15 minutes on one of our shards and are in the process of adding it to all servers).

Enter ishmael


Photo from raphie.

Luckily, the tool can be configured to write most of the data in the report to a database table. Where there’s a database, there’s a former tools developer eager to write a UI on top of it (me). After Timmy, our DBA, showed me a quick prototype of a page he put together for displaying the data, I took it and ran with it. The result is ishmael – a UI on top of mk-query-digest. The name comes from the tools purpose – to help hunt down “whale” queries.

For now, ishmael lets you sort the queries by 3 characteristics – the total amount of time MySQL spent executing the query, the number of times the query was actually executed, and the ratio between the two. It also displays the queries with some highlighting (done using a brutal regex) and lets you click through to see historical data (assuming you’ve run the report more than once) as well as a page that shows the EXPLAIN output for the query and the SHOW CREATE TABLE output for the tables involved (once again, traced back from their aliases using brutal regex hacks).

There are already a bunch of additional features in the pipeline – being able to configure ishmael to switch between different databases, better handling of historical data, etc. We hope other mk-query-digest users find the tool useful and can let us know how the tool can be made better. Patches are welcome.

The docs for mk-query-digest are on the maatkit toolkit website: http://www.maatkit.org/doc/mk-query-digest.html.

The source for ishmael, is on github: http://github.com/mihasya/ishmael
(click “Issues” to view the list of open tickets)

Galleries APIs

Giant Isopod

We love galleries. After all, without galleries how would you find your giant sea bugs?

This post is to quickly announce we’ve added galleries to the API.

A Rose+GUID by Any Other Name ….

Galleries in the API use “compound-ids”. Like tags. An example gallery compound id might look like 9634-72157621980433950. Unlike photos you can’t simply grab the last number off a gallery url and stick it into the API. Yeah, I’m not thrilled about it either, but there are good (read boring) reasons why it works that way.

So when an API method says it takes a gallery_id, we’re talking about the compound-id.

You can however use the flickr.urls.lookupGallery method to go from gallery url to gallery_id. Pass the method the URL for the gallery, and we’ll give you back the gallery info blob.

You can also get gallery IDs from flickr.galleries.getList and flickr.galleries.getInfo.

Behold, a gallery info blob:

<gallery id="6065-72157617483228192" 
    url="http://www.flickr.com/photos/straup/galleries/72157617483228192" 
    owner="35034348999@N01" 
    primary_photo_id="292882708" primary_photo_server="112" primary_photo_farm="1" primary_photo_secret="7f29861bc4" 
    date_create="1241028772" date_update="1270111667" count_photos="17" count_videos="0" >
 <title>Cat Pictures I've Sent To Kevin Collins</title>
 <description>dive dive dive</description>
 </gallery>

The primary_photo_* attributes refer to the “cover photo” for the gallery. The owner is the Flickr user_id (aka NSID) of the member who created the gallery. The id is that compound-id we talked about.

Lists of Galleries

You can fetch all of a member’s galleries using flickr.galleries.getList, sorted from newest to oldest, returning a list of gallery info blobs.

Or you can fetch all the galleries a given photo is in with flickr.galleries.getListForPhoto.

A Bag of Photos

Perhaps most interesting, flickr.galleries.getPhotos will return a list of all the photos for a given gallery. It’s a standard photo response, with a twist.

<photos page="1" pages="1" perpage="500" total="15"> 
   <photo id="2935475111" owner="8147452@N05" secret="e20746148b" server="3068" farm="4" title="Day off from the Death Star." ispublic="1" isfriend="0" isfamily="0" is_primary="1" has_comment="1">
        <comment>best cat picture ever!</comment>
   </photo>
   <photo id="3078977730" owner="68779755@N00" secret="dba9d8105e" server="3229" farm="4" title="&quot;We could stuff it with Kleenex...&quot;" ispublic="1" isfriend="0" isfamily="0" is_primary="0" has_comment="0" /> 
   <photo id="3212123792" owner="10983978@N03" secret="4231501383" server="3391" farm="4" title="1-19-09: Some People Just Don't Get It" ispublic="1" isfriend="0" isfamily="0" is_primary="0" has_comment="0" /> 
     ....
</photos>

In addition to standard photo response attributes, there is also a has_comment attribute which signals whether the gallery creator added a comment about why she included the photo, and whether the child comment element is present. Also is_primary, when set to 1, indicates this is the gallery’s “cover photo”.

CRUD

flickr.galleries.create creates a gallery, with a title, description, and optional primary photo, and will return a gallery element with the compound-id and the URL of the gallery.

<gallery id="50736-72157623680420409" url="http://www.flickr.com/photos/kellan/galleries/72157623680420409" />

flickr.galleries.editMeta is simply for updating the title and description. flickr.galleries.editPhoto confusingly doesn’t edit a photo, but rather the comment about a photo in a gallery.

Of course the money is all in flickr.galleries.addPhoto which allows you to actually build a gallery of photos.

Nota bene: Remember only public-safe can be added to galleries.

The Curated Life

We’ve also added the ability to restrict searches to only photos in galleries, with the in_gallery argument to flickr.photos.search

So whether you’re interested in kittens deemed cute enough for galleries, or hand selected pink photos, or Flickr Commons photos in galleries, or simply photos taken near you (assuming you’re in Brooklyn), in galleries, that’s available.