1

Running a 4 node cluster cassandra version 2.0.9. Recently since a month we are seeing a huge spike in the CPU usage on all the nodes.

Node status

tpstats gives me high Native-transport-requests. Attaching screenshot for 3 nodes tpstats

Node 1 Node status Node 2 Node status Node 3 Node status

From where should I start debugging?

Also if you see from first picture when the load becomes high the read and write becomes low . This is understandable as the majority of the requests drop

Vinoth Kumar J
  • 167
  • 1
  • 10
  • Are you seeing high GC pauses as well? Any tombstone overwhelming exceptions or batch size warnings in your system.log? Usually things like this happen due to bad queries, bad models, or misuse of batch statements. – Aaron Aug 26 '16 at 17:22
  • Thanks Aaron for that suggestion. Yes we do get tombstone overwhelming exceptions(default threshold > 100000). We do some of deletes. Is there a way we can evade from this exception? Should we change the Compaction time to leveled compaction(we want fast read for this table). Should we decrease the gc_grace_seconds to like 3 days? Is there a way by which we can monitor which queries are running slow on the server? We monitored the Native Transport Request thread and see that they are chucking a lot of CPU cycles. Can we the queries associated with these request? – Vinoth Kumar J Aug 27 '16 at 08:12

1 Answers1

2

How to mitigate tombstones? I probably get that question from our dev teams a dozen times per month. The easiest way, is to not do DELETEs, and I'm dead serious about that. Otherwise, you can model your tables in such a way to mitigate tombstones in a better way.

For example, let's say I have a simple table to keep track of order status. As an order can have several different statuses (pending, picking, shipped, received, returned, etc...) a lazy way is to have one row per order, and either DELETE or run an in-place update to change the status (depending on whether or not status is a part of your key). A better way, is to convert it to a time series and perform deletes via a TTL. The table would look something like this:

CREATE TABLE orderStatus (orderid UUID,
    updateTime TIMEUUID,
    status TEXT,
    PRIMARY KEY (ordered, status))
with CLUSTERING ORDER BY (updateTime DESC);

Let's say I know that I really only care about order status for a max of 30 days, so all status upserts have a TTL of 30 days...

INSERT INTO orderStatus (orderid,updateTime,status) 
VALUES (UUID(),now(),'pending') USING TTL 2592000;

That table will support queries for order status by orderid, sorted by the update time descending. That way, I can SELECT from that table for an id with a LIMIT 1, and always get the most recent status. Additionally, those statuses will get deleted automatically after 30 days. Now, TTLing data still creates tombstones. But those tombstones are separate from the newer orders (the ones I probably care about more), so I typically don't have to worry about those tombstones interfering in my queries (because they're all grouped in partitions that I won't be querying often).

That's one example, but I hope the idea behind modeling for tombstone mitigation is clear. Mainly, the idea is to partition your table in such a way that the tombstones are kept separate from the data that you query most-often.

Is there a way by which we can monitor which queries are running slow on the server?

No, there really isn't a way to do that. But, you should be able to request all queries from your developers for problem keyspaces/tables. And that should be easy, because a table should really only be able to support one or two queries. If your developers built a table that supports 5 or 6 different queries, they're doing it wrong.

When you look at the queries, these are some red flags you should question:

  • Unbound queries (SELECTs without WHERE clauses).
  • Queries with ALLOW FILTERING.
  • Use of secondary indexes.
  • Use of IN.
  • Use of BATCH statements (I have seen a batch statement tip-over a node before).
Aaron
  • 55,518
  • 11
  • 116
  • 132