Thanks Martin for explaining. Are you suggesting this expected behavior,
since I am running on single machine I dont mind moving away from merge
table architecture to base table?
I have one more experiment, where I ran monetdb server with single thread
( --set "gdk_nr_threads 1" ) and with ( --set "gdk_nr_threads 4" ). Again
contrary to my expectation same query on base table is faster with 1 thread
( around 25% faster ). We are using MonetDB in multi user scenario, so I
dont mind using --set "gdk_nr_threads 1" setting as dedicated single core
for one user query.
But, I am not sure how multi thread query is slower than single thread
execution. I also saw that for multi thread mode, all cores were busy only
for 6-7 seconds, and rest of the time CPU utilization was ~25%. So looks
like threads were running very small part in parallel, and merging in main
thread took all the time.
I have trace outputs for both modes, if they can be helpful.
Regards,
Mohit.
On Sun, Mar 19, 2017 at 2:42 PM, Martin Kersten
Hi
Simply because MonetDB will already use all cores whenever it can. This means less inter-process communication and less contention for resources. Furthermore, there might be decisions being taken due to lack of knowledge about the partitions, which should be set using the ANALYZE command.
regards, Martin
On 19/03/2017 04:25, Manish gupta wrote:
Dear all, I am checking the performance of merge table vs single table on single machine with 4 cores. The fact table contains ~10million records. I am using # MonetDB 5 server v11.25.9 "Dec2016-SP2" on windows machine ( core i7 16GB physical ram ). I have created a merge table which contains fact table and 3 blank copies of fact table. I am seeing surprisingly slow performance of merge table queries compared to base table queries.
Base table query ================= sql>select sum(f_1_1_1), count(*), f_1_1_7,f_1_1_5 from t3760_279_1_1 group by f_1_1_7,f_1_1_5 order by sum(f_1_1_1) desc limit 10; +-----------+------+---------+---------+ | L2 | L5 | f_1_1_7 | f_1_1_5 | +===========+======+=========+=========+ | 125885351 | 14 | SALE | 8084869 | | 125629268 | 15 | SALE | 6852518 | | 121733416 | 16 | SALE | 5440605 | | 110328868 | 13 | SALE | 4842411 | | 100848236 | 11 | SALE | 8068509 | | 100719291 | 16 | SALE | 5822736 | | 93127875 | 11 | SALE | 6529612 | | 91034094 | 13 | SALE | 3401293 | | 90766181 | 10 | SALE | 8084900 | | 88848574 | 10 | SALE | 2660811 | +-----------+------+---------+---------+ 10 tuples (44.2s)
Merge Table Query ================= sql>select sum(f_1_1_1), count(*), f_1_1_7,f_1_1_5 from mt279_1_1 group by f_1_1_7,f_1_1_5 order by sum(f_1_1_1) desc limit 10; +-----------+------+---------+---------+ | L2 | L5 | f_1_1_7 | f_1_1_5 | +===========+======+=========+=========+ | 125885351 | 14 | SALE | 8084869 | | 125629268 | 15 | SALE | 6852518 | | 121733416 | 16 | SALE | 5440605 | | 110328868 | 13 | SALE | 4842411 | | 100848236 | 11 | SALE | 8068509 | | 100719291 | 16 | SALE | 5822736 | | 93127875 | 11 | SALE | 6529612 | | 91034094 | 13 | SALE | 3401293 | | 90766181 | 10 | SALE | 8084900 | | 88848574 | 10 | SALE | 2660811 | +-----------+------+---------+---------+ 10 tuples (1m 51s)
For the same results ( because merge table is containing the base table and 3 blank copies of base table ), merge table query is taking more than 2.5 times the base table runtime. Why is it so?
Thanks, Mohit
_______________________________________________ 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