Extended usage statistics for SQL was designed to provide DBAs with a quick means of monitoring database activity by individual user, database, host, or even connection.   Present SQL monitoring tools only allow gross monitoring at the server level -- by making changes to the MySQL® & PostgreSQL servers we are able to provide much more detailed info, including historical data, for all these categories... AND, make it easily available from the SQL command line.

We try to answer some simple questions below, consult or Support or Documentation Centers for more complete info.  The data tracked below is based on the MySQL compatible version.
 
 What is ExtSQL?


Pardon us for being repeating ourselves. ExtSQL is NOT a new database server. It is not an external performance monitoring tool. It is a significant set of independently developed software additions and patches to the existing source code of both MySQL & PostgreSQL. It provides a new set of monitoring language features (built into the core server) which greatly ease usage monitoring and accounting.

ExtSQL provides a standardized set of commands and concepts which extend SQL with some new features geared toward making the life of database administrators easier.

It is also unique in that we plan on offering source patches and binaries for older versions of both MySQL and PostgreSQL. The version numbering for ExtSQL mimics the compatible versions for both servers. e.g. ExtSQL-5.0.45 is compatible with a MySQL-5.0.45 installation.
 
What does ExtSQL 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     | Com_select  | Com_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              | Com_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)      | Com_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 'bandala' HISTORY LIMIT 3;
+---------+-------------+------------+---------+------------+
|      db | minutes     | Com_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     |
What about INFORMATION SCHEMA support?


As many of you are aware INFORMATION SCHEMA is already part of the SQL standard and its purpose was to make SQL databases and object more "self describing".   Software Workshop is a member of the INCITS H2 Technical committee (representing the US in SQL discussions) and when our proposal was initially presented there was some discussion that if implemented, it would be part of INFORMATION SCHEMA.

We already have an example working implementation for MySQL as briefly demonstrated below:

mysql> use INFORMATION_SCHEMA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
   ## Note:  the additional tables configured by the DBA for ExtSQL are
   ## prefaced with 'EXTSTATS_'
| EXTSTATS_condb                        |
| EXTSTATS_conuser                      |
| EXTSTATS_db                           |
| EXTSTATS_host                         |
| EXTSTATS_server                       |
| EXTSTATS_user                         |
   ## End of added tables
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |

mysql> SELECT user, minutes, Com_select, Queries FROM EXTSTATS_user 
              WHERE TIMEDIFF(now(), minutes) < '10:00';
+---------+---------------------+------------+---------+
| user    | minutes             | Com_select | Queries |
+---------+---------------------+------------+---------+
| thebook | 2008-07-19 09:28:00 | 2          | 4       |
| thebook | 2008-07-19 09:27:00 | 3          | 3       |
| thebook | 2008-07-19 09:26:00 | 1          | 1       |
| thebook | 2008-07-19 09:25:00 | 2          | 73      |
| root    | 2008-07-19 09:53:00 | 4          | 4       |
| root    | 2008-07-19 09:52:00 | 0          | 1       |
| root    | 2008-07-19 09:50:00 | 1          | 1       |
## NOTE: unlike the special time handling as part of SHOW STATISTICS, no changes were made
## to internal handling of INFORMATION SCHEMA queries.  This makes the full syntax of the SQL
## parser available, BUT.... it exposes that data is recorded in a circular buffer by time
## interval (minutes in our example), with the time '0000-00-00' containing totals since
## server start.
mysql> SELECT user, minutes, Com_select, Queries FROM EXTSTATS_user WHERE user='thebook' LIMIT 5;
+---------+---------------------+------------+---------+
| user    | minutes             | Com_select | Queries |
+---------+---------------------+------------+---------+
| thebook | 2008-07-19 09:28:00 | 2          | 4       |
| thebook | 2008-07-19 09:27:00 | 3          | 3       |
| thebook | 2008-07-19 09:26:00 | 1          | 1       |
| thebook | 2008-07-19 09:25:00 | 2          | 73      |
| thebook | 2008-07-18 16:44:00 | 0          | 14      |
 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:

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

activates the new features and controls how much memory will be allocated, 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 every user.

The five classes of objects presently configured are 'host', 'user', 'db', 'conuser' (connections from user@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 the configuration option, the extended statistics features are completely disabled and have almost no run-time impact.  The ExtSQL then runs as the unmodified version.
                                 

 Is installation difficult?


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

  1. 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).

  2. Stop your server.

  3. Copy the new extsql-my-bin supporting extended statistics into the preceding location.

  4. 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.

  5. Change your my.cnf file to activate the extended usage statistics features.

  6. Restart your server.  You should see activation messages printed in the server log.

  7. Enjoy your new capabilities!
     
 What about performance impact? Are they reliable?  


We have run them through extensive internal testing and on busy production DB servers in multi-processor systems.  While tracking 18 different status variables the increase in server load was measured at approximately 5%.

The changes made to create ExtSQL do internal sanity checking and will disable themselves if a problem appears to have occurred.  There is always a chance of a serious failure and a 'seg fault' occurring, but in ALL our testing we have never seen corruption of actual server data and no server crashes have occurred in recent releases.

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 report or control extended statistics.