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:
Our port includes:
Here is example of new tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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 🙂
Resources
RELATED POSTS
