Re: Merge table performance on single machine with 4 cores

Hai,

On 19 Mar 2017, at 12:49, Manish gupta wrote:

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?

the main (only?) advantage of using merge table on a single machine to be able to simultaneously load data into multiple partitions of a merge table. this is not for increased performance (because it might actually hurt performance), but merely to allow concurrent load.

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.

what do you mean exactly with "using MonetDB in multi user scenario”? From what I have seen (on Linux), 4 cores is fairly minimal for MonetDB. 1 core was clearly too few, even for a single MonetDB user. If you’re going to using 1 core to let a single MonetDB instance serving multiple users, I’m not so sure about the performance. Although it’s contrary to your experiences for now.

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.

The trace outputs are definitely helpful. They help understand where the time went. That’s usually the first question in such cases. You can also general tomograph [1] graphs to see why MonetDB doesn’t use 100%. Regards, Jennie [1] http://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph

Regards, Mohit.

On Sun, Mar 19, 2017 at 2:42 PM, Martin Kersten 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

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