OurSQL Episode 201: What are you doing?

This week we discuss user statistics in the MariaDB and Percona forks of MySQL.

Events
DB Hangops - every other Thursday at 11 am Pacific time.
The MySQL, NoSQL and Cloud Conference and Expo will be taking place Nov 13 and 14 in Buenos Aires Argentina and Nov 17 in Córdoba Argentina.
Upcoming MariaDB events
Upcoming MySQL events
Upcoming conferences with MySQL content

Training
MariaDB Trainings
Tungsten University trainings
Oracle Trainings
Percona Trainings

User Statistics
user_stats in Percona
user statistics in MariaDB
OurDelta documentation on user statistics

To turn on user statistics:
mysql> SET GLOBAL userstat = 1;

To turn on thread statistics (Percona only):
mysql> SET GLOBAL thread_statistics = 1;

Flush commands to reset user statistics:

FLUSH CLIENT_STATISTICS
FLUSH INDEX_STATISTICS
FLUSH TABLE_STATISTICS
FLUSH USER_STATISTICS
FLUSH THREAD_STATISTICS --  Only Percona

SHOW commands to see user statistics:

SHOW CLIENT_STATISTICS
SHOW INDEX_STATISTICS
SHOW TABLE_STATISTICS
SHOW USER_STATISTICS
SHOW THREAD_STATISTICS --  Only Percona

client statistics and user_statistics fields (also thread_statistics on Percona):
total_connections
concurrent_connections
connected_time (seconds)
busy_time (microseconds)
cpu_time (microseconds)
bytes_received
bytes_sent
binlog_bytes_written
rows_read (MariaDB)
rows_sent (MariaDB)
rows_deleted (MariaDB)
rows_inserted (MariaDB)
rows_updated (MariaDB)
rows_fetched (Percona)
rows_updated (Percona)
table_rows_read (Percona)
select_commands
update_commands
other_commands
commit_transactions
rollback_transactions
denied_connections
lost_connections
access_denied
empty_queries
total_ssl_connections
max_statement_time_exceeded (MariaDB)

Example:

select user, rows_read, rows_sent, select_commands, update_commands, other_commands from information_schema.user_statistics;
+----------------+---------+---------+---------------+---------------+--------------+
| user | rows_read | rows_sent | select_commands | update_commands | other_commands |
+----------------+---------+---------+---------------+---------------+--------------+
| amarokuser | 300708 | 215518 | 66115 | 65 | 0 |
| gerry | 437 | 2097 | 63 | 0 | 26 |
| collectd | 0 | 895801 | 82936 | 0 | 0 |
| debian-sys-maint | 0 | 0 | 0 | 0 | 2 |
+----------------+---------+---------+---------------+---------------+--------------+
4 rows in set (0.00 sec)

TABLE_STATISTICS table:
table_schema
table_name
rows_read
rows_changed
rows_changed_x_indexes

Example of rows_changed_x_indexes:

select * from information_schema.table_statistics where table_name = 'ti';
+------------+----------+---------+------------+----------------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+------------+----------+---------+------------+----------------------+
| my_test | ti | 13 | 1 | 2 |
+------------+----------+---------+------------+----------------------+
1 row in set (0.00 sec)

INDEX_STATISTICS table:
Amarok player

select * from table_statistics order by table_schema, table_name;
+------------+---------------+---------+------------+----------------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+------------+---------------+---------+------------+----------------------+
| amarokdb | albums | 1040 | 1 | 4 |
| amarokdb | artists | 41 | 0 | 0 |
| amarokdb | bookmarks | 35 | 1 | 1 |
| amarokdb | bookmark_groups | 2 | 0 | 0 |
| amarokdb | composers | 40 | 0 | 0 |
| amarokdb | genres | 40 | 0 | 0 |
| amarokdb | images | 26 | 1 | 2 |
| amarokdb | lyrics | 10 | 10 | 10 |
| amarokdb | statistics | 64 | 17 | 119 |
| amarokdb | tracks | 149689 | 0 | 0 |
| amarokdb | urls | 149681 | 0 | 0 |
| amarokdb | years | 40 | 0 | 0 |
| mysql | help_category | 2 | 0 | 0 |
| mysql | help_keyword | 3 | 0 | 0 |
| mysql | help_topic | 5 | 0 | 0 |
| mysql | proc | 416 | 0 | 0 |
| mysql | user | 11 | 0 | 0 |
+------------+---------------+---------+------------+----------------------+
17 rows in set (0.00 sec)

index_statistics table:
table_schema
table_name
index_name
rows_read

select * from information_schema.index_statistics where table_schema = 'amarokdb';
+------------+----------+------------------+---------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+------------+----------+------------------+---------+
| amarokdb | statistics | PRIMARY | 17 |
| amarokdb | urls | uniqueid | 39 |
| amarokdb | artists | artists_name | 1 |
| amarokdb | tracks | PRIMARY | 17 |
| amarokdb | urls | PRIMARY | 149642 |
| amarokdb | images | PRIMARY | 26 |
| amarokdb | genres | PRIMARY | 40 |
| amarokdb | lyrics | PRIMARY | 10 |
| amarokdb | statistics | statistics_url | 47 |
| amarokdb | albums | albums_artist | 969 |
| amarokdb | albums | PRIMARY | 70 |
| amarokdb | composers | PRIMARY | 40 |
| amarokdb | bookmarks | PRIMARY | 1 |
| amarokdb | tracks | tracks_url | 149656 |
| amarokdb | artists | PRIMARY | 40 |
| amarokdb | tracks | tracks_album | 8 |
| amarokdb | albums | albums_name_artist | 1 |
| amarokdb | years | PRIMARY | 40 |
+------------+----------+------------------+---------+
18 rows in set (0.00 sec)

Ear candy
How to enable GTIDs in MariaDB 10
How to replicate a slave from 2 different masters in MariaDB

At the movies
This episode in At the Movies we highlight Ike Walker of Flite presenting "Assembling the perfect MySQL Toolbox"

At the movies

On to Where You Can See Us
Where you can see us
Sheeri will be at the Boston MySQL User Group, which meets the 2nd Monday of the month at MIT.

Gerry will be at the Seattle MySQL Meetup, which meets on the 2nd Monday of every month at the Twitter offices in Seattle.

Gerry will be traveling to Argentina to speak at the MySQL NoSQL and Cloud Conference & Expo November 13th and 14th in Buenos Aires, and November 17th in Córdoba.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast


Fatal error: Class 'CToolsCssCache' not found in /home/sheeri/technocation/includes/cache.inc on line 32