Layout of INFORMATION SCHEMA tables used for ExtSQL Introduction The INFORMATION SCHEMA (IS) implementation currently used for ExtSQL is based on MySQL 5.0.x. In this version of MySQL many of the IS tables do not exist on disk, but are created at the moment the user issues a query for their contents. This works well with ExtSQL tracking which storesusage data in memory. This is done to minimize query overhead due to statistics tracking. ExtSQL configuration does allow the DBA to specific a data storage file to be used to store/reload the data as part of a server restart. Terminology The different types of things that can interact with the server are called Classes. The list can be extended depending on the implementation. Some of these are: db - a database/schema. host - a connecting client host. user - a user. server - the server itself. Within a Class, specific instantiations are called Instances. An instance of a user could be domenic or christina. For each instance we are allowed to track individual data items called Vars. The Vars would be items such as 'bytes sent', 'bytes received', 'select queries', 'insert queries', The server Class is unique, the only instance is the server itself. IS Table Layout As show by the sample MySQL queries below. The actual number of IS tables and their column names are dependent on server configuration. As covered in the usage documentation, the DBA has control over what data is collected, how much, and the time increment to be used. For complete info on usage/syntax see: http://www.extsql.com/docs/usage_conf.txt For the examples below, the following configuration data was used to start the MySQL server: extsql_class_list="db, max-20, time-25, units-m, (Questions, Connections, Com_select), host, max-20, time-25, units-m, (Questions, Connections, Com_select), user, max-25, time-30, units-h, (Questions, Connections, Com_select, Com_update), server, max-1, time-3, units-d, (Questions, Connections, Com_select, Com_update, Qcache_hits)" IMPORTANT NOTE: In the configuration above the DBA has configured the system to track a maximum of 25 different users, storing activity by the hour, for a maximum of 25 hours of activity. Internally the data is stored in a circular array of 26 elements, elements 1-25 contain usage for a specific hour (if no usage occurs, an element is not used, element 0 contains total usage), e.g. if user 'joe' issues a 'select' statement at '2010-01-08 09:15:00' the value of the array element for joe at that hour is incremented, along with the value stored at index 0 for the joe's grand total. Since the buffer is circular, the grand total figures would not equal the sum of the time intervals stored. Obviously, the purpose of time tagging is to allow the DBA to examine a system that may have experienced a severe usage spike and determine the exact time the event occured, who was active and what queries were being issued. mysql> show tables in INFORMATION_SCHEMA like 'EXTSTATS%'; +------------------------------------------+ | Tables_in_INFORMATION_SCHEMA (EXTSTATS%) | +------------------------------------------+ | EXTSTATS_db | | EXTSTATS_host | | EXTSTATS_server | | EXTSTATS_user | +------------------------------------------+ 4 rows in set (0.00 sec) mysql> describe EXTSTATS_user; +-------------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+-------+ | user | varchar(20) | NO | | | | | hours | datetime | NO | | 0000-00-00 00:00:00 | | | Questions | bigint(10) | NO | | 0 | | | Connections | bigint(10) | NO | | 0 | | | Com_select | bigint(10) | NO | | 0 | | | Com_update | bigint(10) | NO | | 0 | | +-------------+-------------+------+-----+---------------------+-------+ 6 rows in set (0.00 sec) mysql> describe EXTSTATS_host; +-------------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+-------+ | host | varchar(20) | NO | | | | | minutes | datetime | NO | | 0000-00-00 00:00:00 | | | Questions | bigint(10) | NO | | 0 | | | Connections | bigint(10) | NO | | 0 | | | Com_select | bigint(10) | NO | | 0 | | +-------------+-------------+------+-----+---------------------+-------+ 5 rows in set (0.00 sec) NOTE: the time interval field, either 'hours' or 'minutes' above, is always a full datetime field; however, if data is being stored by the hour (as in the case of user data), restricting a query to minutes would have no additonal effect. Example Syntax mysql> SELECT user, hours, Com_select, Questions, Connections FROM EXTSTATS_user WHERE hours > '2010-01-08 6:00:00'; +----------+---------------------+------------+-----------+-------------+ | user | hours | Com_select | Questions | Connections | +----------+---------------------+------------+-----------+-------------+ | thebook | 2010-01-08 09:00:00 | 24 | 57 | 3 | | thebook | 2010-01-08 08:00:00 | 125 | 633 | 52 | | thebook | 2010-01-08 07:00:00 | 31 | 194 | 19 | | jeffhaye | 2010-01-08 08:00:00 | 0 | 22 | 2 | | jeffhaye | 2010-01-08 07:00:00 | 0 | 11 | 1 | | kidsrigh | 2010-01-08 09:00:00 | 0 | 6 | 1 | | kidsrigh | 2010-01-08 08:00:00 | 6 | 71 | 10 | | kidsrigh | 2010-01-08 07:00:00 | 0 | 53 | 16 | | national | 2010-01-08 08:00:00 | 158 | 934 | 24 | | national | 2010-01-08 07:00:00 | 45 | 961 | 17 | +----------+---------------------+------------+-----------+-------------+ 10 rows in set (0.00 sec) 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 '00:00:00' containing totals since server start. mysql> SELECT user, hours, Com_select, Questions, Connections FROM EXTSTATS_user WHERE hours = '0:00:00'; +-------------+---------------------+------------+-----------+-------------+ | user | hours | Com_select | Questions | Connections | +-------------+---------------------+------------+-----------+-------------+ | thebook | 0000-00-00 00:00:00 | 100523 | 425939 | 37579 | | jeffhaye | 0000-00-00 00:00:00 | 12117 | 69519 | 4269 | | kidsrigh | 0000-00-00 00:00:00 | 3762 | 34198 | 3622 | | national | 0000-00-00 00:00:00 | 108092 | 684275 | 17305 | | root | 0000-00-00 00:00:00 | 28961 | 259437 | 601 | | thebookdemo | 0000-00-00 00:00:00 | 22 | 61 | 17 | +-------------+---------------------+------------+-----------+-------------+ 6 rows in set (0.00 sec) NOTE: If the server has been configured to collect host data (in this case using a time increment of minutes), we query the IS table EXTSTATS_host; mysql> SELECT host, minutes, Com_select, Questions, Connections FROM EXTSTATS_host WHERE minutes > '2010-01-08 9:09:00'; +----------------------+---------------------+------------+-----------+-------------+ | host | minutes | Com_select | Questions | Connections | +----------------------+---------------------+------------+-----------+-------------+ | torch.softwareworksh | 2010-01-08 09:11:00 | 4 | 14 | 2 | | torch.softwareworksh | 2010-01-08 09:10:00 | 126 | 398 | 7 | | cpe-67-253-195-67.ro | 2010-01-08 09:11:00 | 1 | 6 | 1 | +----------------------+---------------------+------------+-----------+-------------+ 3 rows in set (0.01 sec)