Cassandra query response time degradation

cassandraperformance

I am experimenting with Cassandra on 4 sites with 2 nodes on each one. Have small network latency between them. I always query the data in DC where I inserted it. I see very strange behavior: when I increase the nodes number the query response time slowly increasing, but insert time is improving. I am expecting that query time should improve as well. Any suggestion regarding configurations check?

Best Answer

Response time heavily dependent on the query that you're executing & how you're executing them. Linear scalability is achieved only if you're performing reads by primary or at least partition key. If you're doing something like SELECT * FROM table WHERE non_pk_column = 'something' ALLOW FILTERING or SELECT * FROM table, then Cassandra will need to get data from all your nodes, and as you increase the number of nodes, the latency is increasing.

Also, when executing query you need to make sure that you're using token aware load balancing policy that will send query request to the coordinator node that has one of the replicas.

But it's a really broad question, please provide more information about your queries, etc.

Related Solutions

Cassandra – Query a column with collection type

You can index collection types in cassandra 2.1 and later. You are after:
SELECT * FROM <table> WHERE <field> CONTAINS <value_in_list/map/set>

Detailed example:

cqlsh> USE ks;
cqlsh:ks> CREATE TABLE data_points (
            id text PRIMARY KEY,
            created_at timestamp,
            previous_event_id varchar,
            properties map<text,text>
         );
cqlsh:ks> create index on data_points (properties);
cqlsh:ks> INSERT INTO data_points (id, properties) VALUES ('1', { 'fruit' : 'apple', 'band' : 'Beatles' });
cqlsh:ks> INSERT INTO data_points (id, properties) VALUES ('2', { 'fruit' : 'cherry', 'band' : 'Beatles' });
cqlsh:ks> SELECT * FROM data_points WHERE properties CONTAINS 'Beatles';

 id | created_at | previous_event_id | properties
----+------------+-------------------+----------------------------------------
  2 |       null |              null | {'band': 'Beatles', 'fruit': 'cherry'}
  1 |       null |              null |  {'band': 'Beatles', 'fruit': 'apple'}

(2 rows)

Word of warning, secondary indexes don't scale out well as they use a scatter/gather algorithm to find what you need, if you plan to use them for heavy tagging it might be better to denormalize the properties field int a separate table and carry out multiple queries.

Cassandra query performs differently at different times

primary key (empID,uniqueID)

This CF contains data for more that 5K row entires and and each employee contains minimum of 100K columns and the maximum of 1000K columns

That's WAY too many rows per partition. My guess is that the query slowdowns happen when large partitions are queried. It all depends on data cell value size and data width, but as a general rule, I would not model more than 10k-30k rows per partition.

To test this out, you could run nodetool tablehistorgrams on your table to gauge things like max cell count and partition size. Then run your query against both small and large partitions with TRACING ON, and I'm sure you'll see the difference.

Basically, try reworking your model for smaller partitions.

Best Answer

Related Solutions

Cassandra – Query a column with collection type

Cassandra query performs differently at different times

Related Question