Skip to content

2.27.0.0-b61

2.27.0.0-b61: [#20939] YSQL: Optimize GUC support for Connection Manager
@rahulb-yb rahulb-yb tagged this 05 May 22:25 
Summary:
In brief, this patch optimises the support for GUC variables in Connection Manager by reducing the need for the deploy and reset phases when handling client workload queries.

More details:
- Change PG15 ParameterStatus packet handling to allow Connection Manager to always receive ParameterStatus information even if there is no state change observed on the server (this does not affect the current behaviour when connections are not made via Connection Manager)
- Shift reset phase (present in reset.c, code flow after logical connection detaches) to occur right before deploy phase. This allows us to only have a (modified) deploy phase if there is a difference between server and client cached states.
  - This particular feature is guarded by a new gflag - `ysql_conn_mgr_optimized_session_parameters` (true by default). Making it false would revert to the isolated reset and deploy phase, which would always occur even if the logical connection keeps attaching to the same physical connection
- Made some changes around auth backend startup parameter handling to allow server and client states to match from the beginning - this will allow connections to skip the deploy phase if no GUC variables are used at all in the workload.
- Added some unit testing

**Perf observations**
In a pool where all connections utilise the same set of session parameters, the average query execution latency improves with this diff:
Table schema: CREATE TABLE test_table (id INT PRIMARY KEY, value VARCHAR(100)), total of 100 rows populated.
Query used: SELECT value FROM test_table WHERE id = i
**Note**: This query was explicitly handled using the simple query protocol, forcing recreation of cached plan for each execution of the statement for fair comparison across multiple iterations

Total iterations: 5000 per logical connection, all logical connections executing in parallel
| Number of logical connections | Connection Manager enabled, no optimisation | Connection Manager disabled | Connection Manager enabled, optimisation enabled |
| 1 | 2.5634 ms | 1.9285 ms | 2.0537 ms |
| 10 | 10.6648 ms | 6.6640 ms | 7.3293 ms |
| 25 | 25.1909 ms | 14.6216 ms | 17.1418 ms |

Query execution latency is observed to be much more comparable to that of connection manager being disabled with this diff

Jira: DB-9918

Test Plan:
We have existing tests for session parameter support testing with Connection Manager (TestSessionParameters.java)
Verify that these work and ensure detective run is clean

Reviewers: skumar, mkumar, devansh.saxena, stiwary, #db-approvers

Reviewed By: skumar, devansh.saxena, stiwary

Subscribers: svc_phabricator, yql

Differential Revision: https://phorge.dev.yugabyte.com/D41652
Assets 2
Loading