Google’s user_statistics V2 port and changes

September 12, 2008

Author

Vadim Tkachenko

Percona Software

Share this Post:

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:

<br>mysql> select * from information_schema.user_statisticsG<br>*************************** 1. row ***************************<br>                  USER: root<br>     TOTAL_CONNECTIONS: 2<br>CONCURRENT_CONNECTIONS: 0<br>        CONNECTED_TIME: 4417<br>             BUSY_TIME: 7<br>              CPU_TIME: 5<br>        BYTES_RECEIVED: 12163659<br>            BYTES_SENT: 4479<br>  BINLOG_BYTES_WRITTEN: 12164231<br>          ROWS_FETCHED: 2<br>          ROWS_UPDATED: 348382<br>       TABLE_ROWS_READ: 0<br>       SELECT_COMMANDS: 2<br>       UPDATE_COMMANDS: 16<br>        OTHER_COMMANDS: 26<br>   COMMIT_TRANSACTIONS: 15<br> ROLLBACK_TRANSACTIONS: 0<br>    DENIED_CONNECTIONS: 0<br>      LOST_CONNECTIONS: 0<br>         ACCESS_DENIED: 0<br>         EMPTY_QUERIES: 0<br>1 row in set (0.00 sec)<br>

1

mysql> select * from information_schema.user_statisticsG *************************** 1. row *************************** USER: root TOTAL_CONNECTIONS: 2 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 4417 BUSY_TIME: 7 CPU_TIME: 5 BYTES_RECEIVED: 12163659 BYTES_SENT: 4479 BINLOG_BYTES_WRITTEN: 12164231 ROWS_FETCHED: 2 ROWS_UPDATED: 348382 TABLE_ROWS_READ: 0 SELECT_COMMANDS: 2 UPDATE_COMMANDS: 16 OTHER_COMMANDS: 26 COMMIT_TRANSACTIONS: 15 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 0 LOST_CONNECTIONS: 0 ACCESS_DENIED: 0 EMPTY_QUERIES: 0 1 row in set (0.00 sec)

<br>mysql> select * from information_schema.client_STATISTICSG        <br>*************************** 1. row ***************************<br>                CLIENT: 192.168.10.174<br>     TOTAL_CONNECTIONS: 10<br>CONCURRENT_CONNECTIONS: 0<br>        CONNECTED_TIME: 0<br>             BUSY_TIME: 118<br>              CPU_TIME: 118<br>        BYTES_RECEIVED: 2174226<br>            BYTES_SENT: 16222528<br>  BINLOG_BYTES_WRITTEN: 0<br>          ROWS_FETCHED: 51596<br>          ROWS_UPDATED: 0<br>       TABLE_ROWS_READ: 233<br>       SELECT_COMMANDS: 5904<br>       UPDATE_COMMANDS: 0<br>        OTHER_COMMANDS: 30<br>   COMMIT_TRANSACTIONS: 0<br> ROLLBACK_TRANSACTIONS: 0<br>    DENIED_CONNECTIONS: 0<br>      LOST_CONNECTIONS: 0<br>         ACCESS_DENIED: 0<br>         EMPTY_QUERIES: 506<br>*************************** 2. row ***************************<br>                CLIENT: 192.168.10.161<br>     TOTAL_CONNECTIONS: 41539<br>CONCURRENT_CONNECTIONS: 0<br>        CONNECTED_TIME: 0<br>             BUSY_TIME: 3369<br>              CPU_TIME: 3369<br>        BYTES_RECEIVED: 152609007<br>            BYTES_SENT: 1489795778<br>  BINLOG_BYTES_WRITTEN: 0<br>          ROWS_FETCHED: 593836<br>          ROWS_UPDATED: 0<br>       TABLE_ROWS_READ: 549073<br>       SELECT_COMMANDS: 214801<br>       UPDATE_COMMANDS: 0<br>        OTHER_COMMANDS: 124497<br>   COMMIT_TRANSACTIONS: 0<br> ROLLBACK_TRANSACTIONS: 0<br>    DENIED_CONNECTIONS: 0<br>      LOST_CONNECTIONS: 0<br>         ACCESS_DENIED: 0<br>         EMPTY_QUERIES: 70748<br>

1

mysql> select * from information_schema.client_STATISTICSG *************************** 1. row *************************** CLIENT: 192.168.10.174 TOTAL_CONNECTIONS: 10 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 0 BUSY_TIME: 118 CPU_TIME: 118 BYTES_RECEIVED: 2174226 BYTES_SENT: 16222528 BINLOG_BYTES_WRITTEN: 0 ROWS_FETCHED: 51596 ROWS_UPDATED: 0 TABLE_ROWS_READ: 233 SELECT_COMMANDS: 5904 UPDATE_COMMANDS: 0 OTHER_COMMANDS: 30 COMMIT_TRANSACTIONS: 0 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 0 LOST_CONNECTIONS: 0 ACCESS_DENIED: 0 EMPTY_QUERIES: 506 *************************** 2. row *************************** CLIENT: 192.168.10.161 TOTAL_CONNECTIONS: 41539 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 0 BUSY_TIME: 3369 CPU_TIME: 3369 BYTES_RECEIVED: 152609007 BYTES_SENT: 1489795778 BINLOG_BYTES_WRITTEN: 0 ROWS_FETCHED: 593836 ROWS_UPDATED: 0 TABLE_ROWS_READ: 549073 SELECT_COMMANDS: 214801 UPDATE_COMMANDS: 0 OTHER_COMMANDS: 124497 COMMIT_TRANSACTIONS: 0 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 0 LOST_CONNECTIONS: 0 ACCESS_DENIED: 0 EMPTY_QUERIES: 70748

and INDEX/TABLE statistics:

<br>mysql> select * from information_schema.index_STATISTICS limit 10;<br>+--------------+------------+-----------------+-----------+<br>| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME      | ROWS_READ |<br>+--------------+------------+-----------------+-----------+<br>| art119       | img_out119 | from_message_id |      4160 | <br>| art117       | img_out117 | from_message_id |      3324 | <br>| art119       | article119 | forum_id_3      |      1855 | <br>| art84        | img_out84  | from_message_id |      8363 | <br>| art115       | article115 | forum_id_2      |         6 | <br>| art61        | img_out61  | from_message_id |      4475 | <br>| art90        | img_out90  | from_message_id |     42853 | <br>| art104       | forum104   | site_id         |      9660 | <br>| art61        | forum61    | site_id         |     17744 | <br>| art50        | img_out50  | from_message_id |     19306 | <br>+--------------+------------+-----------------+-----------+<br>10 rows in set (0.00 sec)<br><br>mysql> select * from information_schema.table_STATISTICS limit 10;     <br>+--------------+-----------------+-----------+--------------+------------------------+<br>| TABLE_SCHEMA | TABLE_NAME      | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |<br>+--------------+-----------------+-----------+--------------+------------------------+<br>| art86        | forum86         |      2186 |            2 |                      6 | <br>| art104       | forum104        |      9660 |            6 |                     18 | <br>| art57        | link_out57      |   8406371 |         3393 |                  23751 | <br>| mysql        | user            |        15 |            0 |                      0 | <br>| art53        | article53       |   7103377 |         4564 |                  45640 | <br>| art116       | thread_stats116 |         0 |         8479 |                  16958 | <br>| art50        | link_out50      |  11420660 |         2051 |                  14357 | <br>| art60        | article60       |   5048218 |          824 |                   8240 | <br>| art92        | author92        |         0 |          131 |                    262 | <br>| art50        | img_out50       |   1772663 |         2305 |                   6915 | <br>+--------------+-----------------+-----------+--------------+------------------------+<br>10 rows in set (0.01 sec)<br><br><br><br>

1

mysql> select * from information_schema.index_STATISTICS limit 10; +--------------+------------+-----------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+------------+-----------------+-----------+ | art119 | img_out119 | from_message_id | 4160 | | art117 | img_out117 | from_message_id | 3324 | | art119 | article119 | forum_id_3 | 1855 | | art84 | img_out84 | from_message_id | 8363 | | art115 | article115 | forum_id_2 | 6 | | art61 | img_out61 | from_message_id | 4475 | | art90 | img_out90 | from_message_id | 42853 | | art104 | forum104 | site_id | 9660 | | art61 | forum61 | site_id | 17744 | | art50 | img_out50 | from_message_id | 19306 | +--------------+------------+-----------------+-----------+ 10 rows in set (0.00 sec) mysql> select * from information_schema.table_STATISTICS limit 10; +--------------+-----------------+-----------+--------------+------------------------+ | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES | +--------------+-----------------+-----------+--------------+------------------------+ | art86 | forum86 | 2186 | 2 | 6 | | art104 | forum104 | 9660 | 6 | 18 | | art57 | link_out57 | 8406371 | 3393 | 23751 | | mysql | user | 15 | 0 | 0 | | art53 | article53 | 7103377 | 4564 | 45640 | | art116 | thread_stats116 | 0 | 8479 | 16958 | | art50 | link_out50 | 11420660 | 2051 | 14357 | | art60 | article60 | 5048218 | 824 | 8240 | | art92 | author92 | 0 | 131 | 262 | | art50 | img_out50 | 1772663 | 2305 | 6915 | +--------------+-----------------+-----------+--------------+------------------------+ 10 rows in set (0.01 sec)

If you would like to test it here is link to patch for 5.0.67.
https://www.percona.com/blog/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 🙂