https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# general
s
This message was deleted.
b
Not sure why it's causing problems, but the coordinator manages all the segment info, so it needs to get this data and share it with all other nodes.
s
@Ben Krug do you think, splitting the overlord and coordinator would help in un interrupting ingestion ?
b
It's possible, worth a try. Btw, how many rows in druid_segments, druid_pendingSegments, druid_tasks (in the metadata DB)? I wonder whether metadata queries are slow.
k
s
@Ben Krug here it is
b
That's a lot of segments, when you get up towards 1M you can see performance problems. If there are a lot of unused (used=0), you can turn on automated metadata management, if you haven't already. Or compaction, if that's possible. Or, I just heard about

this talk▾

, but haven't watched it yet, it should be good.
s
@Ben Krug thanks for sharing this, this would definitely help in the longer term. But as we are already in production its takes lot of time to get the latest version approved and push the latest version available in Prod.
Does any one know who fires this query (is it Overlord or Coordinator) ? and the reason for these queries ? each query is taking almost 60 seconds and returning empty results which is blocking overall ingestion and made the platform unstable (no response in UI) FYI: we temporarily stopped the ingestion for this specific data source and these queries are stopped firing to mysql and whole platform cleared the lag for all other datasources.
a
You actually may need to tune your mysql database
few recommendations since i am also running close to 3.5M segments on our supercluster id recommend applying the changes mentioned here to your instance as well to improve SELECT query performance
Id also recommend for a cluster of your size to federate the coordinator/overlord
The major ones id recommend for tuning your DB, 1. Modifying the transaction isolation level from default REPEATABLE_READ to READ_COMMITED 2. the innodb_buffer_pool_size to around 60-70% of available memory to improve index/table caching compared to the default 3. (Measure your baseline first before proceeding with this) - Disable query caching on mysql (deprecated as of 5.7) https://www.percona.com/blog/is-your-query-cache-really-disabled/#:~:text=Based%20on%20the%20code%20and,to%200%20and%20restart%20MySQL.
Providing me the version of Druid and Mysql you are on will also help me in understanding what tuning options are available to you
"While checking the logs its thowing an error with mysql and while checking the process that are executed in mysql we see the below queries triggered by coordinators." Whats the error its throwing?
s
Sure, ill checkout the database performance tuning options. we are using druid v25 and mysql 5.7.29. Below is the error throwing in the co-ordinator sometimes and all the ingestion tasks are halted.
Copy code
java.util.concurrent.TimeoutException: Timeout waiting for task.
at com.google.common.util.concurrent.AbstractFuture$Sync.get(AbstractFuture.java:269) ~[guava-16.0.1.jar:?]
at com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:96) ~[guava-16.0.1.jar:?]
at org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor.coalesceAndAwait(SeekableStreamSupervisor.java:4126) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor.updateCurrentOffsets(SeekableStreamSupervisor.java:3771) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor.updateCurrentAndLatestOffsets(SeekableStreamSupervisor.java:3738) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) ~[?:?]
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) ~[?:?]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
2023-08-30T20:43:35,587 WARN [IndexTaskClient-data5g-1] org.apache.druid.java.util.common.RetryUtils - Retrying (1 of 9) in 1,196ms.
org.skife.jdbi.v2.exceptions.UnableToObtainConnectionException: java.sql.SQLException: Cannot get a connection, general error
at org.skife.jdbi.v2.DBI.open(DBI.java:230) ~[jdbi-2.63.1.jar:2.63.1]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:279) ~[jdbi-2.63.1.jar:2.63.1]
at org.apache.druid.metadata.SQLMetadataConnector.lambda$retryWithHandle$0(SQLMetadataConnector.java:142) ~[druid-server-25.0.0.jar:25.0.0]
at org.apache.druid.java.util.common.RetryUtils.retry(RetryUtils.java:129) ~[druid-core-25.0.0.jar:25.0.0]
at org.apache.druid.java.util.common.RetryUtils.retry(RetryUtils.java:81) ~[druid-core-25.0.0.jar:25.0.0]
at org.apache.druid.java.util.common.RetryUtils.retry(RetryUtils.java:163) ~[druid-core-25.0.0.jar:25.0.0]
at org.apache.druid.java.util.common.RetryUtils.retry(RetryUtils.java:153) ~[druid-core-25.0.0.jar:25.0.0]
at org.apache.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:142) ~[druid-server-25.0.0.jar:25.0.0]
at org.apache.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:152) ~[druid-server-25.0.0.jar:25.0.0]
at org.apache.druid.metadata.SQLMetadataStorageActionHandler.getStatus(SQLMetadataStorageActionHandler.java:257) ~[druid-server-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.overlord.MetadataTaskStorage.getStatus(MetadataTaskStorage.java:191) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor$2.getTaskStatus(SeekableStreamSupervisor.java:862) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.common.IndexTaskClient.submitRequest(IndexTaskClient.java:322) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.common.IndexTaskClient.submitRequestWithEmptyContent(IndexTaskClient.java:219) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskClientSyncImpl.getCurrentOffsets(SeekableStreamIndexTaskClientSyncImpl.java:279) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskClientSyncImpl.lambda$getCurrentOffsetsAsync$6(SeekableStreamIndexTaskClientSyncImpl.java:407) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: java.sql.SQLException: Cannot get a connection, general error
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:131) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1413) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.skife.jdbi.v2.DataSourceConnectionFactory.openConnection(DataSourceConnectionFactory.java:36) ~[jdbi-2.63.1.jar:2.63.1]
at org.skife.jdbi.v2.DBI.open(DBI.java:212) ~[jdbi-2.63.1.jar:2.63.1]
... 19 more
Caused by: java.lang.InterruptedException
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.reportInterruptAfterWait(AbstractQueuedSynchronizer.java:2056) ~[?:?]
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2090) ~[?:?]
at org.apache.commons.pool2.impl.LinkedBlockingDeque.takeFirst(LinkedBlockingDeque.java:524) ~[commons-pool2-2.2.jar:2.2]
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:438) ~[commons-pool2-2.2.jar:2.2]
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:361) ~[commons-pool2-2.2.jar:2.2]
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:119) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1413) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.skife.jdbi.v2.DataSourceConnectionFactory.openConnection(DataSourceConnectionFactory.java:36) ~[jdbi-2.63.1.jar:2.63.1]
at org.skife.jdbi.v2.DBI.open(DBI.java:212) ~[jdbi-2.63.1.jar:2.63.1]
... 19 more
2023-08-30T20:43:35,587 WARN [IndexTaskClient-data5g-2] org.apache.druid.java.util.common.RetryUtils - Retrying (1 of 9) in 1,240ms.
org.skife.jdbi.v2.exceptions.UnableToObtainConnectionException: java.sql.SQLException: Cannot get a connection, general error
at org.skife.jdbi.v2.DBI.open(DBI.java:230) ~[jdbi-2.63.1.jar:2.63.1]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:279) ~[jdbi-2.63.1.jar:2.63.1]
at org.apache.druid.metadata.SQLMetadataConnector.lambda$retryWithHandle$0(SQLMetadataConnector.java:142) ~[druid-server-25.0.0.jar:25.0.0]
at org.apache.druid.java.util.common.RetryUtils.retry(RetryUtils.java:129) ~[druid-core-25.0.0.jar:25.0.0]
at org.apache.druid.java.util.common.RetryUtils.retry(RetryUtils.java:81) ~[druid-core-25.0.0.jar:25.0.0]
at org.apache.druid.java.util.common.RetryUtils.retry(RetryUtils.java:163) ~[druid-core-25.0.0.jar:25.0.0]
at org.apache.druid.java.util.common.RetryUtils.retry(RetryUtils.java:153) ~[druid-core-25.0.0.jar:25.0.0]
at org.apache.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:142) ~[druid-server-25.0.0.jar:25.0.0]
at org.apache.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:152) ~[druid-server-25.0.0.jar:25.0.0]
at org.apache.druid.metadata.SQLMetadataStorageActionHandler.getStatus(SQLMetadataStorageActionHandler.java:257) ~[druid-server-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.overlord.MetadataTaskStorage.getStatus(MetadataTaskStorage.java:191) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.seekablestream.supervisor.SeekableStreamSupervisor$2.getTaskStatus(SeekableStreamSupervisor.java:862) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.common.IndexTaskClient.submitRequest(IndexTaskClient.java:322) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.common.IndexTaskClient.submitRequestWithEmptyContent(IndexTaskClient.java:219) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskClientSyncImpl.getCurrentOffsets(SeekableStreamIndexTaskClientSyncImpl.java:279) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskClientSyncImpl.lambda$getCurrentOffsetsAsync$6(SeekableStreamIndexTaskClientSyncImpl.java:407) ~[druid-indexing-service-25.0.0.jar:25.0.0]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: java.sql.SQLException: Cannot get a connection, general error
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:131) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1413) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.skife.jdbi.v2.DataSourceConnectionFactory.openConnection(DataSourceConnectionFactory.java:36) ~[jdbi-2.63.1.jar:2.63.1]
at org.skife.jdbi.v2.DBI.open(DBI.java:212) ~[jdbi-2.63.1.jar:2.63.1]
... 19 more
Caused by: java.lang.InterruptedException
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.reportInterruptAfterWait(AbstractQueuedSynchronizer.java:2056) ~[?:?]
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2090) ~[?:?]
at org.apache.commons.pool2.impl.LinkedBlockingDeque.takeFirst(LinkedBlockingDeque.java:524) ~[commons-pool2-2.2.jar:2.2]
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:438) ~[commons-pool2-2.2.jar:2.2]
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:361) ~[commons-pool2-2.2.jar:2.2]
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:119) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1413) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.skife.jdbi.v2.DataSourceConnectionFactory.openConnection(DataSourceConnectionFactory.java:36) ~[jdbi-2.63.1.jar:2.63.1]
at org.skife.jdbi.v2.DBI.open(DBI.java:212) ~[jdbi-2.63.1.jar:2.63.1]
... 19 more
@Arun C ^
a
Do you have a connection limit configured on ur db?
s
Not at the mysql level. It should be default. we have druid global config set to 100.
Copy code
druid.global.http.numConnections=100
a
The above is the http connections not db connections
I don't think your mysql db is configured correctly
b
+1 read committed and the other suggestions might help. I'd add a few suggestions too. Here's an edit of some notes I wrote myself earlier...
1. AUTOCOMPACTION Often a first step is to enable autocompaction, to reduce segment numbers. Having many hundreds of thousands of segments can make metadata queries slow. 2. METADATA CLEANUP Sometimes this doesn't help, and the metadata tables need to be cleaned up somehow. Check whether automated cleaning of metadata has been set up or needs to be configured to be more aggressive. Automated cleaning of metadata has been available for a while now. It tells the coordinator to use kill tasks appropriately to clean up metadata (and segments, if called for). (Note: in more recent versions, automated cleanup is on by default.) In some cases, there are hundreds of thousands of old segments, and thousands added each day, but cleanup is configured to clean about one thousand a day. If so, settings like druid.coordinator.kill.maxSegments need to be set more aggressively. 3. TABLE INDEXES One possibility is that the indexes might not be up-to-date. If an older version was installed and upgraded, it won't have the latest indexing scheme. Upgrading druid doesn't currently change any indexes, although newer versions install with better indexing. Compare indexes to the indexes in the latest version, and change them if needed. Often the culprits are druid.segments and druid.pendingSegments. The latest code that creates the tables and generates the tables for MySQL is here. (Look for createSegmentTable and createPendingSegmentsTable. There are CREATE INDEX statements (%1$s is the table name) and a UNIQUE constraint, which also creates an index. In an existing installation, you can use 
SHOW CREATE TABLE tablename;
to see the current scheme, and compare.) 4. ANALYZE/OPTIMIZE TABLE If the data is cleaned up and the indexes are up-to-date, but queries are still slow (eg, taking seconds to run), then optimizing the table might help. In MySQL, first try, eg, 
ANALYZE TABLE druid.segments
. This updates cardinality statistics for the indexes, which might help if there have been a lot of updates or deletes over time and cardinality stats are out of date. ANALYZE is a very quick operation. In some cases, ANALYZE doesn't help, but OPTIMIZE does. OPTIMIZE takes longer. (In one case I saw, about 20 minutes, but that's unusual.) It's an "online" operation, meaning it won't interrupt or block other operations. You'll see a message saying that OPTIMIZE isn't supported for InnoDB tables, and it's using RECREATE+ANALYZE instead. That's expected and OK. OPTIMIZE (or, in this case RECREATE+ANALYZE) makes a new copy of the table, copies data over, analyzes, and switches to the new table and drops the old one. Sometimes this dramatically improves performance. (For whatever reason - usually people say it's defragmentation, although some people argue with that.)
b
Hi @sriramdas sivasai The suggestions shared by ben looks very well explained, The problem here is, you have a lot of segments for the specific datasource and coordinator will query to get the list of segments are their details before loading/balancing them against each datanode. 1. Check the druid_segments/pending_segments and clear the unused segments from metadata. 2. enable auto compaction or run manual comopaction to reduce the number of segments and inturn the query time should be optimized.
s
Thanks everyone for all your suggestions. It really help to understand how we can solve the problem. the root cause i observed with my data was caused due to scrap/old data coming of 2020, and queries were being fired on that date (so these queries were slow of 60-90 secs) . Based on your recommendations I have spent good amount time to clean up unused segments and reduced the segments to 1.6M from 3.6M. and enabled/triggered the manual compaction, as we have thousands to million of segments, its taking days to compact and when we enable the compaction, the ingestion is getting slowed down. even now the segment queries that are executed on database is taking around 10 sec causing ingestion slowed down and making coordinator unhealthy( We have druid environment with 3 Broker, 2 Coordinator/overload, 12 historicals, 230 middlemanagers available in our deployment (even after increasing to 250, its still same) do you recommend something to keep ingestion faster ? any best way to perform compaction ? ( CC: @Bibek Sahoo @Arun C @Ben Krug
b
Thank you for the update, For compaction by default 10% of your available slots will be used for auto compaction, You may run manual compaction to compact the segments faster, which might help in queries. Regarding ingestion making faster, you need to check where is the slowness, that might need some analysis on logs and tuning on SPEC like running more parallel task(if slots available) or tuning configs to make quick handoff. Manual Compaction: https://druid.apache.org/docs/latest/data-management/compaction#setting-up-manual-compaction
Configuring LOAD rule to reject old data during ingestion, For late coming datas, more segments will get created and ingestion can be slowed down. https://druid.apache.org/docs/latest/operations/rule-configuration#load-rules
2 Views
Open in Slack
PreviousNext
Powered by