[MDEV-30543] New status variable: max_used_connections_time

Add variable max_used_connections_time to show the time at which
max_used_connections reached its current value. This is useful for
troubleshooting high connection counts. MySQL 8 has this already.

All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the BSD-new
license. I am contributing on behalf of my employer Amazon Web Services.

3 files changed, 69 insertions, 1 deletions

diff --git a/mysql-test/main/status.result b/mysql-test/main/status.result
index 0669bdf3b34..9a4dd7c9302 100644
--- a/mysql-test/main/status.result
+++ b/mysql-test/main/status.result
@@ -447,3 +447,22 @@ Feature_json	2
 connection default;
 set @@global.concurrent_insert= @old_concurrent_insert;
 SET GLOBAL log_output = @old_log_output;
+#
+# MDEV-30543 New status variable: Max_used_connections_time
+#
+FLUSH STATUS;
+connect  con1,localhost,root,,;
+connect  con2,localhost,root,,;
+connection con1;
+disconnect con2;
+SELECT 'DTVALUE' = 'DTVALUE' AS expect_1;
+expect_1
+1
+connect  con3,localhost,root,,;
+connect  con4,localhost,root,,;
+SELECT 'DTVALUE' < 'DTVALUE' as expect_1;
+expect_1
+1
+disconnect con4;
+disconnect con3;
+disconnect con1;
diff --git a/mysql-test/main/status.test b/mysql-test/main/status.test
index 78626615d45..e0f72d68884 100644
--- a/mysql-test/main/status.test
+++ b/mysql-test/main/status.test
@@ -447,3 +447,34 @@ SET GLOBAL log_output = @old_log_output;
 # Wait till we reached the initial number of concurrent sessions
 --source include/wait_until_count_sessions.inc
 
+--echo #
+--echo # MDEV-30543 New status variable: Max_used_connections_time
+--echo #
+
+FLUSH STATUS;
+
+connect (con1,localhost,root,,);
+--sleep 1
+connect (con2,localhost,root,,);
+--sleep 1
+let $time_1=`SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections_time'`;
+--sleep 1
+connection con1;
+disconnect con2;
+--sleep 1
+let $time_2=`SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections_time'`;
+--replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
+eval SELECT '$time_1' = '$time_2' AS expect_1;
+--sleep 1
+connect (con3,localhost,root,,);
+--sleep 1
+connect (con4,localhost,root,,);
+--sleep 1
+let $time_3=`SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections_time'`;
+--replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
+eval SELECT '$time_1' < '$time_3' as expect_1;
+
+disconnect con4;
+disconnect con3;
+disconnect con1;
+
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index ee368def9be..12dd3639365 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -331,6 +331,7 @@ static my_bool opt_debugging= 0, opt_external_locking= 0, opt_console= 0;
 static my_bool opt_short_log_format= 0, opt_silent_startup= 0;
 
 ulong max_used_connections;
+time_t max_used_connections_time;
 static const char *mysqld_user, *mysqld_chroot;
 static char *default_character_set_name;
 static char *character_set_filesystem_name;
@@ -6173,7 +6174,10 @@ void create_new_thread(CONNECT *connect)
 
   uint sum= connection_count + extra_connection_count;
   if (sum > max_used_connections)
+  {
     max_used_connections= sum;
+    max_used_connections_time= time(nullptr);
+  }
 
   /*
     The initialization of thread_id is done in create_embedded_thd() for
@@ -7006,9 +7010,20 @@ static int show_heartbeat_period(THD *thd, SHOW_VAR *var, char *buff,
   return 0;
 }
 
-
 #endif /* HAVE_REPLICATION */
 
+
+static int show_max_used_connections_time(THD *thd, SHOW_VAR *var, char *buff,
+                                 enum enum_var_type scope)
+{
+  var->type= SHOW_CHAR;
+  var->value= buff;
+
+  get_date(buff, GETDATE_DATE_TIME | GETDATE_FIXEDLENGTH, max_used_connections_time);
+  return 0;
+}
+
+
 static int show_open_tables(THD *thd, SHOW_VAR *var, char *buff,
                             enum enum_var_type scope)
 {
@@ -7484,6 +7499,7 @@ SHOW_VAR status_vars[]= {
   {"Master_gtid_wait_timeouts", (char*) offsetof(STATUS_VAR, master_gtid_wait_timeouts), SHOW_LONG_STATUS},
   {"Master_gtid_wait_time",    (char*) offsetof(STATUS_VAR, master_gtid_wait_time), SHOW_LONG_STATUS},
   {"Max_used_connections",     (char*) &max_used_connections,  SHOW_LONG},
+  {"Max_used_connections_time",(char*) &show_max_used_connections_time, SHOW_SIMPLE_FUNC},
   {"Memory_used",              (char*) &show_memory_used, SHOW_SIMPLE_FUNC},
   {"Memory_used_initial",      (char*) &start_memory_used, SHOW_LONGLONG},
   {"Resultset_metadata_skipped", (char *) offsetof(STATUS_VAR, skip_metadata_count),SHOW_LONG_STATUS},
@@ -7822,6 +7838,7 @@ static int mysql_init_variables(void)
   specialflag= 0;
   binlog_cache_use=  binlog_cache_disk_use= 0;
   max_used_connections= slow_launch_threads = 0;
+  max_used_connections_time= 0;
   mysqld_user= mysqld_chroot= opt_init_file= opt_bin_logname = 0;
   prepared_stmt_count= 0;
   mysqld_unix_port= opt_mysql_tmpdir= my_bind_addr_str= NullS;
@@ -9200,6 +9217,7 @@ void refresh_status(THD *thd)
     connections.  This is not perfect, but status data is not exact anyway.
   */
   max_used_connections= connection_count + extra_connection_count;
+  max_used_connections_time= time(nullptr);
 }
 
 #ifdef HAVE_PSI_INTERFACE