{"action":"created","issue":{"url":"https://api.github.com/repos/druid-io/druid/issues/3297","repository_url":"https://api.github.com/repos/druid-io/druid","labels_url":"https://api.github.com/repos/druid-io/druid/issues/3297/labels{/name}","comments_url":"https://api.github.com/repos/druid-io/druid/issues/3297/comments","events_url":"https://api.github.com/repos/druid-io/druid/issues/3297/events","html_url":"https://github.com/druid-io/druid/issues/3297","id":167982645,"number":3297,"title":"On select performance (with multiple partitions)","user":{"login":"KenjiTakahashi","id":431488,"avatar_url":"https://avatars.githubusercontent.com/u/431488?v=3","gravatar_id":"","url":"https://api.github.com/users/KenjiTakahashi","html_url":"https://github.com/KenjiTakahashi","followers_url":"https://api.github.com/users/KenjiTakahashi/followers","following_url":"https://api.github.com/users/KenjiTakahashi/following{/other_user}","gists_url":"https://api.github.com/users/KenjiTakahashi/gists{/gist_id}","starred_url":"https://api.github.com/users/KenjiTakahashi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/KenjiTakahashi/subscriptions","organizations_url":"https://api.github.com/users/KenjiTakahashi/orgs","repos_url":"https://api.github.com/users/KenjiTakahashi/repos","events_url":"https://api.github.com/users/KenjiTakahashi/events{/privacy}","received_events_url":"https://api.github.com/users/KenjiTakahashi/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-07-27T23:48:14Z","updated_at":"2016-07-29T03:24:45Z","closed_at":null,"body":"We've been investigating Druid `select` query performance for some cases where we need to pull a large (hundreds of thousands) number of items from a pretty coarse data (more details can be found at https://groups.google.com/forum/#!topic/druid-user/o_Ex2ygmjAs).\r\n\r\nWe are ingesting data into DAY segments with 50 partitions each and we can't really reduce the number of partitions much. This will be important :-).\r\n\r\nIn some tests, we've noticed that filtered queries are significantly faster than unfiltered ones, even though they were constructed to both return exactly the same data.\r\nThis felt very odd, so we've decided to go down and do some profiling on Druid's historical server. We've used two different profilers, both yielding very similar results.\r\nThe profiles led, unsurprisingly, to the `select` code and the first suspicious thing was that the `lookupName` method at https://github.com/druid-io/druid/blob/druid-0.9.1.1/processing/src/main/java/io/druid/query/select/SelectQueryEngine.java#L135 was called... exactly 50 times the `threshold` set for the query.\r\nFurther profiling and debugging has proven that Druid is indeed retrieving `threshold` items from *each* of the 50 partitions, then merging them together and throwing away the rest. And \"the rest\" in our case was 98% of the retrieved data.\r\n\r\nAs I understand, it was done this way, because there seem to be no constraints as to how data in partitions within segment relate to each other. Theoretically, one could, e.g. put a whole day of some metrics into one partition, then put each hour of the rest into separate partitions, all within one DAY segment. Or maybe set partition numbers out of order. Might not make sense, but seems possible, so I guess it should \"work as expected\".\r\nI guess that different partitions might also reside on different historical instances?\r\n\r\nIn our case, we've been able to do a quick hack that works for some tests and that moved the query times down from >2 minutes to sub-second, so we see a lot to be gained here.\r\n\r\nWith all that, I gotta say that I do not have enough expretise in Druid (and on top, I've not programmed Java for years) to think of a good, generic solution to this. That's why I'm writing it down here, hoping for you to be able to point us into right direction.\r\n\r\nTests and profiles were first done on `0.9.1-rc4`. I have re-run the tests later on `0.9.1.1` and no significant changes were observed, so I assume that the conditions above have not changed.\r\n\r\nLet me know if I can provide any further information. Thanks in advance for any ideas on how to improve the situation."},"comment":{"url":"https://api.github.com/repos/druid-io/druid/issues/comments/236085617","html_url":"https://github.com/druid-io/druid/issues/3297#issuecomment-236085617","issue_url":"https://api.github.com/repos/druid-io/druid/issues/3297","id":236085617,"user":{"login":"navis","id":809395,"avatar_url":"https://avatars.githubusercontent.com/u/809395?v=3","gravatar_id":"","url":"https://api.github.com/users/navis","html_url":"https://github.com/navis","followers_url":"https://api.github.com/users/navis/followers","following_url":"https://api.github.com/users/navis/following{/other_user}","gists_url":"https://api.github.com/users/navis/gists{/gist_id}","starred_url":"https://api.github.com/users/navis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/navis/subscriptions","organizations_url":"https://api.github.com/users/navis/orgs","repos_url":"https://api.github.com/users/navis/repos","events_url":"https://api.github.com/users/navis/events{/privacy}","received_events_url":"https://api.github.com/users/navis/received_events","type":"User","site_admin":false},"created_at":"2016-07-29T03:24:45Z","updated_at":"2016-07-29T03:24:45Z","body":"It's exactly what we want to do with #3292 but not in-progress currently."}}
{"actor":{"display_login":"navis"}}