In an ideal environment, all clients connecting to the database will request a load balanced connection; as well as having load balancing enabled on the database. This quick tip shows how to see the request distribution across a cluster.
As of 7.0, Vertica has had native load balancing. This feature is enabled very simply through the
SET_LOAD_BALANCE_POLICY function. The client connection must also request a load balanced connection (see end of this post). However, it’s very common to see clients not request load balanced sessions which can adversely affect resource load across the cluster.
The following query will return the number of requests, as well as the percent of total requests per node:
ROUND((a.requests / b.total_requests) * 100, 2.0) AS percent
FROM (SELECT node_name,
COUNT(*) AS requests
GROUP BY node_name) a
CROSS JOIN (SELECT COUNT(*) AS total_requests
FROM v_monitor.query_requests) b
ORDER BY percent DESC;
This will return something like:
node_name | requests | percent
v_vmart_node0001 | 3799 | 86.89
v_vmart_node0002 | 458 | 10.48
v_vmart_node0003 | 115 | 2.63
Although this result comes from a sandbox environment, it clearly illustrates how the requests should not be distributed. If request labels are being used, it’s also easy to identify their distribution just by adding
request_label to the predicate.
Requesting a load balanced connection from a client:
- Enabling Native Connection Load Balancing in ODBC
- Enabling Native Connection Load Balancing in JDBC & JDBC Connection Properties
- Enabling Native Connection Load Balancing in ADO.NET & ADO.NET Connection Properties