You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Alvin UW <al...@gmail.com> on 2011/08/23 19:03:59 UTC

Customized Secondary Index Schema

Hello,

As mentioned by Ed Anuff in his blog and slides, one way to build customized
secondary index is:
We use one CF, each row to represent a secondary index, with the secondary
index name as row key.
For example,

Indexes = {
"User_Keys_By_Last_Name" : {
"adams" : "e5d61f2b-…",
"alden" : "e80a17ba-…",
"anderson" : "e5d61f2b-…",
"davis" : "e719962b-…",
"doe" : "e78ece0f-…",
"franks" : "e66afd40-…",
… : …,
}
}

But the whole secondary index is partitioned into a single node, because of
the row key.
All the queries against this secondary index will go to this node. Of
course, there are some replica nodes.

Do you think this is a scalability problem, or any better solution to solve
it?
Thanks.

Re: Customized Secondary Index Schema

Posted by Ed Anuff <ed...@anuff.com>.
Agreed, that's what I meant by "there are a lot of simple ways to split it
up over multiple rows", assuming it necessary.

On Thu, Aug 25, 2011 at 4:24 PM, Konstantin Naryshkin
<ko...@a-bb.net>wrote:

> Why are you keeping all your indexes in the same row? We do a similar thing
> (maintain several indexes over the same data) and we just have an index
> column family with keys like "dest192.168.0.1" which means destination index
> of 192.168.0.1. You can do rows like User_Keys_By_Last_Name_adams and
> User_Keys_By_Last_Name_alden. You can keep the matching main column family
> key as the column name. This will ensure that your index is evenly
> distributed throughout your cluster.
>
> ----- Original Message -----
> From: "Ed Anuff" <ed...@anuff.com>
> To: user@cassandra.apache.org
> Sent: Thursday, August 25, 2011 12:48:49 PM
> Subject: Re: Customized Secondary Index Schema
>
> How many unique last names do you anticipate having? How many characters in
> the last name do you anticipate keeping in your index? You can easily do the
> math to figure out how many you could fit on a node. I think you'll find
> that the ceiling might be quite a bit higher than you think. If you have
> over a couple of hundred million users it might not be the best approach.
> There are a lot of very simple ways to split it up over multiple rows. As is
> the case with most things regarding Cassandra, the off-the-cuff assumptions
> only get you so far before you have to do some math and do some tests.
>
> As I mentioned in my talk, for simple uses cases like this, you probably
> should just start with the built in secondary indexes, but I assume you
> already have explored those.
>
> Ed
>
>
> On Thu, Aug 25, 2011 at 9:27 AM, Alvin UW < alvinuw@gmail.com > wrote:
>
>
> Yes, this is what I am worrying about.
>
>
> 2011/8/24 Ryan King < ryan@twitter.com >
>
>
>
>
>
> On Tue, Aug 23, 2011 at 10:03 AM, Alvin UW < alvinuw@gmail.com > wrote:
> > Hello,
> >
> > As mentioned by Ed Anuff in his blog and slides, one way to build
> customized
> > secondary index is:
> > We use one CF, each row to represent a secondary index, with the
> secondary
> > index name as row key.
> > For example,
> >
> > Indexes = {
> > "User_Keys_By_Last_Name" : {
> > "adams" : "e5d61f2b-…",
> > "alden" : "e80a17ba-…",
> > "anderson" : "e5d61f2b-…",
> > "davis" : "e719962b-…",
> > "doe" : "e78ece0f-…",
> > "franks" : "e66afd40-…",
> > … : …,
> > }
> > }
> >
> > But the whole secondary index is partitioned into a single node, because
> of
> > the row key.
> > All the queries against this secondary index will go to this node. Of
> > course, there are some replica nodes.
> >
> > Do you think this is a scalability problem, or any better solution to
> solve
> > it?
>
> Its certainly a scalability problem in that this solution has a hard
> ceiling (this index can't get larger than the capacity of any single
> node). It will probably work on small datasets, but if your dataset is
> small then why are you using cassandra?
>
> -ryan
>
>
>

Re: Customized Secondary Index Schema

Posted by Konstantin Naryshkin <ko...@a-bb.net>.
Well you could group all the duplicate adams as columns in the same row. This has several advantages:
* one, I am not sure what partitioner you plan to use, but if you plan to do key range queries  over all the same last names, you cannot use a RandomPartitioner since it does not support key ranges properly
* two, since you will allways be looking up all the adams together (since you do not know which you want), it makes sense to store them in a single row so that they are grouped together on disk. One of the big disadvantages of having many columns in a row is that the must all be read together. If you are going to query for all of them at the same time anyways, this disadvantage does not apply to you.

----- Original Message -----
From: "Alvin UW" <al...@gmail.com>
To: user@cassandra.apache.org
Sent: Thursday, August 25, 2011 5:11:07 PM
Subject: Re: Customized Secondary Index Schema

Thanks.
Assume I use this approach, use the last names as the row keys of secondary index, and use the base column family key as the column name.
There may be duplication key issue. We may solve it by composite key, like "adams_1" , "adams_2".
Then, we can query these index by range query starting with "adams_".
Am I right?

I want to know what's the cost difference of rang query and slice query?
If I can use either composite key or composite column name, which one gives me less query cost?




2011/8/25 Konstantin Naryshkin < konstantinn@a-bb.net >


Why are you keeping all your indexes in the same row? We do a similar thing (maintain several indexes over the same data) and we just have an index column family with keys like "dest192.168.0.1" which means destination index of 192.168.0.1. You can do rows like User_Keys_By_Last_Name_adams and User_Keys_By_Last_Name_alden. You can keep the matching main column family key as the column name. This will ensure that your index is evenly distributed throughout your cluster.




----- Original Message -----
From: "Ed Anuff" < ed@anuff.com >
To: user@cassandra.apache.org
Sent: Thursday, August 25, 2011 12:48:49 PM
Subject: Re: Customized Secondary Index Schema

How many unique last names do you anticipate having? How many characters in the last name do you anticipate keeping in your index? You can easily do the math to figure out how many you could fit on a node. I think you'll find that the ceiling might be quite a bit higher than you think. If you have over a couple of hundred million users it might not be the best approach. There are a lot of very simple ways to split it up over multiple rows. As is the case with most things regarding Cassandra, the off-the-cuff assumptions only get you so far before you have to do some math and do some tests.

As I mentioned in my talk, for simple uses cases like this, you probably should just start with the built in secondary indexes, but I assume you already have explored those.

Ed


On Thu, Aug 25, 2011 at 9:27 AM, Alvin UW < alvinuw@gmail.com > wrote:


Yes, this is what I am worrying about.


2011/8/24 Ryan King < ryan@twitter.com >





On Tue, Aug 23, 2011 at 10:03 AM, Alvin UW < alvinuw@gmail.com > wrote:
> Hello,
>
> As mentioned by Ed Anuff in his blog and slides, one way to build customized
> secondary index is:
> We use one CF, each row to represent a secondary index, with the secondary
> index name as row key.
> For example,
>
> Indexes = {
> "User_Keys_By_Last_Name" : {
> "adams" : "e5d61f2b-…",
> "alden" : "e80a17ba-…",
> "anderson" : "e5d61f2b-…",
> "davis" : "e719962b-…",
> "doe" : "e78ece0f-…",
> "franks" : "e66afd40-…",
> … : …,
> }
> }
>
> But the whole secondary index is partitioned into a single node, because of
> the row key.
> All the queries against this secondary index will go to this node. Of
> course, there are some replica nodes.
>
> Do you think this is a scalability problem, or any better solution to solve
> it?

Its certainly a scalability problem in that this solution has a hard
ceiling (this index can't get larger than the capacity of any single
node). It will probably work on small datasets, but if your dataset is
small then why are you using cassandra?

-ryan




Re: Customized Secondary Index Schema

Posted by Alvin UW <al...@gmail.com>.
Thanks.
Assume I use this approach, use the last names as the row keys of secondary
index, and use the base column family key as the column name.
There may be duplication key issue. We may solve it by composite key, like
"adams_1" , "adams_2".
Then, we can query these index by range query starting with "adams_".
Am I right?

I want to know what's the cost difference of rang query and slice query?
If I can use either composite key or composite column name, which one gives
me less query cost?



2011/8/25 Konstantin Naryshkin <ko...@a-bb.net>

> Why are you keeping all your indexes in the same row? We do a similar thing
> (maintain several indexes over the same data) and we just have an index
> column family with keys like "dest192.168.0.1" which means destination index
> of 192.168.0.1. You can do rows like User_Keys_By_Last_Name_adams and
> User_Keys_By_Last_Name_alden. You can keep the matching main column family
> key as the column name. This will ensure that your index is evenly
> distributed throughout your cluster.
>
> ----- Original Message -----
> From: "Ed Anuff" <ed...@anuff.com>
> To: user@cassandra.apache.org
> Sent: Thursday, August 25, 2011 12:48:49 PM
> Subject: Re: Customized Secondary Index Schema
>
> How many unique last names do you anticipate having? How many characters in
> the last name do you anticipate keeping in your index? You can easily do the
> math to figure out how many you could fit on a node. I think you'll find
> that the ceiling might be quite a bit higher than you think. If you have
> over a couple of hundred million users it might not be the best approach.
> There are a lot of very simple ways to split it up over multiple rows. As is
> the case with most things regarding Cassandra, the off-the-cuff assumptions
> only get you so far before you have to do some math and do some tests.
>
> As I mentioned in my talk, for simple uses cases like this, you probably
> should just start with the built in secondary indexes, but I assume you
> already have explored those.
>
> Ed
>
>
> On Thu, Aug 25, 2011 at 9:27 AM, Alvin UW < alvinuw@gmail.com > wrote:
>
>
> Yes, this is what I am worrying about.
>
>
> 2011/8/24 Ryan King < ryan@twitter.com >
>
>
>
>
>
> On Tue, Aug 23, 2011 at 10:03 AM, Alvin UW < alvinuw@gmail.com > wrote:
> > Hello,
> >
> > As mentioned by Ed Anuff in his blog and slides, one way to build
> customized
> > secondary index is:
> > We use one CF, each row to represent a secondary index, with the
> secondary
> > index name as row key.
> > For example,
> >
> > Indexes = {
> > "User_Keys_By_Last_Name" : {
> > "adams" : "e5d61f2b-…",
> > "alden" : "e80a17ba-…",
> > "anderson" : "e5d61f2b-…",
> > "davis" : "e719962b-…",
> > "doe" : "e78ece0f-…",
> > "franks" : "e66afd40-…",
> > … : …,
> > }
> > }
> >
> > But the whole secondary index is partitioned into a single node, because
> of
> > the row key.
> > All the queries against this secondary index will go to this node. Of
> > course, there are some replica nodes.
> >
> > Do you think this is a scalability problem, or any better solution to
> solve
> > it?
>
> Its certainly a scalability problem in that this solution has a hard
> ceiling (this index can't get larger than the capacity of any single
> node). It will probably work on small datasets, but if your dataset is
> small then why are you using cassandra?
>
> -ryan
>
>
>

Re: Customized Secondary Index Schema

Posted by Konstantin Naryshkin <ko...@a-bb.net>.
Why are you keeping all your indexes in the same row? We do a similar thing (maintain several indexes over the same data) and we just have an index column family with keys like "dest192.168.0.1" which means destination index of 192.168.0.1. You can do rows like User_Keys_By_Last_Name_adams and User_Keys_By_Last_Name_alden. You can keep the matching main column family key as the column name. This will ensure that your index is evenly distributed throughout your cluster.

----- Original Message -----
From: "Ed Anuff" <ed...@anuff.com>
To: user@cassandra.apache.org
Sent: Thursday, August 25, 2011 12:48:49 PM
Subject: Re: Customized Secondary Index Schema

How many unique last names do you anticipate having? How many characters in the last name do you anticipate keeping in your index? You can easily do the math to figure out how many you could fit on a node. I think you'll find that the ceiling might be quite a bit higher than you think. If you have over a couple of hundred million users it might not be the best approach. There are a lot of very simple ways to split it up over multiple rows. As is the case with most things regarding Cassandra, the off-the-cuff assumptions only get you so far before you have to do some math and do some tests.

As I mentioned in my talk, for simple uses cases like this, you probably should just start with the built in secondary indexes, but I assume you already have explored those.

Ed


On Thu, Aug 25, 2011 at 9:27 AM, Alvin UW < alvinuw@gmail.com > wrote:


Yes, this is what I am worrying about.


2011/8/24 Ryan King < ryan@twitter.com >





On Tue, Aug 23, 2011 at 10:03 AM, Alvin UW < alvinuw@gmail.com > wrote:
> Hello,
>
> As mentioned by Ed Anuff in his blog and slides, one way to build customized
> secondary index is:
> We use one CF, each row to represent a secondary index, with the secondary
> index name as row key.
> For example,
>
> Indexes = {
> "User_Keys_By_Last_Name" : {
> "adams" : "e5d61f2b-…",
> "alden" : "e80a17ba-…",
> "anderson" : "e5d61f2b-…",
> "davis" : "e719962b-…",
> "doe" : "e78ece0f-…",
> "franks" : "e66afd40-…",
> … : …,
> }
> }
>
> But the whole secondary index is partitioned into a single node, because of
> the row key.
> All the queries against this secondary index will go to this node. Of
> course, there are some replica nodes.
>
> Do you think this is a scalability problem, or any better solution to solve
> it?

Its certainly a scalability problem in that this solution has a hard
ceiling (this index can't get larger than the capacity of any single
node). It will probably work on small datasets, but if your dataset is
small then why are you using cassandra?

-ryan



Re: Customized Secondary Index Schema

Posted by Ed Anuff <ed...@anuff.com>.
How many unique last names do you anticipate having?  How many characters in
the last name do you anticipate keeping in your index?  You can easily do
the math to figure out how many you could fit on a node.  I think you'll
find that the ceiling might be quite a bit higher than you think.  If you
have over a couple of hundred million users it might not be the best
approach.  There are a lot of very simple ways to split it up over multiple
rows.  As is the case with most things regarding Cassandra, the off-the-cuff
assumptions only get you so far before you have to do some math and do some
tests.

As I mentioned in my talk, for simple uses cases like this, you probably
should just start with the built in secondary indexes, but I assume you
already have explored those.

Ed

On Thu, Aug 25, 2011 at 9:27 AM, Alvin UW <al...@gmail.com> wrote:

> Yes, this is what I am worrying about.
>
> 2011/8/24 Ryan King <ry...@twitter.com>
>
>> On Tue, Aug 23, 2011 at 10:03 AM, Alvin UW <al...@gmail.com> wrote:
>> > Hello,
>> >
>> > As mentioned by Ed Anuff in his blog and slides, one way to build
>> customized
>> > secondary index is:
>> > We use one CF, each row to represent a secondary index, with the
>> secondary
>> > index name as row key.
>> > For example,
>> >
>> > Indexes = {
>> > "User_Keys_By_Last_Name" : {
>> > "adams" : "e5d61f2b-…",
>> > "alden" : "e80a17ba-…",
>> > "anderson" : "e5d61f2b-…",
>> > "davis" : "e719962b-…",
>> > "doe" : "e78ece0f-…",
>> > "franks" : "e66afd40-…",
>> > … : …,
>> > }
>> > }
>> >
>> > But the whole secondary index is partitioned into a single node, because
>> of
>> > the row key.
>> > All the queries against this secondary index will go to this node. Of
>> > course, there are some replica nodes.
>> >
>> > Do you think this is a scalability problem, or any better solution to
>> solve
>> > it?
>>
>> Its certainly a scalability problem in that this solution has a hard
>> ceiling (this index can't get larger than the capacity of any single
>> node). It will probably work on small datasets, but if your dataset is
>> small then why are you using cassandra?
>>
>> -ryan
>>
>
>

Re: Customized Secondary Index Schema

Posted by Alvin UW <al...@gmail.com>.
Yes, this is what I am worrying about.

2011/8/24 Ryan King <ry...@twitter.com>

> On Tue, Aug 23, 2011 at 10:03 AM, Alvin UW <al...@gmail.com> wrote:
> > Hello,
> >
> > As mentioned by Ed Anuff in his blog and slides, one way to build
> customized
> > secondary index is:
> > We use one CF, each row to represent a secondary index, with the
> secondary
> > index name as row key.
> > For example,
> >
> > Indexes = {
> > "User_Keys_By_Last_Name" : {
> > "adams" : "e5d61f2b-…",
> > "alden" : "e80a17ba-…",
> > "anderson" : "e5d61f2b-…",
> > "davis" : "e719962b-…",
> > "doe" : "e78ece0f-…",
> > "franks" : "e66afd40-…",
> > … : …,
> > }
> > }
> >
> > But the whole secondary index is partitioned into a single node, because
> of
> > the row key.
> > All the queries against this secondary index will go to this node. Of
> > course, there are some replica nodes.
> >
> > Do you think this is a scalability problem, or any better solution to
> solve
> > it?
>
> Its certainly a scalability problem in that this solution has a hard
> ceiling (this index can't get larger than the capacity of any single
> node). It will probably work on small datasets, but if your dataset is
> small then why are you using cassandra?
>
> -ryan
>

Re: Customized Secondary Index Schema

Posted by Ryan King <ry...@twitter.com>.
On Tue, Aug 23, 2011 at 10:03 AM, Alvin UW <al...@gmail.com> wrote:
> Hello,
>
> As mentioned by Ed Anuff in his blog and slides, one way to build customized
> secondary index is:
> We use one CF, each row to represent a secondary index, with the secondary
> index name as row key.
> For example,
>
> Indexes = {
> "User_Keys_By_Last_Name" : {
> "adams" : "e5d61f2b-…",
> "alden" : "e80a17ba-…",
> "anderson" : "e5d61f2b-…",
> "davis" : "e719962b-…",
> "doe" : "e78ece0f-…",
> "franks" : "e66afd40-…",
> … : …,
> }
> }
>
> But the whole secondary index is partitioned into a single node, because of
> the row key.
> All the queries against this secondary index will go to this node. Of
> course, there are some replica nodes.
>
> Do you think this is a scalability problem, or any better solution to solve
> it?

Its certainly a scalability problem in that this solution has a hard
ceiling (this index can't get larger than the capacity of any single
node). It will probably work on small datasets, but if your dataset is
small then why are you using cassandra?

-ryan

Re: Customized Secondary Index Schema

Posted by Alvin UW <al...@gmail.com>.
Thanks.

2011/8/24 aaron morton <aa...@thelastpickle.com>

> IMHO it's only a scalability problem if those nodes have trouble handling
> the throughput. The load will go all all replicas, not one, unless you turn
> off Read Repair.
>
> If it is a problem then you could manually partition the index into
> multiple rows, bit of a pain thought. I'd wait and see, or crunch some
> numbers before hand.
>
> Cheers
>
> -----------------
> Aaron Morton
> Freelance Cassandra Developer
> @aaronmorton
> http://www.thelastpickle.com
>
> On 24/08/2011, at 5:03 AM, Alvin UW wrote:
>
> > Hello,
> >
> > As mentioned by Ed Anuff in his blog and slides, one way to build
> customized secondary index is:
> > We use one CF, each row to represent a secondary index, with the
> secondary index name as row key.
> > For example,
> >
> > Indexes = {
> > "User_Keys_By_Last_Name" : {
> > "adams" : "e5d61f2b-…",
> > "alden" : "e80a17ba-…",
> > "anderson" : "e5d61f2b-…",
> > "davis" : "e719962b-…",
> > "doe" : "e78ece0f-…",
> > "franks" : "e66afd40-…",
> > … : …,
> > }
> > }
> >
> > But the whole secondary index is partitioned into a single node, because
> of the row key.
> > All the queries against this secondary index will go to this node. Of
> course, there are some replica nodes.
> >
> > Do you think this is a scalability problem, or any better solution to
> solve it?
> > Thanks.
> >
> >
> >
> >
>
>

Re: Customized Secondary Index Schema

Posted by aaron morton <aa...@thelastpickle.com>.
IMHO it's only a scalability problem if those nodes have trouble handling the throughput. The load will go all all replicas, not one, unless you turn off Read Repair. 

If it is a problem then you could manually partition the index into multiple rows, bit of a pain thought. I'd wait and see, or crunch some numbers before hand. 

Cheers

-----------------
Aaron Morton
Freelance Cassandra Developer
@aaronmorton
http://www.thelastpickle.com

On 24/08/2011, at 5:03 AM, Alvin UW wrote:

> Hello,
> 
> As mentioned by Ed Anuff in his blog and slides, one way to build customized secondary index is:
> We use one CF, each row to represent a secondary index, with the secondary index name as row key.
> For example,
> 
> Indexes = {
> "User_Keys_By_Last_Name" : {
> "adams" : "e5d61f2b-…",
> "alden" : "e80a17ba-…",
> "anderson" : "e5d61f2b-…",
> "davis" : "e719962b-…",
> "doe" : "e78ece0f-…",
> "franks" : "e66afd40-…",
> … : …,
> }
> }
> 
> But the whole secondary index is partitioned into a single node, because of the row key.
> All the queries against this secondary index will go to this node. Of course, there are some replica nodes.
> 
> Do you think this is a scalability problem, or any better solution to solve it?
> Thanks.
> 
> 
> 
>