#general
@keweishang: Hi team, we’re considering Pinot for our customer-facing analytics (mainly dashboards). We also want to use the same data store (Pinot table) to export denormalized report (CSV file of 10k - 100k rows filtered from the table, with ~20 selected columns) to our clients. Is Pinot also a good fit in the report generating use case. For example, our backend service could query the Pinot table and generate the CSV report from the query result. Thanks!
@mayanks: Yes 10-100k rows should be fine for report downloading kind of use cases
@keweishang: Thank you @mayanks for your quick reply. Hopefully the Pinot PoC will work out nicely for us :+1:
@mayanks: Sure, let us know if you have questions
#troubleshooting
@shaileshjha061: Hi @mayanks @dlavoie Our API Team is not able to connect to Pinot Broker. Can you help me with this? I am attaching the broker log. ```SEVERE: An I/O error has occurred while writing a response message entity to the container output stream. org.glassfish.jersey.server.internal.process.MappableException: java.io.IOException: Connection closed``` CC: @mohamed.sultan @nadeemsadim
@shaileshjha061: @dlavoie @mayanks Can you please help me troubleshoot this issue?
@mohamed.sultan: we are receiving 404 Error when we try to use broker ip
@mohamed.sultan: not sure why?
@mayanks: Does query via query console work?
@mohamed.sultan: yes. it is working
@mayanks: And can your query broker using curl
@mayanks: If query console works, that means broker up and running. that also implies you can query broker using curl. I’d suggest looking at your client side code. if you are getting 404, that means it is not hitting the right url?
@mohamed.sultan: ```time="2021-05-28T04:59:26Z" level=error msg="Caught exception to execute SQL query select count(*) from vae_alert, Error: caught http exception when querying Pinot: 404 Not Found\n"```
@dlavoie: Which endpoint are you calling on the broker end?
@mohamed.sultan: load balancer ip provided by GCP
@dlavoie: Yes, but what URL path?
@mohamed.sultan: @mags.carlin Can you post the URL path that you append in webservice code?
@mohamed.sultan: CC: @nadeemsadim
@mohamed.sultan: @sandeep ?
@mags.carlin: @dlavoie We are using Pinot client , to pass the host and port as broker
@mags.carlin: It will return as the connection with which we are executing the query directly
@mags.carlin: This is how we used for older instance of Pinot and it's working fine.
@dlavoie: Controller and broker have different endpoint for query
@mags.carlin: We are using broker ip ,right Sultan?
@dlavoie: Broker are queried using `/query/sql`.
@mohamed.sultan: yes @mags.carlin
@nadeemsadim: Not directly querying the pinot broker using curl but using golang pinot connector ie
@mohamed.sultan: seems like /query/sql this is not used
@nadeemsadim: doing it programmatically means querying programmatically by importing pinot-client library
@nadeemsadim: one old pinot cluster.. its working but the latest one its not working
@mohamed.sultan: Exactly!
@nadeemsadim: here.. I dont think we need to append /query/sql because here we just need to specify the host and port
@dlavoie: Have you tested the query with curl first?
@nadeemsadim: it is working with other broker ip of other old cluster but not working with broker ip of new cluster
@dlavoie: Test with curl first please, let’s eliminate the go client from the equation
@mayanks: Yes, this ^^
@mayanks:
@mags.carlin: Curl, needs to be used with /query and broker ip?what is the endpoint
@mayanks: Check the link ^^
@mags.carlin: Okay
@nadeemsadim: we tried with both the pinot cluster broker endpoints and the curl worked with both ! @dlavoie @mayanks
@nadeemsadim: seems issue with only golang connector for one of the pinot clusters
@nadeemsadim:
@patidar.rahul8392: Hello Everyone,I am trying to use hdfs as deepatorage for Pinot I followed all the steps which mentioned in pinot documents and everything worked fine.Then I have created pinot table and on pinot UI I am able to see the tables and segments. Now I am expecting that same segment should be available at the location also which we have mentioned as controller.data.dir (default.fs/hdfs dir) But here the directory is empty. Kindly suggest if I need to do anything else.
@patidar.rahul8392:
@patidar.rahul8392: @fx19880617 @sleepythread
@patidar.rahul8392: This is the config files details. I checked classpath_prefix value also it's pointing the correct jar locations. Kindly help.
@fx19880617: Have you pushed the segment to offline table or it’s a realtime table?
@patidar.rahul8392: It's a realtime table
@fx19880617: ok, so you can query the data now
@patidar.rahul8392: Yes I can query
@fx19880617: are there any segment in `ONLINE` status or they are all in `CONSUMING` status
@patidar.rahul8392: All in consuming
@fx19880617: ah
@fx19880617: then you need to wait for a while
@fx19880617: those segments are in memory at consuming status
@fx19880617: once reach the threshold, pinot will persist them and upload
@fx19880617: then those segments will be in `ONLINE` status
@patidar.rahul8392: Ok is there any time limit which we can set because since last had in our all are in consuming stats
@fx19880617: in table config, you can set flush threshold
@fx19880617:
@patidar.rahul8392:
@fx19880617: You can modify those: ``` "realtime.segment.flush.threshold.size": "0", "realtime.segment.flush.threshold.time": "24h", "realtime.segment.flush.desired.size": "50M",```
@patidar.rahul8392: This is the segment matadata I can see
@fx19880617: make `realtime.segment.flush.threshold.time` to `5m` then it will persist every 5mins
@patidar.rahul8392: Ohh ok got it..thanks a lot @fx19880617.let me update this.
@fx19880617: I think your data volume is not very high, so the consuming segment need to wait a lot of time
@fx19880617: also we don’t recommend to have many segments in a table
@fx19880617: so suggest to change back to the default config once you finished the test
@patidar.rahul8392: Oh ok I have only 4 segments
@patidar.rahul8392: Ok
@fx19880617: I think you mean 4 kafka partitions?
@patidar.rahul8392: Yes
@fx19880617: right, 5mins a segment means pinot will generate 12 * 24 * 4 = 1152 segments per day
@fx19880617: suggest to keep per table segment number < 20k
@patidar.rahul8392: Ohh ok so better in realtime will keep high threshold value.
@patidar.rahul8392: Ok @fx19880617
@fx19880617: right, try to keep segment size to be hundreds mb-ish
@patidar.rahul8392: Okay
@patidar.rahul8392: @fx19880617 Thanks a lot deepatorage worked now.
@patidar.rahul8392: @fx19880617 what is the process to load these hdfs segment files again in pinot .in case I want to recover the lost segments in Pinot.? Do I need to follow the same steps as batch load?
@nadeemsadim: suppose I created a realtime table with some kafka with ssl enabled configs .. now my kafka(Strimzi kafka) crashed or its ssl configs changed along with broker IP .. so can I point the same pinot table to the new kafka broker Ip and updated ssl configs without losing the old data .. Is backup & restore necessary while doing this or can directly update the table creation connfigs @fx19880617 @mayanks cc: @mohamed.sultan @shaileshjha061 @mohamedkashifuddin @hussain @pugal.selvan
@fx19880617: if Kafka topic partition offsets are persisted then you are fine
@nadeemsadim: u mean I dont need to delete the table . .just to update the table configs where we give broekr url and ssl configs
@nadeemsadim: and it will automatically start fetching from new kafka with same topic name and append to the exisitng data loaded into segments from old kafka
@mayanks: As Xiang mentioned, if the offsets are consistent in old vs new Kafka then you are good. For example, let's say Pinot consumed until offset xx in old kafka. Does the new Kafka have the same offset for those messages? If so, yes. If not, then you need to redo the table
@nadeemsadim: ok but as Xiang meantioned earlier that pinot stores offsets in zookeeper which comes with pinot helm installation .. so even if the kafka gets replaced .. the pinot setup zookeeper will be still there with the offsets . .so then we dont need to delete the table since nothing changed in the zookeeper of pinot
@nadeemsadim: for the same topic
@mayanks: Pinot does do that. What I am saying is if old and new Kafka are not consistent (eg message m1 and offset o1 in old kafka is not the same as message m2 in offset o1 in new Kafka), then Pinot won't know about it. It will consume from the offset it saved in ZK, but that offset may not be correct for new Kafka
@nadeemsadim: ok got it .. u mean the ordering of events in both kafka shuld exactly comply on a particular topic .. but since old kafka is lost.. and the new kafka will get only the latest data ..since old data is already consumed in pinot and the retention period of kafka was of only7 days.. so the same ordering of events cant be present on the new kafka .. it will have the latest data starting from the time it is created which is after old kafka crashed/its ssl config /broker url changed
@mayanks: I am also talking about offset. That number is not contiguous like 1, 2, 3, 4,... It is only monotonically increasing. So one can have 1, 3, 5... another one can have 2, 8, 9....
@nadeemsadim: these few highlighted configs in the table c reation script got changed
@nadeemsadim: yes mayank .. offsets can be anything .. its not monotonically increasing
@mayanks: Ok, let me ask you this. Are you doing this in production, or just playing around?
@nadeemsadim: got ur point .. so in some way .. kafka is a single point of failure .. if kafka fails .. we have to take backup and restore only in new realtime table .. since i dont think its possible to create a new akfka instance having the same offsets of the same messages
@mayanks: If you have setup kafka with enough fault tolerance, then it should not fail
@nadeemsadim: we are about to get into prod .. thus need to ensure that whatever we are planning is rock solid ..
@nadeemsadim: right mayank .. 100% agreed ..
@mayanks: :+1:
@shaileshjha061: @dlavoie @mayanks Can you please help me troubleshoot this issue?
#aggregators
@harrynet222: @harrynet222 has joined the channel
#community
@harrynet222: @harrynet222 has joined the channel
#getting-started
@harrynet222: @harrynet222 has joined the channel
#minion-improvements
@laxman: • All in production now. Conversion is at good pace. • After several rounds of tuning, 15 days of REALTIME data (total 90days) got converted to OFFLINE in last 24 hours. • By mid/end of next week all data should be migrated. :crossed_fingers: Early next I will try my Purger task implementation. Thanks @jackie.jxt @fx19880617 @npawar @g.kishore for all your help and support.
@laxman: I have few observations noted for this task and task framework in general. • Lack of monitoring and alerting. This task and task framework doesn’t expose any status/failure/progress metrics. Have to manually monitor this. In case if any reason, conversion gets stuck, there is no way user will know directly. Task status also says SUCCEEDED even when 1 out 5 tables failed. • Conversion task implementation heavily memory intensive. Disk+memory based implementation will be helpful. For most of our tables, I had to configure 1 hour as the bucket period due to this limitation. • Data gap larger than the bucket completely stalls the task.
@npawar: whats the size of the data per day in realtime table?
@laxman: Did you mean deflated disk size or number of records? • We have 8 tables. Conversion task enabled for 7 tables. • They have different incoming rate • Total data size 15TB all together including 2 replicas for 90 days. (90 days * 2 replicas = 2.7 TB occupied 5TB total capacity in each pinot server)
@laxman:
@laxman: logEventView and spanEventView are having 7 days of retention all other tables are 90 days of retention
@npawar: i’m trying to understand why window had to be made just 1 hour. For a specific table, do you know how much is the size of segments for 1 day on the deep store? i’m trying to calculate how much data the task is having to process at once, if the bucket is configured to 12h.
@npawar: and how many minions? and what configuration ?
@laxman: 4 minions pods, 6GB memory for each pod (max heap=3GB, direct memory=2GB)
@laxman: For service call view, we had to turn it to 1 hr. Otherwise conversion tasks are timing out and very slow. Stalling conversion of other tables as well.
@laxman: ```469M /var/pinot/server/data/index/serviceCallView_OFFLINE/serviceCallView_1614726000000_1614729599998_0 489M /var/pinot/server/data/index/serviceCallView_OFFLINE/serviceCallView_1614736800000_1614740399998_0 450M /var/pinot/server/data/index/serviceCallView_OFFLINE/serviceCallView_1614740400000_1614743999999_0 353M /var/pinot/server/data/index/serviceCallView_OFFLINE/serviceCallView_1614751200000_1614754799993_0 358M /var/pinot/server/data/index/serviceCallView_OFFLINE/serviceCallView_1614762000000_1614765599999_0```
@laxman: Latest segment samples for service call view :point_up_2:
@laxman: I made sure segments for all tables are in the recommended range (150MB to 500MB range)
@laxman: ``` backendEntityView: pinotRealtime.taskConfigs.RealtimeToOfflineSegmentsTask.bucketTimePeriod = "1h" domainEventView: pinotRealtime.taskConfigs.RealtimeToOfflineSegmentsTask.bucketTimePeriod = "12h" domainEventSpanView: pinotRealtime.taskConfigs.RealtimeToOfflineSegmentsTask.bucketTimePeriod = "3h" rawServiceView: pinotRealtime.taskConfigs.RealtimeToOfflineSegmentsTask.bucketTimePeriod = "3h" rawTraceView: pinotRealtime.taskConfigs.RealtimeToOfflineSegmentsTask.bucketTimePeriod = "3h" serviceCallView: pinotRealtime.taskConfigs.RealtimeToOfflineSegmentsTask.bucketTimePeriod = "1h" logEventView: pinotRealtime.taskConfigs.RealtimeToOfflineSegmentsTask.bucketTimePeriod = "1h"```
@laxman: ```518M /var/pinot/server/data/index/backendEntityView_OFFLINE/backendEntityView_1614816000000_1614819599960_0 356M /var/pinot/server/data/index/backendEntityView_OFFLINE/backendEntityView_1614826800000_1614830399951_0 524M /var/pinot/server/data/index/backendEntityView_OFFLINE/backendEntityView_1614837600000_1614841199992_0 404M /var/pinot/server/data/index/backendEntityView_OFFLINE/backendEntityView_1614855600000_1614859199995_0 367M /var/pinot/server/data/index/backendEntityView_OFFLINE/backendEntityView_1614866400000_1614869999989_0```
@laxman: Sorry for spamming :grimacing:
@npawar: oh so per hour itself, some tables are creating 500M segments in offline side
@laxman: Yes.
@npawar: and you have several such minion tasks running together, upto 7
@npawar: we need a better system to help determind minion provisioning and capacity :sweat_smile:
@npawar: and be able to control parallelism across tables
@laxman: Yes. After all this tuning, one cycle of all tables take nearly 20 minutes. So, I configured controller scheduler frequency to 20 minutes
@laxman: Conversion progress so far in last 24 hours ``` ➜ pinot-converter ./progress.sh [21/05/29| 2:51AM] Thu Mar 4 20:30:00 IST 2021 backendEntityView Mon Apr 5 05:30:00 IST 2021 domainEventView Tue Mar 9 14:30:00 IST 2021 domainEventSpanView Mon May 24 14:30:00 IST 2021 logEventView Tue Mar 16 02:30:00 IST 2021 rawServiceView Mon Mar 15 14:30:00 IST 2021 rawTraceView Wed Mar 3 16:30:00 IST 2021 serviceCallView```
@npawar: thanks for sharing all these things. will add it to our notes
@jackie.jxt: Great feedbacks! We will track these issues and fix them shortly
@mayanks: @mayanks has joined the channel
#pinot-docsrus
@mohamed.sultan: @mohamed.sultan has joined the channel
@mohamed.sultan: Hi team
@mayanks: @mohamed.sultan Check the channel description for links on how to add docs
@shaileshjha061: @shaileshjha061 has joined the channel
--------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@pinot.apache.org For additional commands, e-mail: dev-h...@pinot.apache.org
