Lobste.rs access pattern statistics for research purposes

Hey fellow Lobsters!

I’m a grad student at MIT working on distributed systems, and have been developing a research prototype of a large-scale SQL-compatible database with built-in support for cache maintenance. It is intended for read-heavy applications, and already significantly outperforms regular databases and the common memcached+database setup.

For the purposes of evaluation of this system, it would be extremely useful to have a good idea of what a realistic workload looks like for a relatively read-heavy, but decently complex site such as Lobste.rs. To that end, I was hoping to get some insight into the Lobste.rs workload by running some analytics queries on the Lobste.rs database and web logs. @pushcx seemed open to this, but suggested that I post here first to solicit some feedback on the queries I want to run and any potential privacy implications they may have.

I realize it wouldn't be okay to give out the raw data, but a rough idea of which page visit distribution (e.g., 84% front-page, 10% comment view, 5% upvote, 1% comment) popularity skew (data of #upvotes vs article ID, or views vs article ID for some period of time would be super helpful!), read-write ratio, etc. would go a long way towards building a reliable evaluation (and guiding future design of the system!). It could also be extremely useful to other researchers who may want to build solutions for "real" web applications.

First, we want to identify the distribution of upvotes across stories. Specifically, how many stories have various (binned) upvote counts. For example, this would tell us how many stories have thousands of votes, how many have 0-100 votes, etc. Note that we do not distinguish between upvotes and downvotes in this case, since they are the same as far as workload generation is concerned. The specific query we'd like to run against the Lobste.rs schema is:

SELECT ROUND(upvotes + downvotes, -2) AS bucket,
       COUNT(id),
   RPAD('', LN(COUNT(id)), '*')
FROM stories GROUP BY bucket

Similarly, we'd like to extract the comment count distribution:

SELECT ROUND(comments_count, -2) AS bucket,
       COUNT(id),
   RPAD('', LN(COUNT(id)), '*')
FROM stories GROUP BY bucket

These both use the technique outlined here to get an approximated histogram, which gives a good overview of the spread and distribution of popularity without giving all the raw data.

We also want the distribution of upvotes across users. Specifically, how many users have various (binned) upvote counts. This aids us in constructing a workload generator that faithfully models some users being more active than others. The rounding ensures that we only learn how many users have a number of votes in each bin 100*i to 100*(i+1):

SELECT ROUND(counted.cnt, -2) AS bucket,
       COUNT(user_id),
   RPAD('', LN(COUNT(user_id)), '*')
FROM (
    SELECT user_id, COUNT(id) AS cnt
FROM votes GROUP BY user_id
) AS counted
GROUP BY bucket

From the nginx logs, we'd like to run the following for however much of the log is okay (more is better):

grep -vE "assets|fetch_url_attributes|check_url_dupe" access.log
 | sed -e 's/.*\(GET\|POST\)/\1/'
       -e 's/ \/\(s\|stories\)\/[^\/]*/ \/stories\/X/'
       -e '/^GET / s/X\/.*/X\/Y/'
   -e 's/ \/comments\/[^\/]*/ \/comments\/X/'
 | awk '/^(GET|POST)/ {print $1" "$2}'
 | grep -E ' /(stories|comments|login|logout|$)'
 | sort | uniq -c | sort -rnk1,1

Phew, that's a mouthful. Let's first see what it produces:

 10 GET /
  9 GET /stories/X/Y
  4 POST /login
  2 POST /stories/X/upvote
  2 POST /stories
  2 POST /comments/X/upvote
  2 POST /comments/X/unvote
  2 POST /comments
  2 GET /comments
  1 POST /stories/X/unvote
  1 POST /logout
  1 GET /login
  1 GET /comments/X/reply

Basically, it looks for requests to "interesting" pages (that is, the frontpage, /stories, /comments, and /login + /logout), masks any dynamic parameters, and the shows the number of requests to each of those resources. This gives us an idea of which types of pages are accessed more frequently, including whether they are generally reads or writes. If it feels too invasive to include absolute counts, percentages of total would also be fine.

We'd love to also learn the distribution of page popularities for both reads and writes, but we figured we'd leave that out of the initial analysis as it does leak a little more information. It's also a little tricky to get right without just dumping all the data. We can approximate it using upvotes and # of comments, but the two aren't necessarily perfectly correlated.

I'd love to hear what you all think about these, and whether you can think of any improvements. The ultimate goal here is to construct a workload generator that can simulate a semi-realistic request load for a Lobste.rs-like website. I think the data above should go a long way towards that goal, though I may have missed something.

Cheers, Jon