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 0E141D99C for ; Thu, 30 Aug 2012 18:45:36 +0000 (UTC) Received: (qmail 21126 invoked by uid 500); 30 Aug 2012 18:45:33 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 21092 invoked by uid 500); 30 Aug 2012 18:45:33 -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 21084 invoked by uid 99); 30 Aug 2012 18:45:33 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Aug 2012 18:45:33 +0000 X-ASF-Spam-Status: No, hits=2.8 required=5.0 tests=FSL_RCVD_USER,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS,URI_HEX X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of tyler@datastax.com designates 209.85.212.44 as permitted sender) Received: from [209.85.212.44] (HELO mail-vb0-f44.google.com) (209.85.212.44) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Aug 2012 18:45:29 +0000 Received: by vbbez10 with SMTP id ez10so2626534vbb.31 for ; Thu, 30 Aug 2012 11:45:08 -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=kJ4n2Z3L8jPfIHwItnqQD2tsZCAUdWKbg3VuV/3HJtU=; b=cCSU3JAL47bLiZXxgWkdIlFulgXvHrAOKdRhQeBXR6mYxMQK4jvAaITUb6mmxna5rg GJiUm6w+cmH1IrBxeWD6VaIK1N0uJcO9GkuI+8LugocOZe+nR1rmmc5gGM9LlPzhou4s baG6Bg4k1INDs709YxbF6apvhW8dxoYZZ/iVKd7ddE/Zk6dlMmc9EheMHtpvUiOYxpwI kDCGBMqTJNf2KUpTPxFhymQoieLQAKcCKbtM13ywgAHu3yaiTV0AedcpVHrzy8bdQ+xF SXqPzfd5r6J86z6D8cMzsASvSEiBRIguIGkhizq4lnHw44AXVtgCSYJzjZJAJQWXcoE2 EobA== MIME-Version: 1.0 Received: by 10.58.239.232 with SMTP id vv8mr4078587vec.37.1346352308236; Thu, 30 Aug 2012 11:45:08 -0700 (PDT) Received: by 10.59.0.102 with HTTP; Thu, 30 Aug 2012 11:45:08 -0700 (PDT) In-Reply-To: <5905460D-32C8-448E-953F-8A46AB907EA8@thelastpickle.com> References: <503D8C5B.3010003@mebigfatguy.com> <5905460D-32C8-448E-953F-8A46AB907EA8@thelastpickle.com> Date: Thu, 30 Aug 2012 13:45:08 -0500 Message-ID: Subject: Re: Why Cassandra secondary indexes are so slow on just 350k rows? From: Tyler Hobbs To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=047d7bf15ff81330b704c8801354 X-Gm-Message-State: ALoCoQkJqHaqhASVYzRXC3LiW28UV8y4UnfDk2nutZRSBK4CkSs4InVW7CPrddMyF85PD+XsL8R2 X-Virus-Checked: Checked by ClamAV on apache.org --047d7bf15ff81330b704c8801354 Content-Type: text/plain; charset=ISO-8859-1 pycassa already breaks up the query into smaller chunks, but you should try playing with the buffer_size kwarg for get_indexed_slices, perhaps lowering it to ~300, as Aaron suggests: http://pycassa.github.com/pycassa/api/pycassa/columnfamily.html#pycassa.columnfamily.ColumnFamily.get_indexed_slices On Wed, Aug 29, 2012 at 11:40 PM, aaron morton wrote: > *from 12 to 20 seconds (!!!) to find 5000 rows*. > > More is not always better. > > Cassandra must materialise the full 5000 rows and send them all over the > wire to be materialised on the other side. Try asking for a few hundred at > a time and see how it goes. > > Cheers > > ----------------- > Aaron Morton > Freelance Developer > @aaronmorton > http://www.thelastpickle.com > > On 29/08/2012, at 6:46 PM, Robin Verlangen wrote: > > @Edward: I think you should consider a queue for exporting the new rows. > Just store the rowkey in a queue (you might want to consider looking at > http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/Distributed-work-queues-td5226248.html ) > and process that row every couple of minutes. Then manually delete columns > from that queue-row. > > 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 Robin Verlangen > >> "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! >>>> >>>> >>> >> > > -- Tyler Hobbs DataStax --047d7bf15ff81330b704c8801354 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable pycassa already breaks up the query into smaller chunks, but you should try= playing with the buffer_size kwarg for get_indexed_slices, perhaps lowerin= g it to ~300, as Aaron suggests: http://pycassa.github.com/pycassa/api/pycassa/columnfamily.html= #pycassa.columnfamily.ColumnFamily.get_indexed_slices

On Wed, Aug 29, 2012 at 11:40 PM, aaron mort= on <aaron@thelastpickle.com> wrote:
=A0*from 12 to 20 seconds (!!!) to find 5000 rows*.
More is not always better.=A0

Cassandra must materia= lise the full 5000 rows and send them all over the wire to be materialised = on the other side. Try asking for a few hundred at a time and see how it go= es.=A0

Cheers
=A0
<= div style=3D"word-wrap:break-word">
-----------------
Aaron Morton
Freelance Deve= loper
@aaronmorton

On 29/08/2012, at 6:46 PM, Robin Verlangen <robin@us2.nl> wrote:

@Edward: I think you should consider a queue for ex= porting the new rows. Just store the rowkey in a queue (you might want to c= onsider looking at=A0 http://cassandra= -user-incubator-apache-org.3065146.n2.nabble.com/Distributed-work-queues-td= 5226248.html=A0) and process that row every couple of minutes. Then man= ually delete columns from that queue-row.

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 Robin Verlangen <robin@us2.nl&= gt;
"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 hav= e expected that 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!








--
Tyler Hobbs DataStax
<= br> --047d7bf15ff81330b704c8801354--