Giving monetdb a fair chance
Hi, I am benchmarking different DB alternatives for our BI application. We are using Mondrian/JPivot on a dataset with ~300 millions row, and several parent/child dimensions. We are running some 50 real life scenario (ie 50 differents cubes using parts of this dataset) and measuring user response time on a server with several stripped Sata3 SSD, 32 GB ram and a i7-3820 CPU @ 3.60GHz (6 cores). During the tests, Monetdb performed by far the worst of Postgresql, VectorWise and (obvioulsy) MonetDB. Things that take 10 seconds with Postgresql take 450 second with monetdb... This was not expected at all, so looking at the server, I saw that MonetDB was consuming all memory (why not), including swap : bad idea I think. I was expecting MonetDB to memory mapped files when needed, real memory, but not use swap as it it was real memory... So here is my question : with a (quite) big dataset, 32GB ram, is there anything I can do to help MonetDB behave according to expectancies ? (maybe I should use ulimit ?). Thanks for your input, Franck
Hi Without insight into your hardware, benchmark queries and database schema for the row stores, little can be said. A typical pitfall could be to use row-based queries such as "SELECT * FROM tableexpr", which would call for expensive tuple reconstruction of all columns. Another explanation can come from a multidimensional index maintained in a rowstore to speedup grouping. Last but not least, it could be grouping order or skewed data distribution. MonetDB provides some tools to see where time goes, which may give a hint. http://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/Stethoscope Your table sizes reported is not extreme at all. Do you use a machine with 32G RAM or a smaller box? regards, Martin On 7/11/13 10:47 AM, Franck Routier wrote:
Hi,
I am benchmarking different DB alternatives for our BI application. We are using Mondrian/JPivot on a dataset with ~300 millions row, and several parent/child dimensions.
We are running some 50 real life scenario (ie 50 differents cubes using parts of this dataset) and measuring user response time on a server with several stripped Sata3 SSD, 32 GB ram and a i7-3820 CPU @ 3.60GHz (6 cores).
During the tests, Monetdb performed by far the worst of Postgresql, VectorWise and (obvioulsy) MonetDB. Things that take 10 seconds with Postgresql take 450 second with monetdb... This was not expected at all, so looking at the server, I saw that MonetDB was consuming all memory (why not), including swap : bad idea I think. I was expecting MonetDB to memory mapped files when needed, real memory, but not use swap as it it was real memory... So here is my question : with a (quite) big dataset, 32GB ram, is there anything I can do to help MonetDB behave according to expectancies ?
(maybe I should use ulimit ?).
Thanks for your input,
Franck
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Without insight into your benchmark queries and database schema for the row stores, little can be said. A typical pitfall could be to use row-based queries such as "SELECT * FROM tableexpr", which would call for expensive tuple reconstruction of all columns. Another explanation can come from a multidimensional index maintained in a rowstore to speedup grouping. Last but not least, it could be grouping order or skewed data distribution. MonetDB provides some tools to see where time goes, which may give a hint. http://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/Stethoscope Your table sizes reported is not extreme at all. regards, Martin On 7/11/13 10:47 AM, Franck Routier wrote:
Hi,
I am benchmarking different DB alternatives for our BI application. We are using Mondrian/JPivot on a dataset with ~300 millions row, and several parent/child dimensions.
We are running some 50 real life scenario (ie 50 differents cubes using parts of this dataset) and measuring user response time on a server with several stripped Sata3 SSD, 32 GB ram and a i7-3820 CPU @ 3.60GHz (6 cores).
During the tests, Monetdb performed by far the worst of Postgresql, VectorWise and (obvioulsy) MonetDB. Things that take 10 seconds with Postgresql take 450 second with monetdb... This was not expected at all, so looking at the server, I saw that MonetDB was consuming all memory (why not), including swap : bad idea I think. I was expecting MonetDB to memory mapped files when needed, real memory, but not use swap as it it was real memory... So here is my question : with a (quite) big dataset, 32GB ram, is there anything I can do to help MonetDB behave according to expectancies ?
(maybe I should use ulimit ?).
Thanks for your input,
Franck
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Franck, thanks for the feedback! Which version of MonetDB are you using? Which OS are you using? Did you install a pre-built binary version of MonetDB, or are did you compile it yourself from sources (HG or released tarball?)? If the latter, which configure options did you use? And, as Martin suggested, would you be able to share your database schema, queries, and possibly even data with us (or at least some more data characteristic)? Alternatively, could you profile the behavior using Stetoscope (as Martin suggests) or by simply prefixing your SQL queries with TRACE and share the (performance/profiling-) results? Thanks! Stefan ----- Original Message -----
Hi
Without insight into your benchmark queries and database schema for the row stores, little can be said. A typical pitfall could be to use row-based queries such as "SELECT * FROM tableexpr", which would call for expensive tuple reconstruction of all columns. Another explanation can come from a multidimensional index maintained in a rowstore to speedup grouping. Last but not least, it could be grouping order or skewed data distribution.
MonetDB provides some tools to see where time goes, which may give a hint. http://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/Stethoscope
Your table sizes reported is not extreme at all.
regards, Martin
On 7/11/13 10:47 AM, Franck Routier wrote:
Hi,
I am benchmarking different DB alternatives for our BI application. We are using Mondrian/JPivot on a dataset with ~300 millions row, and several parent/child dimensions.
We are running some 50 real life scenario (ie 50 differents cubes using parts of this dataset) and measuring user response time on a server with several stripped Sata3 SSD, 32 GB ram and a i7-3820 CPU @ 3.60GHz (6 cores).
During the tests, Monetdb performed by far the worst of Postgresql, VectorWise and (obvioulsy) MonetDB. Things that take 10 seconds with Postgresql take 450 second with monetdb... This was not expected at all, so looking at the server, I saw that MonetDB was consuming all memory (why not), including swap : bad idea I think. I was expecting MonetDB to memory mapped files when needed, real memory, but not use swap as it it was real memory... So here is my question : with a (quite) big dataset, 32GB ram, is there anything I can do to help MonetDB behave according to expectancies ?
(maybe I should use ulimit ?).
Thanks for your input,
Franck
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Hi, Le 11/07/2013 11:53, Stefan Manegold a écrit :
Which version of MonetDB are you using?
Which OS are you using?
Did you install a pre-built binary version of MonetDB, or are did you compile it yourself from sources (HG or released tarball?)? We are using MonetDB Database Server Toolkit v1.1 (Feb2013-SP3), installed on Ubuntu server 12.04LTS. We are using the packages you kindly provide in your repository.
And, as Martin suggested, would you be able to share your database schema, queries, and possibly even data with us (or at least some more data characteristic)?
I will look at this. I guess the schema can be shared. As for the data, it cannot as it relates the hospital medical data.
Alternatively, could you profile the behavior using Stetoscope (as Martin suggests) or by simply prefixing your SQL queries with TRACE and share the (performance/profiling-) results?
Yep, I will do this. Thanks, Franck
Ok, I have an example query, that takes 4 sec in MonetDB. This is considered long, as Postgresql performs the same task in some 200 ms... I have used trace to get detailed information on what happens. But this output is really detailed : 2500 lines ! Here it is : http://pastebin.com/EmhERAeF For information, here the Pg explain plan, if that can be of any help http://explain.depesz.com/s/StM The point is that I don't know what to look for, so visualy parsing this output is quite hard :-) Any hint ? Thanks a lot, Franck
Hi Franck,
thanks for the info!
Given the complexity of your query, it would be very helpful for us if you could also send us the results of running your query prefixed with PLAN and EXPLAIN.
Then we can check which kind of plan we generate from your query.
Looking only at the most expensive operations in your trace (see below) does not instantly ring a bell with me ...
Thanks,
Stefan
| 1986607 | X_757= Ok, I have an example query, that takes 4 sec in MonetDB.
This is considered long, as Postgresql performs the same task in some
200 ms... I have used trace to get detailed information on what happens.
But this output is really detailed : 2500 lines ! Here it is :
http://pastebin.com/EmhERAeF
For information, here the Pg explain plan, if that can be of any help
http://explain.depesz.com/s/StM The point is that I don't know what to look for, so visualy parsing
this
output is quite hard :-)
Any hint ? Thanks a lot,
Franck _______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list --
| Stefan.Manegold@CWI.nl | DB Architectures (DA) |
| www.CWI.nl/~manegold/ | Science Park 123 (L321) |
| +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Here is the result of explain : http://pastebin.com/MKt4XxdF Here is the result for plan : sql>plan select temps_mois.rfoperyea as c0, temps_mois.rfoperdmo as c1, rfovsn.rfovsnide as c2, rfoadv_1.rfoadvsup as c3, rsaaev_2.rsaaevsup as c4, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco' then dwhinv.dwhinvqte else 0 end)) as m0, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = '16' then dwhinv.dwhinvqte else 0 end)) as m1 from rfoper_temps_mois as temps_mois, dwhinv as dwhinv, rfovsn as rfovsn, rfoadv as rfoadv_1, rsaaev as rsaaev_2 where (temps_mois.rfoper___rforefide = 'CHUL') and (dwhinv.dwhinv___rforefide = 'CHUL' and ( more>more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco') or more>more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = '16'))) and dwhinv.dwhinvdtd = temps_mois.rfoperdtd and temps_mois.rfoperyea in ('2011', '2012') and temps_mois.rfoperdmo in ('01', '02') and (rfovsn___rforefide = 'CHUL') and dwhinv.dwhinv___rfovsnide = rfovsn.rfovsnide and rfovsn.rfovsnide = '201204_reel' and (rfoadv_1.rfoadv___rfovdeide='MCO' AND rfoadv_1.rfoadvrvs=1 AND rfoadv_1.rfoadv___rforefide= 'CHUL') and dwhinv.dwhinv_d2rfodstide = rfoadv_1.rfoadvinf and rfoadv_1.rfoadvsup in ('CHIR', 'MED', 'OBS') and (rsaaev_2.rsaaev___rsavedide='PRISE_EN_CHARGE' AND rsaaev_2.rsaaevrvs=1 AND rsaaev_2.rsaaev___rforefide= 'CHUL') and dwhinv.dwhinv___rsaedtide = rsaaev_2.rsaaevinf and rsaaev_2.rsaaevsup = 'REG_HOSPI_SEANCE' group by temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide, rfoadv_1.rfoadvsup, rsaaev_2.rsaaevsup; SELECT: identifier 'more' unknown sql>plan select temps_mois.rfoperyea as c0, temps_mois.rfoperdmo as c1, rfovsn.rfovsnide as c2, rfoadv_1.rfoadvsup as c3, rsaaev_2.rsaaevsup as c4, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco' then dwhinv.dwhinvqte else 0 end)) as m0, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = '16' then dwhinv.dwhinvqte else 0 end)) as m1 from rfoper_temps_mois as temps_mois, dwhinv as dwhinv, rfovsn as rfovsn, rfoadv as rfoadv_1, rsaaev as rsaaev_2 where (temps_mois.rfoper___rforefide = 'CHUL') and (dwhinv.dwhinv___rforefide = 'CHUL' and ( more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco') or more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = '16'))) and dwhinv.dwhinvdtd = temps_mois.rfoperdtd and temps_mois.rfoperyea in ('2011', '2012') and temps_mois.rfoperdmo in ('01', '02') and (rfovsn___rforefide = 'CHUL') and dwhinv.dwhinv___rfovsnide = rfovsn.rfovsnide and rfovsn.rfovsnide = '201204_reel' and (rfoadv_1.rfoadv___rfovdeide='MCO' AND rfoadv_1.rfoadvrvs=1 AND rfoadv_1.rfoadv___rforefide= 'CHUL') and dwhinv.dwhinv_d2rfodstide = rfoadv_1.rfoadvinf and rfoadv_1.rfoadvsup in ('CHIR', 'MED', 'OBS') and (rsaaev_2.rsaaev___rsavedide='PRISE_EN_CHARGE' AND rsaaev_2.rsaaevrvs=1 AND rsaaev_2.rsaaev___rforefide= 'CHUL') and dwhinv.dwhinv___rsaedtide = rsaaev_2.rsaaevinf and rsaaev_2.rsaaevsup = 'REG_HOSPI_SEANCE' group by temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide, rfoadv_1.rfoadvsup, rsaaev_2.rsaaevsup; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | rel | +===============================================================================================================================================================================+ | project ( | | | group by ( | | | | project ( | | | | | join ( | | | | | | join ( | | | | | | | join ( | | | | | | | | join ( | | | | | | | | | join ( | | | | | | | | | | join ( | | | | | | | | | | | select ( | | | | | | | | | | | | table(axabas.dwhinv) [ dwhinv.dwhinv___rforefide NOT NULL, dwhinv.dwhinv___rfodomide NOT NULL, dwhinv.dwhinv___rfovsnide NOT NULL, dwhinv.dwhinv___rfoind | : ide NOT NULL, dwhinv.dwhinvqte, dwhinv.dwhinvdtd NOT NULL, dwhinv.dwhinv_d2rfodstide, dwhinv.dwhinv___rsaedtide, dwhinv.%TID% NOT NULL ] COUNT : | | | | | | | | | | ) [ dwhinv.dwhinv___rforefide NOT NULL = varchar(32)[char(4) "CHUL"], dwhinv.dwhinv___rfodomide NOT NULL = varchar(32)[char(8) "CLINIQUE"], (dwhinv.dwhinv_ | : __rfoindide NOT NULL = varchar(32)[char(18) "cli_nb_entrees_mco"]) or (dwhinv.dwhinv___rfoindide NOT NULL = varchar(32)[char(2) "16"]), dwhinv.dwhinv___rfoindide NOT NULL in : : (varchar(32)[char(18) "cli_nb_entrees_mco"], varchar(32)[char(2) "16"]) ], : | | | | | | | | | | select ( | | | | | | | | | | | | table(axabas.rfoper_temps_mois) [ rfoper_temps_mois.rfoper___rforefide as temps_mois.rfoper___rforefide, rfoper_temps_mois.rfoperyea as temps_mois.rfoper | : yea, rfoper_temps_mois.rfoperdmo as temps_mois.rfoperdmo, rfoper_temps_mois.rfoperdtd as temps_mois.rfoperdtd, rfoper_temps_mois.%TID% NOT NULL as temps_mois.%TID% ] COUNT : | | | | | | | | | | ) [ temps_mois.rfoper___rforefide = varchar(32)[char(4) "CHUL"], temps_mois.rfoperyea in (varchar(32)[char(4) "2011"], varchar(32)[char(4) "2012"]), temps_ | : mois.rfoperdmo in (varchar(32)[char(2) "01"], varchar(32)[char(2) "02"]) ] : | | | | | | | | | ) [ dwhinv.dwhinvdtd NOT NULL = temps_mois.rfoperdtd ], | | | | | | | | | | select ( | | | | | | | | | | | table(axabas.rfovsn) [ rfovsn.rfovsn___rforefide NOT NULL, rfovsn.rfovsnide NOT NULL, rfovsn.%TID% NOT NULL ] COUNT | | | | | | | | | | ) [ rfovsn.rfovsn___rforefide NOT NULL = varchar(32)[char(4) "CHUL"], rfovsn.rfovsnide NOT NULL = varchar(32)[char(11) "201204_reel"] ] | | | | | | | | | ) [ dwhinv.dwhinv___rfovsnide NOT NULL = rfovsn.rfovsnide NOT NULL ], | | | | | | | | | table(axabas.rfoade) [ rfoade.rfoade___rforefide NOT NULL as ade2.rfoade___rforefide, rfoade.rfoade___rfovdeide NOT NULL as ade2.rfoade___rfovdeide, rfoade.rfo | : ade_i_rfodstide NOT NULL as ade2.rfoade_i_rfodstide, rfoade.rfoadervs NOT NULL as ade2.rfoadervs, rfoade.rfoadegch NOT NULL as ade2.rfoadegch, rfoade.rfoadedrt NOT NULL as a : : de2.rfoadedrt, rfoade.%TID% NOT NULL as ade2.%TID% ] COUNT : | | | | | | | ) [ dwhinv.dwhinv_d2rfodstide = ade2.rfoade_i_rfodstide NOT NULL ], | | | | | | | | select ( | | | | | | | | | table(axabas.rfoade) [ rfoade.rfoade___rforefide NOT NULL as ade1.rfoade___rforefide, rfoade.rfoade___rfovdeide NOT NULL as ade1.rfoade___rfovdeide, rfoade.rfo | : ade_i_rfodstide NOT NULL as ade1.rfoade_i_rfodstide, rfoade.rfoadervs NOT NULL as ade1.rfoadervs, rfoade.rfoadegch NOT NULL as ade1.rfoadegch, rfoade.rfoadedrt NOT NULL as a : : de1.rfoadedrt, rfoade.%TID% NOT NULL as ade1.%TID% ] COUNT : | | | | | | | ) [ ade1.rfoadervs NOT NULL = int[tinyint "1"], ade1.rfoade___rfovdeide NOT NULL = varchar(32)[char(3) "MCO"], ade1.rfoade___rforefide NOT NULL = varchar(32)[cha | : r(4) "CHUL"], ade1.rfoade_i_rfodstide NOT NULL in (varchar(32)[char(4) "CHIR"], varchar(32)[char(3) "MED"], varchar(32)[char(3) "OBS"]) ] : | | | | | | ) [ ade2.rfoade___rforefide NOT NULL = ade1.rfoade___rforefide NOT NULL, ade2.rfoade___rfovdeide NOT NULL = ade1.rfoade___rfovdeide NOT NULL, ade2.rfoadervs NOT NU | : LL = ade1.rfoadervs NOT NULL, ade2.rfoadegch NOT NULL >= ade1.rfoadegch NOT NULL, ade2.rfoadedrt NOT NULL <= ade1.rfoadedrt NOT NULL ], : | | | | | | table(axabas.rsaaed) [ rsaaed.rsaaed___rforefide NOT NULL as aed2.rsaaed___rforefide, rsaaed.rsaaed___rsavedide NOT NULL as aed2.rsaaed___rsavedide, rsaaed.rsaaedr | : vs NOT NULL as aed2.rsaaedrvs, rsaaed.rsaaed_i_rsaedtide NOT NULL as aed2.rsaaed_i_rsaedtide, rsaaed.rsaaedgch NOT NULL as aed2.rsaaedgch, rsaaed.rsaaeddrt NOT NULL as aed2. : : rsaaeddrt, rsaaed.%TID% NOT NULL as aed2.%TID% ] COUNT : | | | | | ) [ dwhinv.dwhinv___rsaedtide = aed2.rsaaed_i_rsaedtide NOT NULL ], | | | | | | select ( | | | | | | | table(axabas.rsaaed) [ rsaaed.rsaaed___rforefide NOT NULL as aed1.rsaaed___rforefide, rsaaed.rsaaed___rsavedide NOT NULL as aed1.rsaaed___rsavedide, rsaaed.rsaaedr | : vs NOT NULL as aed1.rsaaedrvs, rsaaed.rsaaed_i_rsaedtide NOT NULL as aed1.rsaaed_i_rsaedtide, rsaaed.rsaaedgch NOT NULL as aed1.rsaaedgch, rsaaed.rsaaeddrt NOT NULL as aed1. : : rsaaeddrt, rsaaed.%TID% NOT NULL as aed1.%TID% ] COUNT : | | | | | ) [ aed1.rsaaedrvs NOT NULL = int[tinyint "1"], aed1.rsaaed___rforefide NOT NULL = varchar(32)[char(4) "CHUL"], aed1.rsaaed___rsavedide NOT NULL = varchar(32)[char(1 | : 5) "PRISE_EN_CHARGE"], aed1.rsaaed_i_rsaedtide NOT NULL = varchar(32)[char(16) "REG_HOSPI_SEANCE"] ] : | | | | ) [ aed2.rsaaed___rforefide NOT NULL = aed1.rsaaed___rforefide NOT NULL, aed2.rsaaed___rsavedide NOT NULL = aed1.rsaaed___rsavedide NOT NULL, aed2.rsaaedrvs NOT NULL = | : aed1.rsaaedrvs NOT NULL, aed2.rsaaedgch NOT NULL >= aed1.rsaaedgch NOT NULL, aed2.rsaaeddrt NOT NULL <= aed1.rsaaeddrt NOT NULL ] : | | | ) [ temps_mois.rfoperyea, temps_mois.rfoperdmo, temps_mois.%TID% NOT NULL, dwhinv.dwhinv___rfodomide NOT NULL, dwhinv.dwhinv___rfoindide NOT NULL, dwhinv.dwhinvqte, dwhi | : nv.%TID% NOT NULL, rfovsn.rfovsnide NOT NULL, rfovsn.%TID% NOT NULL, ade1.rfoade___rforefide NOT NULL as rfoadv_1.rfoadv___rforefide, ade1.rfoade___rfovdeide NOT NULL as rfo : : adv_1.rfoadv___rfovdeide, ade1.rfoadervs NOT NULL as rfoadv_1.rfoadvrvs, ade1.rfoade_i_rfodstide NOT NULL as rfoadv_1.rfoadvsup, ade2.rfoade_i_rfodstide NOT NULL as rfoadv_1 : : .rfoadvinf, ade1.%TID% NOT NULL, ade2.%TID% NOT NULL, aed1.rsaaed___rforefide NOT NULL as rsaaev_2.rsaaev___rforefide, aed1.rsaaed___rsavedide NOT NULL as rsaaev_2.rsaaev___ : : rsavedide, aed1.rsaaedrvs NOT NULL as rsaaev_2.rsaaevrvs, aed1.rsaaed_i_rsaedtide NOT NULL as rsaaev_2.rsaaevsup, aed2.rsaaed_i_rsaedtide NOT NULL as rsaaev_2.rsaaevinf, aed : : 1.%TID% NOT NULL, aed2.%TID% NOT NULL ] : | | ) [ temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide NOT NULL, rfoadv_1.rfoadvsup NOT NULL, rsaaev_2.rsaaevsup NOT NULL ] [ temps_mois.rfoperyea, temps_mois.rf | : operdmo, rfovsn.rfovsnide NOT NULL, rfoadv_1.rfoadvsup NOT NULL, rsaaev_2.rsaaevsup NOT NULL, sys.sum no nil (sys.ifthenelse(sys.ifthenelse(sys.isnull(sys.and(sys.=(dwhinv.d : : whinv___rfodomide NOT NULL, varchar(32)[char(8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(18) "cli_nb_entrees_mco"]))), boolean "false", sys.a : : nd(sys.=(dwhinv.dwhinv___rfodomide NOT NULL, varchar(32)[char(8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(18) "cli_nb_entrees_mco"]))), dwhin : : v.dwhinvqte, decimal(18,6)[tinyint "0"])) as L1, sys.sum no nil (sys.ifthenelse(sys.ifthenelse(sys.isnull(sys.and(sys.=(dwhinv.dwhinv___rfodomide NOT NULL, varchar(32)[char( : : 8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(2) "16"]))), boolean "false", sys.and(sys.=(dwhinv.dwhinv___rfodomide NOT NULL, varchar(32)[char( : : 8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(2) "16"]))), dwhinv.dwhinvqte, decimal(18,6)[tinyint "0"])) as L2 ] : | ) [ temps_mois.rfoperyea as L.c0, temps_mois.rfoperdmo as L.c1, rfovsn.rfovsnide NOT NULL as L.c2, rfoadv_1.rfoadvsup NOT NULL as L.c3, rsaaev_2.rsaaevsup NOT NULL as L.c4, | : L1 as L1.m0, L2 as L2.m1 ] : +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 35 tuples (3.899ms) sql>
Hi Franck These numbers add not up to 450 seconds. I have the suspicion that you have encountered the Linux problem with HugePageMap management. This can lead to unexpected stalls. Sjoerd/Niels can give you the details to avoid it. regards, Martin On 7/11/13 6:40 PM, Stefan Manegold wrote:
Hi Franck,
thanks for the info!
Given the complexity of your query, it would be very helpful for us if you could also send us the results of running your query prefixed with PLAN and EXPLAIN. Then we can check which kind of plan we generate from your query.
Looking only at the most expensive operations in your trace (see below) does not instantly ring a bell with me ...
Thanks, Stefan
| 1986607 | X_757=
[36915397] := algebra.subselect(X_550= :bat[:oid,:str][36915434],X_476= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1950533 | X_756= [36915434] := algebra.subselect(X_548= :bat[:oid,:str][36915434],X_474= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1702679 | X_758= [36915434] := algebra.subselect(X_552= :bat[:oid,:str][36915434],X_478= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1612725 | X_755= [36904002] := algebra.subselect(X_546= :bat[:oid,:str][36915434],X_472= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1286316 | X_787= [18846579] := algebra.subselect(X_517= :bat[:oid,:str][36915434],X_774= [36915397],A8="CLINIQUE",A8="CLINIQUE",true,true,false | | 1243002 | X_754= [36915434] := algebra.subselect(X_544= :bat[:oid,:str][36915434],X_470= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1134769 | X_846= [18846579] := algebra.subselect(X_517= :bat[:oid,:str][36915434],X_774= [36915397],A10="CLINIQUE",A10="CLINIQUE",true,true,fal | | 1132200 | X_759= [36915437] := algebra.subselect(X_554= :bat[:oid,:str][36915437],X_480= :bat[:oid,:oid][36915437],A7="CHUL",A7="CHUL",true,tru | | 1105372 | X_841= [4137747] := algebra.subselect(X_507= :bat[:oid,:str][36915434],X_769= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 914882 | X_752= [36915434] := algebra.subselect(X_540= :bat[:oid,:str][36915434],X_466= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 890052 | X_753= [36915434] := algebra.subselect(X_542= :bat[:oid,:str][36915434],X_468= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 864087 | X_843= [5897070] := algebra.subselect(X_511= :bat[:oid,:str][36915434],X_771= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 764170 | X_844= [5690737] := algebra.subselect(X_513= :bat[:oid,:str][36915434],X_772= [36904002],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 719337 | X_842= [6526518] := algebra.subselect(X_509= :bat[:oid,:str][36915434],X_770= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 718974 | X_789= [3696387] := algebra.subselect(X_519= :bat[:oid,:str][36915434],X_775= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 685787 | X_783= [5690737] := algebra.subselect(X_513= :bat[:oid,:str][36915434],X_772= [36904002],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 648988 | X_847= [3696387] := algebra.subselect(X_519= :bat[:oid,:str][36915434],X_775= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 612241 | X_777= [4137747] := algebra.subselect(X_507= :bat[:oid,:str][36915434],X_769= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 541833 | X_781= [5897070] := algebra.subselect(X_511= :bat[:oid,:str][36915434],X_771= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 508678 | X_779= [6526518] := algebra.subselect(X_509= :bat[:oid,:str][36915434],X_770= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 464423 | X_785= [4803050] := algebra.subselect(X_515= :bat[:oid,:str][36915434],X_773= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 432781 | X_845= [4803050] := algebra.subselect(X_515= :bat[:oid,:str][36915434],X_773= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 401519 | X_791= [1531879] := algebra.subselect(X_521= :bat[:oid,:str][36915437],X_776= [36915437],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 373662 | X_848= [1531879] := algebra.subselect(X_521= :bat[:oid,:str][36915437],X_776= [36915437],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 237526 | X_877= [1057] := algebra.subselect(X_486= :bat[:oid,:str][36915434],X_867= [18846579],A11="16",A11="16",true,true,false); | | 220168 | X_814= [12845] := algebra.subselect(X_486= :bat[:oid,:str][36915434],X_806= [18846579],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco | | 140860 | X_1539= [17073] := sql.projectdelta(X_1044= [17073],X_678= :bat[:oid,:str][36915434],X_693= :bat[:oid,:oid][0],X_694= [7336] := sql.projectdelta(X_1047= [7336],X_684= :bat[:oid,:str][36915434],X_700= :bat[:oid,:oid][0],X_701= [7412] := algebra.subselect(X_481= :bat[:oid,:str][36915434],X_801= [4137747],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 98855 | X_875= [386] := algebra.subselect(X_484= :bat[:oid,:str][36915434],X_863= [5690737],A11="16",A11="16",true,true,false); | | 95194 | X_810= [10596] := algebra.subselect(X_482= :bat[:oid,:str][36915434],X_802= [6526518],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 93175 | X_812= [16687] := algebra.subselect(X_484= :bat[:oid,:str][36915434],X_804= [5690737],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 91491 | X_813= [2797] := algebra.subselect(X_485= :bat[:oid,:str][36915434],X_805= [4803050],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 78025 | X_811= [12501] := algebra.subselect(X_483= :bat[:oid,:str][36915434],X_803= [5897070],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 77577 | X_1094= [13902] := sql.projectdelta(X_1046= [13902],X_616= :bat[:oid,:str][36915434],X_632= :bat[:oid,:oid][0],X_633= [13902] := sql.projectdelta(X_1046= [13902],X_682= :bat[:oid,:str][36915434],X_698= :bat[:oid,:oid][0],X_699= [7086] := algebra.subselect(X_487= :bat[:oid,:str][36915434],X_807= [3696387],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 59667 | X_873= [483] := algebra.subselect(X_482= :bat[:oid,:str][36915434],X_859= [6526518],A11="16",A11="16",true,true,false); | | 55559 | X_874= [420] := algebra.subselect(X_483= :bat[:oid,:str][36915434],X_861= [5897070],A11="16",A11="16",true,true,false); | | 45781 | X_872= [276] := algebra.subselect(X_481= :bat[:oid,:str][36915434],X_857= [4137747],A11="16",A11="16",true,true,false); | | 37478 | X_878= [250] := algebra.subselect(X_487= :bat[:oid,:str][36915434],X_869= [3696387],A11="16",A11="16",true,true,false); | | 37083 | X_876= [170] := algebra.subselect(X_485= :bat[:oid,:str][36915434],X_865= [4803050],A11="16",A11="16",true,true,false); | | 27566 | X_816= [5148] := algebra.subselect(X_488= :bat[:oid,:str][36915437],X_808= [1531879],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 12331 | X_1139= [17073] := sql.projectdelta(X_1044= [17073],X_645= :bat[:oid,:str][36915434],X_660= :bat[:oid,:oid][0],X_661= [97] := algebra.subselect(X_488= :bat[:oid,:str][36915437],X_871= [1531879],A11="16",A11="16",true,true,false); | | 4839 | X_884= [0] := algebra.subselect(X_496= :bat[:oid,:str][0],A11="16",A11="16",true,true,false); | | 3144 | X_1141= [13902] := sql.projectdelta(X_1046= [13902],X_649= :bat[:oid,:str][36915434],X_665= :bat[:oid,:oid][0],X_666= [7336] := sql.projectdelta(X_1047= [7336],X_618= :bat[:oid,:str][36915434],X_634= :bat[:oid,:oid][0],X_635= [17073] := sql.projectdelta(X_1044= [17073],X_484= :bat[:oid,:str][36915434],X_495= :bat[:oid,:oid][0],X_496= [7688] := sql.projectdelta(X_1041= [7688],X_606= :bat[:oid,:str][36915434],X_621= :bat[:oid,:oid][0],X_622= [11079] := sql.projectdelta(X_1042= [11079],X_707= :bat[:oid,:lng][36915434],X_722= :bat[:oid,:oid][0],X_723= [542],X_1154= [542]) := algebra.join(X_1145= :bat[:oid,:str][542],X_127= [126847]); | | 1754 | X_2212= [17073] := sql.projectdelta(X_1044= [17073],X_513= :bat[:oid,:str][36915434],X_528= :bat[:oid,:oid][0],X_529= [17073] := sql.projectdelta(X_1044= [17073],X_612= :bat[:oid,:str][36915434],X_627= :bat[:oid,:oid][0],X_628= [2967] := sql.projectdelta(X_1045= [2967],X_647= :bat[:oid,:str][36915434],X_662= :bat[:oid,:oid][0],X_663= [11079] := sql.projectdelta(X_1042= [11079],X_608= :bat[:oid,:str][36915434],X_623= :bat[:oid,:oid][0],X_624= [7688] := sql.projectdelta(X_1041= [7688],X_573==" ":bat[:oid,:timestamp][36915434],X_588= :bat[:oid,:oid][0],X_589= | | 1379 | X_2256= [13902] := sql.projectdelta(X_1046= [13902],X_486= :bat[:oid,:str][36915434],X_500= :bat[:oid,:oid][0],X_501= [7688] := sql.projectdelta(X_1041= [7688],X_481= :bat[:oid,:str][36915434],X_489= :bat[:oid,:oid][0],X_490= [13902] := sql.projectdelta(X_1046= [13902],X_517= :bat[:oid,:str][36915434],X_533= :bat[:oid,:oid][0],X_534= [12921] := sql.projectdelta(X_1043= [12921],X_483= :bat[:oid,:str][36915434],X_493= :bat[:oid,:oid][0],X_494= [12921] := sql.projectdelta(X_1043= [12921],X_610= :bat[:oid,:str][36915434],X_625= :bat[:oid,:oid][0],X_626= [11079] := sql.projectdelta(X_1042= [11079],X_482= :bat[:oid,:str][36915434],X_491= :bat[:oid,:oid][0],X_492= [12921] := sql.projectdelta(X_1043= [12921],X_511= :bat[:oid,:str][36915434],X_526= :bat[:oid,:oid][0],X_527= ----- Original Message -----
Ok, I have an example query, that takes 4 sec in MonetDB. This is considered long, as Postgresql performs the same task in some 200 ms...
I have used trace to get detailed information on what happens. But this output is really detailed : 2500 lines ! Here it is : http://pastebin.com/EmhERAeF For information, here the Pg explain plan, if that can be of any help http://explain.depesz.com/s/StM
The point is that I don't know what to look for, so visualy parsing this output is quite hard :-) Any hint ?
Thanks a lot, Franck
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Martin, Le 11/07/2013 22:44, Martin Kersten a écrit :
These numbers add not up to 450 seconds.
Yes, 450 account for the multiple requests Mondrian sends to make up its ROLAP cube. But I have tried to narrow my searches to one query that seemed significant (more than 4 seconds vs less than 200 ms on Pg).
I have the suspicion that you have encountered the Linux problem with HugePageMap management. This can lead to unexpected stalls.
Sjoerd/Niels can give you the details to avoid it. Great, this seems like an interesting track to follow...
Thanks a lot for your input, Reagards, Franck
Hi, Le 11/07/2013 11:20, Martin Kersten a écrit :
Without insight into your benchmark queries and database schema for the row stores, little can be said.
Sure... but I just wanted to get past the obvious problems before going into the details.
A typical pitfall could be to use row-based queries such as "SELECT * FROM tableexpr", which would call for expensive tuple reconstruction of all columns. No, we don't. In fact, Mondrian doesn't, to be precise. Another explanation can come from a multidimensional index maintained in a rowstore to speedup grouping. We did not declare any index in MonetDB. Maybe this is the problem ? Last but not least, it could be grouping order or skewed data distribution.
MonetDB provides some tools to see where time goes, which may give a hint. http://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/Stethoscope I will investigate and try to find out what is happening. I'll come back with more information.
Your table sizes reported is not extreme at all. That is what I thought... and why I'm asking, as my current results are really not expected...
Regards, Franck
participants (4)
-
Franck Routier -
Martin Kersten -
Martin Kersten -
Stefan Manegold