Druid Query Performance

Druid version 0.10.1.2.6.4.0-91

query/cpu/time",“value”:116685 (they collocate broker nodes with NiFi nodes, no doubt this number is high)

query/time",“value”:25944 (no cache hit)

query/node/ttfb",“value”:25711

query/node/time",“value”:6

query/node/bytes",“value”:878,

One hourly query only query 50MB data, but never hit the cache. Why cache/total/misses and cache/total/hits both 0?

“version”:“0.10.1.2.6.4.0-91”,“metric”:“segment/scan/pending”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/numEntries”,“value”:0}]

version":“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/sizeBytes”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/hits”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/misses”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/evictions”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/hitRate”,“value”:0.0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/averageBytes”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/timeouts”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/errors”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/numEntries”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/sizeBytes”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/hits”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/misses”,“value”:0}]

,“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/evictions”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/hitRate”,“value”:0.0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/averageBytes”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/timeouts”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/errors”,“value”:0}]

We enable the cache on Broker as well as historical nodes:

on Broker -

druid.broker.cache.useCache=true

druid.broker.cache.populateCache=true

on historical -

druid.historical.cache.useCache=true

druid.historical.cache.populateCache=true

druid.cache.sizeInBytes = 2684354560

But on both brokers: ( only 2 G free memory left )

total used free shared buffers cached

Mem: 503 500 2 0 0 364

-/+ buffers/cache: 136 367

Swap: 1 0 1

So for query retrieving large amount of data segments, I will suggest them to dedicate nodes for Druid. But for hourly query which only retrieving 34MB data, why the query not cached?

Hey Xiaojing,

Which type of query are you using?

By default Druid won’t cache scan queries or groupBys because they’re generally quite large.

Check out the configuration option druid.{node}.cache.unCacheable the default value is [“groupBy”, “select”]

Cheers,

Dylan

Thanks Dylan.

now I put following, but still no cache hit. And why query/cache/total/hits and query/cache/total/misses",“value”:0 all 0?

druid.broker.cache.unCacheable=

Hey Xiaojing,

Which type of query are you using?

By default Druid won’t cache scan queries or groupBys because they’re generally quite large.

Check out the configuration option druid.{node}.cache.unCacheable the default value is [“groupBy”, “select”]

Cheers,

Dylan

Druid version 0.10.1.2.6.4.0-91

query/cpu/time",“value”:116685 (they collocate broker nodes with NiFi nodes, no doubt this number is high)

query/time",“value”:25944 (no cache hit)

query/node/ttfb",“value”:25711

query/node/time",“value”:6

query/node/bytes",“value”:878,

One hourly query only query 50MB data, but never hit the cache. Why cache/total/misses and cache/total/hits both 0?

“version”:“0.10.1.2.6.4.0-91”,“metric”:“segment/scan/pending”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/numEntries”,“value”:0}]

version":“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/sizeBytes”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/hits”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/misses”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/evictions”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/hitRate”,“value”:0.0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/averageBytes”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/timeouts”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/delta/errors”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/numEntries”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/sizeBytes”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/hits”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/misses”,“value”:0}]

,“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/evictions”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/hitRate”,“value”:0.0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/averageBytes”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/timeouts”,“value”:0}]

“version”:“0.10.1.2.6.4.0-91”,“metric”:“query/cache/total/errors”,“value”:0}]

We enable the cache on Broker as well as historical nodes:

on Broker -

druid.broker.cache.useCache=true

druid.broker.cache.populateCache=true

on historical -

druid.historical.cache.useCache=true

druid.historical.cache.populateCache=true

druid.cache.sizeInBytes = 2684354560

But on both brokers: ( only 2 G free memory left )

        total       used       free     shared    buffers     cached

Mem: 503 500 2 0 0 364

-/+ buffers/cache: 136 367

Swap: 1 0 1

So for query retrieving large amount of data segments, I will suggest them to dedicate nodes for Druid. But for hourly query which only retrieving 34MB data, why the query not cached?

druid.historical.cache.unCacheable=

query/node/ttfb is very high 24666