Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 9C511CBCC for ; Wed, 16 May 2012 19:27:26 +0000 (UTC) Received: (qmail 96226 invoked by uid 500); 16 May 2012 19:27:24 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 96208 invoked by uid 500); 16 May 2012 19:27:24 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 96199 invoked by uid 99); 16 May 2012 19:27:24 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 16 May 2012 19:27:24 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of feestend@gmail.com designates 209.85.214.44 as permitted sender) Received: from [209.85.214.44] (HELO mail-bk0-f44.google.com) (209.85.214.44) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 16 May 2012 19:27:18 +0000 Received: by bkty8 with SMTP id y8so1093404bkt.31 for ; Wed, 16 May 2012 12:26:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=1fm8OmQmcgE0cLkea/w2kTlfsHIKLrnqhGrRzKeDm+A=; b=UOA5Ie4tk9Nhzw0yI6bdFX/sQ6ufI7mjEfa9uJ+p3Nxu7r4G98ucRrOwAE7kQ9iLzI nlomCQsGoMVJGA+t3uddt0onAcr44EOJ6M9lLKPSjGw0OSZEDcIDakhYPcuUA/nz4AL7 Aa5IR2Ewp9uPPSXIH23m2VUUOrkkGEz/z2Bp3QWNwAjWHpOjouwcsbv5ge0vvr++Hd4L NYSi3QuFFSqofcuUT29Hwr34YkDLfPgD0iuVbYUAm0TbrkbQehFbVU9zf/goYxQuBiIc EcHZcraxIfox+aJSUBxg3SqK2jMDt6Ac7oUN3VUwID8SgCDUPI+EMcSPbCZly+g2933W DPqg== MIME-Version: 1.0 Received: by 10.204.145.78 with SMTP id c14mr1616213bkv.43.1337196417711; Wed, 16 May 2012 12:26:57 -0700 (PDT) Received: by 10.205.132.202 with HTTP; Wed, 16 May 2012 12:26:57 -0700 (PDT) In-Reply-To: <4FB3FA15.60204@mebigfatguy.com> References: <63CCA5D3F3175843B5C153AD218C2FBF08E498@MSEXCHM83.morningstar.com> <4FB3FA15.60204@mebigfatguy.com> Date: Wed, 16 May 2012 21:26:57 +0200 Message-ID: Subject: Re: understanding of native indexes: limitations, potential side effects,... From: David Vanderfeesten To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=00151759338c78ff3204c02c4da5 --00151759338c78ff3204c02c4da5 Content-Type: text/plain; charset=ISO-8859-1 This corresponds with my thoughts, but I don't see the issue with high cardinality columns. In worst case you get potentially as many rows in the index as in the indexed cf (each having one column). On Wed, May 16, 2012 at 9:03 PM, Dave Brosius wrote: > Each index you define on the source CF is created using an internal CF > that has as its key the value of the column it's indexing, and as its > columns, all the keys of all the rows in the source CF that have that > value. So if all your rows in your source CF have the same value, then your > index cf will have one row with N columns for each N rows in the original > CF. > > > > > On 05/16/2012 02:58 PM, David Vanderfeesten wrote: > > Txs Jeremiah, > But I am not sure I am following " number of columns could be equal to > number of rows ". Is native index implemented as one cf shared over all > the indexes (one row in the idx CF corresponding to one index) or is there > an internal index cf per index?. My (potential wrong) mindset was the > latter. In that case if you would index a column with a very high > cardinality like for example serialNbr, this corresponding internal idx cf > will just lead to almost the same nbr of rows as the original cf containing > the serialnbr. I can''t match that what you are explaining... > > - David > > On Wed, May 16, 2012 at 6:23 PM, Jeremiah Jordan < > JEREMIAH.JORDAN@morningstar.com> wrote: > >> The limitation is because number of columns could be equal to number of >> rows. If number of rows is large this can become an issue. >> >> -Jeremiah >> >> ------------------------------ >> *From:* David Vanderfeesten [feestend@gmail.com] >> *Sent:* Wednesday, May 16, 2012 6:58 AM >> *To:* user@cassandra.apache.org >> *Subject:* understanding of native indexes: limitations, potential side >> effects,... >> >> Hi >> >> I like to better understand the limitations of native indexes, potential >> side effects and scenarios where they are required. >> >> My understanding so far : >> - Is that indexes on each node are storing indexes for data locally on >> the node itself. >> - Indexes do not return values in a sorted way (hashes of the indexed row >> keys are defining the order) >> - Given by the design referred in the first bullet, a coordinator node >> receiving a read of a native index, needs to spawn a read to multiple >> nodes(set of nodes together covering at least the complete key space + >> potentially more to assure read consistency level). >> - Each write to an indexed column leads to an additional local read of >> the index to update the index (kind of obvious but easily forgotten when >> tuning your system for write-only workload) >> - When using a where clause in CQL you need at least to specify an equal >> condition on a native indexed column. Additional conditions in the where >> clause are filtered out by the coordinator node receiving the CQL query. >> - native indexes do not support very well columns with high number of >> discrete values throughout the entire CF. >> >> Is upper understanding correct and complete? >> Some doubts: >> - about the limitation of indexing columns with high number of discrete >> values: >> I assume native indexes are implemented with an internally managed CF >> per index. With high cardinality values, in worst case, the number of rows >> in the index are identical to the number of rows of the indexed CF. Or are >> there other reasons for the limitation, and if that's the case, is there >> a guideline on the max. nbr of cardinality that is still reasonable? >> -Are column updates and the update of the indexes (read + write action) >> atomic and isolated from concurrent updates? >> >> Txs! >> >> David >> >> >> >> >> > > --00151759338c78ff3204c02c4da5 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable This corresponds with my thoughts, but I don't see the issue with high = cardinality columns. In worst case you get potentially as many rows in the = index as in the indexed cf (each having one column).

On Wed, May 16, 2012 at 9:03 PM, Dave Brosius <dbrosius@mebigfatguy= .com> wrote:
=20 =20 =20
Each index you define on the source CF is created using an internal CF that has as its key the value of the column it's indexing, and a= s its columns, all the keys of all the rows in the source CF that have that value. So if all your rows in your source CF have the same value, then your index cf will have one row with N columns for each N rows in the original CF.




On 05/16/2012 02:58 PM, David Vanderfeesten wrote:
Txs Jeremiah,
But I am not sure I am following " number of columns could be equal to number of rows ".=A0 Is native index implemented as one= cf shared over all the indexes (one row in the idx CF corresponding to one index) or=A0 is there an internal index cf per index?. My (potential wrong) mindset was the latter. In that case if you would index a column with a very high cardinality like for example serialNbr,=A0 this corresponding internal idx cf will just lead to almost the same nbr of rows as the original cf containing the serialnbr. I can''t match that what you are explaining...

- David

On Wed, May 16, 2012 at 6:23 PM, Jeremiah Jordan <JEREMIAH.JORDAN@morningstar.com> wrote:
The limitation is because number of columns could be equal to number of rows.=A0 If number of rows is large this can become an issue.

-Jeremiah

From: David Vanderfeesten [feestend@gmail.com]
Sent: Wednesday, May 16, 2012 6:58 AM
To: user@cassandra.apache.org
Subject: understanding of native indexes: limitations, potential side effects,...

Hi

I like to better understand the limitations of native indexes, potential side effects and scenarios where they are required.

My understanding so far :
- Is that indexes on each node are storing indexes for data locally on the node itself.
- Indexes do not return values in a sorted way (hashes of the indexed row keys are defining the order)
- Given by the design referred in the first bullet, a coordinator node receiving a read of a native index, needs to spawn a read to multiple nodes(set of nodes together covering at least the complete key space + potentially more to assure read consistency level).
- Each write to an indexed column leads to an additional local read of the index to update the index (kind of obvious but easily forgotten when tuning your system for write-only workload)<= br> - When using a where clause in CQL you need at least to specify an equal condition on a native indexed column. Additional conditions in the where clause are filtered out by the coordinator node receiving the CQL query.
- native indexes do not support very well columns with high number of discrete values throughout the entire CF.

Is upper understanding correct and complete?
Some doubts:
- about the limitation of indexing columns with high number of discrete values:
I assume native indexes=A0 are implemented with an internally managed CF per index. With high cardinality values, in worst case, the number of rows in the index are identical to the number of rows of the indexed CF. Or are there other reasons for the limitation, and if that's the case, is there a guideline on the max. nbr of cardinality that is still reasonable?
-Are column updates and the update of the indexes (read + write action) atomic and isolated from concurrent updates?

Txs!

David







--00151759338c78ff3204c02c4da5--