Saturday, January 03, 2009

Maintaining summaries in a highly concurrent fashion

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.

The table contained won prizes looked basically like this:
SQL> create table winners
2 (
3 prize_id number primary key,
4 user_id number
5 );

Table created
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:
SQL> create table winners_cnt_t
2 (
3 slot_id number primary key,
4 cnt number
5 ) organization index;

Table created

SQL> insert into winners_cnt_t
2 select level-1, 0
3 from dual
4 connect by level <= 11;

11 rows inserted

SQL> commit;

Commit complete
Make sure your slots count is a prime number. Then you can hide it behind the view to make things transparent:
create view winners_cnt as
select sum(cnt) cnt
from winners_cnt_t;
After that, all you have to do is maintain number of winners the following way...
update winners_cnt_t
set cnt=cnt+1
where slot_id=mod(p_prize_id, 11);
...which will distribute updates evenly among all rows in the table. You can vary number of slots to match your degree of concurrency.


  1. Hi Alex,
    have you spread your winners_cnt_t across multiple blocks to avoid block or latch contentions? Or was this no issue in your case?

  2. Martin,

    thanks for the question.

    We've never hit any significant buffer busy buffer or CBC latch waits there, no.

    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).