Update of /cvsroot/monetdb/sql/src/sql
In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv14693/src/sql
Modified Files:
history.sql
Added Files:
times.sql
Log Message:
Commit of the intermediate state of the new history scheme.
The 'patch' to SQL to produces 'times' results have been removed.
The hardwired code and expensive SQL call to update the history
table has been changed into a more open infrastructure that will
rely on an upcall to two SQL procedures that are in control
of filtering and archiving the performance information.
Platform dependent tests may require separate approval.
Stress testing the updates in a parallel (dataflow) setting
is one of the open issues, because some spurious bat leaks
where noticed by playing with the commands in the history.sql
file only.
U history.sql
Index: history.sql
===================================================================
RCS file: /cvsroot/monetdb/sql/src/sql/history.sql,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -d -r1.1 -r1.2
--- history.sql 31 Oct 2008 19:51:11 -0000 1.1
+++ history.sql 3 Nov 2008 15:32:04 -0000 1.2
@@ -1,45 +1,51 @@
-- The query history mechanism of MonetDB/SQL relies on a few hooks
-- inside the kernel. The most important one is the SQL global
-- variable 'history', which is used by all sessions.
-
-set history=true;
+-- it is set automatically at the end of this script.
-- Whenever a query is compiled and added to the cache, it is also entered
--- into the 'queryDefinition' table using a hardwired call to 'keepQuery'.
+-- into the 'queryHistory' table using a hardwired call to 'keepQuery'.
-create table queryDefinitions(
+create table queryHistory(
id int primary key,
defined timestamp,
name string,
query string,
- parse int,
- optimize int
+ parse bigint,
+ optimize bigint
);
--- Each query call is stored in the table queryCall using 'keepCall'.
+-- Each query call is stored in the table callHistory using 'keepCall'.
-- At regular intervals the query history table should be cleaned.
-create table queryCalls(
- id int references sys.queryDefinitions(id),
+-- This can be done manually on the SQL console, or be integrated
+-- in the keepQuery and keepCall.
+create table callHistory(
+ id int references queryHistory(id),
called timestamp,
arguments string,
- elapsed int,
- inblock int,
- oublock int
+ elapsed bigint,
+ inblock bigint,
+ oublock bigint
);
create view queryLog as
-select * from queryDefinitions qd, queryCalls ql
+select * from queryHistory qd, callHistory ql
where qd.id= ql.id;
-- the signature is used in the kernel, don't change it
create procedure keepQuery(
- id int,
+ i int,
query string,
- parse int,
- optimize int)
+ parse bigint,
+ optimize bigint)
begin
- insert into queryDefinitions
- values(id, now(),user, query, parse, optimize);
+ declare cnt int;
+ set cnt = (select count(*) from queryHistory where id= i);
+ if cnt = 0
+ then
+ insert into queryHistory
+ values(i, now(),user, query, parse, optimize);
+ end if;
end;
-- the signature is used in the kernel, don't change it
@@ -47,16 +53,18 @@
id int,
called timestamp,
arguments string,
- elaps int,
- inblock int,
- oublock int)
+ elapsed bigint,
+ inblock bigint,
+ oublock bigint)
begin
- insert into queryCalls
- values(id, called, arguments, elaps, inblock,oublock);
+ insert into callHistory
+ values(id, called, arguments, elapsed, inblock,oublock);
end;
--- the remainder are samples to test in isolation.
-call keepQuery(1,'select 1;',100,20);
-call keepCall(1,now(),'user.s0_0(1)', 912,0,0);
-call keepCall(1,now(),'user.s0_0(2)', 899,0,0);
-select * from queryLog;
+-- The remainder are initialization calls to make sure
+-- the functions are compiled into MAL and stored away.
+-- If they are not ran, then the upcalls will fail.
+call keepQuery(0,'--init history',0,0);
+call keepCall(0,now(),'--init history',0,0,0);
+
+set history=true;
--- NEW FILE: times.sql ---
-- Provide a simple equivalent for the UNIX times command
create procedure times()
external name sql.times;
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
