https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
v
Could you share the Druid version that cluster is running?
j
“version”: “0.14.2-incubating”,
v
wow that is old! I remember the 0.14 release line fondly because that was the first release with the new web console
I see you are using Druid SQL 
druid/v2/sql
in the 0.14 line DruidSQL was still considered an experimental feature. I know that since then there were a lot of improvements to some edge cases in SQL parsing. Are there any specific queries that you notice causing the high CPU usage on these brokers?
j
I will try to get those queries (those are triggered by other team) but currently brokers are not responding queries at all. even those that we used to get in 1 second. and brokers still reporting 100% CPU
v
It is possible that some query with an edge case parsing issue (in your version) is locking up a thread and one at a time all threads get jammed
Are there queries with a large number of filter expressions like in and IN?
Also you must have been running 0.14 for a while . Did something change recently?
j
Our stakeholder (owner of the queries) recently (~past 2 months) added a new query, that polls for the previous 30d worth of data
That is the only known change - potentially we are hitting a max data retention/performance threshold on our cluster?
j
Seems this is what they are running:
Copy code
"""
New Engagement.
Every 10 minutes.
"""

END = datetime.utcnow()
START = END - timedelta(days=30)

# Now
client.groupby(
    datasource="vision_conformed",
    granularity="all",
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    having=Having(type="greaterThan", aggregation="count", value=5),
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="now"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
    context={"timeout": 300_000_000},
)

client.groupby(
    datasource="vision_conformed",
    granularity="all",
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    having=Having(type="greaterThan", aggregation="count", value=16),
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="now"),
            Filter(dimension="pzncon_event", value="seen"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
    context={"timeout": 300_000_000},
)

# PCC
client.groupby(
    datasource="vision_conformed",
    granularity="all",
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    having=Having(type="greaterThan", aggregation="count", value=5),
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="watchid"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
    context={"timeout": 300_000_000},
)

client.groupby(
    datasource="vision_conformed",
    granularity="all",
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    having=Having(type="greaterThan", aggregation="count", value=16),
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="watch_id"),
            Filter(dimension="pzncon_event", value="seen"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
    context={"timeout": 300_000_000},
)

"""
Engagement.
Every 10 minutes.
"""

END = datetime.utcnow()
START = END - timedelta(hours=24)

# Now
client.groupby(
    datasource="vision_conformed",
    granularity="all",
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="now"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
)

client.groupby(
    datasource="vision_conformed",
    granularity="all",
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="now"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
)

# PCC
client.groupby(
    datasource="vision_conformed",
    granularity="all",
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="watchid"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
)

client.groupby(
    datasource="vision_conformed",
    granularity="all",
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="watchid"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
)

"""
Popular.
Every 10 minutes.
"""

END = datetime.utcnow()
DURATION = timedelta(hours=1)

# PCC
client.groupby(
    datasource="vision_conformed",
    granularity={"type": "duration", "duration": duration.total_seconds() * 1000},
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="watchid"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
)

# Now
client.groupby(
    datasource="vision_conformed",
    granularity={"type": "duration", "duration": duration.total_seconds() * 1000},
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="watchid"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
)

"""
Trending.
Every 10 minutes.
"""

END = datetime.utcnow()
SLOTS = 5
DURATION = timedelta(hours=1)

# Now
client.groupby(
    datasource="vision_conformed",
    granularity={"type": "duration", "duration": duration.total_seconds() * 1000},
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="now"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
)

# PCC
client.groupby(
    datasource="vision_conformed",
    granularity={"type": "duration", "duration": duration.total_seconds() * 1000},
    dimensions=["pzncon_content_id"],
    aggregations={"count": doublesum("count")},
    filter=Filter(
        type="and",
        fields=[
            Filter(dimension="pzncon_content_id_type", value="watchid"),
            Filter(dimension="pzncon_event", value="consumed"),
        ],
    ),
    intervals=f"{start.strftime('%Y-%m-%dT%H:%M:%S')}/{end.strftime('%Y-%m-%dT%H:%M:%S')}",
)
s
What is the cardinality of the 
pzncon_content_id
dimension?
j
It's not necessarily unique per each row - whenever anyone sees/consumes a content title within a production client app, a new row would be added
i.e. different individuals could consume the same content title
(can definitely discuss further during our sync shortly - https://disney.zoom.us/j/5025748165?pwd=U3JsUjBMNXduMmlaTXFGRG4wbjlCdz09)
Open in Slack
PreviousNext
Powered by