You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Mike Peters <ca...@softwareprojects.com> on 2011/10/01 07:19:21 UTC
Cassandra 0.8 Counters Inverted Index?
Hi,
We're using Cassandra 0.8 counters in production and loving it!
One issue we're running into is we need an efficient mechanism to
retrieve the "top 100" results, sorted by count values.
We have tens of thousands of counters growing rapidly (one counter per
each combination of date.source_id). What we're looking for is, what's
the best way to retrieve the top 100 "sources" for a given date, without
having to iterate through all counters created for that date?
Right now to accomplish this, we are managing an inverted index of count
values. This is very inefficient and kills our write performance,
because after every counter-increment, we have to read its value and
store it into an inverted index that looks like this:
Key, CounterName
000005 2011-10-01.source1
000009 2011-10-01.source2
000010 2011-10-01.source3
If source2 just generated 100 "hits", we need to delete the row with the
key of "000009" from the inverted index and insert a new one with the
new counter value for source2:
Key, CounterName
000005 2011-10-01.source1
000010 2011-10-01.source3
000109 2011-10-01.source2
The additional reads and deletes are killing our performance.
Any one has any ideas about a more efficient way to utilize counters and
support "top 100" results?
Looking forward to any ideas and feedback you can share.
Thanks,
Mike Peters
Re: Cassandra 0.8 Counters Inverted Index?
Posted by Richard Low <rl...@acunu.com>.
On Mon, Oct 3, 2011 at 9:14 AM, Pierre-Yves Ritschard
<py...@smallrivers.com> wrote:
> Unfortunately there's no way to do this in Cassandra right now, except
> by using another row as index, like you're doing right now.
>
> Of course you could also store by source_id.date and have a batch job
> iterate over all sources to compute the top 100. It would not be real
> time any more though.
Indexes are used to trade-off some insert performance for write
performance. The index you describe is optimal for reads, so writes
take a hit. As Pierre says, the only way to maintain an index in
Cassandra is to read, delete and insert on every increment. This is
how secondary indexes work under the hood in Cassandra, although they
are not implemented for counters. It's more expensive for counters
though since a counter read is in general more expensive.
So to speed up inserts, you have to take the hit on reads. The other
extreme is to not build an index at all and read in all the counters
and sort on the client. But given you have 10,000s of counters, this
will be slow, but inserts are optimal. A batch job will work too,
provided you are happy to have it non-real time, or slightly out of
date.
Richard.
--
Richard Low
Acunu | http://www.acunu.com | @acunu
Re: Cassandra 0.8 Counters Inverted Index?
Posted by Pierre-Yves Ritschard <py...@smallrivers.com>.
Unfortunately there's no way to do this in Cassandra right now, except
by using another row as index, like you're doing right now.
Of course you could also store by source_id.date and have a batch job
iterate over all sources to compute the top 100. It would not be real
time any more though.
- pyr
On Sun, 02 Oct 2011 15:01:20 -0400
Mike Peters <ca...@softwareprojects.com> wrote:
> Any ideas?
>
>
> Thanks,
> Mike Peters
>
> On 10/1/2011 1:19 AM, Mike Peters wrote:
> >
> >
> > Hi,
> >
> > We're using Cassandra 0.8 counters in production and loving it!
> >
> > One issue we're running into is we need an efficient mechanism to
> > retrieve the "top 100" results, sorted by count values.
> >
> > We have tens of thousands of counters growing rapidly (one counter
> > per each combination of date.source_id). What we're looking for
> > is, what's the best way to retrieve the top 100 "sources" for a
> > given date, without having to iterate through all counters created
> > for that date?
> >
> > Right now to accomplish this, we are managing an inverted index of
> > count values. This is very inefficient and kills our write
> > performance, because after every counter-increment, we have to read
> > its value and store it into an inverted index that looks like this:
> >
> > Key, CounterName
> > 000005 2011-10-01.source1
> > 000009 2011-10-01.source2
> > 000010 2011-10-01.source3
> >
> > If source2 just generated 100 "hits", we need to delete the row
> > with the key of "000009" from the inverted index and insert a new
> > one with the new counter value for source2:
> >
> > Key, CounterName
> > 000005 2011-10-01.source1
> > 000010 2011-10-01.source3
> > 000109 2011-10-01.source2
> >
> > The additional reads and deletes are killing our performance.
> >
> > Any one has any ideas about a more efficient way to utilize
> > counters and support "top 100" results?
> >
> > Looking forward to any ideas and feedback you can share.
> >
> >
> > Thanks,
> > Mike Peters
> >
>
Re: Cassandra 0.8 Counters Inverted Index?
Posted by Mike Peters <ca...@softwareprojects.com>.
Any ideas?
Thanks,
Mike Peters
On 10/1/2011 1:19 AM, Mike Peters wrote:
>
>
> Hi,
>
> We're using Cassandra 0.8 counters in production and loving it!
>
> One issue we're running into is we need an efficient mechanism to
> retrieve the "top 100" results, sorted by count values.
>
> We have tens of thousands of counters growing rapidly (one counter per
> each combination of date.source_id). What we're looking for is,
> what's the best way to retrieve the top 100 "sources" for a given
> date, without having to iterate through all counters created for that
> date?
>
> Right now to accomplish this, we are managing an inverted index of
> count values. This is very inefficient and kills our write
> performance, because after every counter-increment, we have to read
> its value and store it into an inverted index that looks like this:
>
> Key, CounterName
> 000005 2011-10-01.source1
> 000009 2011-10-01.source2
> 000010 2011-10-01.source3
>
> If source2 just generated 100 "hits", we need to delete the row with
> the key of "000009" from the inverted index and insert a new one with
> the new counter value for source2:
>
> Key, CounterName
> 000005 2011-10-01.source1
> 000010 2011-10-01.source3
> 000109 2011-10-01.source2
>
> The additional reads and deletes are killing our performance.
>
> Any one has any ideas about a more efficient way to utilize counters
> and support "top 100" results?
>
> Looking forward to any ideas and feedback you can share.
>
>
> Thanks,
> Mike Peters
>