Reduce the number of DB requests made in ValidateServerCapabilities()

[rawlinp]

I'm submitting a ...

• improvement request (usability, performance, tech debt, etc.)

Traffic Control components affected ...

• Traffic Ops

Current behavior:

TO currently makes a DB request per server in the ValidateServerCapabilities() function:

trafficcontrol/traffic_ops/traffic_ops_golang/deliveryservice/servers/servers.go

Line 541 in b99eda5

sCaps, err = dbhelpers.GetServerCapabilitiesFromName(name, tx)

. When a delivery service is being assigned to a large number of servers, this slows down the total execution time unnecessarily.

New behavior:

Instead of making a DB request per server, the function should make a single DB request to get data for all the servers at once, then perform the validation. This will make it much faster to assign a delivery service to large numbers of servers at a time.

Minimal reproduction of the problem with instructions:

Assign a delivery service with required capabilities to a large number of servers with those capabilities at a time.

[RaviTezu]

@rawlinp I would like to take this up. Could you please assign this to me?

[RaviTezu]

@rawlinp Just want to check that I am going in the right direction before I start on this.

• I am thinking of modifying GetServerCapabilitiesFromName method to take a list of server names and then modify the query in way that it can fetch the data/capabilities for all the servers and return them for validation.
• Also, I would like to know where I can get more information on how to construct the query. Thanks.

[rawlinp]

@RaviTezu that sounds like a reasonable idea. This query should be a good example for you to modify for GetServerCapabilitiesFromName:

trafficcontrol/traffic_ops/traffic_ops_golang/deliveryservice/deliveryservices_required_capabilities.go

Lines 348 to 360 in b99eda5

	SELECT
	s.id,
	s.cdn_id,
	c.name,
	ARRAY_REMOVE(ARRAY_AGG(ssc.server_capability ORDER BY ssc.server_capability), NULL) AS capabilities
	FROM server s
	LEFT JOIN server_server_capability ssc ON ssc.server = s.id
	JOIN cachegroup c ON c.id = s.cachegroup
	JOIN topology_cachegroup tc ON tc.cachegroup = c.name
	WHERE
	s.cdn_id = (SELECT cdn_id FROM deliveryservice WHERE id = $1)
	AND tc.topology = $2
	GROUP BY s.id, s.cdn_id, c.name