Details

      Description

      I was looking at user statistics for 8.0 and noticed some broken fields so compared them to 5.7.23.

      Here's my findings (YES=it works, NO=it doesn't):

      FIELD 5.7 8.0
      CONCURRENT_CONNECTIONS NO NO
      TOTAL_CONNECTIONS YES NO
      COMMIT_TRANSACTIONS NO NO
      ROLLBACK_TRANSACTIONS YES YES
      DENIED_CONNECTIONS NO NO
      ACCESS_DENIED NO NO
      LOST_CONNECTIONS NO NO

      (listed only interesting fields)

      Some notes:
      1. Noticed in PS-4662 that concurrent_connections was removed from thread_statistics - maybe it should be removed from user statistics as well since it doesn't seem to work (from client statistics also?)
      2. Lost connections I tried to reproduce by killing a client - so my test case maybe wasn't correct.
      3. For access_denied and denied_connections there's already PS-3956 reported.
      4. If you check "show user_statistics" for 8.0 you'll notice user "#mysql_system#" and for some reason only for this one number of total connections is increasing - it's not clear to me which user exactly is this since it's not visible in the user table - might be another issue
      5. I would say that all or most of the above applies to "thread_statistics" and "client_statistics" also (eg. commit_transactions don't increase anywhere etc.)

      Here's some example setup:

      SET GLOBAL userstat=ON;
      SET GLOBAL thread_statistics=ON;
      CREATE USER grantee@'%' IDENTIFIED BY 'grantee';
      CREATE DATABASE test;
      USE test;
      CREATE TABLE t1(a int);
      CREATE DATABASE test2;
      USE test2;
      CREATE TABLE t2(a int);
      GRANT ALL PRIVILEGES ON test2.* TO grantee;
      

      Test cases I did:
      1. logged with grantee user, started transaction, did rollback, started another, commited
      2. tried to log with incorrect password, tried to use test database for which I don't have permissions
      3. killed client connection
      4. started multiple connections with the same user at the same time

      Here's just an example of the statistics output:
      5.7:

      5.7.23>show user_statistics\G;
      *************************** 1. row ***************************
                        User: grantee
           Total_connections: 3
      Concurrent_connections: 0
              Connected_time: 248
                   Busy_time: 0
                    Cpu_time: 0
              Bytes_received: 378
                  Bytes_sent: 0
        Binlog_bytes_written: 0
                Rows_fetched: 6
                Rows_updated: 2
             Table_rows_read: 1
             Select_commands: 6
             Update_commands: 2
              Other_commands: 7
         Commit_transactions: 0
       Rollback_transactions: 1
          Denied_connections: 0
            Lost_connections: 0
               Access_denied: 0
               Empty_queries: 0
       Total_ssl_connections: 3
      
      5.7.23>show thread_statistics\G;
       *************************** 5. row ***************************
                    Thread_id: 9
            Total_connections: 1
       Concurrent_connections: 0
               Connected_time: 397
                    Busy_time: 60
                     Cpu_time: 0
               Bytes_received: 284
                   Bytes_sent: 0
         Binlog_bytes_written: 0
                 Rows_fetched: 4
                 Rows_updated: 2
              Table_rows_read: 1
              Select_commands: 4
              Update_commands: 2
               Other_commands: 7
          Commit_transactions: 0
        Rollback_transactions: 1
           Denied_connections: 0
             Lost_connections: 0
                Access_denied: 0
                Empty_queries: 0
        Total_ssl_connections: 1
      

      8.0.12rc2

      8.0.12>show user_statistics\G;
      *************************** 1. row ***************************
                        User: grantee
           Total_connections: 1
      Concurrent_connections: 0
              Connected_time: 1069.8265680999998
                   Busy_time: 130.152549
                    Cpu_time: 0.017500540000000002
              Bytes_received: 562
                  Bytes_sent: 0
        Binlog_bytes_written: 271
                Rows_fetched: 10
                Rows_updated: 3
             Table_rows_read: 106
             Select_commands: 10
             Update_commands: 3
              Other_commands: 10
         Commit_transactions: 0
       Rollback_transactions: 2
          Denied_connections: 0
            Lost_connections: 0
               Access_denied: 0
               Empty_queries: 0
       Total_ssl_connections: 1
      *************************** 2. row ***************************
                       User: #mysql_system#
          Total_connections: 5
      Concurrent_connections: 0
             Connected_time: 0
                  Busy_time: 0
                   Cpu_time: 0
             Bytes_received: 0
                 Bytes_sent: 0
       Binlog_bytes_written: 0
               Rows_fetched: 0
               Rows_updated: 0
            Table_rows_read: 0
            Select_commands: 0
            Update_commands: 0
             Other_commands: 0
        Commit_transactions: 0
      Rollback_transactions: 0
         Denied_connections: 0
           Lost_connections: 0
              Access_denied: 0
              Empty_queries: 0
      Total_ssl_connections: 0
      
      8.0.12>show thread_statistics\G;
      *************************** 1. row ***************************
                  Thread_id: 16
          Total_connections: 1
             Connected_time: 227.36335179999998
                  Busy_time: 0.24405300000000002
                   Cpu_time: 0.003991795
             Bytes_received: 333
                 Bytes_sent: 0
       Binlog_bytes_written: 271
               Rows_fetched: 1
               Rows_updated: 4
            Table_rows_read: 82
            Select_commands: 1
            Update_commands: 4
             Other_commands: 10
        Commit_transactions: 0
      Rollback_transactions: 3
         Denied_connections: 0
           Lost_connections: 0
              Access_denied: 0
              Empty_queries: 0
      Total_ssl_connections: 0
      

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  zsolt.parragi Zsolt Parragi
                  Reporter:
                  tomislav.plavcic@percona.com Tomislav Plavcic
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 2 days, 4 hours, 20 minutes
                    2d 4h 20m