0

For my current project i'm using Cassandra Db for fetching data frequently. Within every second at least 30 Db requests will hit. For each request at least 40000 rows needed to fetch from Db. Following is my current code and this method will return Hash Map. 

 public Map<String,String> loadObject(ArrayList<Integer> tradigAccountList){

        com.datastax.driver.core.Session session;
        Map<String,String> orderListMap = new HashMap<>();
        List<ResultSetFuture> futures = new ArrayList<>();
        List<ListenableFuture<ResultSet>> Future;

        try {
            session =jdbcUtils.getCassandraSession();
            PreparedStatement statement = jdbcUtils.getCassandraPS(CassandraPS.LOAD_ORDER_LIST);

            for (Integer tradingAccount:tradigAccountList){
                futures.add(session.executeAsync(statement.bind(tradingAccount).setFetchSize(3000)));
            }
            Future = Futures.inCompletionOrder(futures);

            for (ListenableFuture<ResultSet> future : Future){
                for (Row row: future.get()){
                    orderListMap.put(row.getString("cliordid"), row.getString("ordermsg"));
                }
            }

        }catch (Exception e){
        }finally {
        }
        return orderListMap;
    }

My data request query is something like this, "SELECT cliordid,ordermsg FROM omsks_v1.ordersStringV1 WHERE tradacntid = ?". My Cassandra cluster has 2 nodes with 32 concurrent read and write thread for each and my Db schema as follow

CREATE TABLE omsks_v1.ordersstringv1_copy1 (
    tradacntid int,
    cliordid text,
    ordermsg text,
    PRIMARY KEY (tradacntid, cliordid)
) WITH bloom_filter_fp_chance = 0.01
AND comment = ''
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair_chance = 0.0
AND speculative_retry = '99.0PERCENTILE'
AND caching = {
    'keys' : 'ALL',
    'rows_per_partition' : 'NONE'
}
AND compression = {
    'sstable_compression' : 'LZ4Compressor'
}
AND compaction = {
    'class' : 'SizeTieredCompactionStrategy'
};

My problem is getting Cassandra timeout exception, how to optimize my code to handle all these requests

IsharaD
  • 322
  • 2
  • 4
  • 17

1 Answers1

2

It would be better if you would attach the snnipet of that Exception (Read/write exception). I assume you are getting read time out. You are trying to fetch a large data set on a single request.

For each request at least 40000 rows needed to fetch from Db

If you have a large record and resultset is too big, it throws exception if results cannot be returned within a time limit mentioned in Cassandra.yaml.

read_request_timeout_in_ms

You can increase the timeout but this is not a good option. It may resolve the issue (may not throw exception but will take more time to return result).

Solution: For big data set you can get the result using manual pagination (range query) with limit.

SELECT cliordid,ordermsg FROM omsks_v1.ordersStringV1 WHERE tradacntid > = ? and cliordid > ? limit ?;

Or use range query

SELECT cliordid,ordermsg FROM omsks_v1.ordersStringV1 WHERE tradacntid = ? and cliordid >= ? and cliordid <= ?;

This will be much more faster than fetching the whole resultset.

You can also try by reducing the fetch size. Although it will return the whole resultset.

public Statement setFetchSize(int fetchSize) to check if exception is thrown.

setFetchSize controls the page size, but it doesn't control the maximum rows returned in a ResultSet.

Another point to be noted:

What's the size of tradigAccountList?

Too many requests at a time also may lead to timeout. Large size of tradigAccountList and a lot of read requests are done at a time (load balancing of requests are handled by Cassandra and how many requests can be handled depends on cluster size and some other factors) may cause this exception .

Some related Links:

Cassandra read timeout

NoHostAvailableException With Cassandra & DataStax Java Driver If Large ResultSet

Cassandra .setFetchSize() on statement is not honoured

Chaity
  • 1,348
  • 13
  • 20