Add connection_status system table view to check the connetion status and other information

[crazycs520]

Feature Request

Is your feature request related to a problem? Please describe:

Add the following connection_summary table in INFORMATION_SCHEMA.

CREATE TABLE `connection_summary` (
  `id` bigint(20) DEFAULT NULL,
  `user` varchar(16) DEFAULT NULL,
  `host` varchar(64) DEFAULT NULL,
  `db` varchar(64) DEFAULT NULL,
  `create_time` timestamp(6) NULL DEFAULT NULL COMMENT 'the creation time of this connection',
  `begin_time` timestamp(6) NULL DEFAULT NULL COMMENT 'the begin time of the following summary information',
  `total_wait_cmd_time` bigint(20) DEFAULT NULL COMMENT 'unit is second',
  `total_exec_cmd_time` bigint(20) DEFAULT NULL,
  `total_write_resp_time` bigint(20) DEFAULT NULL
)

The main information the I want to gain from connection_summary table is the following summary information:

• total_wait_cmd_time: the total wait command duration of this connection since the begin_time.
• total_exec_cmd_time: the total exec command duration of this connection since the begin_time.
• total_write_resp_time: the total write command response duration of this connection since the begin_time.

I add begin_time since I want to reset the value of those summary columns (total_xxx_time). So I also want to make that summary information only contain the most recent data, so I may also want to add a global variable, such as set @@tidb_connection_summary_summary_refresh_interval=1800.

With the connection summary information(total_xxx_time), it is very easy to know which connection is very idle, and if the total_wait_cmd_time value is very large, it means the load pressure of this connection is very small.

Describe the feature you'd like:

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[crazycs520]

@breeswish PTAL

[breezewish]

Ideas are great! However I have some product oriented concerns: How does MySQL and Oracle provide such information?

[crazycs520]

@morgo PTAL

[morgo]

This is awesome! So excited to see someone looking into this. /cc @dveeden

Ideas are great! However I have some product oriented concerns: How does MySQL and Oracle provide such information?

In MySQL, they use performance_schema, but across a few separate tables. In Percona Server they also have THREAD_STATISTICS if you are looking for inspiration.

We also have some feature requests for TiDB that don't exactly match this but may be useful:

• https://github.com/pingcap/tidb/issues/23923
• https://github.com/pingcap/tidb/issues/22140 (slightly different: transaction scoped, and as a summary)

A couple of specific suggestions:

• Please include connection_attributes. Modern clients will capture some additional info like the client version, OS name, PID, etc. We can represent it as JSON. These are useful in debugging - clients can be broken!
• Can you us ns instead of seconds? For OLTP, seconds is really long. And other timers such as statements_summary are in ns. There are helper functions to convert such as FORMAT_NANO_TIME() which we can show in examples.
• For the names create_time, begin_time etc, try and use the style and case that matches statement_summary. i.e. s/total_wall_cmd_time/SUM_STATEMENT_LATENCY/.

[LemonHX]

@breeswish

Ideas are great! However I have some product oriented concerns: How does MySQL and Oracle provide such information?

for MySQL 8

has following information in performance_schema

• events_stages_summary_by_?_by_event_name
• events_statements_summary_by_?_by_event_name
• events_transactions_summary_by_?_by_event_name
• events_waits_summary_by_?_by_event_name
• memory_summary_by_?_by_event_name

? could be either user, host, or account(per connection) and also exist for global

for acquiring the user information could join the table information_schema.processlist and performance_schema.accounts

performance_schema.accounts has rows

• USER
• HOST
• CURRENT_CONNECTIONS
• TOTAL_CONNECTIONS

`information_schema.processlist has rows

• ID
• USER
• HOST
• DB
• COMMAND
• TIME
• STATE
• INFO

[dveeden]

Please do add these:

• Connection Type (plain, TLS, UNIX socket, named pipe, etc)
• TLS Version
• TLS Cipher

See also "Visibility into TLS version" on https://mysqlserverteam.com/ssltls-improvements-in-mysql-5-7-10/

Also see what performance_schema.threads has in MySQL 8.0.26:

mysql-8.0.26 > SELECT * FROM performance_schema.threads WHERE NAME='thread/sql/one_connection'\G
*************************** 1. row ***************************
          THREAD_ID: 51
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 8
   PROCESSLIST_USER: msandbox
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: executing
   PROCESSLIST_INFO: SELECT * FROM performance_schema.threads WHERE NAME='thread/sql/one_connection'
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 25605
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

[breezewish]

There is no "thread" concept in TiDB. We need some adoption.

[dveeden]

There is no "thread" concept in TiDB. We need some adoption.

          THREAD_ID: 51
...
     PROCESSLIST_ID: 8
...
       THREAD_OS_ID: 25605

TiDB has the connection ID, which is the PROCESSLIST_ID here (matches the ID column from SHOW PROCESSLIST)

The THREAD_OS_ID could map to the ID of the goroutine. It could also map to OS tasks for the tidb-server process.

$ ls /proc/$(pgrep -x tidb-server)/task/
27504  27506  27508  27510  27512  27515  27517  27519  27525  27530  27608
27505  27507  27509  27511  27513  27516  27518  27520  27526  27531

This could be used to move specific tasks to a different CGroup giving them different resource constraints, priorities, etc. However I don't think with the way Go and TiDB works that this makes any sense. So I would suggest to not expose this.

And the THREAD_ID here is for performance_schema and doesn't apply to TiDB as long as there is no performance_schema

[dragonly]

Idea is great! I am concerned that will the connection_status eventually explode into another dashboard in INFORMATION_SCHEMA? As so many people are asking for fields in this table, IMHO we definitely need a proposal for this feature.

[crazycs520]

Maybe rename the table name from connection_status to connection_summary is better.