September 12, 2008

Google’s user_statistics V2 port and changes

Posted by Vadim

Recently Google published V2 release of patches, one of them user_statistics we use in our releases.
New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes:

      New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc)
      New command SHOW CLIENT_STATISTICS, which shows statistics per client's hostname, not per user
      FLUSH USER_ and CLIENT_STATISTICS commands

Our port includes:

  • INFORMATION_SCHEMA tables - USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS
  • Access protection - USER_STATISTICS, CLIENT_STATISTICS available only for user with SUPER or PROCESS privileges. INDEX_STATISTICS, TABLE_STATISTICS shows only information of tables that current user has access to (SELECT privileges required). This is important for hosting providers


Here is example of new tables:

PLAIN TEXT
SQL:
  1. mysql> SELECT * FROM information_schema.user_statistics\G
  2. *************************** 1. row ***************************
  3.                   USER: root
  4.      TOTAL_CONNECTIONS: 2
  5. CONCURRENT_CONNECTIONS: 0
  6.         CONNECTED_TIME: 4417
  7.              BUSY_TIME: 7
  8.               CPU_TIME: 5
  9.         BYTES_RECEIVED: 12163659
  10.             BYTES_SENT: 4479
  11.   BINLOG_BYTES_WRITTEN: 12164231
  12.           ROWS_FETCHED: 2
  13.           ROWS_UPDATED: 348382
  14.        TABLE_ROWS_READ: 0
  15.        SELECT_COMMANDS: 2
  16.        UPDATE_COMMANDS: 16
  17.         OTHER_COMMANDS: 26
  18.    COMMIT_TRANSACTIONS: 15
  19.  ROLLBACK_TRANSACTIONS: 0
  20.     DENIED_CONNECTIONS: 0
  21.       LOST_CONNECTIONS: 0
  22.          ACCESS_DENIED: 0
  23.          EMPTY_QUERIES: 0
  24. 1 row IN SET (0.00 sec)

PLAIN TEXT
SQL:
  1. mysql> SELECT * FROM information_schema.client_STATISTICS\G       
  2. *************************** 1. row ***************************
  3.                 CLIENT: 192.168.10.174
  4.      TOTAL_CONNECTIONS: 10
  5. CONCURRENT_CONNECTIONS: 0
  6.         CONNECTED_TIME: 0
  7.              BUSY_TIME: 118
  8.               CPU_TIME: 118
  9.         BYTES_RECEIVED: 2174226
  10.             BYTES_SENT: 16222528
  11.   BINLOG_BYTES_WRITTEN: 0
  12.           ROWS_FETCHED: 51596
  13.           ROWS_UPDATED: 0
  14.        TABLE_ROWS_READ: 233
  15.        SELECT_COMMANDS: 5904
  16.        UPDATE_COMMANDS: 0
  17.         OTHER_COMMANDS: 30
  18.    COMMIT_TRANSACTIONS: 0
  19.  ROLLBACK_TRANSACTIONS: 0
  20.     DENIED_CONNECTIONS: 0
  21.       LOST_CONNECTIONS: 0
  22.          ACCESS_DENIED: 0
  23.          EMPTY_QUERIES: 506
  24. *************************** 2. row ***************************
  25.                 CLIENT: 192.168.10.161
  26.      TOTAL_CONNECTIONS: 41539
  27. CONCURRENT_CONNECTIONS: 0
  28.         CONNECTED_TIME: 0
  29.              BUSY_TIME: 3369
  30.               CPU_TIME: 3369
  31.         BYTES_RECEIVED: 152609007
  32.             BYTES_SENT: 1489795778
  33.   BINLOG_BYTES_WRITTEN: 0
  34.           ROWS_FETCHED: 593836
  35.           ROWS_UPDATED: 0
  36.        TABLE_ROWS_READ: 549073
  37.        SELECT_COMMANDS: 214801
  38.        UPDATE_COMMANDS: 0
  39.         OTHER_COMMANDS: 124497
  40.    COMMIT_TRANSACTIONS: 0
  41.  ROLLBACK_TRANSACTIONS: 0
  42.     DENIED_CONNECTIONS: 0
  43.       LOST_CONNECTIONS: 0
  44.          ACCESS_DENIED: 0
  45.          EMPTY_QUERIES: 70748

and INDEX/TABLE statistics:

PLAIN TEXT
SQL:
  1. mysql> SELECT * FROM information_schema.index_STATISTICS LIMIT 10;
  2. +--------------+------------+-----------------+-----------+
  3. | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME      | ROWS_READ |
  4. +--------------+------------+-----------------+-----------+
  5. | art119       | img_out119 | from_message_id |      4160 |
  6. | art117       | img_out117 | from_message_id |      3324 |
  7. | art119       | article119 | forum_id_3      |      1855 |
  8. | art84        | img_out84  | from_message_id |      8363 |
  9. | art115       | article115 | forum_id_2      |         6 |
  10. | art61        | img_out61  | from_message_id |      4475 |
  11. | art90        | img_out90  | from_message_id |     42853 |
  12. | art104       | forum104   | site_id         |      9660 |
  13. | art61        | forum61    | site_id         |     17744 |
  14. | art50        | img_out50  | from_message_id |     19306 |
  15. +--------------+------------+-----------------+-----------+
  16. 10 rows IN SET (0.00 sec)
  17.  
  18. mysql> SELECT * FROM information_schema.table_STATISTICS LIMIT 10;     
  19. +--------------+-----------------+-----------+--------------+------------------------+
  20. | TABLE_SCHEMA | TABLE_NAME      | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
  21. +--------------+-----------------+-----------+--------------+------------------------+
  22. | art86        | forum86         |      2186 |            2 |                      6 |
  23. | art104       | forum104        |      9660 |            6 |                     18 |
  24. | art57        | link_out57      |   8406371 |         3393 |                  23751 |
  25. | mysql        | user            |        15 |            0 |                      0 |
  26. | art53        | article53       |   7103377 |         4564 |                  45640 |
  27. | art116       | thread_stats116 |         0 |         8479 |                  16958 |
  28. | art50        | link_out50      |  11420660 |         2051 |                  14357 |
  29. | art60        | article60       |   5048218 |          824 |                   8240 |
  30. | art92        | author92        |         0 |          131 |                    262 |
  31. | art50        | img_out50       |   1772663 |         2305 |                   6915 |
  32. +--------------+-----------------+-----------+--------------+------------------------+
  33. 10 rows IN SET (0.01 sec)

If you would like to test it here is link to patch for 5.0.67.
http://www.mysqlperformanceblog.com/mysql/experimental/userstatv2.patch. When we consider it stable we will include it in our releases.

Btw working with patches I found very useful utils patchutils, which recommend you if you also need to separate one big patch to several small :)

Posted by Vadim @ 2:18 pm :: patch  
Print This Post Print This Post book mark Google’s user_statistics V2 port and changes in del.icio.usdel.icio.us :: submit Google’s user_statistics V2 port and changes to digg.comdigg  
Comment RSS  
Related posts: :check-unused-keys: A tool to interact with INDEX_STATISTICS::Userstats patches with information schema support::How to pretty-print my.cnf with a one-liner:
 

9 Comments »

  1. 1. Mark Callaghan

    Wow. Who knew patches could be incorporated so quickly. Great work.

    Comment :: September 12, 2008 @ 3:20 pm

  2. 2. Kevin Burton

    Nice….

    I think the biggest obstacle to V2 adoption is to get the patches into smaller bite size chunks.

    It’s looking like 5.0.67 is the sweet spot though…..with with the Google SMP patches.

    If you can get a build with these patches and your InnoDB performance patches I think this is the release we’ll target.

    There are still some cool V2 features though… I’m going to try to blog about them when I get some time.

    Comment :: September 12, 2008 @ 9:11 pm

  3. 3. peter

    Kevin,

    What is showed here is not the Googles V2 complete patch set but the given accounting patch ported to Percona patch set. The user statistics patches in particular are very safe because they do not change any internal logic this is why we put them in the “safe to use” release.

    Comment :: September 12, 2008 @ 10:37 pm

  4. 4. peter

    Couple of comments

    1) It is interesting how much speed overhead if any we got with these patches.
    2) empty_queries is misleading It really means empty_results though I guess it is best if Google renames it so we do not add more differences than needed
    3) Busy Time, CPU time all counted with better accuracy while displayed as seconds only this may not be accurate enough in some cases.
    4) The patch should be counting CPU time per query which can be very nice addition to the processlist schema besides time_ms we have already.

    Comment :: September 12, 2008 @ 10:46 pm

  5. 5. Mark Callaghan

    @Peter — What is CPU time per query? Select_commands counts all SELECT statements. Update_commands counts all INSERT/UPDATE/REPLACE/DELETE statements and Other_commands counts everything else. Cpu_time / (Select_commands + Update_commands + Other_commands) displays time per client-server protocol command. Do you want Cpu_time for each type of command? The expressions are easy to evaluate since you provide this data via the Information Schema.

    Comment :: September 13, 2008 @ 7:50 am

  6. 6. Kevin Burton

    Peter,

    I realize it wasn’t the complete patch…. I was congratulating you on isolating one of the features from the larger V2 so that it can be easily added to MySQL AB’s sources and moved between versions easier.

    Kevin

    Comment :: September 13, 2008 @ 11:29 am

  7. 7. peter

    Thanks Kevin :)

    This is exactly the point.

    Comment :: September 15, 2008 @ 9:40 am

  8. 8. True

    How to apply this patch ? I am using mysql 5.0.77.

    I dont see table index_statistics

    Comment :: March 2, 2009 @ 1:56 pm

  9. 9. Baron Schwartz

    Get one of our builds. I assume you are using a standard MySQL server. Download our version from http://www.percona.com/percona-lab.html and then see the docs here: http://www.percona.com/docs/wiki/patches:userstatv2

    Comment :: March 2, 2009 @ 8:22 pm

 



Subscribe without commenting