Changeset: 1a3171304e2f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=1a3171304e2f
Modified Files:
monetdb5/mal/mal_runtime.c
monetdb5/mal/mal_runtime.h
monetdb5/modules/mal/sysmon.c
monetdb5/modules/mal/sysmon.h
monetdb5/modules/mal/sysmon.mal
sql/ChangeLog
sql/backends/monet5/sql.mal
sql/backends/monet5/sql_upgrades.c
sql/scripts/26_sysmon.sql
Branch: userstats
Log Message:
Adding a new monitoring function user_statistics
A table-returning function in which we keep some statistics for each SQL user.
Initial implementation was done by Martin (K.)
diffs (truncated from 317 to 300 lines):
diff --git a/monetdb5/mal/mal_runtime.c b/monetdb5/mal/mal_runtime.c
--- a/monetdb5/mal/mal_runtime.c
+++ b/monetdb5/mal/mal_runtime.c
@@ -27,9 +27,43 @@
QueryQueue QRYqueue = NULL;
+UserStats USRstats;
+lng usize = 0;
+
size_t qsize = 0, qhead = 0, qtail = 0;
static oid qtag= 1; // A unique query identifier
+static
+str
+updateUserStats(Client cntxt, lng ticks, time_t started, time_t finished, str
query)
+{
+ int idx = (int) cntxt->idx;
+
+ if(idx > MAL_MAXCLIENTS){
+ }
+
+ if(usize == 0){
+ USRstats = (UserStats) GDKzalloc( sizeof (struct USERSTAT) *
(size_t) (usize= MAL_MAXCLIENTS));
+ }
+ if(idx > usize){
+ USRstats = (UserStats) GDKrealloc( USRstats, sizeof (struct
USERSTAT) * (size_t) (usize += MAL_MAXCLIENTS));
+ }
+ if( USRstats == NULL){
+ }
+ USRstats[idx].username= strdup(cntxt->username);
+ USRstats[idx].querycount++;
+ USRstats[idx].totalticks += ticks;
+ if( ticks > USRstats[idx].maxticks && query){
+ USRstats[idx].started = started;
+ USRstats[idx].finished = finished;
+ USRstats[idx].maxticks = ticks;
+ if( USRstats[idx].maxquery)
+ GDKfree(USRstats[idx].maxquery);
+ USRstats[idx].maxquery= strdup(query);
+ }
+ return MAL_SUCCEED;
+}
+
void
mal_runtime_reset(void)
{
@@ -155,7 +189,7 @@ runtimeProfileInit(Client cntxt, MalBlkP
paused += (QRYqueue[i].status[0] == 'p' ||
QRYqueue[i].status[0] == 'r'); /* running, prepared or paused */
}
assert(qhead < qsize);
- if( (int) (qsize - paused) < MAL_MAXCLIENTS){
+ if( qsize - paused < (size_t) MAL_MAXCLIENTS){
qsize += MAL_MAXCLIENTS;
QRYqueue = (QueryQueue) GDKrealloc( QRYqueue, sizeof (struct
QRYQUEUE) * qsize);
if ( QRYqueue == NULL){
@@ -184,6 +218,7 @@ runtimeProfileInit(Client cntxt, MalBlkP
QRYqueue[qhead].workers = (int) stk->workers;
QRYqueue[qhead].status = "running";
QRYqueue[qhead].cntxt = cntxt;
+ QRYqueue[qhead].ticks = GDKusec();
stk->tag = mb->tag = QRYqueue[qhead].tag;
advanceQRYqueue();
MT_lock_unset(&mal_delayLock);
@@ -213,6 +248,7 @@ runtimeProfileFinish(Client cntxt, MalBl
mb->tag = stk->tag;
break;
}
+ updateUserStats(cntxt, QRYqueue[i].ticks,
QRYqueue[i].start, QRYqueue[i].finished, QRYqueue[i].query);
QRYqueue[i].status = "finished";
QRYqueue[i].finished = time(0);
QRYqueue[i].cntxt = 0;
diff --git a/monetdb5/mal/mal_runtime.h b/monetdb5/mal/mal_runtime.h
--- a/monetdb5/mal/mal_runtime.h
+++ b/monetdb5/mal/mal_runtime.h
@@ -35,11 +35,23 @@ typedef struct QRYQUEUE{
int idx;
int workers;
int memory;
+ lng ticks;
time_t start;
time_t finished;
} *QueryQueue;
mal_export size_t qhead, qtail, qsize;
+/* We keep a few statistics per user to identify unexpected behavior */
+typedef struct USERSTAT{
+ str username;
+ int querycount;
+ lng totalticks;
+ time_t started;
+ time_t finished;
+ lng maxticks;
+ str maxquery;
+} *UserStats;
+
typedef struct WORKINGSET{
Client cntxt;
MalBlkPtr mb;
@@ -58,4 +70,5 @@ mal_export lng getVolume(MalStkPtr stk,
mal_export lng getBatSpace(BAT *b);
mal_export QueryQueue QRYqueue;
+mal_export UserStats USRstats;
#endif
diff --git a/monetdb5/modules/mal/sysmon.c b/monetdb5/modules/mal/sysmon.c
--- a/monetdb5/modules/mal/sysmon.c
+++ b/monetdb5/modules/mal/sysmon.c
@@ -18,6 +18,116 @@
*/
str
+SYSMONstatistics(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ BAT *user, *querycount, *totalticks, *started, *finished, *query,
*maxticks;
+ bat *u = getArgReference_bat(stk,pci,0);
+ bat *c = getArgReference_bat(stk,pci,1);
+ bat *t = getArgReference_bat(stk,pci,2);
+ bat *s = getArgReference_bat(stk,pci,3);
+ bat *f = getArgReference_bat(stk,pci,4);
+ bat *q = getArgReference_bat(stk,pci,5);
+ bat *m = getArgReference_bat(stk,pci,6);
+ lng i;
+ int sz;
+ timestamp tsn;
+ str msg = MAL_SUCCEED;
+
+ (void) cntxt;
+ (void) mb;
+ sz = MAL_MAXCLIENTS; // reserve space for all possible clients.
+ user = COLnew(0, TYPE_str, sz, TRANSIENT);
+ querycount = COLnew(0, TYPE_lng, sz, TRANSIENT);
+ totalticks = COLnew(0, TYPE_lng, sz, TRANSIENT);
+ started = COLnew(0, TYPE_timestamp, sz, TRANSIENT);
+ finished = COLnew(0, TYPE_timestamp, sz, TRANSIENT);
+ query = COLnew(0, TYPE_str, sz, TRANSIENT);
+ maxticks = COLnew(0, TYPE_lng, sz, TRANSIENT);
+ if ( user == NULL || querycount == NULL || totalticks == NULL ||
started == NULL || finished == NULL || query == NULL || maxticks == NULL){
+ BBPreclaim(user);
+ BBPreclaim(started);
+ BBPreclaim(querycount);
+ BBPreclaim(totalticks);
+ BBPreclaim(finished);
+ BBPreclaim(query);
+ BBPreclaim(maxticks);
+ throw(MAL, "SYSMONqueue", SQLSTATE(HY013) MAL_MALLOC_FAIL);
+ }
+
+ MT_lock_set(&mal_delayLock);
+ for ( i = 0; i < MAL_MAXCLIENTS; i++)
+ if( USRstats[i].querycount && cntxt->user == MAL_ADMIN ){
+
+ if (BUNappend(user, USRstats[i].username, false) !=
GDK_SUCCEED) {
+ msg = createException(MAL, "SYSMONqueue",
"Username issue");
+ goto bailout;
+ }
+
+ if( USRstats[i].maxquery == 0){
+ if (BUNappend(query, "none", false) !=
GDK_SUCCEED ){
+ msg = createException(MAL,
"SYSMONqueue", "Maxquery issue 1");
+ goto bailout;
+ }
+ }else {
+ if (BUNappend(query, USRstats[i].maxquery,
false) != GDK_SUCCEED ){
+ msg = createException(MAL,
"SYSMONqueue", "Maxquery issue 2");
+ goto bailout;
+ }
+ }
+
+ /* convert number of seconds into a timestamp */
+ tsn = timestamp_fromtime(USRstats[i].started);
+ if (is_timestamp_nil(tsn)) {
+ msg = createException(MAL, "SYSMONqueue",
SQLSTATE(22003) "cannot convert time");
+ goto bailout;
+ }
+ if (BUNappend(started, &tsn, false) != GDK_SUCCEED){
+ msg = createException(MAL, "SYSMONqueue",
"Started");
+ goto bailout;
+ }
+
+ tsn = timestamp_fromtime(USRstats[i].finished);
+ if (is_timestamp_nil(tsn)) {
+ msg = createException(MAL, "SYSMONqueue",
SQLSTATE(22003) "cannot convert time");
+ goto bailout;
+ }
+ if (BUNappend(finished, &tsn, false) != GDK_SUCCEED){
+ msg = createException(MAL, "SYSMONqueue",
"Finished");
+ goto bailout;
+ }
+ if (BUNappend(querycount, &USRstats[i].querycount,
false) != GDK_SUCCEED){
+ msg = createException(MAL, "SYSMONqueue",
"querycount");
+ goto bailout;
+ }
+ if (BUNappend(totalticks, &USRstats[i].totalticks,
false) != GDK_SUCCEED){
+ msg = createException(MAL, "SYSMONqueue",
"totalticks");
+ goto bailout;
+ }
+
+ }
+ MT_lock_unset(&mal_delayLock);
+ BBPkeepref( *u =user->batCacheid);
+ BBPkeepref( *c =querycount->batCacheid);
+ BBPkeepref( *t =totalticks->batCacheid);
+ BBPkeepref( *q =query->batCacheid);
+ BBPkeepref( *s =started->batCacheid);
+ BBPkeepref( *f =finished->batCacheid);
+ BBPkeepref( *m =maxticks->batCacheid);
+ return MAL_SUCCEED;
+
+bailout:
+ MT_lock_unset(&mal_delayLock);
+ BBPunfix(user->batCacheid);
+ BBPunfix(started->batCacheid);
+ BBPunfix(querycount->batCacheid);
+ BBPunfix(totalticks->batCacheid);
+ BBPunfix(finished->batCacheid);
+ BBPunfix(query->batCacheid);
+ BBPunfix(maxticks->batCacheid);
+ return msg;
+}
+
+str
SYSMONqueue(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
BAT *tag, *sessionid, *user, *started, *status, *query, *finished,
*workers, *memory;
diff --git a/monetdb5/modules/mal/sysmon.h b/monetdb5/modules/mal/sysmon.h
--- a/monetdb5/modules/mal/sysmon.h
+++ b/monetdb5/modules/mal/sysmon.h
@@ -15,5 +15,6 @@ mal_export str SYSMONpause(Client cntxt,
mal_export str SYSMONresume(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
mal_export str SYSMONstop(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
mal_export str SYSMONqueue(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
+mal_export str SYSMONstatistics(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
#endif /* _SYSMON_H */
diff --git a/monetdb5/modules/mal/sysmon.mal b/monetdb5/modules/mal/sysmon.mal
--- a/monetdb5/modules/mal/sysmon.mal
+++ b/monetdb5/modules/mal/sysmon.mal
@@ -39,3 +39,7 @@ comment "Stop a single query a.s.a.p.";
pattern queue()(tag:bat[:lng], sessionid:bat[:int], user:bat[:str],
started:bat[:timestamp],
status:bat[:str], query:bat[:str], finished:bat[:timestamp],
workers:bat[:int], memory:bat[:int])
address SYSMONqueue;
+
+pattern statistics()(usr:bat[:str], qrycnt:bat[:lng], totalticks:bat[:lng],
started:bat[:timestamp],
+finished:bat[:timestamp], maxqry:bat[:str], maxticks:bat[:lng])
+address SYSMONstatistics;
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,3 +1,8 @@
# ChangeLog file for sql
# This file is updated with Maddlog
+* Thu Apr 2 2020 Martin Kersten <[email protected]>
+- Extended the system monitor with a table-returning function
+ user_statistics() which keeps some statistics for each SQL user, e.g. the
+ user's query count, total time spent, and maximal query seen.
+
diff --git a/sql/backends/monet5/sql.mal b/sql/backends/monet5/sql.mal
--- a/sql/backends/monet5/sql.mal
+++ b/sql/backends/monet5/sql.mal
@@ -577,6 +577,10 @@ pattern sysmon_queue()(tag:bat[:lng], se
status:bat[:str], query:bat[:str], finished:bat[:timestamp],
workers:bat[:int], memory:bat[:int])
address SYSMONqueue;
+pattern user_statistics()(usr:bat[:str], qrycnt:bat[:lng],
totalticks:bat[:lng], started:bat[:timestamp],
+finished:bat[:timestamp], maxqry:bat[:str], maxticks:bat[:lng])
+address SYSMONstatistics;
+
unsafe pattern sysmon_pause(tag:bte)
address SYSMONpause;
unsafe pattern sysmon_pause(tag:sht)
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -2311,7 +2311,19 @@ sql_update_jun2020(Client c, mvc *sql, c
"grant execute on procedure sys.pause(bigint) to
public;\n"
"grant execute on procedure sys.resume(bigint) to
public;\n"
- "grant execute on procedure sys.stop(bigint) to
public;\n");
+ "grant execute on procedure sys.stop(bigint) to
public;\n"
+
+ "create function sys.user_statistics() returns table(\n"
+ "username string,\n"
+ "querycount bigint,\n"
+ "totalticks bigint,\n"
+ "started timestamp,\n"
+ "finished timestamp,\n"
+ "maxquery string,\n"
+ "maxticks bigint\n"
+ ")\n"
+ "external name sql.sysmon_statistics;\n"
+ );
pos += snprintf(buf + pos, bufsize - pos,
"update sys.functions set system = true where system <>
true and schema_id = (select id from sys.schemas where name = 'sys')"
diff --git a/sql/scripts/26_sysmon.sql b/sql/scripts/26_sysmon.sql
--- a/sql/scripts/26_sysmon.sql
+++ b/sql/scripts/26_sysmon.sql
@@ -35,3 +35,17 @@ grant execute on procedure sys.resume(bi
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
