consul-template with large number of keys in KV

[av...@wix.com]

Hi All, 

    I am a consul-template newby. 

    I have a plan to use consul KV to save all our mysql clusters architecture , which will be dynamically updated upon changes in orchestrator (https://github.com/github/orchestrator) 

Now, we have more the 200 mysql servers and I need to keep track of the changes in ay of those servers. 

Now the consul-template is planed to be used in order to create an sql command which will be run into proxysql (https://github.com/sysown/proxysql)

Below is the template I cam up with . 

{{ $clusters := tree "DB/MYSQL/CLUSTERS" | byKey }} {{ range $cluster, $pairs := $clusters }}

#############################

######{{$cluster}}###########

############################

{{ with $cluster_id := key (printf "DB/MYSQL/CLUSTERS/%s/CLUSTER_ID" $cluster)  | parseInt }} {{ $dcs := tree ( printf "DB/MYSQL/CLUSTERS/%s/DCS/" $cluster)| byKey }}

{{ range $dc, $pairs  := $dcs }}

## {{ $dc }} ##

{{ $slaves:= tree (printf "DB/MYSQL/CLUSTERS/%s/DCS/%s/SLAVES/" $cluster $dc ) | byKey}}

{{ range $slave, $pairs_slaves := $slaves }}{{ $slave := (print $slave ) | toLower }}

REPLACE into mysql_servers (hostgroup_id,hostname) values ({{$cluster_id}}, '{{$slave}}'); {{end}}

{{end}}{{ $cluster_lower := (print $cluster ) | toLower }}

REPLACE into mysql_replication_hostgroups values({{$cluster_id}},{{ add $cluster_id 1000 }},{{(printf "'mysql_%s'" $cluster_lower)}});{{end}}{{end}}

## LOAD CHANGES TO MEM AND TO DISK ##

load mysql servers to runtime;

save mysql servers to disk;

The problem, obviously , is that I am watching too many dependencies (at least one per mysql server) and getting  :

2017/07/10 19:20:26.043522 [WARN] (runner) watching 216 dependencies - watching this many dependencies could DDoS your consul cluster

The problem is getting even worse as this needs to be run on 12 proxysql servers so that makes 2592 dependencies.

From reading I came up with some possibilities:

1. Instead of saving everything as KV in consul, each cluster will be saved as one JSON string. 
2. Store the whole mysql architecture as on JSON string (not one per cluster) 
3. I've read about the  explode command, so maybe I can just read the whole cluster topology and then parse it in the template, not sure it's a good option

I would appreciate any recommendation regarding this .

Thanks

[Armon Dadgar]

Hey,

There are two big improvements you can make to this. Firstly, you should re-use the data you’ve already fetched.

For example, the initial call to `tree “DB/MYSQL/CLUSTERS” is fetching all the keys under that prefix. The other lookups to get "DB/MYSQL/CLUSTERS/%s/CLUSTER_ID” and "DB/MYSQL/CLUSTERS/%s/DCS/“ etc are all subsets of that same tree. You should instead pull the data out of the $clusters variable to avoid re-fetching a different sub-tree.

The second thing is to use the de-duplicaiton mode (https://github.com/hashicorp/consul-template#de-duplication-mode). This way the very expensive template rendering is only done by one instance, and the work of rendering is “de-duplicated” among each of the proxysql machines.

Hope that helps!

Best Regards,

Armon Dadgar

--
This mailing list is governed under the HashiCorp Community Guidelines - https://www.hashicorp.com/community-guidelines.html. Behavior in violation of those guidelines may result in your removal from this mailing list.
 
GitHub Issues: https://github.com/hashicorp/consul/issues
IRC: #consul on Freenode
---
You received this message because you are subscribed to the Google Groups "Consul" group.
To unsubscribe from this group and stop receiving emails from it, send an email to consul-tool...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/consul-tool/8f0bf9a7-eae2-4b18-834e-35ec87050ee5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[av...@wix.com]

Thanks alot for the advice, I managed to use only one tree command and using explode:

{{ $clusters := tree "DB/MYSQL/CLUSTERS" | explode }} {{ range $cluster, $cluster_pair := $clusters }}

#############################

######{{$cluster}}###########

############################

{{ with $cluster_id :=  (print $cluster_pair.CLUSTER_ID )   | parseInt }} {{ range $dc, $dc_pair := $cluster_pair.DCS}}

## {{ $dc }} ##

{{ range $slave, $slave_pair := $dc_pair.SLAVES }}{{ $slave := (print $slave ) | toLower }}

REPLACE into mysql_servers (hostgroup_id,hostname) values ({{$cluster_id}}, '{{$slave}}'); {{end}}

{{end}}{{ $cluster_lower := (print $cluster ) | toLower }}

## LOAD CHANGES TO MEM AND TO DISK ##

REPLACE into mysql_replication_hostgroups values({{$cluster_id}},{{ add $cluster_id 1000 }},{{(printf "'mysql_%s'" $cluster_lower)}});{{end}}{{end}}

load mysql servers to runtime;

save mysql servers to disk;

WDYT about this one ? 

[Armon Dadgar]

Hey,

I think you’ll be much better off now! This should reduce you from ~200 watchers to a single one!

Best Regards,

Armon Dadgar

To view this discussion on the web visit https://groups.google.com/d/msgid/consul-tool/8aa84a75-c561-431c-b649-17b6d08af7a5%40googlegroups.com.

[Andrei Burd]

Thanks for the explode examples!

Just decreased the number of watches from 244 to 1!