Cassandra partitioning strategy for systems with skewed traffic

Question

Please bear with me for slightly longer problem description. I am a newbie to Cassandra world and I am trying to migrate my current product from oracle based data layer to Cassandra.

In order to support range queries I have created an entity like below:

create table if not exists my_system.my_system_log_dated(
  id uuid,
  client_request_id text,
  tenant_id text,
  vertical_id text,
  channel text,
  event text,
  event_type text,
  created_date date,
  primary key((created_date, tenant_id, vertical_id, channel, event), 
  event_type, client_request_id, id)
) with clustering order by (created_date desc);

Now, I have come across several documentation/resources/blogs that mentions that I should be keeping my partition size less than 100 mb for an optimally performing cluster. With the volume of traffic my system handles per day for a certain combinations of partitioning key, there is no way i can keep it less than 100 mb with above partitioning key.

To fix this i introduced a new factor called bucket_id and was thinking of assigning it hour of the day value to further break partitions into smaller chunks and keep them less than 100 mb(Even though this means i have to do 24 reads to serve traffic details for one day, but i am fine with some inefficiency in reads). Here is the schema with bucket id

 create table if not exists my_system.my_system_log_dated(
  id uuid,
  client_request_id text,
  tenant_id text,
  vertical_id text,
  channel text,
  event text,
  bucket_id int,
  event_type text,
  created_date date,
  primary key((created_date, tenant_id, vertical_id, channel, event, 
  bucket_id), event_type, client_request_id, id)
) with clustering order by (created_date desc);

Even with this, couple of combinations of goes more than 100 mb while all other volume sits comfortably within the range.

With this situation in mind I have below questions:

1. Is it an absolute blunder to have few of your partitions go beyond 100 mb limit?
2. Though with even smaller bucket say 15 min window, I get all combinations of partition key under 100 mb but that too creates heavily skewed partitions, meaning that high volume combinations of partition key goes up till 80 mb while remaining once are well under 15 mb. Is this something that will adversely impact performance of my cluster?
3. Is there a better way to solve this problem?

Here is some more info that I thought may be useful:

• Avg row size for this entity is around 200 bytes
• I am also considering a load future proofing factor of 2 and estimating for double the load.
• Peak load for a specific combination of partition key is around 2.8 Million records in a day
• the same combination has peak traffic hour of about 1.4 million records
• and the same in 15 min window is around 550,000 records.

Thanks in advance for your inputs!!

Answer 1

Your approach with the bucket id looks good. Answering your questions:

1. No, it's not a hard limit, and actually, it might be too low taking into account hardware improvements over the last few years. I have seen partitions of 2 GB and 5 GB (though they can give you a lot of headaches when doing repairs), but those are extreme cases. Don't go near those values. Bottom line, if you don't go WAY above those 100 MB, you will be fine. If you have at least 15 GB of ram, use G1GC and you're golden.
2. A uniform distribution on the partition sizes is important to keep the data load balanced throughout the cluster, and it's also good so that you're confident that your queries will be close to an average latency (because they will be reading the approximate same sizes of data), but it's not something that will give performance issues on its own.
3. The approach looks good, but if that's a time series, which I think it is taking into account what you said, then I recommend that you use TWCS (Time Window Compaction Strategy) in my_system.my_system_log_dated. Check how to configure this compaction strategy, because the time window you set will be very important.