Thanks folks for all your inputs! Yes, I totally agree that we need to have a custom column family for indexing. However, we're trying to upgrade our existing cluster from non-vnode to vnode, and queries using secondary indexes breaks badly which used to be good with non-vnode.
Btw, there is no data in the table. Table is empty. Query is fired on the empty table. >From the tracing ouput, I don't understand why it's doing multiple scans on one node. With non-vnode, there is only one scan per node & same query works fine. If you look at the output1.txt attached earlier, coordinator is firing index scan on a given node (for example, 192.168.51.22 in the below snippet from output1.txt) multiple times for different token ranges. Why can't it fire only one time? With non-vnode, it's only one time & query comes back very fast. Executing indexed scan for [min(-9223372036854775808), max(-9193352069377957523)] | 23:11:30,992 | *192.168.51.22* | Executing indexed scan for (max(-9193352069377957523), max(-9136021049555745100)] | 23:11:30,998 | 192.168.51.25 | Executing indexed scan for (max(-9136021049555745100), max(-8959555493872108621)] | 23:11:30,999 | *192.168.51.22 *| Executing indexed scan for (max(-8959555493872108621), max(-8929774302283364912)] | 23:11:31,000 | 192.168.51.25 | Executing indexed scan for (max(-8929774302283364912), max(-8854653908608918942)] | 23:11:31,001 | *192.168.51.22* | On Fri, Sep 19, 2014 at 9:39 AM, Tyler Hobbs <ty...@datastax.com> wrote: > Jon's advice is definitely still true, but in 2.1 there is > https://issues.apache.org/jira/browse/CASSANDRA-1337, which parallelizes > the fetching of ranges. > > On Fri, Sep 19, 2014 at 6:57 AM, Parag Patel <ppa...@clearpoolgroup.com> > wrote: > >> Agreed. We only use secondary indexes for column families that are >> relatively small (~5k rows). For anything larger, we store the data into a >> wide row (but this depends on your data model) >> >> -----Original Message----- >> From: jonathan.had...@gmail.com [mailto:jonathan.had...@gmail.com] On >> Behalf Of Jonathan Haddad >> Sent: Friday, September 19, 2014 4:01 AM >> To: user@cassandra.apache.org >> Subject: Re: Slow down of secondary index query with VNODE (C* version >> 1.2.18, jre6). >> >> Keep in mind secondary indexes in cassandra are not there to improve >> performance, or even really be used in a serious user facing manner. >> >> Build and maintain your own view of the data, it'll be much faster. >> >> >> >> On Thu, Sep 18, 2014 at 6:33 PM, Jay Patel <pateljay3...@gmail.com> >> wrote: >> > Hi there, >> > >> > We are seeing extreme slow down (500ms to 1s) in query on secondary >> > index with vnode. I'm seeing multiple secondary index scans on a given >> > node in trace output when vnode is enabled. Without vnode, everything >> is good. >> > >> > Cluster size: 6 nodes >> > Replication factor: 3 >> > Consistency level: local_quorum. Same behavior happens with >> > consistency level of ONE. >> > >> > Snippet from the trace output. Pls see the attached output1.txt for >> > the full log. Are we hitting any bug? Do not understand why >> > coordinator sends requests multiple times to the same node (e.g. >> > 192.168.51.22 in below >> > output) for different token ranges. >> > >> >>>> >> > >> > Executing indexed scan for [min(-9223372036854775808), >> > max(-9193352069377957523)] | 23:11:30,992 | 192.168.51.22 | Executing >> > indexed scan for (max(-9193352069377957523), >> > max(-9136021049555745100)] | 23:11:30,998 | 192.168.51.25 | Executing >> > indexed scan for (max(-9136021049555745100), >> > max(-8959555493872108621)] | 23:11:30,999 | 192.168.51.22 | Executing >> > indexed scan for (max(-8959555493872108621), >> > max(-8929774302283364912)] | 23:11:31,000 | 192.168.51.25 | Executing >> > indexed scan for (max(-8929774302283364912), >> > max(-8854653908608918942)] | 23:11:31,001 | 192.168.51.22 | Executing >> > indexed scan for (max(-8854653908608918942), >> > max(-8762620856967633953)] | 23:11:31,002 | 192.168.51.25 | >> > Executing indexed scan for (max(-8762620856967633953), >> > max(-8668275030769104047)] | 23:11:31,003 | 192.168.51.22 | Executing >> > indexed scan for (max(-8668275030769104047), >> > max(-8659066486210615614)] | 23:11:31,003 | 192.168.51.25 | Executing >> > indexed scan for (max(-8659066486210615614), >> > max(-8419137646248370231)] | 23:11:31,004 | 192.168.51.22 | Executing >> > indexed scan for (max(-8419137646248370231), >> > max(-8416786876632807845)] | 23:11:31,005 | 192.168.51.25 | Executing >> > indexed scan for (max(-8416786876632807845), >> > max(-8315889933848495185)] | 23:11:31,006 | 192.168.51.22 | Executing >> > indexed scan for (max(-8315889933848495185), >> > max(-8270922890152952193)] | 23:11:31,006 | 192.168.51.25 | Executing >> > indexed scan for (max(-8270922890152952193), >> > max(-8260813759533312175)] | 23:11:31,007 | 192.168.51.22 | Executing >> > indexed scan for (max(-8260813759533312175), >> > max(-8234845345932129353)] | 23:11:31,008 | 192.168.51.25 | Executing >> > indexed scan for (max(-8234845345932129353), >> > max(-8216636461332030758)] | 23:11:31,008 | 192.168.51.22 | >> > >> > Thanks, >> > Jay >> > >> >> >> >> -- >> Jon Haddad >> http://www.rustyrazorblade.com >> twitter: rustyrazorblade >> > > > > -- > Tyler Hobbs > DataStax <http://datastax.com/> >
