This is based on the userstatv2 patch from Percona and OurDelta.

The original code comes, as far as I know, from Google (Mark Callaghan's team) with additional work from Percona, Ourdelta and Weldon Whipple.

This code provides the same functionallity, but with a lot of changes to make it faster and better fit the MariaDB infrastucture.

Added new status variables:
- Com_show_client_statistics, Com_show_index_statistics, Com_show_table_statistics, Com_show_user_statistics
- Access_denied_errors, Busy_time (clock time), Binlog_bytes_written, Cpu_time, Empty_queries, Rows_sent, Rows_read

Added new variable / startup option 'userstat' to control if user statistics should be enabled or not

Added my_getcputime(); Returns cpu time used by this thread.
New FLUSH commands:
- FLUSH SLOW QUERY LOG
- FLUSH TABLE_STATISTICS
- FLUSH INDEX_STATISTICS
- FLUSH USER_STATISTICS
- FLUSH CLIENT_STATISTICS

New SHOW commands:
- SHOW CLIENT_STATISTICS
- SHOW USER_STATISTICS
- SHOW TABLE_STATISTICS
- SHOW INDEX_STATISTICS

New Information schemas:
- CLIENT_STATISTICS
- USER_STATISTICS
- INDEX_STATISTICS
- TABLE_STATISTICS

Added support for all new flush commands to mysqladmin

Added handler::ha_... wrappers for all handler read calls to do statistics counting
- Changed all code to use new ha_... calls
- Count number of read rows, changed rows and rows read trough an index

Added counting of number of bytes sent to binary log (status variable Binlog_bytes_written)
Added counting of access denied errors (status variable Access_denied_erors)

Bugs fixed:
- Fixed bug in add_to_status() and add_diff_to_status() where longlong variables where threated as long
- CLOCK_GETTIME was not propely working on Linuxm

client/mysqladmin.cc:
  Added support for all new flush commmands and some common combinations:
  
  flush-slow-log
  flush-table-statistics
  flush-index-statistics
  flush-user-statistics
  flush-client-statistics
  flush-all-status
  flush-all-statistics
configure.in:
  Added checking if clock_gettime needs the librt.
  (Fixes Bug #37639 clock_gettime is never used/enabled in Linux/Unix)
include/my_sys.h:
  Added my_getcputime()
include/mysql_com.h:
  Added LIST_PROCESS_HOST_LEN & new REFRESH target defines
mysql-test/r/information_schema.result:
  New information schema tables added
mysql-test/r/information_schema_all_engines.result:
  New information schema tables added
mysql-test/r/information_schema_db.result:
  New information schema tables added
mysql-test/r/log_slow.result:
  Added testing that flosh slow query logs is accepted
mysql-test/r/status_user.result:
  Basic testing of user, client, table and index statistics
mysql-test/t/log_slow.test:
  Added testing that flosh slow query logs is accepted
mysql-test/t/status_user-master.opt:
  Ensure that we get a fresh restart before running status_user.test
mysql-test/t/status_user.test:
  Basic testing of user, client, table and index statistics
mysys/my_getsystime.c:
  Added my_getcputime()
  Returns cpu time used by this thread.
sql/authors.h:
  Updated authors to have core and original MySQL developers first.
sql/event_data_objects.cc:
  Updated call to mysql_reset_thd_for_next_command()
sql/event_db_repository.cc:
  Changed to use new ha_... calls
sql/filesort.cc:
  Changed to use new ha_... calls
sql/ha_partition.cc:
  Changed to use new ha_... calls
  Fixed comment syntax
sql/handler.cc:
  Changed to use new ha_... calls
  Reset table statistics
  Added code to update global table and index status
  Added counting of rows changed
sql/handler.h:
  Added table and index statistics variables
  Added function reset_statistics()
  Added handler::ha_... wrappers for all handler read calls to do statistics counting
  Protected all normal read calls to ensure we use the new calls in the server.
  Made ha_partition a friend class so that partition code can call the old read functions
sql/item_subselect.cc:
  Changed to use new ha_... calls
sql/lex.h:
  Added keywords for new information schema tables and flush commands
sql/log.cc:
  Added flush_slow_log()
  Added counting of number of bytes sent to binary log
  Removed not needed test of thd (It's used before, so it's safe to use)
  Added THD object to MYSQL_BIN_LOG::write_cache() to simplify statistics counting
sql/log.h:
  Added new parameter to write_cache()
  Added flush_slow_log() functions.
sql/log_event.cc:
  Updated call to mysql_reset_thd_for_next_command()
  Changed to use new ha_... calls
sql/log_event_old.cc:
  Updated call to mysql_reset_thd_for_next_command()
  Changed to use new ha_... calls
sql/mysql_priv.h:
  Updated call to mysql_reset_thd_for_next_command()
  Added new statistics functions and variables needed by these.
sql/mysqld.cc:
  Added new statistics variables and structures to handle these
  Added new status variables:
  - Com_show_client_statistics, Com_show_index_statistics, Com_show_table_statistics, Com_show_user_statistics
  - Access_denied_errors, Busy_time (clock time), Binlog_bytes_written, Cpu_time, Empty_queries, Rows_set, Rows_read
  Added new option 'userstat' to control if user statistics should be enabled or not
sql/opt_range.cc:
  Changed to use new ha_... calls
sql/opt_range.h:
  Changed to use new ha_... calls
sql/opt_sum.cc:
  Changed to use new ha_... calls
sql/records.cc:
  Changed to use new ha_... calls
sql/set_var.cc:
  Added variable 'userstat'
sql/sp.cc:
  Changed to use new ha_... calls
sql/sql_acl.cc:
  Changed to use new ha_... calls
  Added counting of access_denied_errors
sql/sql_base.cc:
  Added call to statistics functions
sql/sql_class.cc:
  Added usage of org_status_var, to store status variables at start of command
  Added functions THD::update_stats(), THD::update_all_stats()
  Fixed bug in add_to_status() and add_diff_to_status() where longlong variables where threated as long
sql/sql_class.h:
  Added new status variables to status_var
  Moved variables that was not ulong in status_var last.
  Added variables to THD for storing temporary values during statistics counting
sql/sql_connect.cc:
  Variables and functions to calculate user and client statistics
  Added counting of access_denied_errors and lost_connections
sql/sql_cursor.cc:
  Changed to use new ha_... calls
sql/sql_handler.cc:
  Changed to use new ha_... calls
sql/sql_help.cc:
  Changed to use new ha_... calls
sql/sql_insert.cc:
  Changed to use new ha_... calls
sql/sql_lex.h:
  Added SQLCOM_SHOW_USER_STATS, SQLCOM_SHOW_TABLE_STATS, SQLCOM_SHOW_INDEX_STATS, SQLCOM_SHOW_CLIENT_STATS
sql/sql_parse.cc:
  Added handling of:
  - SHOW CLIENT_STATISTICS
  - SHOW USER_STATISTICS
  - SHOW TABLE_STATISTICS
  - SHOW INDEX_STATISTICS
  Added handling of new FLUSH commands:
  - FLUSH SLOW QUERY LOGS
  - FLUSH TABLE_STATISTICS
  - FLUSH INDEX_STATISTICS
  - FLUSH USER_STATISTICS
  - FLUSH CLIENT_STATISTICS
  Added THD parameter to mysql_reset_thd_for_next_command()
  Added initialization and calls to user statistics functions
  Added increment of statistics variables empty_queries, rows_sent and access_denied_errors.
  Added counting of cpu time per query
sql/sql_plugin.cc:
  Changed to use new ha_... calls
sql/sql_prepare.cc:
  Updated call to mysql_reset_thd_for_next_command()
sql/sql_select.cc:
  Changed to use new ha_... calls
  Indentation changes
sql/sql_servers.cc:
  Changed to use new ha_... calls
sql/sql_show.cc:
  Added counting of access denied errors
  Added function for new information schema tables:
  - CLIENT_STATISTICS
  - USER_STATISTICS
  - INDEX_STATISTICS
  - TABLE_STATISTICS
  Changed to use new ha_... calls
sql/sql_table.cc:
  Changed to use new ha_... calls
sql/sql_udf.cc:
  Changed to use new ha_... calls
sql/sql_update.cc:
  Changed to use new ha_... calls
sql/sql_yacc.yy:
  Add new show and flush commands
sql/structs.h:
  Add name_length to KEY to avoid some strlen
  Added cache_name to KEY for fast storage of keyvalue in cache
  Added structs USER_STATS, TABLE_STATS, INDEX_STATS
  Added function prototypes for statistics functions
sql/table.cc:
  Store db+table+index name into keyinfo->cache_name
sql/table.h:
  Added new information schema tables
sql/tztime.cc:
  Changed to use new ha_... calls

1 files changed, 78 insertions, 5 deletions

diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 0805eda59e4..141be7802d4 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -615,6 +615,7 @@ THD::THD()
   mysys_var=0;
   binlog_evt_union.do_union= FALSE;
   enable_slow_log= 0;
+
 #ifndef DBUG_OFF
   dbug_sentry=THD_SENTRY_MAGIC;
 #endif
@@ -817,7 +818,63 @@ void THD::init(void)
   update_charset();
   reset_current_stmt_binlog_row_based();
   bzero((char *) &status_var, sizeof(status_var));
+  bzero((char *) &org_status_var, sizeof(org_status_var));
   sql_log_bin_toplevel= options & OPTION_BIN_LOG;
+  select_commands= update_commands= other_commands= 0;
+  /* Set to handle counting of aborted connections */
+  userstat_running= opt_userstat_running;
+  last_global_update_time= current_connect_time= time(NULL);
+}
+
+ 
+/* Updates some status variables to be used by update_global_user_stats */
+
+void THD::update_stats(void)
+{
+  /* sql_command == SQLCOM_END in case of parse errors or quit */
+  if (lex->sql_command != SQLCOM_END)
+  {
+    /* The replication thread has the COM_CONNECT command */
+    DBUG_ASSERT(command == COM_QUERY || command == COM_CONNECT);
+
+    /* A SQL query. */
+    if (lex->sql_command == SQLCOM_SELECT)
+      select_commands++;
+    else if (! sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND)
+    {
+      /* Ignore 'SHOW ' commands */
+    }
+    else if (is_update_query(lex->sql_command))
+      update_commands++;
+    else
+      other_commands++;
+  }
+}
+
+
+void THD::update_all_stats()
+{
+  time_t save_time;
+  ulonglong end_cpu_time, end_utime;
+  double busy_time, cpu_time;
+
+  /* This is set at start of query if opt_userstat_running was set */
+  if (!userstat_running)
+    return;
+
+  end_cpu_time= my_getcputime();
+  end_utime=    my_micro_time_and_time(&save_time);
+  busy_time= (end_utime - start_utime)  / 1000000.0;
+  cpu_time=  (end_cpu_time - start_cpu_time) / 10000000.0;
+  /* In case there are bad values, 2629743 is the #seconds in a month. */
+  if (cpu_time > 2629743.0)
+    cpu_time= 0;
+  status_var_add(status_var.cpu_time, cpu_time);
+  status_var_add(status_var.busy_time, busy_time);
+
+  /* Updates THD stats and the global user stats. */
+  update_stats();
+  update_global_user_stats(this, TRUE, save_time);
 }
 
 
@@ -984,9 +1041,8 @@ THD::~THD()
    from_var     from this array
 
   NOTES
-    This function assumes that all variables are long/ulong.
-    If this assumption will change, then we have to explictely add
-    the other variables after the while loop
+    This function assumes that all variables at start are long/ulong and
+    other types are handled explicitely
 */
 
 void add_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var)
@@ -998,6 +1054,13 @@ void add_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var)
 
   while (to != end)
     *(to++)+= *(from++);
+
+  /* Handle the not ulong variables. See end of system_status_var */
+  to_var->bytes_received=       from_var->bytes_received;
+  to_var->bytes_sent+=          from_var->bytes_sent;
+  to_var->binlog_bytes_written= from_var->binlog_bytes_written;
+  to_var->cpu_time+=            from_var->cpu_time;
+  to_var->busy_time+=           from_var->busy_time;
 }
 
 /*
@@ -1010,7 +1073,8 @@ void add_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var)
     dec_var      minus this array
   
   NOTE
-    This function assumes that all variables are long/ulong.
+    This function assumes that all variables at start are long/ulong and
+    other types are handled explicitely
 */
 
 void add_diff_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var,
@@ -1023,6 +1087,14 @@ void add_diff_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var,
 
   while (to != end)
     *(to++)+= *(from++) - *(dec++);
+
+  to_var->bytes_received=       (from_var->bytes_received -
+                                 dec_var->bytes_received);
+  to_var->bytes_sent+=          from_var->bytes_sent - dec_var->bytes_sent;
+  to_var->binlog_bytes_written= (from_var->binlog_bytes_written -
+                                 dec_var->binlog_bytes_written);
+  to_var->cpu_time+=            from_var->cpu_time - dec_var->cpu_time;
+  to_var->busy_time+=           from_var->busy_time - dec_var->busy_time;
 }
 
 #define SECONDS_TO_WAIT_FOR_KILL 2
@@ -2773,7 +2845,8 @@ void thd_increment_bytes_sent(ulong length)
 {
   THD *thd=current_thd;
   if (likely(thd != 0))
-  { /* current_thd==0 when close_connection() calls net_send_error() */
+  {
+    /* current_thd == 0 when close_connection() calls net_send_error() */
     thd->status_var.bytes_sent+= length;
   }
 }