Re: MonetDB performance disappointing

Hi, I can give you the schema. Regards, Martin

Am 30.06.2015 um 13:26 schrieb Martin Kersten :

Hi

Ok. Understood. You always should run the query in cold and hot mode, i.e. run it multiple times within the same session. If necessary, MonetDB will create auxiliary index structures during the first run.

You can definitely run SQL commands from a file. In mclient you have the \< option or you use a command line argument.

regards, Martin

On 30/06/15 13:17, Martin Schwitalla wrote:

...

Hi,

I can`t do that, because the database contains sensitive data. I`m not allowed to give someone else the data. I don`t know if the schema would help…

I think of something else…Currently I`m executing the query only once and look at the time. Could it help if I run the query many 10 or 100 times in order, so that the caches and memory can be filled with the right data? I think that this could help the OS to fill it with the needed data. But I don`t see any possibility to run sql statements from a file. Am I`m missing something?

Best regards, Martin

...

Am 28.06.2015 um 13:14 schrieb Niels Nes mailto:Niels.Nes@cwi.nl>:

On Fri, Jun 26, 2015 at 02:14:18PM +0200, Martin Schwitalla wrote:

...

Well ok, the values for the between statement were wrong. It should be between 189 and 368 :) The result is now right, but the execution time stays the same or is even minimal longer than before. I have uploaded a trace to my dropbox of the query.

https://www.dropbox.com/s/q2j9g0gsvaylkw6/query_trace_.txt?dl=0

Martin

Could you send the output of plan, ie plan followed by your query? That should return the relational plan.

It seems the join order is incorrect (not optimal) and MonetDB seems to have some problems with the many 'OR's in your query.

Niels

...

Regards, Martin

Am 26.06.2015 um 13:45 schrieb Martin Schwitalla < MSchwitalla1@gmx.de mailto:MSchwitalla1@gmx.de>:

Hi,

the prefilter makes it faster, but somehow i get three times more tupels in the result than before. How is that possible?

((calls.sample_id BETWEEN 226 AND 369) AND calls.sample_id IN (227, 230, 233, 234, 237, 190, 195, 198, 199, 203, 270, 273, 276, 189, 343, 366, 367, 368))) AS anon_2

Best regards, Martin

Am 25.06.2015 um 22:08 schrieb Martin Kersten < martin@monetdb.org mailto:martin@monetdb.org>:

Hi

On 25/06/15 17:38, Martin Schwitalla wrote: ...

I added some primary and foreign key constraints and set the database to readonly, but it didn`t do much. The trace tells me that some joins take the most time and some subselects also take much time. But nothing besides that. The query has 10 joins.

... Indeed a complex, probably generated SQL query. The trace information of the expensive joins and thetaselects would be my first target.

An expensive join might typically result if its hash support does not fit in memory, which will cause disk accesses. The predicate (calls.sample_id IN (227, 230, 233,.... and the like probably produce to multiple thetaselect that are merged in the end. It might help (worth trying) to put a range pre-filter (call.sample between 227 and 368 and calls.sample in (227,....))

The fact that minimal-pipe is better then the default-pipe indicates a lot of competition for scarce resources during execution.

regards, Martin _______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

...

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098sip:4098@sip.cwi.nl url:https://www.cwi.nl/people/nielse-mail:Niels.Nes@cwi.nl mailto:Niels.Nes@cwi.nl _______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list