Need steps for clickhouse distributed query implementation
I have installed clickhouse in 2 different machines A(96GB RAM , 32 core) & B (96GB RAM , 32 core) and i also configured replica using zookeeper.
I am able to ingest and fetch the data from both the machines and replication also working fine.
Now i would like to utilize 2 clickhouse servers for single query to improve the query performance.
I tried for distributed query but i am failed to configure hence could please provide the clear steps to implement distributed query
clickhouse
asked Dec 28 '18 at 11:02
user3383468user3383468
111
add a comment |
I have installed clickhouse in 2 different machines A(96GB RAM , 32 core) & B (96GB RAM , 32 core) and i also configured replica using zookeeper.
I am able to ingest and fetch the data from both the machines and replication also working fine.
Now i would like to utilize 2 clickhouse servers for single query to improve the query performance.
I tried for distributed query but i am failed to configure hence could please provide the clear steps to implement distributed query
clickhouse
asked Dec 28 '18 at 11:02
user3383468user3383468
111
add a comment |
I have installed clickhouse in 2 different machines A(96GB RAM , 32 core) & B (96GB RAM , 32 core) and i also configured replica using zookeeper.
I am able to ingest and fetch the data from both the machines and replication also working fine.
Now i would like to utilize 2 clickhouse servers for single query to improve the query performance.
I tried for distributed query but i am failed to configure hence could please provide the clear steps to implement distributed query
clickhouse
asked Dec 28 '18 at 11:02
user3383468user3383468
111
I have installed clickhouse in 2 different machines A(96GB RAM , 32 core) & B (96GB RAM , 32 core) and i also configured replica using zookeeper.
I am able to ingest and fetch the data from both the machines and replication also working fine.
Now i would like to utilize 2 clickhouse servers for single query to improve the query performance.
I tried for distributed query but i am failed to configure hence could please provide the clear steps to implement distributed query
clickhouse
clickhouse
asked Dec 28 '18 at 11:02
user3383468user3383468
111
asked Dec 28 '18 at 11:02
user3383468user3383468
111
asked Dec 28 '18 at 11:02
user3383468user3383468
111
asked Dec 28 '18 at 11:02
user3383468user3383468
111
asked Dec 28 '18 at 11:02
user3383468user3383468
111
111
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
If I right understood you, the distributed query is executed just on one server utilizing both its replicas.
To fix it need to change the strategy of replicas selection by the load balancer to in_order (it defined in user.xml):
<yandex>
<profiles>
<default>
<!-- .. -->
<load_balancing>in_order</load_balancing>
</default>
</profiles>
<!-- .. -->
</yandex>
Refs:
https://clickhouse.yandex/docs/en/operations/settings/settings/#load-balancing
https://clickhouse.yandex/docs/en/operations/table_engines/distributed/
answered Dec 28 '18 at 18:32
vladimir77vladimir77
2,0241624
I changed the load balance but still query utilizing single server resource
– user3383468
Dec 29 '18 at 6:01
SELECT name, value FROM system.settings WHERE name IN ('max_parallel_replicas', 'distributed_product_mode', 'load_balancing') ┌─name─────────────────────┬─value────┐ │ load_balancing │ in_order │ │ max_parallel_replicas │ 2 │ │ distributed_product_mode │ allow │ └──────────────────────────┴──────────┘
– user3383468
Dec 29 '18 at 6:01
SELECT * FROM clusters ┌─cluster─┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name────┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┐ │ logs │ 1 │ 1 │ 1 │ xx.xx.xx.142 │ xx.xx.xx.142 │ 9000 │ 1 │ default │ │ │ logs │ 1 │ 1 │ 2 │ xx.xx.xx.143 │ xx.xx.xx.143 │ 9000 │ 1 │ default │ │ └─────────┴───────────┴──────────────┴─────────────┴──────────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┘
– user3383468
Dec 29 '18 at 6:01
It looks like your cluster has just ONE shard and two replicas. The same query won't be parallelized between replicas, only between shards. You need to reconfigure cluster to have more than 1 shard. Please look at these articles: github.com/yandex/ClickHouse/issues/2161 altinity.com/blog/2018/5/10/…
– vladimir77
Dec 30 '18 at 10:02
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53957481%2fneed-steps-for-clickhouse-distributed-query-implementation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If I right understood you, the distributed query is executed just on one server utilizing both its replicas.
To fix it need to change the strategy of replicas selection by the load balancer to in_order (it defined in user.xml):
<yandex>
<profiles>
<default>
<!-- .. -->
<load_balancing>in_order</load_balancing>
</default>
</profiles>
<!-- .. -->
</yandex>
Refs:
https://clickhouse.yandex/docs/en/operations/settings/settings/#load-balancing
https://clickhouse.yandex/docs/en/operations/table_engines/distributed/
answered Dec 28 '18 at 18:32
vladimir77vladimir77
2,0241624
I changed the load balance but still query utilizing single server resource
– user3383468
Dec 29 '18 at 6:01
SELECT name, value FROM system.settings WHERE name IN ('max_parallel_replicas', 'distributed_product_mode', 'load_balancing') ┌─name─────────────────────┬─value────┐ │ load_balancing │ in_order │ │ max_parallel_replicas │ 2 │ │ distributed_product_mode │ allow │ └──────────────────────────┴──────────┘
– user3383468
Dec 29 '18 at 6:01
SELECT * FROM clusters ┌─cluster─┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name────┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┐ │ logs │ 1 │ 1 │ 1 │ xx.xx.xx.142 │ xx.xx.xx.142 │ 9000 │ 1 │ default │ │ │ logs │ 1 │ 1 │ 2 │ xx.xx.xx.143 │ xx.xx.xx.143 │ 9000 │ 1 │ default │ │ └─────────┴───────────┴──────────────┴─────────────┴──────────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┘
– user3383468
Dec 29 '18 at 6:01
It looks like your cluster has just ONE shard and two replicas. The same query won't be parallelized between replicas, only between shards. You need to reconfigure cluster to have more than 1 shard. Please look at these articles: github.com/yandex/ClickHouse/issues/2161 altinity.com/blog/2018/5/10/…
– vladimir77
Dec 30 '18 at 10:02
add a comment |
If I right understood you, the distributed query is executed just on one server utilizing both its replicas.
To fix it need to change the strategy of replicas selection by the load balancer to in_order (it defined in user.xml):
<yandex>
<profiles>
<default>
<!-- .. -->
<load_balancing>in_order</load_balancing>
</default>
</profiles>
<!-- .. -->
</yandex>
Refs:
https://clickhouse.yandex/docs/en/operations/settings/settings/#load-balancing
https://clickhouse.yandex/docs/en/operations/table_engines/distributed/
answered Dec 28 '18 at 18:32
vladimir77vladimir77
2,0241624
I changed the load balance but still query utilizing single server resource
– user3383468
Dec 29 '18 at 6:01
SELECT name, value FROM system.settings WHERE name IN ('max_parallel_replicas', 'distributed_product_mode', 'load_balancing') ┌─name─────────────────────┬─value────┐ │ load_balancing │ in_order │ │ max_parallel_replicas │ 2 │ │ distributed_product_mode │ allow │ └──────────────────────────┴──────────┘
– user3383468
Dec 29 '18 at 6:01
SELECT * FROM clusters ┌─cluster─┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name────┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┐ │ logs │ 1 │ 1 │ 1 │ xx.xx.xx.142 │ xx.xx.xx.142 │ 9000 │ 1 │ default │ │ │ logs │ 1 │ 1 │ 2 │ xx.xx.xx.143 │ xx.xx.xx.143 │ 9000 │ 1 │ default │ │ └─────────┴───────────┴──────────────┴─────────────┴──────────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┘
– user3383468
Dec 29 '18 at 6:01
It looks like your cluster has just ONE shard and two replicas. The same query won't be parallelized between replicas, only between shards. You need to reconfigure cluster to have more than 1 shard. Please look at these articles: github.com/yandex/ClickHouse/issues/2161 altinity.com/blog/2018/5/10/…
– vladimir77
Dec 30 '18 at 10:02
add a comment |
If I right understood you, the distributed query is executed just on one server utilizing both its replicas.
To fix it need to change the strategy of replicas selection by the load balancer to in_order (it defined in user.xml):
<yandex>
<profiles>
<default>
<!-- .. -->
<load_balancing>in_order</load_balancing>
</default>
</profiles>
<!-- .. -->
</yandex>
Refs:
https://clickhouse.yandex/docs/en/operations/settings/settings/#load-balancing
https://clickhouse.yandex/docs/en/operations/table_engines/distributed/
answered Dec 28 '18 at 18:32
vladimir77vladimir77
2,0241624
If I right understood you, the distributed query is executed just on one server utilizing both its replicas.
To fix it need to change the strategy of replicas selection by the load balancer to in_order (it defined in user.xml):
<yandex>
<profiles>
<default>
<!-- .. -->
<load_balancing>in_order</load_balancing>
</default>
</profiles>
<!-- .. -->
</yandex>
Refs:
https://clickhouse.yandex/docs/en/operations/settings/settings/#load-balancing
https://clickhouse.yandex/docs/en/operations/table_engines/distributed/
answered Dec 28 '18 at 18:32
vladimir77vladimir77
2,0241624
answered Dec 28 '18 at 18:32
vladimir77vladimir77
2,0241624
answered Dec 28 '18 at 18:32
vladimir77vladimir77
2,0241624
answered Dec 28 '18 at 18:32
vladimir77vladimir77
2,0241624
2,0241624
I changed the load balance but still query utilizing single server resource
– user3383468
Dec 29 '18 at 6:01
SELECT name, value FROM system.settings WHERE name IN ('max_parallel_replicas', 'distributed_product_mode', 'load_balancing') ┌─name─────────────────────┬─value────┐ │ load_balancing │ in_order │ │ max_parallel_replicas │ 2 │ │ distributed_product_mode │ allow │ └──────────────────────────┴──────────┘
– user3383468
Dec 29 '18 at 6:01
SELECT * FROM clusters ┌─cluster─┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name────┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┐ │ logs │ 1 │ 1 │ 1 │ xx.xx.xx.142 │ xx.xx.xx.142 │ 9000 │ 1 │ default │ │ │ logs │ 1 │ 1 │ 2 │ xx.xx.xx.143 │ xx.xx.xx.143 │ 9000 │ 1 │ default │ │ └─────────┴───────────┴──────────────┴─────────────┴──────────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┘
– user3383468
Dec 29 '18 at 6:01
It looks like your cluster has just ONE shard and two replicas. The same query won't be parallelized between replicas, only between shards. You need to reconfigure cluster to have more than 1 shard. Please look at these articles: github.com/yandex/ClickHouse/issues/2161 altinity.com/blog/2018/5/10/…
– vladimir77
Dec 30 '18 at 10:02
add a comment |
I changed the load balance but still query utilizing single server resource
– user3383468
Dec 29 '18 at 6:01
SELECT name, value FROM system.settings WHERE name IN ('max_parallel_replicas', 'distributed_product_mode', 'load_balancing') ┌─name─────────────────────┬─value────┐ │ load_balancing │ in_order │ │ max_parallel_replicas │ 2 │ │ distributed_product_mode │ allow │ └──────────────────────────┴──────────┘
– user3383468
Dec 29 '18 at 6:01
SELECT * FROM clusters ┌─cluster─┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name────┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┐ │ logs │ 1 │ 1 │ 1 │ xx.xx.xx.142 │ xx.xx.xx.142 │ 9000 │ 1 │ default │ │ │ logs │ 1 │ 1 │ 2 │ xx.xx.xx.143 │ xx.xx.xx.143 │ 9000 │ 1 │ default │ │ └─────────┴───────────┴──────────────┴─────────────┴──────────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┘
– user3383468
Dec 29 '18 at 6:01
It looks like your cluster has just ONE shard and two replicas. The same query won't be parallelized between replicas, only between shards. You need to reconfigure cluster to have more than 1 shard. Please look at these articles: github.com/yandex/ClickHouse/issues/2161 altinity.com/blog/2018/5/10/…
– vladimir77
Dec 30 '18 at 10:02
I changed the load balance but still query utilizing single server resource
– user3383468
Dec 29 '18 at 6:01
I changed the load balance but still query utilizing single server resource
– user3383468
Dec 29 '18 at 6:01
SELECT name, value FROM system.settings WHERE name IN ('max_parallel_replicas', 'distributed_product_mode', 'load_balancing') ┌─name─────────────────────┬─value────┐ │ load_balancing │ in_order │ │ max_parallel_replicas │ 2 │ │ distributed_product_mode │ allow │ └──────────────────────────┴──────────┘
– user3383468
Dec 29 '18 at 6:01
SELECT name, value FROM system.settings WHERE name IN ('max_parallel_replicas', 'distributed_product_mode', 'load_balancing') ┌─name─────────────────────┬─value────┐ │ load_balancing │ in_order │ │ max_parallel_replicas │ 2 │ │ distributed_product_mode │ allow │ └──────────────────────────┴──────────┘
– user3383468
Dec 29 '18 at 6:01
SELECT * FROM clusters ┌─cluster─┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name────┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┐ │ logs │ 1 │ 1 │ 1 │ xx.xx.xx.142 │ xx.xx.xx.142 │ 9000 │ 1 │ default │ │ │ logs │ 1 │ 1 │ 2 │ xx.xx.xx.143 │ xx.xx.xx.143 │ 9000 │ 1 │ default │ │ └─────────┴───────────┴──────────────┴─────────────┴──────────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┘
– user3383468
Dec 29 '18 at 6:01
SELECT * FROM clusters ┌─cluster─┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name────┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┐ │ logs │ 1 │ 1 │ 1 │ xx.xx.xx.142 │ xx.xx.xx.142 │ 9000 │ 1 │ default │ │ │ logs │ 1 │ 1 │ 2 │ xx.xx.xx.143 │ xx.xx.xx.143 │ 9000 │ 1 │ default │ │ └─────────┴───────────┴──────────────┴─────────────┴──────────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┘
– user3383468
Dec 29 '18 at 6:01
It looks like your cluster has just ONE shard and two replicas. The same query won't be parallelized between replicas, only between shards. You need to reconfigure cluster to have more than 1 shard. Please look at these articles: github.com/yandex/ClickHouse/issues/2161 altinity.com/blog/2018/5/10/…
– vladimir77
Dec 30 '18 at 10:02
It looks like your cluster has just ONE shard and two replicas. The same query won't be parallelized between replicas, only between shards. You need to reconfigure cluster to have more than 1 shard. Please look at these articles: github.com/yandex/ClickHouse/issues/2161 altinity.com/blog/2018/5/10/…
– vladimir77
Dec 30 '18 at 10:02
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53957481%2fneed-steps-for-clickhouse-distributed-query-implementation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
