Pardon us for 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.
 

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

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

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

sql> SHOW STATISTICS Com_select, Questions, Com_update FROM db LIKE 'bandala' HISTORY LIMIT 3;
+---------+-------------+------------+-----------+------------+
|      db | minutes     | Com_select | Questions | Com_update |
+---------+-------------+------------+-----------+------------+
| bandala | 11/20 13:56 | 216        |     382   | 318343     |
| bandala | 11/20 13:55 | 642        |    1618   | 1386347    |
| bandala | 11/20 13:54 | 280        |     699   | 646855     |

SHOW STATISTICS ( * | Var list) FROM Class [WHERE Var ( '<' | '>' | '=' ) num]
[LIKE 'Instance pattern'] [ORDER BY Var] [HISTORY] [LIMIT rows_or_time]

Because this is a new SQL addition, if you type just SHOW STATISTICS, you will get some usage and ExtSQL subsystem information -- not just an error message. In normal SQL you could have expected:

SHOW STATISTICS * FROM user WHERE user like '%joe%' and Bytes_sent > 500

For now LIKE is a separate clause in the syntax that matches just Class instances. Present syntax is limited in the WHERE clause and ORDER BY is also being worked on. This is available if INFORMATION SCHEMA is supported in your base version of the ExtSQL server (see below).
 

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 5.0.X as briefly demonstrated below:

mysql> use INFORMATION_SCHEMA;

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                            |

| EXTSTATS_condb                        |
| EXTSTATS_conuser                      |
| EXTSTATS_db                           |
| EXTSTATS_host                         |
| EXTSTATS_server                       |
| EXTSTATS_user 

| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |

mysql> SELECT user, minutes, Com_select, Questions FROM EXTSTATS_user 
              WHERE TIMEDIFF(now(), minutes) < '10:00';
+---------+---------------------+------------+-----------+
| user    | minutes             | Com_select | Questions |
+---------+---------------------+------------+-----------+
| domenic | 2008-07-19 09:28:00 | 2          | 4         |
| domenic | 2008-07-19 09:27:00 | 3          | 3         |
| domenic | 2008-07-19 09:26:00 | 1          | 1         |
| domenic | 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         |

mysql> SELECT user, minutes, Com_select, Questions FROM EXTSTATS_user WHERE user='domenic' LIMIT 5;
+---------+---------------------+------------+---------+
| user    | minutes             | Com_select | Questions |
+---------+---------------------+------------+---------+
| domenic | 2008-07-19 09:28:00 | 2          | 4       |
| domenic | 2008-07-19 09:27:00 | 3          | 3       |
| domenic | 2008-07-19 09:26:00 | 1          | 1       |
| domenic | 2008-07-19 09:25:00 | 2          | 73      |
| domenic | 2008-07-18 16:44:00 | 0          | 14      |

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, Questions),
                   host, max-100, time-50, units-h, (Com_select, Com_update, Slow_queries)"

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 server then runs as the unmodified version.
                                 

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!
    

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% - 20% depending on the amount of data being tracked and recorded.

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

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. Additonal users may be allowed with the following:

extsql_users='domenic,john'