[MDEV-31609] Send initial values of system variables in first OK packet Created: 2023-07-03  Updated: 2024-01-15

Status: Open
Project: MariaDB Server
Component/s: Protocol
Fix Version/s: 11.5

Type: New Feature Priority: Critical
Reporter: markus makela Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-15935 Connection Redirection Mechanism in M... Closed
relates to MXS-4635 Provide load balancing metadata to co... Closed

 Description   

Currently, all drivers that need to know the values of some system variables must obtain this information by doing a separate SELECT. This adds latency to the connection creation.

The OK packet that's sent in response to successful authentication could contain initial values of the system variables that are tracked by session_track_system_variables. With this, drivers that support system variable tracking could read these values on startup and avoid the cost of the extra query.

An extension to this would be to provide some information about the status of the server to the drivers. One example of this would be the Threads_connected status variable that could help drivers that have connection pools to spread the load better if one of the servers in a cluster ends up with more connections than the others.

 Comments   
Comment by Sergei Golubchik [ 2023-07-07 ]

it would mean a lot of information in the first OK packet that most clients don't need. is it ok? will it be noticeable?

Comment by markus makela [ 2023-07-07 ]

One suggestion was to add a matching connection attribute that the client would first send (e.g. _send_system_variables=<list of variable names>) that the server would understand and only send the ones that are requested. This would also allow the feature to be decoupled from session_track_system_variables.

Comment by markus makela [ 2023-07-07 ]

If we assume that the information tracked with session_track_system_variables is important enough to track while a connection is alive, it should also be of use when the connection is first created. With this assumption, we can look at how many bytes it takes to express this information.

Currently most connectors would perform a separate SELECT to retrieve the values. This is what it would optimally look like:

  b1 00 00 00 03 53 45 4c    45 43 54 20 40 40 61 75    .....SELECT @@au
 
  74 6f 63 6f 6d 6d 69 74    2c 40 40 63 68 61 72 61    tocommit,@@chara
 
  63 74 65 72 5f 73 65 74    5f 63 6c 69 65 6e 74 2c    cter_set_client,
 
  40 40 63 68 61 72 61 63    74 65 72 5f 73 65 74 5f    @@character_set_
 
  63 6f 6e 6e 65 63 74 69    6f 6e 2c 40 40 63 68 61    connection,@@cha
 
  72 61 63 74 65 72 5f 73    65 74 5f 72 65 73 75 6c    racter_set_resul
 
  74 73 2c 63 6f 6e 6e 65    63 74 69 6f 6e 5f 69 64    ts,connection_id
 
  28 29 2c 40 40 6d 61 78    5f 61 6c 6c 6f 77 65 64    (),@@max_allowed
 
  5f 70 61 63 6b 65 74 2c    40 40 73 79 73 74 65 6d    _packet,@@system
 
  5f 74 69 6d 65 5f 7a 6f    6e 65 2c 40 40 74 69 6d    _time_zone,@@tim
 
  65 5f 7a 6f 6e 65 2c 40    40 74 78 5f 69 73 6f 6c    e_zone,@@tx_isol
 
  61 74 69 6f 6e                                        ation           
#
 
T 127.0.0.1:4006 -> 127.0.0.1:40546 [AP] #2
 
  02 00 00 01 09 01 22 00    00 02 03 64 65 66 00 00    ......"....def..
 
  00 0c 40 40 61 75 74 6f    63 6f 6d 6d 69 74 00 0c    ..@@autocommit..
 
  3f 00 01 00 00 00 08 80    00 00 00 00 2c 00 00 03    ?...........,...
 
  03 64 65 66 00 00 00 16    40 40 63 68 61 72 61 63    .def....@@charac
 
  74 65 72 5f 73 65 74 5f    63 6c 69 65 6e 74 00 0c    ter_set_client..
 
  21 00 15 00 00 00 fd 00    00 27 00 00 30 00 00 04    !........'..0...
 
  03 64 65 66 00 00 00 1a    40 40 63 68 61 72 61 63    .def....@@charac
 
  74 65 72 5f 73 65 74 5f    63 6f 6e 6e 65 63 74 69    ter_set_connecti
 
  6f 6e 00 0c 21 00 15 00    00 00 fd 00 00 27 00 00    on..!........'..
 
  2d 00 00 05 03 64 65 66    00 00 00 17 40 40 63 68    -....def....@@ch
 
  61 72 61 63 74 65 72 5f    73 65 74 5f 72 65 73 75    aracter_set_resu
 
  6c 74 73 00 0c 21 00 15    00 00 00 fd 00 00 27 00    lts..!........'.
 
  00 25 00 00 06 03 64 65    66 00 00 00 0f 63 6f 6e    .%....def....con
 
  6e 65 63 74 69 6f 6e 5f    69 64 28 29 00 0c 3f 00    nection_id()..?.
 
  0a 00 00 00 03 a1 00 00    00 00 2a 00 00 07 03 64    ..........*....d
 
  65 66 00 00 00 14 40 40    6d 61 78 5f 61 6c 6c 6f    ef....@@max_allo
 
  77 65 64 5f 70 61 63 6b    65 74 00 0c 3f 00 15 00    wed_packet..?...
 
  00 00 08 a0 00 00 00 00    28 00 00 08 03 64 65 66    ........(....def
 
  00 00 00 12 40 40 73 79    73 74 65 6d 5f 74 69 6d    ....@@system_tim
 
  65 5f 7a 6f 6e 65 00 0c    21 00 09 00 00 00 fd 00    e_zone..!.......
 
  00 27 00 00 21 00 00 09    03 64 65 66 00 00 00 0b    .'..!....def....
 
  40 40 74 69 6d 65 5f 7a    6f 6e 65 00 0c 21 00 12    @@time_zone..!..
 
  00 00 00 fd 00 00 27 00    00 24 00 00 0a 03 64 65    ......'..$....de
 
  66 00 00 00 0e 40 40 74    78 5f 69 73 6f 6c 61 74    f....@@tx_isolat
 
  69 6f 6e 00 0c 21 00 2d    00 00 00 fd 00 00 27 00    ion..!.-......'.
 
  00 05 00 00 0b fe 00 00    02 00 46 00 00 0c 01 31    ..........F....1
 
  07 75 74 66 38 6d 62 33    07 75 74 66 38 6d 62 33    .utf8mb3.utf8mb3
 
  07 75 74 66 38 6d 62 33    05 33 34 34 37 31 0a 31    .utf8mb3.34471.1
 
  30 37 33 37 34 31 38 32    34 03 55 54 43 06 53 59    073741824.UTC.SY
 
  53 54 45 4d 0f 52 45 50    45 41 54 41 42 4c 45 2d    STEM.REPEATABLE-
 
  52 45 41 44 05 00 00 0d    fe 00 00 02 00             READ.........

If I counted it correctly, that's 688 bytes in total.

By sending the values in the first OK packet, the amount of data sent is reduced to 274 bytes. In addition to the reduction in data volume, it also avoids one extra roundtrip that would have to be done.

  0e 01 00 02 00 00 00 02    40 00 00 00 fc 03 01 00    ........@.......
 
  0e 0a 61 75 74 6f 63 6f    6d 6d 69 74 02 4f 4e 00    ..autocommit.ON.
 
  1d 14 63 68 61 72 61 63    74 65 72 5f 73 65 74 5f    ..character_set_
 
  63 6c 69 65 6e 74 07 75    74 66 38 6d 62 34 00 21    client.utf8mb4.!
 
  18 63 68 61 72 61 63 74    65 72 5f 73 65 74 5f 63    .character_set_c
 
  6f 6e 6e 65 63 74 69 6f    6e 07 75 74 66 38 6d 62    onnection.utf8mb
 
  34 00 1e 15 63 68 61 72    61 63 74 65 72 5f 73 65    4...character_se
 
  74 5f 72 65 73 75 6c 74    73 07 75 74 66 38 6d 62    t_results.utf8mb
 
  34 00 10 0d 63 6f 6e 6e    65 63 74 69 6f 6e 5f 69    4...connection_i
 
  64 01 31 00 1e 12 6d 61    78 5f 61 6c 6c 6f 77 65    d.1...max_allowe
 
  64 5f 70 61 63 6b 65 74    0a 31 30 37 33 37 34 31    d_packet.1073741
 
  38 32 34 00 15 10 73 79    73 74 65 6d 5f 74 69 6d    824...system_tim
 
  65 5f 7a 6f 6e 65 03 55    54 43 00 14 11 74 68 72    e_zone.UTC...thr
 
  65 61 64 73 5f 63 6f 6e    6e 65 63 74 65 64 01 31    eads_connected.1
 
  00 11 09 74 69 6d 65 5f    7a 6f 6e 65 06 53 59 53    ...time_zone.SYS
 
  54 45 4d 00 1d 0c 74 78    5f 69 73 6f 6c 61 74 69    TEM...tx_isolati
 
  6f 6e 0f 52 45 50 45 41    54 41 42 4c 45 2d 52 45    on.REPEATABLE-RE
 
  41 44                                                 AD

The worst-case performance drop that would come with this change is for connections that are open for a very short time. This overhead can be avoided completely by dropping the CLIENT_SESSION_TRACK capability bit from the initial handshake response. I assume that the tracking of system variables is irrelevant for these types of connections as the likelihood of a system variable change happening in the middle of one is unlikely.

Generated at Thu Feb 08 10:25:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.