4 ms·

Hm. How does Cassandra really handle rows if the author is recommending storing timestamps in the same row? Sounds like a really good way of getting un-shardabl

by bluecmd 11y ago

Hm. How does Cassandra really handle rows if the author is recommending storing timestamps in the same row? Sounds like a really good way of getting un-shardable atoms (whatever that's called in Cassandra) and hotspots.

PretzelPirate 11y ago
Please forgive my formatting. It seems formatting text on HN is more difficult than using Cassandra. Cassandra is interesting because you really have a partition key to define a "row" and then a set of columns (cells). When you define your clustering key, you are really defining how your data is stored on disk. If you had the following table (the columns are meaningless, so just ignore that you wouldn't ever use this dumb table): table1 (personName, addedtime, color) where PartitionKey = personName ClusteringKey = addedtime and lets say you had the following rows: personName= "n1", addedtime= "6/16/2015 1:00AM", color = "blue" personName= "n1", addedtime= "6/16/2015 1:15AM", color = "black" personName= "n2", addedtime= "6/16/2015 1:15AM", color = "red" would be stored like: RowKey = n1 -> name = 6/16/2015 1:00AM, Value = , timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:00AM:addedtime , Value = "6/16/2015 1:00AM", timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:00AM:color, Value = "blue", timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM, Value = , timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM:addedtime , Value = "6/16/2015 1:15AM", timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM:color, Value = "black", timestamp = <some ticks from when the data was written> RowKey = n2 -> name = 6/16/2015 1:15AM, Value = , timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM:addedtime , Value = "6/16/2015 1:15AM", timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM:color, Value = "red", timestamp = <some ticks from when the data was written> NOTE: The value of timestamp and added time are both stored as ticks, but I was too lazy to convert/calculate real ticks. Notice how the the column names (denoted by name) is actually the value of the clustering key concatenated with the actual column name. Also notice that columns are stored first in order of the clustering key, but each column with the same clustering key is also stored in order of its column name. Also notice that the first column that represents addedTime, doesn't have a value because its value is stored as the column name. Where in SQL you might expect each set of columns you insert to be a physical row, in Cassandra, you really have a partition and a set of cells which are physically sorted by clustering key and column name. You can see this yourself if you setup Cassandra and then use cassandra-cli to interact with your data using thrift.
- bluecmd 11y ago
  I know all that, I have extensive experience with NoSQL databases like HBase. But the thing I'm asking about how does Cassandra handle CPU hotspotting on a single row. If you have timestamp as a column you will never be able to shard a metric across servers, so accessing that metric will be I/O bound to whatever underlying storage. My hypothesis is that if you save data as "metric.timestamp" as row instead you will get much better scalability.
  - PretzelPirate 11y ago
    The short answer is that Cassandra doesn't handle hotspotting, its up to you to data model to avoid that. Timestamp is your clustering key, its only the partition key that determines how the data is sharded. If you have a table with firstName, lastName, loginTime and partition key = firstName, clustering key = loginTime, all rows with firstName ="Tim" will live on the same server regardless of loginTime. If you are often querying for logins, you may think that you should instead make the partition key = (firstName, loginTime), but that would leave your data in an almost irretrievable state since you would need to know the exact login times. It would also leave you looking at multiple partitions in every query, which isn't as performance as a single partition. A better partition key would be (firstName, dayAndHourOfLoginTime) so your partitions would be for each first name and each hour. Then your query would still have to know what hours to look for, but its a simple query pattern. If you are often looking at data across more than one hour, you probably want to rethink the key even further because its more efficient to look at a single partition.
    - bluecmd 11y ago
      Aha! Re-reading your previous comment I understood what you meant. So Cassandra has a "logical" key that is the primary key, and the actual key from HBase-like DBs is derrived from cluster key and the primary key. So in essence it's "metric.timestamp" as a row key but only schema defined. Nice.

by bluecmd 11y ago

PretzelPirate 11y ago

Please forgive my formatting. It seems formatting text on HN is more difficult than using Cassandra. Cassandra is interesting because you really have a partition key to define a "row" and then a set of columns (cells). When you define your clustering key, you are really defining how your data is stored on disk. If you had the following table (the columns are meaningless, so just ignore that you wouldn't ever use this dumb table): table1 (personName, addedtime, color) where PartitionKey = personName ClusteringKey = addedtime and lets say you had the following rows: personName= "n1", addedtime= "6/16/2015 1:00AM", color = "blue" personName= "n1", addedtime= "6/16/2015 1:15AM", color = "black" personName= "n2", addedtime= "6/16/2015 1:15AM", color = "red" would be stored like: RowKey = n1 -> name = 6/16/2015 1:00AM, Value = , timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:00AM:addedtime , Value = "6/16/2015 1:00AM", timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:00AM:color, Value = "blue", timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM, Value = , timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM:addedtime , Value = "6/16/2015 1:15AM", timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM:color, Value = "black", timestamp = <some ticks from when the data was written> RowKey = n2 -> name = 6/16/2015 1:15AM, Value = , timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM:addedtime , Value = "6/16/2015 1:15AM", timestamp = <some ticks from when the data was written> -> name = 6/16/2015 1:15AM:color, Value = "red", timestamp = <some ticks from when the data was written> NOTE: The value of timestamp and added time are both stored as ticks, but I was too lazy to convert/calculate real ticks. Notice how the the column names (denoted by name) is actually the value of the clustering key concatenated with the actual column name. Also notice that columns are stored first in order of the clustering key, but each column with the same clustering key is also stored in order of its column name. Also notice that the first column that represents addedTime, doesn't have a value because its value is stored as the column name. Where in SQL you might expect each set of columns you insert to be a physical row, in Cassandra, you really have a partition and a set of cells which are physically sorted by clustering key and column name. You can see this yourself if you setup Cassandra and then use cassandra-cli to interact with your data using thrift.

bluecmd 11y ago
I know all that, I have extensive experience with NoSQL databases like HBase. But the thing I'm asking about how does Cassandra handle CPU hotspotting on a single row. If you have timestamp as a column you will never be able to shard a metric across servers, so accessing that metric will be I/O bound to whatever underlying storage. My hypothesis is that if you save data as "metric.timestamp" as row instead you will get much better scalability.
- PretzelPirate 11y ago
  The short answer is that Cassandra doesn't handle hotspotting, its up to you to data model to avoid that. Timestamp is your clustering key, its only the partition key that determines how the data is sharded. If you have a table with firstName, lastName, loginTime and partition key = firstName, clustering key = loginTime, all rows with firstName ="Tim" will live on the same server regardless of loginTime. If you are often querying for logins, you may think that you should instead make the partition key = (firstName, loginTime), but that would leave your data in an almost irretrievable state since you would need to know the exact login times. It would also leave you looking at multiple partitions in every query, which isn't as performance as a single partition. A better partition key would be (firstName, dayAndHourOfLoginTime) so your partitions would be for each first name and each hour. Then your query would still have to know what hours to look for, but its a simple query pattern. If you are often looking at data across more than one hour, you probably want to rethink the key even further because its more efficient to look at a single partition.
  - bluecmd 11y ago
    Aha! Re-reading your previous comment I understood what you meant. So Cassandra has a "logical" key that is the primary key, and the actual key from HBase-like DBs is derrived from cluster key and the primary key. So in essence it's "metric.timestamp" as a row key but only schema defined. Nice.