Hi Viktor, On Thu, Dec 08, 2011 at 04:51:03PM +0100, Viktor Rosenfeld wrote:
Hi,
Stefan Manegold wrote:
0) For easier readbility of the generated MAL plans, you might what to run your mserver5 in read-only mode, i.e., either set the database read-only via
monetdb set readonly=yes
or start mserver5 directly with extra commandline option "--readonly".
A side note: Is it correct that when I start mserver directly I can only use the demo database and monetdb user? If I try another database and/or another user I get an error message about wrong credentials.
Each mserver5 process serves only one database, the default is "demo". You can tell mserver5 via a --dbname commandline option, which database to serve. See `man mserver5` and `mserver5 --help` for more details. Please note that I mentioned the direct use of mserver5 only in case you are already familiar with that. In general, we strongly recomment to have monetdbd (merovingian) manage your database and respective server processes.
1) In case you have a multi-core/-CPU system, please first disable some of MonetDB inherent plan parallelisation (aka. "mitosis") by setting (in/via mclient) therespective optimizer pipeline as follows:
set optimizer='no_mitosis_pipe';
and then try to run your query, again, and report whether it works better or not.
I observe no difference in runtime or data written to disk when I use the no_mitosis_pipe optimizer. In fact, except for a few differently named BATs in the plan, there is only one difference I could spot:
--- q7-with-mitosis.plan 2011-12-08 13:58:37.181199000 +0100 +++ q7-without-mitosis.plan 2011-12-08 13:58:56.297199000 +0100 - X_178:bat[:oid,:lng] := batcalc.-(X_175,50:lng,true,false); + X_178:bat[:oid,:lng] := batcalc.-(X_175,50:lng);
Ah, I now releaize that you have point predicates in you query/ies, right? In that case, mitosis does not trigger since we want to (build and) exploit hash indices in the base column(s).
2) To analyze / profile the smaller case (that runs to completion), please run it in mclient prefixed with keyword "TRACE", both with the default optimizer pipeline and with mitosis disabled as described above, and report both results (i.e., the detailed profiling traces).
The problem is that these traces are *very* large. I have 3 queries which fill up the disk on the large dataset. 2 of these also cause considerable disk I/O when evaluated on the small dataset (6 MB and 100 MB respectively). The traces of these 2 queries take up 537 MB and 3.7 GB.
The 3rd query runs quickly on the small dataset and according to iostat only writes 92 kB to disk. The trace is 206 kB big, so I could post this if it would help.
I've attached the MAL plans with and without mitosis for the first query which writes 6 MB to disk.
Briefly looking at your plans, I notice that they contain BAT iterators in MAL; that's why the traces blow up. I don't have the time right now to explain what that means (the "experts will know" ;-)). It appears that they are triggered by the "between SYMMETRIC" construct. I filed a bug report / feature request abut this: http://bugs.monetdb.org/show_bug.cgi?id=2945 For now, if you would be able to omit the "SYMMETRIC", I expect at least the traces not to blow up. I cannot tell, whether also the memory usage / IO explosion would be avoided by this ...
I can also upload the traces to the web, but I'm not sure if that is useful because they are so large. Please let me know if it would help. I've also sorted the traces and can provide the statements taking up the most time.
Given the above, there is no need to share these huge traces. In case you manage to avoid the "SYMMETRIC", or once we have fixed it's translation to (efficient) MAL, and then the queries still "explode", the new (the smaller) traces would be helpful to dissect the problem further.
BTW, ticks is microseconds, correct? The output of stethoscope suggests it.
yes.
3) To check which MAL statement causes the excessive (intermediate) result creation, you can use the "stethoscope" tool to "listen" to teh server. Before running your query via mclient, start (in a separate terminal) the stethoscope tool as follows:
Except for the quick query these also produce very large results.
Do not redirect or pipe the output to a files, as this might loose some data due to a yet unresolved bug / design flaw. Rather, once the query finishes (or crashes) cut&paste the stethoscope output from the terminal to a file.
Once stethoscope is running and has connected to the server, run your query as usually (perferably again with both optimizer setting, but in separate sessions), and share the final stethoscope output with us.
For the quick query, cutting and pasting from a terminal and redirecting to a file produced the same number of lines. But if I understand correctly, you are only interested in the last couple of statements in the case of a crash?
Yes, indeed.
Can you also provide a command line to use for stethoscope, i.e. which columns to display and if I could filter for some events? Currently I'm using +trwbs.
For now, just use the default.
I will try to simplify the queries while keeping the disk I/O behavior. I hope that this will result in smaller traces.
Thanks, Viktor
Stefan -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |