Hi. on apache pinot realtime table upsert mode APP...
# troubleshooting
i
Hi. on apache pinot realtime table upsert mode APPEND, how can i limit how many value was appended then refer to new records instead ? we are doing a product tracking like inventory changes and etc, 1 day can be more than 100 changes, so we want to avoid too many value appended to the column if possible. is this doable on pinot config ?
x
in this case, I think you can try to enable minion compaction task to compact the table and reduce the table size
i
ah thanks @Xiang Fu. currently our workaround is add another column
epoch_days
then add a transform function for the column
ToEpochDaysBucket(now(),1)
. then add the value to to
primaryKeyColumns
on our schema. at least this is the most doable for our project atm
x
yeah, if you always need the newest value then it means you still need to keep track of all the values
how do you query data in this case, you only care about today’s data?
what if some product is not updated for 3 days ?
i
we only care about the latest data, from there we can get the log id which is appended then match back with our main postgresql db log id
i
ya last rows or latest data is fine, as long as we have a historical or list of values on our log id
x
ok
i
Copy code
"taskTypeConfigsMap": {
      "UpsertCompactionTask": {
        "schedule": "0 8 * ? * *",
        "bufferTimePeriod": "1d",
        "invalidRecordsThresholdPercent": "30",
        "invalidRecordsThresholdCount": "300000",
        "tableMaxNumTasks": "100",
        "validDocIdsType": "SNAPSHOT"
      }
    }
im kinda confused on this config, especially on
invalidRecordsThresholdPercent
,
invalidRecordsThresholdPercent
and
invalidRecordsThresholdCount
, do you have time to explain abit if you dont mind ?
x
it’s the threshold to rebuild segment, either count or percent
if a segment has 30% invalid (out-dated) records, then the minion task will compact it
in your case, you can make the task something like
Copy code
"UpsertCompactionTask": {
        "schedule": "0 * * ? * *",
        "bufferTimePeriod": "0d",
        "invalidRecordsThresholdPercent": "30",
        "tableMaxNumTasks": "100",
        "validDocIdsType": "SNAPSHOT"
}
you can also make the threshold lower like 10% to compact more often
i
our incoming data volume on this table is pretty huge, plus the ec2 server just 8gb ram, even now we running on production with this specs haha.
so kinda afraid to compact big size segments, probably will do it often
x
right, minion is a separated process
so you can make your best judgement on this.
i
yeah working on that
thanks for the advice man
@Xiang Fu sorry one question more, is the
UpsertCompactionTask
will clear invalid records per segment or it will combine multiple segments into one too ?
nvm just did a read on that, that is on another task that rolls up segments, sorry for interrupting
x
it will remove invalid docs, so you will find the older segments getting smaller
i
thanks for the further clarification man
btw @Xiang Fu sorry again, how can i have better memory usage with upsert tables ? seems like i test on production my pinot-server always out of memory
Copy code
pinot-server:
    image: apachepinot/pinot:1.3.0-21-amazoncorretto
    platform: linux/amd64
    container_name: pinot-server
    command: >
      StartServer
      -zkAddress zookeeper:2181
      -serverHost pinot-server
    volumes:
      - ./pinot_data/data:/data
      - ./pinot_data/server-data:/tmp/data
      - ./config:/config
      - ./input:/input
    restart: unless-stopped
    ports:
      - "8098:8098"
      - "8097:8097"
    healthcheck:
      test: [ "CMD-SHELL", "curl -f http://localhost:8097/health/readiness || exit 1" ]
      interval: 30s
      timeout: 120s
      retries: 15
      start_period: 120s
    depends_on:
      - pinot-controller
    environment:
      JAVA_OPTS: "-Xms1G -Xmx1G -XX:+UseG1GC"
    deploy:
      resources:
        limits:
          cpus: '1.0'
          memory: '2G'
abit context on my docker compose config
x
for upsert, the memory usage is proportional to the size of the primary keys, you need to give larger size for the server. Our suggestion is to give 0.5 * limit size of the memory for xmx and xms
you could also try startree version of upsert which is using disk version for upsert instead of heap memory.
i
startree version is a paid version of pinot is it ?
x
correct, I suggest u you to run the oss one first
i
if u dont mind, are there any docs / link that i can refer to for StarTree’s OSS version of apache pinot ?
nvm sorry just noticed got free one, currently testing