[Monetdb-developers] Another big group by query

Martin Kersten Martin.Kersten at cwi.nl
Sun Apr 12 20:37:22 CEST 2009


Stefan Manegold wrote:
> On Sun, Apr 12, 2009 at 03:15:36AM +0200, Stefan de Konink wrote:
>> Hi All,
>>
>>
>> Might be better to go by the mailinglist instead of adding many people 
>> to the CC.
> 
> Good. Exactly that is why we have mailing lists ;-)
> 
>> Currently I am running the latest CVS code disabled mitosis 
>> and am running the following code:
>>
>> CREATE TABLE "sys"."way_nds" (
>>          "way" int NOT NULL,
>>          "idx" int NOT NULL,
>>          "to_node" int,
>>          CONSTRAINT "pk_way_nds" PRIMARY KEY ("way", "idx")
>> );
>>
>> select count(way), way, to_node from way_nds group by way, to_node 
>> having count(way) > 1;
>>
>> The table contains about 362420451 rows, and I realised too late there 
>> could be many results comming from it.
> 
> I don't know, whether the fact that is does/might create "many result" is
> related to the "hanging" situation at all ...
> 
>> top - 03:12:15 up 1 day, 17:28,  8 users,  load average: 1.00, 1.00, 1.00
>> Tasks: 193 total,   1 running, 192 sleeping,   0 stopped,   0 zombie
>> Cpu(s):  0.0%us,  0.1%sy,  0.0%ni, 94.6%id,  5.2%wa,  0.0%hi,  0.0%si, 
>> 0.0%st
>> Mem:  66113464k total, 39353192k used, 26760272k free,   326968k buffers
>> Swap: 134215032k total,    94284k used, 134120748k free,  8489128k cached
>>
>>    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 
>>
>> 11856 konink    20   0 51.5g  34g 5.9g S  0.3 54.6  45:34.93 mserver5
>>
>>
>> But again the load is low, the memory empty and it seems to do so few. 
> 
> load is 1.0 --- i.e., one thrad full throttle --- unless it is a "fake" load
no full throttle, just 1 process... :(
> due to a Dead/Dummy/zombie process that is (e.g.) waiting for I/O --- in
> top, type "1" to get the summaries for all cores and "H" to list all threads
> and "u"+[Enter] to list processes of all users; then "P" to order on CPU
> usage and report; then "M" to oder on memory usage (34g res is IMHO
> everything but "empty") and report.
> 
>> Is there something that would be interesting to look at?
> 
> Basically, it would be great to know which MAL statement is currently being
> executed on which inputs --- prefixing your SQL statement with TRACE is one
> option (though I don't know whether that flushes it's output instantly; if
> not, we should consider adding such functionality excatly for such "hanging"
> situations --- in any case, it list only a statement after it has finished;
yes, because then you know the statistics.
> hence, comparing the TRACE output with that of EXPLAIN is required); running
> in the MAL debugger (prefix SQL statement with DEBUG) is the other option
> (though I don't know, whether the MAL debugger has some "automatic single
> stepping/tracing" functionality ...)
in the MAL debugger, type 'help'  et voila, it has a trace option ;)

You can trace the execution even for a server started already using
the stethoscope.

http://monetdb.cwi.nl/projects/monetdb/MonetDB/Documentation/Stethoscope.html
> 
> Stefan
> 
>>
>>
>> Stefan
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by:
>> High Quality Requirements in a Collaborative Environment.
>> Download a free trial of Rational Requirements Composer Now!
>> http://p.sf.net/sfu/www-ibm-com
>> _______________________________________________
>> Monetdb-developers mailing list
>> Monetdb-developers at lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/monetdb-developers
>>
>>
> 





More information about the developers-list mailing list