Joe Daly: Drizzle Statistics Scoreboard (logging_stats plugin)
Drizzle has a Scoreboard for collecting statistics (logging_stats plugin) I introduced this about 6 months ago with little user documentation unless you are into reading header files which have a descriptive writeup. Below is a detailed explanation of whats included from the user perspective and if your really interested some design details. A good portion of the user level statistics was inspired by the google v2 patch for MySQL.
Tables
drizzle> use data_dictionary;
drizzle> describe CUMULATIVE_USER_STATS;
+---------------------+---------+-------+---------+-----------------+-----------+
| Field | Type | Null | Default | Default_is_NULL | On_Update |
+---------------------+---------+-------+---------+-----------------+-----------+
| USER | VARCHAR | FALSE | | FALSE | |
| BYTES_RECEIVED | VARCHAR | FALSE | | FALSE | |
| BYTES_SENT | VARCHAR | FALSE | | FALSE | |
| DENIED_CONNECTIONS | VARCHAR | FALSE | | FALSE | |
| LOST_CONNECTIONS | VARCHAR | FALSE | | FALSE | |
| ACCESS_DENIED | VARCHAR | FALSE | | FALSE | |
| CONNECTED_TIME_SEC | VARCHAR | FALSE | | FALSE | |
| EXECUTION_TIME_NSEC | VARCHAR | FALSE | | FALSE | |
| ROWS_FETCHED | VARCHAR | FALSE | | FALSE | |
| ROWS_UPDATED | VARCHAR | FALSE | | FALSE | |
| ROWS_DELETED | VARCHAR | FALSE | | FALSE | |
| ROWS_INSERTED | VARCHAR | FALSE | | FALSE | |
+---------------------+---------+-------+---------+-----------------+-----------+
drizzle> describe CUMULATIVE_SQL_COMMANDS;
+----------------+---------+-------+---------+-----------------+-----------+
| Field | Type | Null | Default | Default_is_NULL | On_Update |
+----------------+---------+-------+---------+-----------------+-----------+
| USER | VARCHAR | FALSE | | FALSE | |
| COUNT_SELECT | BIGINT | FALSE | | FALSE | |
| COUNT_DELETE | BIGINT | FALSE | | FALSE | |
| COUNT_UPDATE | BIGINT | FALSE | | FALSE | |
| COUNT_INSERT | BIGINT | FALSE | | FALSE | |
| COUNT_ROLLBACK | BIGINT | FALSE | | FALSE | |
| COUNT_COMMIT | BIGINT | FALSE | | FALSE | |
| COUNT_CREATE | BIGINT | FALSE | | FALSE | |
| COUNT_ALTER | BIGINT | FALSE | | FALSE | |
| COUNT_DROP | BIGINT | FALSE | | FALSE | |
| COUNT_ADMIN | BIGINT | FALSE | | FALSE | |
+----------------+---------+-------+---------+-----------------+-----------+
In addition the functionality of “show status” and “show global status” resides in the GLOBAL_STATUS, and SESSION_STATUS tables which are constructed from the Scoreboard. The SESSION_STATEMENTS, and GLOBAL_STATEMENTS tables also reside in the plugin.
Desgin Overview
The Scoreboard is a pre-allocated vector of vectors of ScoreboardSlots. It can be thought of as a vector of buckets where each bucket contains pre-allocated ScoreboardSlots. To determine which bucket gets used for recording statistics the modulus operator is used on the session_id. This will result in a bucket to search for a unused ScoreboardSlot.
Startup Options
logging_stats_max_user_count – this is the maximum number of users that statistics will be aggregated for, if this is exceeded new users will not be logged. This should be allocated sufficiently large enough to not reach the maximum, its memory usage is minimal.
logging_stats_bucket_count – the number of buckets to allocate, see Design Overview on bucket description.
logging_stats_scoreboard_size – the number of ScoreboardSlots to pre-allocate, this represents approximately the number of concurrent sessions the system can have. If the system were to exceed this statistics would not be logged for a particular session. It should be allocated to be sufficiently larger then the maximum number of concurrent sessions that is anticipated.
Future Options
This is to be determined based on what users want, the infrastructure is there now to make adding and aggregating new statistics fairly easy.
URL: http://www.8bitsofbytes.com/?p=20
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
