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 <martin@monetdb.org> wrote:

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