The Software Workshop inc. - software that fits! â„¢                  ExtSQL - Extended SQL
.
 
. Home   Contact Us    login
.
 
 
 

    Home

    Documentation

    Downloads

    FAQ

    News & Events

    Privacy Policy

    Report Problem

    Support

    Terms & Conditions


Quick Feedback!
This is a new project,
feedback is welcome
(and will be read!)

Introduction

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 our Support or Documentation Centers for more complete info. 

 What is ExtSQL?


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.
 

What does ExtSQL do?

Almost any variable you can see in the SHOW STATUS command can be tracked. The examples below are based on the MySQL compatible version.:

Show number of select and insert statements given by all users since server start:
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      |
Show number of select, updates, and total queries issued by all client hosts which have connected to this server:
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       |
Show number of select, updates, and total queries from all users@client host machines where more than 10,000 queries were issued:
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 |
Show number of select, updates, and total queries for DB bandala for the past three minutes:
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     |
A brief description of the full command syntax:

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

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 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                            |
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, 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         |
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, 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      |

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

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

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

extsql_users='domenic,john'

 

[Home]    [FAQ List]    [About Us]    [Contact Us]   
 

NOTE: MySQL® is a registered trademark of Sun Microsystems. 
ExtSQL® is registered trademark of Software Workshop Inc.
ExtSQL is a separate product and should not be confused with MySQL
or PostgreSQL. It contains independently developed additional features,
released under the GPL,v.2.

©Copyright 1996-2009 Software Workshop Inc. 
1