Hi all, can I made pinot as a mirror of postgre? b...
# troubleshooting
s
Hi all, can I made pinot as a mirror of postgre? but the thing is on postgre there's data updates is it really applicable to do it? my approach here is moving the data from postgre -> airbyte -> kafka -> pinot ,so there's possibility that the data is duplicated in pinot when there's an update in postgre.
x
you can use pinot upsert feature
s
but when using upsert feature it consume a lot of memory and when I tried to query the whole table(example 16 million row with 16 million primary key), the pinot server instance goes down and it restarts again
if I use metadataTTL then the upsert only remember in the range of time between specific range, but when I use metadataTTL = 0(which mean it will always remember the primary key) the server not responding
x
yes, you need to give more memory for upsert case. what’s your Primary key ? is it a string or can you make it an integer?
s
it's an integer, I use LONG type of data
x
ok, so it will be 128mb for PK only
how much memory are you giving for pinot server? You can configure xmx and xms to like 4 or 8g for the pinot server
s
i have a -Xms4G -Xmx32G for 1 server
my total memory was 64GB
x
then you can try
-Xms32G -Xmx32G
s
it gave me this when I tried to query the whole table with limit = 16000000
Error Code: 427
1 servers [server3_R] not responded
Error Code: 200
Copy code
QueryExecutionError:
Received error query execution result block: {250=ExecutionTimeoutError
ProcessingException(errorCode:250, message:ExecutionTimeoutError)
	at org.apache.pinot.common.exception.QueryException.clinit(QueryException.java:116)
	at org.apache.pinot.common.response.broker.BrokerResponseNative.clinit(BrokerResponseNative.java:61)
	at org.apache.pinot.core.query.reduce.BrokerReduceService.reduceOnDataTable(BrokerReduceService.java:73)
	at org.apache.pinot.broker.requesthandler.SingleConnectionBrokerRequestHandler.processBrokerRequest(SingleConnectionBrokerRequestHandler.java:149)}
org.apache.pinot.query.service.dispatch.QueryDispatcher.runReducer(QueryDispatcher.java:462)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submitAndReduce(QueryDispatcher.java:133)
org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:255)
org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleRequest(BaseBrokerRequestHandler.java:168)
x
increase the query timeout ?
seems that server is not respond within the default timeout
s
do I use this in the query: option(timeoutMs=20000)
x
yes
or just
Copy code
SET timeoutMs=20000;
Select xxxxx
s
it resulted in a
Error Code: 427
1 servers [server3_R] not responded
x
can you check any error in Server3 log for error?
s
Copy code
ERROR [BaseCombineOperator] [query-runner-on-41833-2-thread-92] Caught serious error while processing query: QueryContext{...}
java.lang.OutOfMemoryError: Java heap space

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler

ERROR [OpChainSchedulerService] [query-runner-on-41833-2-thread-91] (OpChain{1690742341000000008_0_2}): Completed erroneously null {200=QueryExecutionError:
java.lang.OutOfMemoryError: Java heap space}
x
if it’s oom, then likely due to the server memory issue
have you set
-Xms32G -Xmx32G
s
yes I did use `-Xms32G -Xmx32G`so I was using docker container for the pinot setup, I'm using
docker-compose.yml
. I edit the yml then restart the container, it should've apply the edited yml right?
x
should be , you can enter into the container and do
ps wuax
to check the jvm parameters
s
yes it shows this: /usr/lib/jvm/java-11-amazon-corretto/bin/java -Dplugins.dir=/opt/pinot/plugins -Xms32G -Xmx32G -XX:+UseG1GC -XX:MaxGCPauseMillis=200 -Xloggc:gc-se
x
can you send me the full server log if you see the java heap issue again as well sending me the query you want to run
s
I run this. and then the server instance is dead and gives this log:
Copy code
ERROR [BaseCombineOperator] [pqr-64] Timed out while polling results block, numBlocksMerged: 33 (query: QueryContext{_tableName='c_orderline_REALTIME', _subquery=null, _selectExpressions=[a_asset_id, ad_client_id, ad_org_id, ad_orgtrx_id, alasandiskonest, altquantity2, c_activity_id, c_bpartner_id, c_bpartner_location_id, c_campaign_id, c_charge_id, c_currency_id, c_order_id, c_orderline_id, c_orderline_uu, c_project_id, c_projectphase_id, c_projecttask_id, c_tax_id, c_uom_id, completerealisasi, created, createdby, createproduction, createshipment, datedelivered, dateinvoiced, dateordered, datepromised, description, discount, discountamt, discountamtest, discounttype, freightamt, gl_account_id, grade, isactive, isdescription, line, linenetamt, link_orderline_id, m_attributesetinstance_id, m_product_id, m_promotion_id, m_shipper_id, m_warehouse_id, pp_cost_collector_id, ppnincluded, priceactual, pricecost, priceentered, pricelimit, pricelist, processed, qtydelivered, qtyentered, qtyinvoiced, qtylostsales, qtyordered, qtyreserved, recordTimestamp, ref_orderline_id, rramt, rrstartdate, s_resourceassignment_id, second_lowest_price, updated, updatedby, user1_id, user2_id, vendorcategory, z_abw_est, z_abw_real, z_age, z_alasan_mundur, z_diskon_per_kg, z_grade_telur, z_harga_estimasi, z_harga_pricelist_est, z_harga_realisasi, z_hargapasar, z_hargapasarrealisasi, z_kondisi, z_lokasi, z_pricelist_estimasi, z_qty_ekor_est, z_qty_ekor_real, z_qty_kg_est, z_qty_kg_real, z_sapprice, z_savings, z_tbl_alasandiskon_id, z_umur_ayam, z_week_ayam], _distinct=false, _aliasList=[null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null], _filter=null, _groupByExpressions=null, _havingFilter=null, _orderByExpressions=null, _limit=16800000, _offset=0, _queryOptions={serverReturnFinalResult=true, timeoutMs=10000}, _expressionOverrideHints={}, _explain=NONE})

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-62"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-70"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-100"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "ParticipantHealthReportTimerTask"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-80"
Exception in thread "ad_org__0__1__20250530T0513Z" java.lang.OutOfMemoryError: Java heap space

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-59"
Exception in thread "ad_orginfo__0__1__20250530T0515Z" java.lang.OutOfMemoryError: Java heap space

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-83"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-82"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pool-26-thread-1"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-121"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "pqw-107"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "ad_orgtype__0__1__20250530T0415Z"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "c_orderline__0__61__20250602T2030Z"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "grizzly-nio-kernel(38) SelectorRunner"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "grizzly-nio-kernel(1) SelectorRunner"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "Start a Pinot [SERVER]-SendThread(zookeeper:2181)"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "metrics-meter-tick-thread-2"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "grizzly-http-server-40"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "metrics-meter-tick-thread-1"
x
got it. this is a full table scan. So you need all the data to be streamed back to client?
How large is this table?
I suppose each row is large?
s
actually I'm just testing if it can scans the whole table or no, it consist of 95 columns with 16 million rows of data, while c_order has 146 columns with 14 million rows of data
x
For large data access, this is not the right way 🙂 Even if the db could give you the whole data, your web browser will crash as well. For whole table scan, you can try queries like
select * from table order by xxx, limit 1
s
I see, so the whole table can be in upsert as long as we have the right setup for the heap memory if not it will cause OOM.
x
yes, the OSS version of pinot has on-heap mode only which can support 100gb level. Once you are reaching TB level, you could try out startree distribution which has on-disk version for upsert
s
how can I do the startree distribustion?
x
s
what would be the best setting if there are fix data and there are data that was updated for the past weeks, should I still use upsert for it or I can use other approach?