This message was deleted.
# generals
Slackbot
12/24/2023, 11:20 AMThis message was deleted.
a
Amatya Avadhanula
12/24/2023, 12:51 PMHi, Which Druid version is this and what indexes exist currently?
b
Benjamin Hopp
12/24/2023, 6:55 PMIt looks like you have over 1M segments in the metadata database. If you enable coordinator auto kill it will remove unused segments as they are dropped. Possibly something to explore in addition to indexes.
a
Arun C
12/25/2023, 1:47 AMSend the Druid version + Mysql configuration file. I run fairly large druid clusters with about 4-5M segments each. We've had to do several adjustments to get as much performance as we can get out of our mysql instance. Though with fairly large clusters 4s might be normal given your hardware configuration.
1. Disable query cache https://www.percona.com/blog/is-your-query-cache-really-disabled/#:~:text=Based%20on%20the%20code%20and,to%200%20and%20restart%20MySQL.
2. Set transaction isolation level to READ_COMMITTED
3. set your innodb_buffer_pool_size to 80% of the memory available on the machine https://www.percona.com/blog/choosing-innodb_buffer_pool_size/
4. Enable --skip-name-resolve if you are connecting over direct IP
Arun C
12/25/2023, 1:59 AMDepending on the version of druid you are using and the indexes configured on the table there may be additional options, but would need to know the current druid version + indexes currently configured on your druid_segments table
s
Saurabh Pande
12/26/2023, 6:47 AM Copy code
Current Druid version: 0.19.0
MySQL version: mysql Ver 15.1 Distrib 10.2.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2a
Amatya Avadhanula
12/26/2023, 8:14 AM0.19.0 is pretty old. Would it be possible to upgrade?
Amatya Avadhanula
12/26/2023, 8:18 AMIf not, you could create the following indexes manually:
Copy code
1. CREATE INDEX idx_druid_segments_used ON druid_segments(used)
2. CREATE INDEX idx_druid_segments_datasource_used_end_start ON druid_segments(dataSource, used, end, start)s
Saurabh Pande
12/26/2023, 9:07 AMYes, we are looking forward to upgrade only but evaluating on bunch of stuffs as 0.19.0 has became pretty old so figuring out whether we’d need step by step upgrades or can we jump directly to latest.
Saurabh Pande
12/26/2023, 9:08 AMOn the indexes part, we also came with same type of indexes to create on druid_segments table but my only concern here is how much this would hamper ingestion performance? Because once indexes will be there, writing segments metadata to MySQL DB becomes little heavy operation as it also needs to update index with every write
a
Amatya Avadhanula
12/26/2023, 12:59 PMThese are indexes that Druid uses by default in the latest versions
s
Saurabh Pande
12/26/2023, 1:02 PMOhh, okay, thanks!
Saurabh Pande
12/26/2023, 1:09 PMGot it, I think then rather solving for this, we’d majorly focus on upgrading the druid only. Considering 0.19.0 has became pretty old, do you recommend we should check whether direct jump to latest one should be done rather than upgrading version by version? We are already going through the release notes of each version and haven’t yet found a significant breaking change till 27.0.0 version, may be there could be some miss from our end.
b
Benjamin Hopp
12/26/2023, 1:29 PMThere is no reason you can't make a direct jump. However, once you upgrade there are changes that prevent rolling back. Also please make note of the changes in behavior around JSON data types, NULL handling, and 3 value logic in recent versions as those can lead to changes in query results.
s
Saurabh Pande
12/27/2023, 10:13 AMSure @Benjamin Hopp, Do you have/know any migration doc as such performed by some team/company where they have provided a consolidated view over issues they faced while & after migration? It’d be great to go through it along with release notes as well.
b
Ben Krug
01/04/2024, 8:49 PMbtw, I'm not sure whether upgrading will add the indexes. In the past it didn't, but newer versions might. Upgrading seems like a good idea since 19 is pretty old. But adding the indexes is a good idea too.
👍 1