Fishpool

To content | To menu | To search

Wednesday 8 April 2009

Using the Infobright Community Edition for event log storage

Apart from the primary "here's how we ended up using Infobright for data warehousing and how is that working out" topic I'm going to discuss in my MySQL Conf presentation I'll touch on another application, the use of Infobright's open-source Community Edition server for collection and storage of event logs. This is a system we've implemented in the past couple of months to solve a number of data management problems that were gradually becoming problematic for our infrastructure.

We've traditionally stored structured event log data in databases for ease of management. Since Habbo uses MySQL for most everything else, putting the log tables in the same databases was pretty natural. However, there are significant problems to this approach:

  • MyISAM tables suffer from concurrency issues and crash-recovery is very slow due to table consistency check
  • InnoDB tables suffer from I/O bottlenecks and crash-recovery is very slow due to rollback segment processing
  • Both scale badly to hundreds of millions of rows (especially if indexing is required), and mixing them is not a recommended practice
  • Storage becomes an issue over time, especially as indexes can easily require many times as much disk than the data, and an event log is going to have a LOT of rows
  • Partitioning has only recently become available, and before that, managing "archive" tables needed manual effort
  • Perhaps worst of all (as it's very hard to measure), if any of this is happening on the primary DB servers, it's competing for buffer pool memory with transactional tables, thus slowing down everything due to cache misses

Over the years, we've tackled these issues in many ways. However, with our initial experience of scaling an Infobright installation for data warehousing needs, a pretty simple solution became apparent, and we rapidly implemented an asynchronous, buffered mechanism to stream data into an ICE database. We're early with this implementation, but it has turned out to be a satisfactory high-performance solution. Even better, it's a very simple thing to implement, even in a clustered service spanning many hosts, as long as log tables don't need to be guaranteed 100% complete or up-to-date to the last second. Here's a description of the simple solution; extending that to the complex solution providing those guarantees is left as an exercise to the reader.

Rather than running single INSERTs to a log table or writing lines to a text file log, each server buffers a small set of events, eg for the past second in a memory buffer. These are then sent over a message bus or lightweight RPC call to a log server, which writes them to a log file that is closed and switched to a new file after every megarow or every few minutes, whichever is smaller. A second process running on this log server wakes up periodically and loads each of these files (minus the last one, which is still being written to) into the database with LOAD DATA INFILE.

This has multiple general benefits:

  • Buffered messaging requires much less time on the "client" servers compared to managing database connections and executing thousands of small transactions
  • The asynchronous processing ensures database contention can not produce random delays to the normal service operation
  • Batch loading of text files is implemented by every DB server, so there's little in this implementation that is proprietary or dependent on any particular DB solution
Using the Infobright ICE as the backend database provides a number of additional specific benefits:
  • Excellent data load performance
  • No index management, yet capability to run queries on the data without first extracting it to another DB
  • No degradation of performance as deployment size grows, as would happen even to a MyISAM table should it have any indexes
  • Compressed storage, so less spinning iron required
  • Columnar datapack organization should not require table partitioning even over long periods
This works very well for structured events. For unstructured data, a different solution is required, which I will discuss at some later date.

Update: Mark Callaghan asked in the comments for some quantified details. We have not spent the time to produce repeatable benchmarks, so all I can offer on that front is anecdotal data - it's very conclusive for us, given it's addressing our real concerns, but less so for others. That said, ICE does not support inserts, only batch loads, so the solution had to be engineered to use that, which added some complexity, but brought orders of magnitude more performance. A simple benchmark run showed that the end-to-end performance for this exceeded 100,000 events per second when running all parts of the client-logserver-database chain on a single desktop machine.

Query performance depends on the queries made. Summary data is 2-3 orders of magnitude faster to access, the bigger the dataset, the bigger the performance benefit - but expecting that for single row accesses would disappoint badly. Storage compression varies wildly depending on the data in question -- we've seen up to 15:1 compression on some real-world data sets, but others (such as storing email addresses in a varchar column) actually expand on storage. This is why I think of this as a solution for structured, quantified event logs, not for general unstructured log file storage.

Thursday 2 April 2009

Amazon order sizes, ideal behaviour, and proof of market friction

I wrote last fall about the "sweet spot" in pricing and spending patterns for a microtransaction-based service and business model, where I posited that given flexible consumption, revenue could be maximized by ensuring the lowest possible minimum price point; one which is preferably closer to 1 cent than one 1 euro. Depending on the goods sold and amount of logistics overhead, the minimum profitable price may of course be much higher, and depending on the payment mechanisms available, the minimum price for which the consumers effort overhead exceeds the cost of the good may also be fairly significant. A chocolate bar may be sellable for 40 cents, while few durable goods can achieve a price point lower than a few euros. For virtual goods, the minimum pricing is mostly a question of efficient mechanism for transferring low amounts of money, because the minimum "size" of the good sold can be in theory reduced ad infinitum, and distribution costs are a non-issue.

Last week there was a Facebook Developer Garage day in San Francisco where a couple of interesting presentations were given. I wasn't there, but browsing through the material I found this slide about the distribution of order sizes among Amazon customers (slide 10 in the deck):

It's interesting to see the similarities on this chart to the behavior in virtual goods. In this data, the observed behavior follows the power law model in an ideal fashion at price points over $25, but the drop-off below that order size is remarkably fast. This is the result of primarily the goods sold and the logistical overheads implicit in that; it just doesn't make much sense for someone to order $5 worth of goods from Amazon given the shipping costs and delays incurred on top of the purchase.

For virtual goods, the drop-off point can be much lower, but still, a similar drop off does happen - again because below a certain price point and transactional overhead level, neither the consumer of the good nor its producer see value in the market. At prices above that, the transactional model does however exhibit the power law distribution. Again, by reducing the minimum marketable and profitable price point, there is a big potential customer base to be gained at the bottom end of the pricing scale. Most companies leave an amazing amount of revenue on the table by not addressing this issue.

I'm still thinking of OnLive. Why is that?

I pretty much blasted OnLive the other day as something that doesn't hold a candle to the distribution power that is the web. Still I keep wondering what's the draw of it. Positioned against the consoles business, it does have clear benefits, clear enough that Nintendo's Reggie Fils-Aime felt it necessary to try to dismiss it. That is, with the exception that it doesn't run console games, only PC games. Today though I read this post from Keith Boesky (RT @jussil), and sure, looking at it from the perspective of building it for acquisition, yeah, it makes perfect sense. I guess my weakness is I always try to understand things like this as standalone businesses, when they're probably not meant to be that. My bad.

Friday 27 March 2009

Why OnLive will not be the massive tectonic shift so many are currently predicting

Among the things announced this week in GDC were two developments in entirely different directions on a particular axis of games technology: first, the OnLive network of thin clients showing network-streamed video games rendered on a server cluster somewhere, and second, the Mozilla/Khronos Group initiative to develop an OpenGL-accelerated, JavaScript-programmed 3D canvas in a web browser. Both have one thing in common: make it possible to run 3D apps (games) on standard devices without prior installations. How they go about that goal is radically different. One of them will fail.

OnLive is not the first company to attempt their idea. It's a basic extension to the same theme that has been around since at least the inception of the X Window System and Sun NeWS in early 80s - graphical thin clients showing applications running somewhere in the network. Further, the idea was explored for 3D games in the late 90s by G-Cluster, which apparently is still around in Japan in some form or another. In my opinion, it's a misguided approach. Certainly there's value to server-side processing, even of graphics, but the final rendering just makes so much more sense to be done on the client even when all of the application logic is remote.

What kind of client? Well, anything that can run a high-performance VM for Java or JavaScript (ie, a modern browser), and has 3D acceleration functionalities built into the graphics pipeline. This includes basically every network-connected device from the cost of $200 upwards: all smart phones, all netbooks, all laptops, all games consoles, and so on. Some of those devices are still intentionally crippled by their manufacturers in terms of operating system support for the required features, and clearly the 3D Canvas development hasn't been finished yet. The hardware capabilities, however, are already deployed to hundreds of millions of consumers.

Ignoring that deployed base and trying to scale a server-side rendering solution to the same figures is just mad. And that's not even considering the framerate and responsiveness constraints that are inescapable simply because of the round-trip network latency of such a system: on a high-bandwidth wired network 10s of milliseconds (not everyone can be situated within a few kilometers of the server cluster), and on radio networks, 100s of ms. Developing high-framerate games under those circumstances is hard enough when you only need to deal within transmitting positional data and adjusting for lag and jitter in both ends - making the games playable when every action made by the player needs to go to the server and back before it shows up is practically speaking impossible.

(Update an hour later) I suppose I should acknowledge that clearly the OnLive approach does have certain benefits to it: no piracy, little hacking of the typical kinds, little opportunity to cheat, and no need for investing in PunkBuster-type technology in the game clients, since none of that is running locally. However, all that just simply will not matter when weighed against the enormous brunt of having to run all that rendering in the wrong end of the MMO network and ignoring the opportunities to disperse so much of the investment and energy requirements to the gamers.

Saturday 21 March 2009

What would you like to hear from me in MySQL Conf?

I'm going to be talking in MySQL Conf 2009 about our business intelligence and data warehousing solutions for Habbo. Since this blog is syndicated on Planet MySQL, and I presume many of the people going to the conference read it, here's a question: would you like to hear about the why's of our technology selection (eg, IT management level questions), the techniques we use for analysis (for the BI analyst or startup technologist), or about the nuts and bolts of the database implementation itself (for the DBAs in the crowd)? I'm going to be touching on all three aspects, perhaps more, but can and will focus on one of the areas in more detail.

Thursday 12 March 2009

Working on presentations

I started working past the abstract of my upcoming MySQL Conf 2009 presentation on scaling business intelligence solutions for Habbo this week. Since I learned of an interesting new presentation tool, Prezi from Jussi Laakkonen, I decided to give it a shot with this presentation, since PowerPoint and other slideware tools have never really felt like a good medium to me.

Prezi is certainly fun to play with, and pretty useful for turning a rapid mindmap-style set of notes into a presentation that while perhaps basic, at least is stylistically "different". However, several limitations rapidly become apparent as well. Before reading any further though; go and take a look at their 4-minute demo video and perhaps even try the online demo for a minute or two. I don't want to taint your first impression with these criticisms...

First of all; for me presentation means a performance. A powerpoint deck sent over email is not a presentation; it's an extremely clumsy alternative to a well-written 3-page white paper. Having someone read aloud a bunch of bullet points or throw page after page of text on a wall is not a presentation either - that's the bane of modern corporate culture.

Someone communicating their thoughts verbally, preferably in person (though sometimes a video recording or a teleconference will have to make do) using a set of visual support material is a presentation. Oftentimes they are informative or interesting, sometimes thought-provoking. Once in a while memorable. That's what I'd like to learn to do, though I make no claims to having reached any of those levels yet. One has to have aspirational targets, though.

Now, my workflow for trying to get there goes roughly as follows:

Write down an outline of the ideas I want to communicate, either as a mindmap or as a plain-text document. I typically end up skipping mindmaps here, because I'm lazy and haven't found any mindmap tool yet to be as fast as just plain pounding on a keyboard.

Expand that outline to include all the data I want to show to support my arguments. This is the point where it would have paid off if I had started with a mindmap; but like I said, I'm lazy, sometimes to the point of optimizing time now to spend more time in the next 15 minutes. My rationalization for this is that since the world can change in 15 minutes (insert your own example), what's the point of doing anything too early? Buy that or not :)

Create rough visualizations of all of the data, because I hate having to read text or numbers while listening to someone speak, and I figure I shouldn't have to put others through what I hate doing. Pictures it is, then.

Make the first pass on the sequence in which to present all this stuff. Here's one of the first conceptual challenges I have with Prezi: I get the usefulness of mindmaps, and I love the contextual documentation a good mindmap can produce; but how can a mindmap, a naturally non-linear medium, be the optimal format for what ultimately is going to be a linear storyline to be spoken through by someone? I'm not about to be stopped by a slight conceptual conflict, though.

With slideware tools, this is usually the point where I move the text into a slides outline for the first time. What I've written by this point will be rewritten almost in its entirety by the time I'm through, but rather than organize clouds in a mindmap, I'm now starting to work on the order of ideas as collections of slides.

Once the rough shape of the presentation is in place, I start working on three things at once: the text of the presentation (which I tend to draft up as speech notes just to get a feel for the number of words required to describe the idea, even if I ultimately don't refer to them much), the text on the slides (which tends to be lots and lots of individual words or short sentences shown one by one, rather than lists of bullets), and the rest of the visual support material (eg, actual pictures, rather than the charts and visualizations).

At the end of this phase, I have what I might call a first presentable draft. What's left now is polish: make sure the fonts match, text is aligned, pictures look smooth, transitions work, and so on. While I work on the polish, I'm likely to run through the presentation speaking it through a few times, to time myself, make sure I remember the linear order (since until now I've been working on all parts of it at once), and so on. Because getting the timing right is enough work on its own, I tend to want to leave the visual polish up to a good template; I apply one now, and a jumble of text from random places gets converted to the same style, slide backgrounds are matched up (some have likely been copied from older decks and would not necessarily have the exact same style), and so on.

Now for the parts where Prezi could, but as of yet doesn't support me in this process:

  1. It is impossible to rearrange text in a truly mindmap fashion. Either it's in the same text block and can at best be copy-pasted, or it's independent phrases that have to be manipulated one-by-one.
  2. Because styles can not be applied at the END of the workflow, I have to constantly interrupt my thinking of the ideas by thinking of the polish.
  3. Partially related to point 1, a keyboard pounder like myself is forced to grab the mouse to reposition, or end up writing a notepad-like text document into a Flash text field.
  4. And related to all of the above; copying sections of material from one presentation to another is impossible, so either each presentation must be a from-scratch operation (which might be okay if the mindmapping worked really smoothly and I could at least collect a media library over time), or they have to be evolutionary changes as new versions. Having multiple presentations partially cover the same stuff (as in; short product overview at the beginning of a presentation) is very cumbersome. You might argue it's not a good presentation style to include significant amounts of boilerplate anyway, and I might agree with you, but then the from-scratch creation process just becomes that much more important.
I made already an outline of the presentation I mentioned in the beginning in Prezi, but given these limitations, I may just have to re-do it as slideware after all. I'm bummed about that, though, and I'm sure it'll develop past these limitations.

Tuesday 10 March 2009

More interesting LinkedIn advertising profiling

I wrote earlier about how LinkedIn provides profile-based targeting of Google AdWords on their site. Today I learned that for over six months, they've been experimenting with New York Times on targeting content on nytimes.com based on the common visitors' LinkedIn profiles. I hadn't noticed this because NYTimes doesn't belong to my regular news sources; it probably should, as they've certainly been innovating on making news more relevant in this day and age, and I applaud them for that effort.

Anyway, had a brief look at how this is implemented as well. It's a fairly interesting JavaScript widget pulling in RSS feeds from different NY Times sections, though for now I didn't catch at what point did nytimes.com ask linkedin.com for the industry segment for me. Never mind though; such a cross-site request over AJAX would be pretty trivial to implement.

This of course resembles what Facebook Beacon was supposed to do, has some similarities also to Facebook Connect, and, I would assume, where MySpaceID and Google Friend Connect might also evolve as time passes. It certainly is a sign of things to come, where the siloing of data between different services starts to break down and information leak between them more and more, intentionally as well as by accident. There are all kinds of interesting business opportunities, privacy issues as well as enhanced user experience possibilities such partnerships will uncover. Will have to think about this further.

Monday 16 February 2009

Kaksi ehdotusta Helsingin valtuustolle

(This being an entirely local subject, I'm making an exception from my normal practice and posting in Finnish)

Rautatieaseman kello näyttää peittyvän rakennustelineisiin estäen puolisen miljoonaa kellonajan vilkaisua päivässä. Ei pitäisi olla hirvittävän vaikeaa tai kallista asentaa telineisiin pieniä projektoreita jotka voisivat heijastaa suojakankaisiin kellonajan ja näin palvella kaupunkilaisia myös epäilemättä välttämättömän remonttiprojektin ajan.

Miljoonalta pääkaupunkilaiselta vaihdetaan matkakortit uusiin tänä vuonna, kun vanhasta tekniikasta luovutaan. Jostain syystä uuden kortin väritys on kuitenkin ainakin uutisoinnin mukaan projektin vaikein päätösasia. Hetkonen? Mistään pankista ei enää saa maksukorttia ensin päättämättä minkälaisella grafiikalla se tehtaalta käyttäjälleen kustomoidaan yksittäistoimituksen, mutta täysin etäohjelmoitavien, ennakolta vakioitujen korttien kanssa ei voida tarjota kuin yhtä graafista mallia? Onko se nyt oikeasti niin vaikeata tilata miljoonan samanlaisen kortin sijaan vaikka neljää eri väriä ja kuviota neljännesmiljoona kutakin ja antaa meidän maksajien valita minkälaista korttia haluamme busseissa ja ratikoissa esitellä?

Saturday 31 January 2009

Whoa, Google decided the whole internet is unsafe

Is it just me, or did Google's malware warning system just decide that the entire internet "may harm your computer"?

Dunno what that's about, but the behaviour persisted over all searches until I signed out of my Google account. After signing back in, the search works correctly again. Update: clearly, others noticed this, too.

Wednesday 21 January 2009

How LinkedIn's special keyword Google AdSense works

Have you noticed how Google AdWords shows you directly targeted advertising on LinkedIn? Something that goes far beyond AdWord's normal capabilities of targeting. For example, I always see recruitment ads for a local Java consultancy house known for their agile process expertise, no matter what I'm doing on LinkedIn. It certainly doesn't have anything to do with the page the ads are on, nor is it related to search keywords. As a proof, take the case of searching for a farming expert in Nepal: I'm still seeing ads for that consultancy, Scrum process management tools, and SOA testing. If there's a connection to my activities, I'm at loss what it is.

However, I know exactly what they ARE targeting. My own profile, which mentions all those things, and more. LinkedIn and Google are not advertising to my stated intent (what I'm searching for), or the page content (as an AdSense customer might expect), but myself and what I've told the service about me.

Continue reading...

- page 4 of 22 -