The 5000 readings mentioned would be against a single sensor on a single sensor 
unit.

The scope of the queries on this table is intended to be fairly simple. Here 
are some example queries, without 'sharding', that we would perform on this 
table:

SELECT "time","readings" FROM "sensorReadings"WHERE "sensorUnitId"=5123 AND 
"sensorId"=17 AND time<=?ORDER BY time DESC LIMIT 5000
SELECT "time","readings" FROM "sensorReadings"WHERE "sensorUnitId"=5123 AND 
"sensorId"=17 AND time>=?ORDER BY time LIMIT 5000
SELECT "time","readings" FROM "sensorReadings"WHERE "sensorUnitId"=5123 AND 
"sensorId"=17 AND time<=? AND classification=?
ORDER BY time DESC LIMIT 5000
where 'classification' is secondary index that we expect to add.

In some cases, we have to revisit all values too so a complete table scan is 
needed:
SELECT "time","readings" FROM "sensorReadings"
Getting the "next" and "previous" 5000 readings is also something we do, but is 
manageable from our standpoint as we can look at the range-end timestamps that 
are returned and use those in the subsequent queries.

SELECT "time","readings" FROM "sensorReadings"WHERE "sensorUnitId"=5123 AND 
"sensorId"=17 AND time>=? AND time<=?ORDER BY time LIMIT 5000
Splitting the bulk content out of the main table is something we considered too 
but we didn't find any detail on whether that would solve our timeout problem. 
If there is a reference for using this approach, it would be of interest to us 
to avoid any assumptions on how we would approach it.

A question: Is the probability of a timeout directly linked to a longer seek 
time in reading through a partition's contents? If that is the case, splitting 
the partition keys into a separate table would be straightforward.

Regards,
Jason

      From: Jack Krupansky <jack.krupan...@gmail.com>
 To: user@cassandra.apache.org; Jason Kania <jason.ka...@ymail.com> 
 Sent: Friday, March 11, 2016 6:22 PM
 Subject: Re: Strategy for dividing wide rows beyond just adding to the 
partition key
   
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 <jason.ka...@ymail.com> 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 <jack.krupan...@gmail.com>
 To: "user@cassandra.apache.org" <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 <jason.ka...@ymail.com> 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 <jack.krupan...@gmail.com>
 To: user@cassandra.apache.org; Jason Kania <jason.ka...@ymail.com> 
 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 <jason.ka...@ymail.com> 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




   



   



   

Reply via email to