The ysqlsh CLI is the shell for interacting with the YugabyteDB YSQL API.
-h (or --host) flag specifies the host name of the machine on which the server is running. Example:
[root@cloud-server-0 yb2]# ysqlsh -h 127.0.0.1 -c "SELECT inet_server_addr() \"IP of Connected Node\";"
IP of Connected Node
----------------------
127.0.0.1
(1 row)
Typically your YugabyteDB cluster will consist of more than one node. To distribute the load if you are planning to run many ysqlsh command in parallel, you’ll want to distribute the connections across the cluster.
While there isn’t a flag avialable which load balances the connection for ysqlsh, you can easily do this on your own.
Let’s take a look at a cluster which has 9 nodes.
[root@cloud-server-0 ~]# ysqlsh -h 127.0.0.1 -c "SELECT host FROM yb_servers() ORDER BY host;"
host
-----------
127.0.0.1
127.0.0.2
127.0.0.3
127.0.0.4
127.0.0.5
127.0.0.6
127.0.0.7
127.0.0.8
127.0.0.9
(9 rows)
To connect to a random node to distribute the connections, I can do something like this:
[root@cloud-server-0 yb2]# ysqlsh -h $(ysqlsh -h 127.0.0.1 -Atc "SELECT host FROM yb_servers() ORDER BY random() LIMIT 1;") -c "SELECT inet_server_addr() \"IP of Connected Node\";"
IP of Connected Node
----------------------
127.0.0.9
(1 row)
[root@cloud-server-0 yb2]# ysqlsh -h $(ysqlsh -h 127.0.0.1 -Atc "SELECT host FROM yb_servers() ORDER BY random() LIMIT 1;") -c "SELECT inet_server_addr() \"IP of Connected Node\";"
IP of Connected Node
----------------------
127.0.0.5
(1 row)
[root@cloud-server-0 yb2]# ysqlsh -h $(ysqlsh -h 127.0.0.1 -Atc "SELECT host FROM yb_servers() ORDER BY random() LIMIT 1;") -c "SELECT inet_server_addr() \"IP of Connected Node\";"
IP of Connected Node
----------------------
127.0.0.4
(1 row)
When using this method to randomly distribute alot of connections, know that the distribution will not be exactly even. That is, some nodes will receive more connection requests than others.
[root@cloud-server-0 yb2]# for i in {1..9000}; do ysqlsh -h $(ysqlsh -h 127.0.0.1 -Atc "SELECT host FROM yb_servers() ORDER BY random() LIMIT 1;") -Atc "SELECT inet_server_addr();" >> servers.txt; done;
[root@cloud-server-0 yb2]# sort servers.txt | uniq -c
996 127.0.0.1
985 127.0.0.2
985 127.0.0.3
1008 127.0.0.4
997 127.0.0.5
957 127.0.0.6
1026 127.0.0.7
1023 127.0.0.8
1023 127.0.0.9
If you are looking for a truely even distribution of connections, then a “round robin” approach is the way to go!
[root@cloud-server-0 yb2]# hosts=($(ysqlsh -h 127.0.0.1 -Atc 'SELECT host FROM yb_servers();')); for i in {1..9000}; do ysqlsh -h ${hosts[$(($i % ${#hosts[@]}))]} -Atc "SELECT inet_server_addr();" >> servers_round_robin.txt ; done;
[root@cloud-server-0 yb2]# sort servers_round_robin.txt | uniq -c
1000 127.0.0.1
1000 127.0.0.2
1000 127.0.0.3
1000 127.0.0.4
1000 127.0.0.5
1000 127.0.0.6
1000 127.0.0.7
1000 127.0.0.8
1000 127.0.0.9
Have Fun!
