Database Optimization: Query Performance vs. Request Performance

When it comes time to optimize your web application's database performance, there are (at least) two types of tools you can leverage. You can use an analysis tool like pgFouine, which is focused on individual query performance, or a tool like Scout or New Relic, which is focused on request performance.

pgFouine is fantastic at isolating any single query that, in aggregate, is slowing down your application the most. This is a function of the query's invocation count multiplied by its average execution time. Of course, it will report not just the single most expensive query, but the top N most expensive queries. (The tool will also will show you the most frequently executed queries and the overall single slowest queries, but these metrics are less useful for determining what queries to focus on).

But what if the top N queries are all taking roughly the same amount of time? Which one(s) should you try to optimize or eliminate? pgFouine cannot help you make this decision intelligently. Instead you need a tool that will allow you to focus on performance at the level web requests.  Consider that each request your web application handles is likely comprised of many queries. So if you have one type of request that is taking 80% of your web application's processing time, then you probably want to see the full set of queries that together are causing these requests to be non-performant. Perhaps you can eliminate many of these queries, with an improved application design. This is when a tool such as New Relic (or Scout's "Application" feature) becomes invaluable.

For each type of request, you can see the most expensive queries, but more importantly the full set of queries that are being issued for a given request. (These tools are equally useful for finding application layer code that is non-performant, but I'm only concerned with database performance here.) With a request-level view you can start to evaluate your overall design to determine whether you can eliminate certain queries altogether. For example, you might realize that you are issuing two similar but different queries that can be combined into a single query. pgFouine might show that these two queries are equally performant, while a request-level analysis tool will show you that they are being executed side-by-side while serving a single request. This is the hint one needs to start understanding where optimizations can be made at a design level higher than an individual query.

PostgreSQL Column-to-Row Transposition

I recently had a need to generate a geolocation history of user activity. The result set needed to be a linear history of users' activities, with each row consisting of a user identifier, activity type, location, and timestamp. Unfortunately our database schema stored the location and timestamp of three different types of activities across 3 separate pairs of columns in the same table. To accomplish the required output, I needed to transpose the columns into rows. To accomplish this, I was able to make use of PostgreSQL's array constructor syntax and unnest array function.

From a table with the following columns:

• user_id
• activity1_location
• activity1_timestamp
• activity2_location
• activity3_timestamp
• activity3_location
• activity3_timestamp

I issued the following query:

SELECT user_id, 
unnest(ARRAY['activity1', 
             'activity2',
             'activity3']) as "activity type",

unnest(ARRAY[activity1_location, 
             activity2_location, 
             activity3_location]) as "location",

unnest(ARRAY[activity1_timestamp, 
             activity2_timestamp, 
             activity3_timestamp]) as "timestamp";

The unnest function generates multiple rows, one row per element of the specified array. This produces a result such as:

user_id | activity_type | location    | timestamp

--------+---------------+-------------+--------------------

      1 | activity1     | address1    | 2012-03-13 00:00:00

      1 | activity2     | address2    | 2012-03-13 00:00:01

      1 | activity3     | address3    | 2012-03-13 00:00:02

      1 | activity1     | address4    | 2012-03-13 00:01:00

      1 | activity2     | address5    | 2012-03-13 00:01:01

      1 | activity3     | address6    | 2012-03-13 00:01:02

      2 | activity1     | address7    | 2012-03-14 00:00:00

      2 | activity2     | address8    | 2012-03-14 00:00:01

      2 | activity3     | address9    | 2012-03-14 00:00:02

...

I learned about these PostgrSQL array functions from this highly recommended slide presentation, "Postgres: The Bits You Haven't Found".

Quicker Code Reviews

This year I resolve to spend less time doing code reviews by only reviewing the test code. This should work, right?

Android "External Storage" Poorly Named

As a user of an Android device, I've always been a little confused about the various types of data storage that are available, and where exactly my apps are storing their (well really, my) data. There's "internal storage", which I've always assumed is akin to the internal hard drive on a normal computer: durable, persistent storage. And then there's "external storage", which I've assumed meant a removable SD card. So I always assumed that if I were to take the memory card out of my phone and toss the phone in the water, I would at least maintain in my possession all of the data that I've explicitly moved to (or configured apps to automatically save to) "external storage". Well, it turns out that's not quite the case. What Android docs call "external" storage is really just a "non-private" data space. And a removable SD card may or may not be where this non-private storage resides, as it can actually be a partition of the internal storage! Come on Google! Why call this "external storage" at all? It's "non-private" or "public" storage. Please rename it.

Here are the official docs:

Using the External Storage
Every Android-compatible device supports a shared "external storage" that you can use to save files. This can be a removable storage media (such as an SD card) or an internal (non-removable) storage. Files saved to the external storage are world-readable and can be modified by the user when they enable USB mass storage to transfer files on a computer.

It's possible that a device using a partition of the internal storage for the external storage may also offer an SD card slot. In this case, the SD card is not part of the external storage and your app cannot access it (the extra storage is intended only for user-provided media that the system scans).

Subversion Directory Tree Conflicts

Came across this animation on a blog while looking for some answers on how to properly resolve a Subversion tree conflict on a directory. This about describes how I feel at the moment, after having already spent a large part of the day working with merging source code branches. In fact, I often feel like this, when I can't find proper documentation for the software I'm using.

For what it's worth, the "answer" I was looking for was found in the last paragraph here, which tells me that Subversion will be of no help in resolving my particular problem. Joy!

Other tree conflicts

There are other cases which are labelled as tree conflicts simply because the conflict involves a folder rather than a file. For example if you add a folder with the same name to both trunk and branch and then try to merge you will get a tree conflict. If you want to keep the folder from the merge target, just mark the conflict as resolved. If you want to use the one in the merge source then you need to SVN delete the one in the target first and run the merge again. If you need anything more complicated then you have to resolve manually.             

Scala "for" iteration with indexes

In Scala, to iterate through a collection of items while keeping an index, Seq.zipWithIndex:


for (e <- items.zipWithIndex) {
  println(e._1 + " at index " + e._2)
}


(I find this especially useful when writing Scala code that calls into Java library setter methods that are index-based.)

Getting Started is the Hardest Part

Too often, when I'm trying to get started on a small, personal software project, I'm stymied by the time it takes to get the development environment and project infrastructure setup. With a full-time job as a developer, an addiction to cycling, and the responsibilities associated with being the parent of a two-year child, it's hard to find the mental energy and time to work on even a small software idea.  So when I do have an hour of mental energy available, the last thing I want to spend it on is project setup and configuration task. Maven archetypes to the rescue! Archetypes allow you to setup your project nearly instantly, and if you have appropriate Maven support in your IDE, you'll be ready to code within second (okay, minutes). If--and this is a big if--you can find an appropriately up-to-date archetype that provides the exact stack of technologies upon which your project will rely. So far, I don't seem to have such luck (can any one tell me where I can find well designed sampling of Scala-based Maven archetypes?) So instead of trying to start off with someone else's half-baked archetype each time I need to start a project, I've decided to take the time create my own archetype(s) that I can reuse and evolve for my own needs. The following Maven reference page was all I needed to figure out how to generated my own custom archetypes: http://maven.apache.org/archetype/maven-archetype-plugin/advanced-usage.html.