test program to test range scan in the presence of high overwrites

ycql_overwrite_range_query.py

 pip install yb-cassandra-driver

from cassandra.cluster import Cluster
from cassandra import ConsistencyLevel
import time
import random
from multiprocessing.dummy import Pool as ThreadPool


cluster = Cluster(['127.0.0.1'])
session = cluster.connect()
def create_table():
  start_time = time.time()
  session.execute("""CREATE KEYSPACE IF NOT EXISTS k""");
  session.execute("""USE k""");
  session.execute("""DROP TABLE IF EXISTS events""");
  now_time = time.time()
  print("Dropped (if exists): events table")
  print("Time: %s ms" % ((now_time - start_time) * 1000))
  print("====================")
  start_time = time.time()
  session.execute("""
        CREATE TABLE IF NOT EXISTS events(
           bkt    text,
           subbkt int,
           ctr    int,
           PRIMARY KEY((bkt), subbkt)
        ) WITH CLUSTERING ORDER BY (subbkt ASC) AND TRANSACTIONS = {'enabled' : true} AND TABLETS=1
    """)
  now_time = time.time()
  print("Created events table")
  print("Time: %s ms" % ((now_time - start_time) * 1000))
  print("====================")

def load_data():
  # Load Phase params
  num_subbkts = 4
  num_increments = 20000
  session.execute("""USE k""");
  insert_stmt = session.prepare("""INSERT INTO events (bkt, subbkt, ctr) VALUES (?, ?, ?)""")
  start_time = time.time()
  for idx in range(num_subbkts):
    session.execute(insert_stmt, ["bkt-1", idx, 1])
  now_time = time.time()
  print("Insert Time: %s ms" % ((now_time - start_time) * 1000))

  incr_stmt = session.prepare("""UPDATE events SET ctr = ctr + 1 WHERE bkt = ? and subbkt = ?""")
  start_time = time.time()
  for idx in range(num_subbkts):
    for jdx in range(num_increments):
      session.execute(incr_stmt, ["bkt-1", idx])
  now_time = time.time()
  print("Counter Increment Time: %s ms" % ((now_time - start_time) * 1000))

def query_data(order_by, limit):
  session.execute("""USE k""");
  stmt = """SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt {} LIMIT {}""".format(order_by, limit)
  prepared_stmt = session.prepare(stmt)
  start_time = time.time()
  results = session.execute(prepared_stmt, ["bkt-1", 4])
  now_time = time.time()
  print("Query: " + stmt)
  for row in results:
    print row
  print("Read Time: %s ms" % ((now_time - start_time) * 1000))


# Main
create_table()
load_data()
query_data("ASC", 1)
query_data("ASC", 2)
query_data("DESC", 1)
query_data("DESC", 2)
query_data("ASC", 1)
query_data("ASC", 2)
query_data("DESC", 1)
query_data("DESC", 2)
query_data("ASC", 1)
query_data("ASC", 2)
query_data("DESC", 1)
query_data("DESC", 2)

Output:

$ python ~/notes/ycql_overwrite_range_query.py
Dropped (if exists): events table
Time: 1080.39498329 ms
====================
Created events table
Time: 46.8528270721 ms
====================
Insert Time: 9.63807106018 ms
Counter Increment Time: 65681.0901165 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt ASC LIMIT 1
Row(subbkt=0, ctr=20001)
Read Time: 3.60608100891 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt ASC LIMIT 2
Row(subbkt=0, ctr=20001)
Row(subbkt=1, ctr=20001)
Read Time: 2.91895866394 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt DESC LIMIT 1
Row(subbkt=3, ctr=20001)
Read Time: 3.19695472717 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt DESC LIMIT 2
Row(subbkt=3, ctr=20001)
Row(subbkt=2, ctr=20001)
Read Time: 2.8281211853 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt ASC LIMIT 1
Row(subbkt=0, ctr=20001)
Read Time: 1.02615356445 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt ASC LIMIT 2
Row(subbkt=0, ctr=20001)
Row(subbkt=1, ctr=20001)
Read Time: 1.00898742676 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt DESC LIMIT 1
Row(subbkt=3, ctr=20001)
Read Time: 1.03783607483 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt DESC LIMIT 2
Row(subbkt=3, ctr=20001)
Row(subbkt=2, ctr=20001)
Read Time: 1.03306770325 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt ASC LIMIT 1
Row(subbkt=0, ctr=20001)
Read Time: 1.00898742676 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt ASC LIMIT 2
Row(subbkt=0, ctr=20001)
Row(subbkt=1, ctr=20001)
Read Time: 1.27601623535 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt DESC LIMIT 1
Row(subbkt=3, ctr=20001)
Read Time: 1.03211402893 ms
Query: SELECT subbkt, ctr FROM events WHERE bkt = ? and subbkt < ? ORDER BY subbkt DESC LIMIT 2
Row(subbkt=3, ctr=20001)
Row(subbkt=2, ctr=20001)
Read Time: 1.02591514587 ms