Introduction to Extended Statistics

        Documentation

        Downloads

        Support

What do they do?

Almost any variable you can see in the SHOW STATUS command can be tracked. Probably the simplest way to explain is via simple examples:

sql> SHOW STATISTICS Com_select, Com_insert, Queries FROM user;
+----------+---------+--------+----------+
| user     | select  | insert | Queries  |
+----------+---------+--------+----------+
| bandala  | 8302675 | 95973  | 23153940 |
| sandymao | 1702812 | 6205   | 3829023  |
| ponnetli | 24909   | 4784   | 95646    |

sql> SHOW STATISTICS Com_select, Queries, Bytes_sent FROM host;
+-------------------+----------+----------+------------+
| host              | select   | Queries  | Bytes_sent |
+-------------------+----------+----------+------------+
| db2.thebook.com   | 17715223 | 44224076 | 4143634981 |
| lathe.thebook.com | 2738061  | 9743215  | 3913397495 |
| telkomadsl.co.za  | 195      | 5390     | 539604     |

sql> SHOW STATISTICS Com_select, Queries, Bytes_sent FROM conuser WHERE Queries > 10000;
+----------------------------+---------+----------+------------+
| conuser   (user@host)      | select  | Queries  | Bytes_sent |
+--------- ------------------+---------+----------+------------+
| bandala@db2.thebook.com    | 8306726 | 23163320 | 3439850933 |
| sandymao@db2.thebook.com   | 1704040 |  3831803 | 3365501841 |
| ponnetli@lathe.thebook.com |   24920 |    95662 |  156529077 |

sql> show statistics Com_select, Queries, Bytes_sent FROM db LIKE 'mandala' HISTORY LIMIT 3;
+---------+-------------+--------+---------+------------+
|      db | minutes     | select | Queries | Bytes_sent |
+---------+-------------+--------+---------+------------+
| bandala | 11/20 13:56 | 216    |     382 | 318343     |
| bandala | 11/20 13:55 | 642    |    1618 | 1386347    |
| bandala | 11/20 13:54 | 280    |     699 | 646855     |
+---------+-------------+--------+---------+------------+

How are they configured?

The DBA has complete control over the amount of data collected. Configuration info is placed in the appropriate config file used to configure server operation at startup. For example in MySQL's my.cnf:

statistics_class_list="user, max-100, time-120, units-m, (Com_select, Com_insert, Queries),
host, max-100, time-50, units-h, (Com_select, Queries, Bytes_sent, Bytes_received)"

activates Extended Statistics and not only how much memory will be allocated, but also time units for historical data, and also which STATUS variables will be recorded, i.e. we are tracking at most 100 users and will record historical data for the last 120 minutes of activity (if no user records activity during an interval, no storage is used). We will record both the number of SELECT and INSERT queries, along with total queries issued by the user.

The five classes of objects presently configured are 'host', 'user', and 'db' . There is also support for 'conuser' (connections by user from host) and 'condb' (connection to db from host). There is also a 'server' class that records summary information for the entire server, similar to SHOW STATUS.

Without this line, the Extended Statistics are completely disabled and have almost no run-time impact.

Is installation difficult?

The Extended Statistics were designed to be easy to install and SAFE to install. We understand how nervous any DBA can get when making software changes to a server. The process is:

Insure you have properly running MySQL installation already installed. Take note of where your mysqld (the actual server daemon) is installed and make a backup copy of the daemon and also your DBs (just in case).
Stop your server.
Copy the new mysqld supporting Extended Statistics into the preceding location.
Start your server. Since you have not activated the package, you should see normal server operation. This confirms the binary is built properly for your version of MySQL, OS, and Architecture.
Change your my.cnf file to activate Extended Statistics.
Restart your server. You should see activation messages printed in the server log.
Enjoy your new capabilities!

Are they reliable? Did you change anything existing in MySQL?

We have run them through extensive internal testing and on busy production DB servers in multi-processor systems. The Extended Statistics do internal sanity checking and will disable themselves if a problem appears to have occurred. There is always a chance of a serious problem and a 'seg fault' occurring, but in our testing we have never seen corruption of actual server data.

A special effort was made to insure no changes were made to how MySQL presently tracks information presented in SHOW STATUS. Indeed, that capability is useful in checking gross number reported by a "SHOW STATISTICS * from server" (which also give summary numbers).

What about security?

We hear you! Unless you explicitly specify otherwise in the my.cnf file, only the 'root' user is allowed to give commands that use Extended Statistics.