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 9D4D2DDC7 for ; Wed, 29 Aug 2012 06:44:55 +0000 (UTC) Received: (qmail 981 invoked by uid 500); 29 Aug 2012 06:44:53 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 962 invoked by uid 500); 29 Aug 2012 06:44:53 -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 951 invoked by uid 99); 29 Aug 2012 06:44:52 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 29 Aug 2012 06:44:52 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=FSL_RCVD_USER,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (nike.apache.org: local policy) Received: from [209.85.220.172] (HELO mail-vc0-f172.google.com) (209.85.220.172) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 29 Aug 2012 06:44:46 +0000 Received: by vcbfo14 with SMTP id fo14so280652vcb.31 for ; Tue, 28 Aug 2012 23:44:25 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type:x-gm-message-state; bh=Sy4jXIWzJURyRNcOwtQCaXo/iUfXjmzATLY/Tj2S+4Y=; b=arigC2IWghghBHuMzX/PsiqbFq+maUOn6RVGWBhRxslUSoZIDkaaI849fNgWXrasSV 8PfLZnqUYzsFRSZAx3djyvogOiwfdLddh2FRr5FmEYkmSW/QqKpkiSKPeO2Dd+sWbCuX Xnjjt2j9gOo8Zd3+9hgAq+NjjvqD32lrtWiN07clH75CrqLl7oubZo48y/iHNyySzyyM wf8Oxvqk3nA2QemzHoswSpDwesIyrJQdwVjPbiutjYe+tODwlR9ynjq7YpHonic0ySW4 H2bTyf8VMdU0zEwQlLpWlXAC19vyxjb+IrefQgZzdMVPMoMy3TH7MQutJcpbeLarXFOz erfA== MIME-Version: 1.0 Received: by 10.58.4.33 with SMTP id h1mr446278veh.38.1346222665845; Tue, 28 Aug 2012 23:44:25 -0700 (PDT) Received: by 10.58.252.168 with HTTP; Tue, 28 Aug 2012 23:44:25 -0700 (PDT) In-Reply-To: <503D8C5B.3010003@mebigfatguy.com> References: <503D8C5B.3010003@mebigfatguy.com> Date: Wed, 29 Aug 2012 08:44:25 +0200 Message-ID: Subject: Re: Why Cassandra secondary indexes are so slow on just 350k rows? From: Robin Verlangen To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=047d7b5d8831c94aef04c861e33c X-Gm-Message-State: ALoCoQlogskYyeK64//4s57qw8h0/QpJtY7nP8g4li0sxpAUkC4ZTc71/tnnlSDBGzGzcFcFPFg+ --047d7b5d8831c94aef04c861e33c Content-Type: text/plain; charset=ISO-8859-1 "What this means is that eventually you will have 1 row in the secondary index table with 350K columns" Is this really true? I would have expected that Cassandra used internal index sharding/bucketing? With kind regards, Robin Verlangen *Software engineer* * * W http://www.robinverlangen.nl E robin@us2.nl Disclaimer: The information contained in this message and attachments is intended solely for the attention and use of the named addressee and may be confidential. If you are not the intended recipient, you are reminded that the information remains the property of the sender. You must not use, disclose, distribute, copy, print or rely on this e-mail. If you have received this message in error, please contact the sender immediately and irrevocably delete this message and any copies. 2012/8/29 Dave Brosius > If i understand you correctly, you are only ever querying for the rows > where is_exported = false, and turning them into trues. What this means is > that eventually you will have 1 row in the secondary index table with 350K > columns that you will never look at. > > It seems to me you that perhaps you should just hold your own "manual > index" cf that points to non exported rows, and just delete those columns > when they are exported. > > > > On 08/28/2012 05:23 PM, Edward Kibardin wrote: > >> I have a column family with the secondary index. The secondary index is >> basically a binary field, but I'm using a string for it. The field called >> *is_exported* and can be *'true'* or *'false'*. After request all loaded >> rows are updated with *is_exported = 'false'*. >> >> I'm polling this column table each ten minutes and exporting new rows as >> they appear. >> >> But here the problem: I'm seeing that time for this query grows pretty >> linear with amount of data in column table, and currently it takes *from 12 >> to 20 seconds (!!!) to find 5000 rows*. From my understanding, indexed >> request should not depend on number of rows in CF but from number of rows >> per one index value (cardinality), as it's just another hidden CF like: >> >> "true" : rowKey1 rowKey2 rowKey3 ... >> "false": rowKey1 rowKey2 rowKey3 ... >> >> I'm using Pycassa to query the data, here the code I'm using: >> >> column_family = pycassa.ColumnFamily(**cassandra_pool, >> column_family_name, read_consistency_level=2) >> is_exported_expr = create_index_expression('is_**exported', >> 'false') >> clause = create_index_clause([is_**exported_expr], count = 5000) >> column_family.get_indexed_**slices(clause) >> >> Am I doing something wrong, but I expect this operation to work MUCH >> faster. >> >> Any ideas or suggestions? >> >> Some config info: >> - Cassandra 1.1.0 >> - RandomPartitioner >> - I have 2 nodes and replication_factor = 2 (each server has a full data >> copy) >> - Using AWS EC2, large instances >> - Software raid0 on ephemeral drives >> >> Thanks in advance! >> >> > --047d7b5d8831c94aef04c861e33c Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
"What this means is that e= ventually you will have 1 row in the secondary index table with 350K column= s"

Is this really true? I would have expected th= at Cassandra used internal index sharding/bucketing?

With kind regards,

Robin Verlangen
Software engineer

E robin@us2.nl

Disclaimer: The information= contained in this message and attachments is intended solely for the atten= tion and use of the named addressee and may be confidential. If you are not= the intended recipient, you are reminded that the information remains the = property of the sender. You must not use, disclose, distribute, copy, print= or rely on this e-mail. If you have received this message in error, please= contact the sender immediately and irrevocably delete this message and any= copies.



2012/8/29 Dave Brosius <dbrosius= @mebigfatguy.com>
If i understand you correctly, you are only ever querying for the rows wher= e is_exported =3D false, and turning them into trues. What this means is th= at eventually you will have 1 row in the secondary index table with 350K co= lumns that you will never look at.

It seems to me you that perhaps you should just hold your own "manual = index" cf that points to non exported rows, and just delete those colu= mns when they are exported.



On 08/28/2012 05:23 PM, Edward Kibardin wrote:
I have a column family with the secondary index. The secondary index is bas= ically a binary field, but I'm using a string for it. The field called = *is_exported* and can be *'true'* or *'false'*. After reque= st all loaded rows are updated with *is_exported =3D 'false'*.

I'm polling this column table each ten minutes and exporting new rows a= s they appear.

But here the problem: I'm seeing that time for this query grows pretty = linear with amount of data in column table, and currently it takes *from 12= to 20 seconds (!!!) to find 5000 rows*. From my understanding, indexed req= uest should not depend on number of rows in CF but from number of rows per = one index value (cardinality), as it's just another hidden CF like:

=A0 =A0 =A0 =A0 "true" : rowKey1 rowKey2 rowKey3 ...
=A0 =A0 =A0 =A0 "false": rowKey1 rowKey2 rowKey3 ...

I'm using Pycassa to query the data, here the code I'm using:

=A0 =A0 =A0 =A0 column_family =3D pycassa.ColumnFamily(cassandra_poo= l, column_family_name, read_consistency_level=3D2)
=A0 =A0 =A0 =A0 is_exported_expr =3D create_index_expression('is_exported', 'false')
=A0 =A0 =A0 =A0 clause =3D create_index_clause([is_exported_expr], c= ount =3D 5000)
=A0 =A0 =A0 =A0 column_family.get_indexed_slices(clause)

Am I doing something wrong, but I expect this operation to work MUCH faster= .

Any ideas or suggestions?

Some config info:
=A0- Cassandra 1.1.0
=A0- RandomPartitioner
=A0- I have 2 nodes and replication_factor =3D 2 (each server has a full da= ta copy)
=A0- Using AWS EC2, large instances
=A0- Software raid0 on ephemeral drives

Thanks in advance!



--047d7b5d8831c94aef04c861e33c--