We are now running a beta site for TWIMPACT for Japan only. It only works with japanese tweets and works quite well. What is interesting is the battle between a former politician 555hamako and masason the president of Softbank, a large telecommunications company in Japan.

First, masason started out december 2009 with a quick rise to the top. Then 555hamako followed beginning of 2010 (the year of elections) with an even steeper rise to take the crown. Also, it looks like masason has not managed to attract the same size of an audience as before, his TWIMPACT stalls a little at the end. Maybe he was just watching the winter Olympics.

I guess we will be starting to adapt the TWIMPACT rating to degrade over time to provide a better view of the current impact a user has. Even though it is hard to keep on rising one keeps its TWIMPACT at the moment. This effect is much more visible on the global site where you can find a lot of not-so-spammy-spam twitterers that rise quickly and should fall down over time again after they hit the ceiling.

I have used the database left over from my last insert test and added a few indexes which takes around 30-40 minutes per index. I did not check in more detail about the time it takes as we tend to create the index while working on the database anyway.

Now for todays results. The queries are quite simple, but in our case practical. I get a cursor for 1.000.000 documents as a result of a simple query and count the amount of documents that have the value of one of the documents properties:

def cursor = db.find().limit(1000000)
// alternative: query one of the indexed properties
// def cursor = db.find(new BasicDBObject("property", new BasicDBObject("\$ne", null))).limit(1000000)
cursor.each { doc ->
  def value = doc.get("property")
  def count = db.getCount(new BasicDBObject("property", value))
}

The time was taken for each of the "db.getCount()" calls and it turns out that around 40-50% of all queries result in negligible query time (< 1ms) which is the smallest time frame I can measure right now. This needs to be taking into account when evaluating the graphs as they only show the queries with at least 1ms duration (log scale plot).

In the plot you see query time versus the result of getCount(). As expected higher counts may take longer,

Some explanation is necessary for the plots. random means that I get some documents and count one of the properties (the same for all documents). I do not know the order in which the documents come, so they are unrelated to the property I am counting. correlated is the counting if I query the documents using an index and the count the property that was indexed. The assumption here was that it might be easier for the database to count all documents having a certain property value if I previously queried all documents having a non-null property value.

This holds true for the long index but not for the string index. The latter behaves about the same as my random counts.

The results show that count queries are very fast, but only if indexed.

What we also need for twimpact.com are some more advanced queries. I assume that the results for those also depend on how we design our documents to fit our needs. The design will take some time and I will get back with results of design and advanced queries at a later date.

PostgreSQL

That is a reason I am looking at non-SQL alternatives. The list includes redis, the Cassandra Project and MongoDB.

I do admit, I only looked shortly at redis, but this is due to the fact that it is a very simple key/value store and we do need some query functionality. Some playing with Cassandra and the Java driver was awkward and in the end I had MongoDB up and running in no time.

The setup is as follows:

• 4GB MacBook, 2.4Ghz Intel Core 2 Duo, slow disk
• MongoDB: mongodb-osx-x86_64-2009-09-19
• (i had to work in parallel, so there might be some swapping)

// initialize MongoDB (started with a complete new one for each test)
def db = new Mongo("twimpact")
DBCollection coll = db.getCollection("twimpact");
// coll.createIndex(new BasicDBObject("retweet_id", 1)) // long index
// coll.createIndex(new BasicDBObject("from_user", 1))  // short string index
def offset = 0
def limit = 10000
def rowCount = sql.count("tweets")
while(offset < rowCount) {
  // get batch of tweets form PostgreSQL server
  def data = sql.rows("SELECT * FROM tweets OFFSET ${offset} LIMIT ${limit}")
  // convert each row into a document and insert
  data.each { row ->
    BasicDBObject info = new BasicDBObject();
    row.each { key, value ->
      info.put(key, value);
    }
    coll.insert(info);
  }
  offset += data.size()
}

The time was taken for requesting the data from the SQL data (not shown in the graphs) and for the row loop. In case of the bulk insert test the row loop first stored 5000 new documents in a pre-allocated array and then inserted them:

…
  DBObject[] bulk = DBObject[5000]
  … loop …
  // two times as 10000 was too big for the driver
  coll.insert(bulk)
...

The documents we created were not that big, but have some real-world importance to use with their structure. They might be changed to adapt to the non-schema world though. Here is a good example:

{
  "id": 3551935825,
  "user_id": 1657468,
  "retweet_id": 15965974 ,
  "from_user": "thinkberg", 
  "from_user_id": 6190551, 
  "to_user": null , 
  "to_user_id": null, 
  "text": "RT @Neurotechnology interesting post, RT @chris23 Augmented Reality Meets Brain-Computer Interface http://bit.ly/3fg9OG", 
  "iso_language_code": "en", 
  "source": "<a href="http://adium.im" rel="nofollow">Adium</a>", 
  "created_at": "Wed Aug 26 2009 06:49:09 GMT+0200 (CEST)",
  "updated_at": "Wed Aug 26 2009 06:50:11 GMT+0200 (CEST)",
  "version": 0,
  "retweet_user_id": null
}

And now for the results. Just like expected there is a downgrade in performance as soon as a certain size of the database is reached. MongoDB took about 2.8GB of my RAM and had to create new data files during the process.

The first insert test did not create or update any index so there is a sustained performance over the whole time. There are remarkable dips which probably happened whenever I unlocked the laptop or switched from one application to another.

Looking at the insert with a number (long) index it appears that the performance degrades slightly and stabilizes shortly after about 20.000.000 inserts. I guess this might be the point where RAM shortness comes into play as you can see similar behavior in the string and bulk/string index tests.

A dramatic performance boost had the bulk inserting. Unfortunately I had to insert each batch in two bulks of 5.000 tweets each as the driver reported that the object was too big" when using an array of 10.000 tweets. While single inserts stabilize around 1000 tweets/s at the end, the bulk insert still reached about 1500-2000 tweets/s.

Looking at where the insert performance started and where it ended might let you conclude that this is going to be slow, but from my experience with a much smaller PostgreSQL database (~4.000.000 tweets) on this laptop I am impressed. Being able to insert around 1000 tweets/s is way faster than what we experience with the current system at twimpact.com where we accumulate an analyzer backlog. Given the fact that this test was performed on my laptop and not a production system it is to be expected that the reality looks much better :-)

But inserting is not all, even though this is what we do a lot. Next I am going to take the database and do some query testing to see whether it fits our needs.

All done in grails, which I love.

external SATA dock

Then you can do off-RAID backup to the disks. Those disks probably last longer than your DVD backups.

Amazon S3

All data transfer is encrypted. The data files are encrypted at either offsite backup but not on the RAID for easy access.

Industry Liaison Manager

Machine Learning

Neurotechnology Research

My main focus now will be to manage our industry relations, organize talks and seminars and work on technology transfer. The research project works on non-invasive neurotechnology to improve sensors, data analysis and apply the results in neuro-usability and other applications related to man-machine interaction.

This is going to be a challenging and most interesting job.

Still a long way to go.

Update: (2009-01-28) In the meantime I implemented the property handling which only fails for some strange UTF-8 property values. Now the litmus test runs 99% through. Using MacOS X Finder to test looks promising.

Google Reader

Unlike these friends I never really started to use twitter and even deleted my account there, as well as in a few other social networking systems. I give away so much already so I don't want to make the harvesting too easy. What strikes me though is, why a service like twitter has taken away the public chat room from classic instant messaging systems. It works much like IRC (Internet Relay Chat) where you can just join into an open chat. However, it looks crude that you have to read the others chat to actually communicate.

I guess the real advantage of twitter is the simple user interfaces on loads of different systems that heavy weight instant messaging systems failed to provide until now.