Saturday, January 03, 2009

I was involved in designing a highly-concurrent OLTP system last year (more than 300K users during peak hours) which were allowing users to play online and win some prizes. As part of the prize winning logic we had to maintain count of prizes won in order to be able to display it in real-time.

That is -- we were storing prize_id among with the user_id who won it. There were a couple of millions prizes available for winning. You can accomplish won prize counting in a number of ways:

Count it each time.Do this only if you partner up with the same folks who sell you hardware and Oracle CPU licenses.

Create on-commit materialized view with count.Though much better than previous KIWI'ish approach, it brings a bit of overhead for maintaining a materialized view log plus, if you don't take special cares, may blocks users during commit. Nevertheless, this is usually a valid approach, especially if you want to leverage features like query rewrite. We didn't really need it and, to make things more interesting, someone was winning a prize every so milliseconds. Do simple things (insert into winners, mview log, update summaries table, delete from mview log) a lot of times and wander how quickly it starts to add up.

Maintain summaries yourself.This is relatively easy to do, especially when application does nothing but calls a set of PL/SQL APIs (which means you can do whatever you want to archive the required output).

However, doing a simple update winners_cnt set cnt=cnt+1 is going to serialize all the winners so we need an easy way to spread stuff out. The simplest, yet very efficient, way to archive this is to do something like this:

One thing worth mentioning is that CBC latch contention is usually (but not necessarily, of course) a result of a poor schema design (think excessive LIOs) and when things are done properly from a ground up, it might be a surprise how little so-called "inevitable" problems you can really get.

BBW? We never projected enough wins for it to became a reality, but hash partitioning this IOT was under consideration during design, yes. That being said, a lot of other tables+indexes were hash partitioned in order to escape getting contention on a much more busiest tables.

Speaking of the design in general, it resulted in a database machine being four times bigger than we ever needed (and this is like 5 numbers figure of savings on Oracle licenses alone).