Hello we hit really interesting problem and would ...
# troubleshooting
g
Hello we hit really interesting problem and would be really appreciated if you can help us understand why it become a problem. After restart it self resolved, but i need to provide a stable service and want to understand why it happen. 1. Cluster - deployed in Kubernetes in GCP, not using helm directly, we used to generate the separate files a. Servers: 21x(8cpu and 64RAM and 2Tb storage) b. Brokers: 4x(8cpu and 32RAM) c. Controller/Zookeeper: 3x(8cpu and 64RAM and 1Tb storage) d. Version: 1.3.0 with image - 1.3.0-21-amazoncorretto 2. Schema: attached inside the thread 3. Realtime: table attached inside the thread 4. Data: a. 1.96TB Reported Size (Controller UI) b. 2002 segments (Controller UI) 5. Only data was ingested before/during/after the issue, so no queries or playing with swagger apis/UIs, it was only ingestion from kafka Issue description: During ingestion of data all servers cpu hit 100% (7.95/8 cpu per server used) and it didn't went down. During normal ingestion each server keeps around 2-2.5 CPU usage, but during the issue all of them used all 8 of their CPUs, issue continued for 8hours before i manually descaled all servers and started them back up. I have attached logs and metrics we have as well for 1 server, but from our investigation all of them did the same thing.
server cpu usage.png,server logs 1.png,server logs 2.png,server logs 3.png,server logs 4.png,server memory usage.png,server network traffic.png,schema.json
realtime-table-config.json
Sadly don't have the full log file for the server, but the logs are repeating themselves for adding segments and "SegmentOnlineOfflineStateModel.onBecomeOnlineFromOffline"
the topic we consume from kafka has 100 partitions and replication factor of 3
i think it is something because of the memory, because it is really high, even tho i have set it to 50% of the k8s threshold (-XX:ActiveProcessorCount=8 -Xms4G -Xmx32G -XX:+UseG1GC -XX:MaxGCPauseMillis=200)
happened again during load that i don't think is that big
in this guide https://startree.ai/resources/capacity-planning-in-apache-pinot-part-1 i see you recommend a 1 vCpu per 1 partition, which is not the case, but i'm using the suggested machine size
in theory if i want to be able to handle the topic i would need 100 cpus? or 300 because of the replication ?
This happens when we hit 10k rate on metric pinot_server_realtimeRowsFetched_OneMinuteRate
each message is around 4.5kb
x
I think what you can do to improve the perf is: 1. Configure xms to the same xmx 32g. 2. Add completionMode to Download to avoid extra segment download https://docs.pinot.apache.org/configuration-reference/table#segmentsconfig 3. Decouple controller from data path: https://docs.pinot.apache.org/operators/operating-pinot/decoupling-controller-from-the-data-path
g
1. will try that, the memory of the application was weird it was about 55-60GB /64GB limit, even tho no queries were being processed 2. Added 3. this is the config for the servers and controllers:
Copy code
apiVersion: v1
kind: ConfigMap
metadata:
  name: pinot-server-config
  labels:
    app: pinot
    component: server
data:
  pinot-server.conf: |-
    pinot.server.netty.port=8098
    pinot.server.adminapi.port=8097
    pinot.server.instance.dataDir=/var/pinot/server/data/index
    pinot.server.instance.segmentTarDir=/var/pinot/server/data/segment
    pinot.set.instance.id.to.hostname=true
    pinot.server.instance.realtime.alloc.offheap=true
    pinot.query.server.port=7321
    pinot.query.runner.port=7732
    realtime.segment.serverUploadToDeepStore = true
    pinot.server.instance.segment.stream.download.untar=true
    pinot.server.instance.segment.stream.download.untar.rate.limit.bytes.per.sec=100000000
    pinot.server.instance.table.level.max.parallel.segment.downloads=4
    pinot.server.instance.enable.split.commit=true
    pinot.server.netty.native.transports.enabled=true
i think i have implemented all performance improvements i could find in your docs
Copy code
apiVersion: v1
kind: ConfigMap
metadata:
  name: pinot-controller-config
  labels:
    app: pinot
    component: controller
data:
  pinot-controller.conf: |-
    controller.port=9000
    pinot.set.instance.id.to.hostname=true
    # Cluster name
    controller.helix.cluster.name=pinot-quickstart
    # ZooKeeper configuration
    controller.zk.str=pinot-zookeeper:2181
    # External access
    controller.access.protocols.http.port=9000
    # Controller periodic tasks
    controller.task.scheduler.enabled=true
    controller.task.frequencyPeriod=5m
    # System resources
    controller.data.dir=/var/pinot/controller/data
    controller.local.temp.dir=/tmp/pinot-controller-tmp
    controller.deleted.segments.retentionInDays=1
    # Optional: Throttling config for high volume
    controller.realtime.segment.commit.timeoutMs=120000
    # tuning
    controller.allow.hlc.tables=false
    controller.enable.split.commit=true
Also after a lot of investigation and testing it seems that at full speed a single partition needs about 1.6 cpu, which means during full load ingestion with 100 partitions and replication factor of 3 would mean we need 480 cpus
x
oh, so you are actually using controller ebs for deep store and controller http endpoint for segments download
well, I think with 2, you can save more time for segment build
g
deepstore i think is not configured or i don't really understand it correctly, because we are just using google disks
x
Also when you get 3 to move deepstore to GCS, it will also relief a lot of pressure from controller side for realtime segments commit
deep store serves mainly as a cheap storage for data backup, but will also serve as data sources for minion tasks etc
it’s in general makes the whole system more robust and scalable
g
can you help me configure it or at least understand what am i missing
g
but this is not the file system that is attached to the pod itself?
x
it’s not
it’s using Google cloud storage(GCS)
like Amazon S3
g
ooooo, we don't have this
x
but you are using GCP right ?
g
Yes
but not sure i can configure/use it
x
Then you should have the access or just request the access and quota from your cloud/infra team
g
if i don't use the deep store thing, how would this affect the performance
i mean, can i disable it completely?
x
controller will be bottleneck as all the servers will try to upload segments to controller
so you need server to uploadSegments to deep store directly to bypass this data path
g
now i understand it, why/how it works
x
deep store can help removing controller from the hotspot
so controller only handles metadata updates and data management work
g
and if this is the case, then do i need that much resources for controller?
as currently it has 8cpus and 64 ram and during everything i have seen max of 0.7 cpu usage and 9GB ram of usage
image.png
zookeeper is using a bit more, but still not anywhere near the recommendations - https://startree.ai/resources/capacity-planning-in-apache-pinot-part-1
x
I think controller might blocked by IO as for your traffic pattern, controller is mainly doing segments upload and save to local disk stuffs
if you add deep store, then commit will be way faster and controller can handle more commits, then cpu usage might go up 🙂
you can try to half size of the controller for now
and see how the usage of it
g
ok, so i need to configure properly deep store with GCS and possibly add more vCPUs to servers because of the big amount of partitions of the topic
in order for them not to die like this
x
I would add deep store first
then re-benchmark the throughput thing for servers
g
one more thing i think i found as an issue. If you check out the size.in.bytes you can see it is around 25mb, even tho the realtime table has a 500mb configuration. I did some digging and found that there is a default flush.threshold.size of 100000 and it seems that it is respected, even tho it is default. Meaning if i want a segment of 500mb i would need to override the threshold size with an average message size
Copy code
{
  "segment.realtime.endOffset": "3405878",
  "segment.start.time": "1534184236957",
  "segment.time.unit": "MILLISECONDS",
  "segment.flush.threshold.size": "100000",
  "segment.realtime.startOffset": "3377687",
  "segment.size.in.bytes": "24639413",
  "segment.end.time": "1579261376287",
  "segment.total.docs": "100004",
  "segment.realtime.numReplicas": "3",
  "segment.creation.time": "1746706039300",
  "segment.index.version": "v3",
  "segment.crc": "4258377168",
  "segment.partition.metadata": "{\"columnPartitionMap\":{\"customer_id\":{\"numPartitions\":100,\"functionName\":\"Murmur\",\"partitions\":[0,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,21,25,27,28,29,32,33,36,37,39,40,41,43,44,46,47,49,50,51,52,53,54,55,59,60,61,62,63,64,65,66,68,70,72,73,74,76,77,79,80,81,82,84,85,86,87,90,91,92,93,94,95,98,99],\"functionConfig\":null}}}",
  "segment.realtime.status": "DONE",
  "segment.download.url": "http://pinot-controller-0.pinot-controller.pinot.svc.cluster.local:9000/segments/bets/bets__0__0__20250508T1207Z"
}
when i did "realtime.segment.flush.threshold.segment.size": "500M", "realtime.segment.flush.threshold.size": "2000000" it works as expected but i need to set ignore all validations in the swagger api when creating the table
x
yeah, this
realtime.segment.flush.threshold.size
is a deprecated config, you can remove it then it should be
-1
as default
then pinot should configure a default value of
5_000_000
I remember if you set his value to
0
then pinot should auto adjust the flush size, can you check latest a few segments metadta?
e.g.
bets__0__100__202505xxxxxZ
g
they are all the same
x
hmm, so you only have
Copy code
"realtime.segment.flush.threshold.segment.rows": "0",
      "realtime.segment.flush.threshold.time": "24h",
      "realtime.segment.flush.threshold.segment.size": "500M",
      "realtime.segment.flush.threshold.size": "0"
initially for the table flush config?
g
yes, but instead of changing it i decided to create a new one with updated values
because loading 2tb of data is slow in terms of querying from other dbs
x
Ok, I think more rows is good to make each segment sizable like 500mb to 1.5gb.
g
loaded around 200gb of data and it seems all was fine with it
so do you suggest to go with around 5m rows per segment (this should be around 1.1/1.2gb a segment)
x
In that case, you should definitely make sure the
completionMode
to
Download
. This will avoid the local segment build which may take long time
I think you can start with 2MM
g
currently with 2m rows it is between 450 and 550mb each segment
x
that is already good number, you data is not that large
2tb is about 4k segments
g
i also have a question in regards to how rows/docs are calculated
since we are using unnesting
1 kafka message on average becomes 4 rows
x
rows=docs, so it’s after the flattening
g
ok, i thought the same, but wanted to be sure
x
ack
g
will also reduce the number of partitions as i think 100 is way too much, given that we want to handle around 100k/s at peak load
this should also reduce the number of cores/memory needed and make the data smaller i think
btw the end goal is to load around 13b (unnested) rows into pinot - according to calculations this should be around 5tb of data at the end after compression
with that in mind should i still keep the 2M rows segment ?
x
If you are using 1.3 release and this number is good
g
yes, 1.3 release
one more question in regards to indexing, do i need to put the columns that have rangeIndex inside noDictionaryColumns? The date columns are current time of creation so there are close 0 duplicates if we consider milliseconds granularity and on seconds there might be, but i need them only for range queries of type select someData from bets where customer_id = X and creation_date > Y and creation_date < Z
also is there a big difference between seconds and days filtration? Should i try to optimize queries to use day granularity when i don't care about exact timestamp. i mean will it make a huge difference in this case based on experience
x
suggest to create columns per time granularity like days or seconds if you have the needs for filter at that level. Also startree index might be better with those fields
g
i tried to create star tree index, but with realtime upsert table i got some error that i can't do it
x
correct. upsert table doesn’t support startree index, unless you do hybrid table kind of setup
also upsert table requires all pk in the memory, that might be another memory overhead
g
i want to avoid it at least for now, as it creates too much moving parts that can break and since i don't have retention of the data i think i can get away with it
also what i need as functionality is to have constant upsert per say, such that i only ever have 1 record inside pinot about a given PK
but if segment is completed then i can have duplicates
which is not ideal, but i have to figure out a way to deal with it
x
create a day granular column might beneficial for a long run and it’s usually very small size if majority of the data are the same value
pinot upsert will handle completed segments unless you move those segments from realtime table to offline table
g
wait so if i have upsert and keep it all inside the realtime table i will never have duplicates?
x
correct
g
wow, that is awesome, btw i have read the docs multiple times and it is not really easy to grasp this part from there
as for the day granularity how do i populate it, as current setup doesn't seems to work in schema: { "name": "creation_date", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:SECONDS", "notNull": true }, { "name": "creation_date_day", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:DAYS", "transformFunction": "toEpochDays(creation_date)", "notNull": true }, in table: { "columnName": "creation_date", "transformFunction": "CreationDate" }
x
basically if you can ensure after a given days there won’t be any update for a PK, then you can move those segments to offline table
g
image.png
in our business those days can be up to 4 years, so not really an option
x
toEpochDays
will divide epoch millis to epoch day value
so you need to convert it back to epoch millis
e.g.
1747138079000
is millis epoch, the days epoch value is
20221
, then convert it back to
1747094400000
g
got it, thanks
x
you can use
fromEpochDays(toEpochDays(creation_date))
as the transform functino
g
do i need to change the format as well?
or can keep it as it is ?
i am reading about Timestamp index, can i omit the _day column and just put timestamp index instead of range index and it should work just as fast correct ?
or it is better in terms of speed/readability to be manually created
x
yes, Timestamp index should work better
manually created columns will be better an easier for usage if you keep some convention
otherwise users need to understand what’s the index and the how to write it in the query
g
got it, thanks