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 65E2B18A46 for ; Fri, 11 Mar 2016 23:22:57 +0000 (UTC) Received: (qmail 14034 invoked by uid 500); 11 Mar 2016 23:22:54 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 13992 invoked by uid 500); 11 Mar 2016 23:22:54 -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 13982 invoked by uid 99); 11 Mar 2016 23:22:54 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 11 Mar 2016 23:22:54 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 0D54F1A00DB for ; Fri, 11 Mar 2016 23:22:54 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.379 X-Spam-Level: ** X-Spam-Status: No, score=2.379 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, KAM_LINEPADDING=1.2, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id u957w_i9g2Qu for ; Fri, 11 Mar 2016 23:22:50 +0000 (UTC) Received: from mail-vk0-f46.google.com (mail-vk0-f46.google.com [209.85.213.46]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 956D25F2C4 for ; Fri, 11 Mar 2016 23:22:49 +0000 (UTC) Received: by mail-vk0-f46.google.com with SMTP id c3so152186403vkb.3 for ; Fri, 11 Mar 2016 15:22:49 -0800 (PST) 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; bh=W+ShCpDVk/coUyEuTK3HpQxPK4ubVdSlrwglZrsEYqY=; b=WTlvj6z0d4vQfRkyHs/EJ9Ft2mHBiKEdbrXJJgormz54WvoIZyRj9xQlzCd2kNy+8f fIKlLM3DI7H8Iel2kZhQPRoxyuiZ35zKPCXw5o/89aOkh/XdlgriLISIIkvF6Y5VW6D8 l8jxnE5akkupd4no7ya18pXJ2WfYe3HcgjDPt3BRb+qkuGaW5oSu1PLCnYTWDhxANhA3 0CIOxf3W8Hf60FjgG43FoAv1GLv3d+1hkbXMp0Br9ntZ+waAxNlNhgN3GvNpINJHwY8+ DVLA6vyZK5AW1+ubZLAlGDR6mkAUBCKHGTI+9OCsYLVT+yPc3EO8qAxyQUiZLrA+OsUd 4Cog== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to; bh=W+ShCpDVk/coUyEuTK3HpQxPK4ubVdSlrwglZrsEYqY=; b=DrjDNplzx9DP8FfrqXTCwnazECONDtvREZ3pacLK/yTEa4p6ZF6WZD4QS3WB1sb1Vb jebOafXW/DobpSfQPL4QetQal/+NECTesUBPgMM++cl1nq45ii3NrNrUuNHMpg2VZvEX DmbNlBWPTwcR+tn8v8XmjNsjYCikfAupkMmxK38Tv4vO4769oot5RcV9W6XRjYo6Te3Q UxSLx00XgypIUuMUG9vvjdZ7lwa7Z0vElhpL2denf1YO9aEJKmPOwbgaVK84MfrtFg1i iHcv2t7iKF7LAN9QEUyIsjnb6hl6Pfo7alPPdjlB2fu7OiaGFBszz9v8E/rBOWo7jI2y 27mQ== X-Gm-Message-State: AD7BkJJeA+4ZyqZBOWHiiSnfl4nfIGEHIIg9feIuX6tQVAIMhMVNPT1lFEOuF9C9bjaU8lxgw/3lAJGIck+RQw== MIME-Version: 1.0 X-Received: by 10.31.8.205 with SMTP id 196mr12568284vki.144.1457738568541; Fri, 11 Mar 2016 15:22:48 -0800 (PST) Received: by 10.31.54.13 with HTTP; Fri, 11 Mar 2016 15:22:48 -0800 (PST) In-Reply-To: <1842220918.12093.1457735878521.JavaMail.yahoo@mail.yahoo.com> References: <1842220918.12093.1457735878521.JavaMail.yahoo@mail.yahoo.com> Date: Fri, 11 Mar 2016 18:22:48 -0500 Message-ID: Subject: Re: Strategy for dividing wide rows beyond just adding to the partition key From: Jack Krupansky To: user@cassandra.apache.org, Jason Kania Content-Type: multipart/alternative; boundary=001a1144f9128dae4d052dce35de --001a1144f9128dae4d052dce35de Content-Type: text/plain; charset=UTF-8 Thanks for the additional information, but there is still not enough color on the queries and too much focus on a premature data model. Is this 5000 readings for a single sensor of a single sensor unit, or for all sensors of a specified unit, or... both? I presume you want "next" and "previous" 5000 readings as well as first and last, but... you will have to confirm that. One technique is to store the bulk of your raw sensor data in a separate table and then simply store the PK of that data in your time series. That way you can have a much wider row of time series (number of rows) without hitting a bulk size issue for the partition. But... I don't want to jump to solutions until we have a firmer handle on the query side of the fence. -- Jack Krupansky On Fri, Mar 11, 2016 at 5:37 PM, Jason Kania wrote: > Jack, > > Thanks for the response. > > We are targeting our database design to 10000 sensor units and each sensor > unit has 32 sensors. We are seeing about 700 events per day per sensor, > each providing about 2K of data. Based on keeping each partition to about > 10 Mb (based on readings we saw on performance), we chose to break our > partitions on a weekly basis. This is possibly finer than we need as we > were seeing timeouts only once a single partition was about 150Mb in size > > When pulling in data, we will typically need to pull 1 to 4 months of data > for our analysis and will use only the sensorUnitId and sensorId to > uniquely identify the data source with the timeShard value used to break up > our partitions. We have handling to sequentially scan based on our > "timeShard" value, but don't have a good handle on the determination of the > "timeShard" portion of the partition key at read time. The data starts > coming in when a subscriber starts using our system and finishes when they > discontinue service or put the service on hold temporarily. > > When I talk about hotspots, it isn't the time series data that is the > concern, it is with respect to storing the maximum and minimum timeShard > values in another table for subsequent lookup or the cost of running the > current implementation of SELECT DISTINCT. We need to run queries such as > getting the first or last 5000 sensor readings when we don't know the time > frame at which they occurred so cannot directly supply the timeShard > portion of our partition key. > > I appreciate your input, > > Thanks, > > Jason > > ------------------------------ > *From:* Jack Krupansky > *To:* "user@cassandra.apache.org" > *Sent:* Friday, March 11, 2016 4:45 PM > > *Subject:* Re: Strategy for dividing wide rows beyond just adding to the > partition key > > I'll stay away from advising on a specific schema per se, but I'll stick > to the advice that you need to make sure that your queries are depending > solely on the columns of the primary key or relatively short slices/scans, > rather than run the risk of very long scans or having to process multiple > partitions for a single query. That's canned to some extent, but still > essential. > > Of course we generally wish to avoid hotspots, but with time series they > are unavoidable. I mean, sure you could place successive events at separate > partitions, but then you can't do any kind of scanning/slicing. > > But, events for separate sensors are not true hotspots in the traditional > sense - unless you have only a single sensor/unit. > > After considering your queries, the next step is to consider the > cardinality of your data - how many sensors, how many units, rate of > events, etc. That will feedback into queries as well, such as how big a > slice or scan might be, as well as sizing of partitions. > > So, how many sensor units do you expect, how many sensors per unit, and > expected rate of events per sensor? > > Try not to jump too quickly to specific solutions - there really is a > method to understanding all of this other stuff upfront. > > -- Jack Krupansky > > On Thu, Mar 10, 2016 at 12:39 PM, Jason Kania > wrote: > > Jack, > > Thanks for the response. I don't think I provided enough information and > used the wrong terminology as your response is more the canned advice is > response to Cassandra antipatterns. > > To make this clearer, this is what we are doing: > > create table sensorReadings ( > sensorUnitId int, > sensorId int, > time timestamp, > timeShard int, > readings blob, > primary key((sensorUnitId, sensorId, timeShard), time); > > where timeShard is a combination of year and week of year > > For known time range based queries, this works great. However, the > specific problem is in knowing the maximum and minimum timeShard values > when we want to select the entire range of data. Our understanding is that > if we update another related table with the maximum and minimum timeShard > value for a given sensorUnitId and sensorId combination, we will create a > hotspot and lots of tombstones. If we SELECT DISTINCT, we get a huge list > of partition keys for the table because we cannot reduce the scope with a > where clause. > > If there is a recommended pattern that solves this, we haven't come across > it. > > I hope makes the problem clearer. > > Thanks, > > Jason > > ------------------------------ > *From:* Jack Krupansky > *To:* user@cassandra.apache.org; Jason Kania > *Sent:* Thursday, March 10, 2016 10:42 AM > *Subject:* Re: Strategy for dividing wide rows beyond just adding to the > partition key > > There is an effort underway to support wider rows: > https://issues.apache.org/jira/browse/CASSANDRA-9754 > > This won't help you now though. Even with that improvement you still may > need a more optimal data model since large-scale scanning/filtering is > always a very bad idea with Cassandra. > > The data modeling methodology for Cassandra dictates that queries drive > the data model and that each form of query requires a separate table > ("query table.") Materialized view can automate that process for a lot of > cases, but in any case it does sound as if some of your queries do require > additional tables. > > As a general proposition, Cassandra should not be used for heavy filtering > - query tables with the filtering criteria baked into the PK is the way to > go. > > > -- Jack Krupansky > > On Thu, Mar 10, 2016 at 8:54 AM, Jason Kania > wrote: > > Hi, > > We have sensor input that creates very wide rows and operations on these > rows have started to timeout regulary. We have been trying to find a > solution to dividing wide rows but keep hitting limitations that move the > problem around instead of solving it. > > We have a partition key consisting of a sensorUnitId and a sensorId and > use a time field to access each column in the row. We tried adding a time > based entry, timeShardId, to the partition key that consists of the year > and week of year during which the reading was taken. This works for a > number of queries but for scanning all the readings against a particular > sensorUnitId and sensorId combination, we seem to be stuck. > > We won't know the range of valid values of the timeShardId for a given > sensorUnitId and sensorId combination so would have to write to an > additional table to track the valid timeShardId. We suspect this would > create tombstone accumulation problems given the number of updates required > to the same row so haven't tried this option. > > Alternatively, we hit a different bottleneck in the form of SELECT > DISTINCT in trying to directly access the partition keys. Since SELECT > DISTINCT does not allow for a where clause to filter on the partition key > values, we have to filter several hundred thousand partition keys just to > find those related to the relevant sensorUnitId and sensorId. This problem > will only grow worse for us. > > Are there any other approaches that can be suggested? We have been looking > around, but haven't found any references beyond the initial suggestion to > add some sort of shard id to the partition key to handle wide rows. > > Thanks, > > Jason > > > > > > > > --001a1144f9128dae4d052dce35de Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thanks for the additional information, but there is still = not enough color on the queries and too much focus on a premature data mode= l.

Is this 5000 readings for a single sensor of a single= sensor unit, or for all sensors of a specified unit, or... both?

I presume you want "next" and "previous"= ; 5000 readings as well as first and last, but... you will have to confirm = that.

One technique is to store the bulk of your r= aw sensor data in a separate table and then simply store the PK of that dat= a in your time series. That way you can have a much wider row of time serie= s (number of rows) without hitting a bulk size issue for the partition. But= ... I don't want to jump to solutions until we have a firmer handle on = the query side of the fence.

-- Jack Krup= ansky

On Fri, Mar 11, 2016 at 5:37 PM, Jason Kania= <jason.kania@ymail.com> wrote:
Jack,

Thanks for the response.

We are targ= eting our database design to 10000 sensor units and each sensor unit has 32= sensors. We are seeing about 700 events per day per sensor, each providing= about 2K of data. Based on keeping each partition to about 10 Mb (based on= readings we saw on performance), we chose to break our partitions on a wee= kly basis. This is possibly finer than we need as we were seeing timeouts o= nly once a single partition was about 150Mb in size

When pulling in dat= a, we will typically need to pull 1 to 4 months of data for our analysis an= d will use only the sensorUnitId and sensorId to uniquely identify the data= source with the timeShard value used to break up our partitions. We have h= andling to sequentially scan based on our "timeShard" value, but = don't have a good handle on the determination of the "timeShard&qu= ot; portion of the partition key at read time. The data starts coming in wh= en a subscriber starts using our system and finishes when they discontinue = service or put the service on hold temporarily.

When I talk about hotspots, it isn't the time seri= es data that is the concern, it is with respect to storing the maximum and = minimum timeShard values in another table for subsequent lookup or the cost= of running the current implementation of SELECT DISTINCT. We need to run q= ueries such as getting the first or last 5000 sensor readings when we don&#= 39;t know the time frame at which they occurred so cannot directly supply t= he timeShard portion of our partition key.

<= span>
I appreciate your input,

Thanks,

Jason

From:= Jack Krupansky <jack.krupansky@gmail.com>
To: "user@cassandra.apache.org" <user@cassandra.apac= he.org>
Sent: Fr= iday, March 11, 2016 4:45 PM

Subject: Re: Strategy for dividing wide rows be= yond just adding to the partition key

I'll stay away fro= m advising on a specific schema per se, but I'll stick to the advice th= at you need to make sure that your queries are depending solely on the colu= mns of the primary key or relatively short slices/scans, rather than run th= e risk of very long scans or having to process multiple partitions for a si= ngle query. That's canned to some extent, but still essential.

Of course we generally wish to avoid hotspots, bu= t with time series they are unavoidable. I mean, sure you could place succe= ssive events at separate partitions, but then you can't do any kind of = scanning/slicing.

But, events for s= eparate sensors are not true hotspots in the traditional sense - unless you= have only a single sensor/unit.

Af= ter considering your queries, the next step is to consider the cardinality = of your data - how many sensors, how many units, rate of events, etc. That = will feedback into queries as well, such as how big a slice or scan might b= e, as well as sizing of partitions.

So, how many sensor units do you expect, how many sensors per unit, and ex= pected rate of events per sensor?

T= ry not to jump too quickly to specific solutions - there really is a method= to understanding all of this other stuff upfront.

-- Jack Krupansky

On Thu, Mar 10, 2016 at 12:39 PM, Jason Kania = <jason.kania@ymail.com> = wrote:
Jack,

Thanks for the response. I don= 9;t think I provided enough information and used the wrong terminology as y= our response is more the canned advice is response to Cassandra antipattern= s.

T= o make this clearer, this is what we are doing:

create table = sensorReadings (
sensorUnitId int,
=
sensorId int,
time timestamp,
<= div dir=3D"ltr">timeShard int,
readings blob,<= /div>
primary key((sensorUnitId, sensorId= , timeShard), time);

where timeShard is a combination of year and wee= k of year

For known tim= e range based queries, this works great. However, the specific problem is i= n knowing the maximum and minimum timeShard values when we want to select t= he entire range of data. Our understanding is that if we update another rel= ated table with the maximum and minimum timeShard value for a given sensorU= nitId and sensorId combination, we will create a hotspot and lots of tombst= ones. If we SELECT DISTINCT, we get a huge list of partition keys for the t= able because we cannot reduce the scope with a where clause.

If there is a reco= mmended pattern that solves this, we haven't come across it.

I hope makes the problem= clearer.

Thanks,

Jason

From: Jack Krupansky <jack.krupansk= y@gmail.com>
To: user@cassandra.apache.org; Jas= on Kania <jason.kania@ymail.com>
Sent: Thursday, M= arch 10, 2016 10:42 AM
Subject: Re: Strategy for dividing wide rows beyond jus= t adding to the partition key
<= div>

-- Jac= k Krupansky

On Thu, Mar 10, 2016 at 8:54 AM, Jason Kania <= span dir=3D"ltr"><jason.kania@ymail.com> w= rote:
Hi,

We have sensor input that creates very wide rows and operations on t= hese rows have started to timeout regulary. We have been trying to find a s= olution to dividing wide rows but keep hitting limitations that move the pr= oblem around instead of solving it.

We have a partition key consisting of a sensorUnitId and a sen= sorId and use a time field to access each column in the row. We tried addin= g a time based entry, timeShardId, to the partition key that consists of th= e year and week of year during which the reading was taken. This works for = a number of queries but for scanning all the readings against a particular = sensorUnitId and sensorId combination, we seem to be stuck.

We won't know the ra= nge of valid values of the timeShardId for a given sensorUnitId and sensorI= d combination so would have to write to an additional table to track the va= lid timeShardId. We suspect this would create tombstone accumulation proble= ms given the number of updates required to the same row so haven't trie= d this option.

=
Alternatively, we hit a different bottleneck in the = form of SELECT DISTINCT in trying to directly access the partition keys. Si= nce SELECT DISTINCT does not allow for a where clause to filter on the part= ition key values, we have to filter several hundred thousand partition keys= just to find those related to the relevant sensorUnitId and sensorId. This= problem will only grow worse for us.

Are there any other approache= s that can be suggested? We have been looking around, but haven't found= any references beyond the initial suggestion to add some sort of shard id = to the partition key to handle wide rows.

Thanks,

Jason



<= /blockquote>



=

= --001a1144f9128dae4d052dce35de--