[MonetDB-users] problems with the interpretation of the query history
Hi. I’m a new MonetDB user and I use the MonetDB server v5.14.2 with the MonetDB/SQL module v2.32.2 on a windows xp platform.
I have some problems with the interpretation of the queryhistory and the callhistory.
My first question is: What is the meaning of the parse and the optimize attributes in the queryhistory. Is the optimize attribute used to store the time which the front-end needs to parse the sql statement into a mal plan respectively is the optimize attribute used to store the time used for the optimization done for the mal plan. I’m asking because couldn’t find an explanation in the sql manual.
My next questions concern the xtime and the rtime attributes of the callhistory. Sometimes for some queries the xtime is 0 usec. Does this indicate that the query was already executed once before and therefore there isn’t a preparation time needed for the result set?
Sometimes for some queries only the rtime is 0 usec but the the xtime is > 0 usec. What does this means? There is a preparation time for a result set but there is no time needed to construct and return the result set to the user. For me this makes no sense.
Sometimes there is the xtime and the rtime 0 usec. Does this mean that the query never runs in the database engine? Does somebody know the reason for this behavior?
Lust but not least in the sql manual you can read that all storage is in K/M/G bytes. But where can I find out if K bytes M bytes or G bytes are used?
Thank you for any help
Gerrard
(I apologize for any grammar and spelling mistakes in this posting but my English is not the best.)
Hello,
Welcome to the world of MonetDB. Most of the development takes place on Linux platforms, so I may not answer all your questions right away.
Gerrard_56 wrote:
Hi. I’m a new MonetDB user and I use the MonetDB server v5.14.2 with the MonetDB/SQL module v2.32.2 on a windows xp platform.
I have some problems with the interpretation of the queryhistory and the callhistory.
My first question is: What is the meaning of the parse and the optimize attributes in the queryhistory.
Parsing is the time the SQL parser takes to take input and built internal structure. Optimizer is the sum of the times spent by the optimizers to prepare for execution.
Is the optimize attribute used to store the time which the front-end needs to parse the sql statement into a mal plan respectively is the optimize attribute used to store the time used for the optimization done for the mal plan. I’m asking because couldn’t find an explanation in the sql manual.
thanks for reminding.
My next questions concern the xtime and the rtime attributes of the callhistory. Sometimes for some queries the xtime is 0 usec. Does this indicate that the query was already executed once before and therefore there isn’t a preparation time needed for the result set?
create table callHistory( id wrd references queryHistory(id), -- references query plan ctime timestamp, -- time the first statement was executed arguments string, xtime bigint, -- time from the first statement until result export rtime bigint, -- time to ship the result to the client foot bigint, -- footprint for all bats in the plan memory bigint, -- storage size of intermediates created tuples wrd, -- number of tuples in the result set inblock bigint, -- number of physical blocks read oublock bigint -- number of physical blocks written );
The comment says it all. I don't know the timing opportunities on Windows.
Sometimes for some queries only the rtime is 0 usec but the the xtime is > 0 usec. What does this means? There is a preparation time for a result set but there is no time needed to construct and return the result set to the user. For me this makes no sense.
hmm... have to check that code, because it may be interfering with parallel execution. Which means that the information is delivered too quick.
Sometimes there is the xtime and the rtime 0 usec. Does this mean that the query never runs in the database engine? Does somebody know the reason for this behavior?
Lust but not least in the sql manual you can read that all storage is in K/M/G bytes. But where can I find out if K bytes M bytes or G bytes are used?
They will appear when you perform tracing the execution. Overall, you dont have sight on the amount of data is being used.
Thank you for any help
Martin Kersten
Gerrard
(I apologize for any grammar and spelling mistakes in this posting but my English is not the best.)
np, it not my first language either
Martin Kersten wrote:
Hello,
Welcome to the world of MonetDB. Most of the development takes place on Linux platforms, so I may not answer all your questions right away.
Gerrard_56 wrote:
Hi. I’m a new MonetDB user and I use the MonetDB server v5.14.2 with the MonetDB/SQL module v2.32.2 on a windows xp platform.
I have some problems with the interpretation of the queryhistory and the callhistory.
My first question is: What is the meaning of the parse and the optimize attributes in the queryhistory.
Parsing is the time the SQL parser takes to take input and built internal structure. Optimizer is the sum of the times spent by the optimizers to prepare for execution.
Is the optimize attribute used to store the time which the front-end needs to parse the sql statement into a mal plan respectively is the optimize attribute used to store the time used for the optimization done for the mal plan. I’m asking because couldn’t find an explanation in the sql manual.
thanks for reminding.
My next questions concern the xtime and the rtime attributes of the callhistory. Sometimes for some queries the xtime is 0 usec. Does this indicate that the query was already executed once before and therefore there isn’t a preparation time needed for the result set?
create table callHistory( id wrd references queryHistory(id), -- references query plan ctime timestamp, -- time the first statement was executed arguments string, xtime bigint, -- time from the first statement until result export rtime bigint, -- time to ship the result to the client foot bigint, -- footprint for all bats in the plan memory bigint, -- storage size of intermediates created tuples wrd, -- number of tuples in the result set inblock bigint, -- number of physical blocks read oublock bigint -- number of physical blocks written );
The comment says it all. I don't know the timing opportunities on Windows.
Gerrard 56 wrote:
Sometimes for some queries only the rtime is 0 usec but the the xtime is
0
usec. What does this means? There is a preparation time for a result set but there is no time needed to construct and return the result set to the user. For me this makes no sense.
hmm... have to check that code, because it may be interfering with parallel execution. Which means that the information is delivered too quick.
Is there any alternative to the query history in order to get some reliable timing. Because as I wrote before in my posting “sql execution time” the option with the –t argument doesn’t work well.
Should be the timing obtained by the –t argument the same as the xtime + rtime? Because in most cases the timing obtained by the –t argument is > xtime + rtime. For example the –t time for the tpch query nr. 3 is 15824.172 msec the xtime is 3031000 usec and the rtime is 11265000 usec
Sometimes there is the xtime and the rtime 0 usec. Does this mean that the query never runs in the database engine? Does somebody know the reason for this behavior?
Lust but not least in the sql manual you can read that all storage is in K/M/G bytes. But where can I find out if K bytes M bytes or G bytes are used?
They will appear when you perform tracing the execution. Overall, you dont have sight on the amount of data is being used.
Thank you for any help
Martin Kersten
Gerrard
(I apologize for any grammar and spelling mistakes in this posting but my English is not the best.)
np, it not my first language either
begin:vcard fn:Martin Kersten n:Kersten;Martin org:CWI (Centrum Wiskunde & Informatica) adr:;;Science Park 123;Amsterdam;;1098 XG;Netherlands email;internet:Martin.Kersten@cwi.nl tel;work:CWI (Centrum Wiskunde & Informatica) tel;fax:+31 20 592 4199 x-mozilla-html:FALSE version:2.1 end:vcard
This SF.Net email is sponsored by the Verizon Developer Community Take advantage of Verizon's best-in-class app development support A streamlined, 14 day to market process makes app distribution fast and easy Join now and get one step closer to millions of Verizon customers http://p.sf.net/sfu/verizon-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (2)
-
Gerrard_56 -
Martin Kersten