Re: [MonetDB-users] query slowdown with concurrent write

4 Feb 2011

      Hi Martin and Stefan,
Thanks for the quick response, and sorry if this created another thread, I'm
not familiar with how to use this mailing list.

I am using:
MonetDB v5.22.3 on ubuntu 9.04 64bit
Queries and data loading use the php library.

To further explain my use case, I am planning on having monetdb be updated
about once a minute with 1000-10000 new rows of data. At the same time I
will be having as many clients as possible making queries on the database. I
have so far seen that 5 clients can be handled with satisfiable response
times.  Is this a suitable application for monetdb?  The query times I have
seen so far are great.  My problems are handling the queries with concurrent
writes and if possible allowing for more clients to make simultaneous
queries.

Re: Martin's questions:

I tried using the recycle optimizer in the default pipe and my queries
didn't slow down, which is awesome. But, my problem now is that the values
aren't actually added to the dataset until an insert transaction occurs when
I am not querying the database.  Therefore, if I continuously query the
database then the inserts are never seen, but if I stop querying for a
second and  another insert happens the database is updated.  I am fine with
some delay in the insert time, but is there anyway I could have it executed
slowly alongside the queries, or am I too greedy :)

The pipe I created is the same as the default pipe with the recycler
optimizer added:
bennett_pipe=inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,recycle,reduce,dataflow,history,multiplex,garbageCollector

My schema and and an example query are:

CREATE TABLE my_test_table (
  account_id bigint,
  dim1 bigint,
  dim2 bigint,
  dim3 bigint,
  dim5 varchar(50),
  dim6 varchar(50),
  dim7 varchar(256),
  dim8 bigint,
  dim9 varchar(256),
  dim10 varchar(256),
  unique_id bigint,
  my_time timestamp,
  summable int
);

QUERY:
    select dim5, sum(summable), count(distinct unique_id) from my_test_table
where account_id = 76 and my_time >= '2010-07-03 00:00:00' and my_time <
'2010-07-10 00:00:00' group by dim5;

Re Stefan's questions:

The results from the trace are below.  I apologize if any of the data looks
incorrect, I scrubbed my table's field names to be more generic.

I let the database cool down for 1 minute and 10 minutes, but the queries
remained slow.

If I restarted the database, the queries resumed their quick response times.

FAST QUERY TIME:
sql>trace select dim5, sum(summable) as summables, count(distinct unique_id)
as unqiue_ids from my_test_table where account_id = 76 and my_time >=
'2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5;
+-----------------------+--------+--------+
| dim1                  | summables | unique_ids |
+=======================+========+========+
| test1                 |    251 |     18 |
| test2                 |     48 |     11 |
| test3                 |      1 |      1 |
| test4                 |      2 |      1 |
| test4                 |    106 |      2 |
+-----------------------+--------+--------+
5 tuples (125.534ms)
+-------+------------------------------------------------------------------------------------------+
| ticks | stmt
                      |
+=======+==========================================================================================+
|     3 | _5 := sql.mvc();
                      |
|    18 | _11:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","account_id |
:       : ",2);
                       :
|     7 | _6:bat[:oid,:lng] <tmp_1410>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","accou |
:       : nt_id",0);
                      :
|     4 | _15:bat[:oid,:lng] <tmp_1102>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","account_id |
:       : ",1);
                       :
|     6 | _18:bat[:oid,:oid] <tmp_1357>[0] :=
sql.bind_dbat(_5=21685368,"sys","my_test_table",1);    |
|    21 | _13<tmp_1527>[8000000] :=
algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat[: |
:       : oid,:lng][0]);
                      :
|     4 | _19<tmpr_1357>[0] := bat.reverse(_18=nil:bat[:oid,:oid]);
                       |
|    10 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table" |
:       : ,"my_time",0);
                      :
|    27 | _14<tmp_1532>[8000000] :=
algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]);                |
|    11 | _23 := calc.timestamp(A1="2010-07-03 00:00:00");
                      |
|    12 | _17<tmp_1527>[8000000] :=
algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]);                |
|     4 | _24 := calc.timestamp(A2="2010-07-10 00:00:00");
                      |
|     7 | _20<tmp_1532>[8000000] :=
algebra.kdifference(_17=nil,_19=<tmpr_1357>[0]);               |
|     4 | _27:bat[:oid,:timestamp] <tmp_1131>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","my_t |
:       : ime",2);
                      :
| 29575 | _25<tmp_1531>[359554] :=
algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-03 00: |
:       : 00:00.000,_24=2010-07-10 00:00:00.000,true,false);
                      :
|    12 | _31:bat[:oid,:timestamp] <tmp_1132>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","my_t |
:       : ime",1);
                      :
|    24 | _28<tmp_1527>[359554] :=
algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestamp] |
:       : [0]);
                       :
|    23 | _32<tmp_1536>[0] :=
algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00:00 |
:       : .000,_24=2010-07-10 00:00:00.000,true,false);
                       :
|    35 | _29<tmp_1501>[0] :=
algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00:00 |
:       : .000,_24=2010-07-10 00:00:00.000,true,false);
                       :
|     8 | _39:bat[:oid,:str] <tmp_1364>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",0);
                                                      :
|    12 | _30<tmp_1534>[359554] := algebra.kunion(_28=nil,_29=nil);
                       |
|     5 | _44:bat[:oid,:str] <tmp_1103>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5 |
:       : e",2);
                      :
|    23 | _33<tmp_1501>[359554] := algebra.kunion(_30=nil,_32=nil);
                       |
|     6 | _46<tmp_1536>[8000000] :=
algebra.kdifference(_39=nil:bat[:oid,:str],_44=<tmp_1103>:bat[ |
:       : :oid,:str][0]);
                       :
|     8 | _34<tmp_1534>[359554] := algebra.kdifference(_33=nil,_19=nil);
                      |
|     9 | _47<tmp_1501>[8000000] :=
algebra.kunion(_46=nil,_44=nil:bat[:oid,:str]);                |
|     4 | _48:bat[:oid,:str] <tmp_1104>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",1);
                                                            :
|    10 | _50<tmp_1527>[8000000] :=
algebra.kunion(_47=nil,_48=nil:bat[:oid,:str]);                |
|     6 | _66:bat[:oid,:lng] <tmp_1413>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",0);
                                                       :
|     3 | _68:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",2);
                                                             :
|     7 | _69<tmp_1501>[8000000] :=
algebra.kdifference(_66=nil:bat[:oid,:lng],_68=<tmp_1101>:bat[ |
:       : :oid,:lng][0]);
                       :
|     9 | _70<tmp_1541>[8000000] :=
algebra.kunion(_69=nil,_68=nil:bat[:oid,:lng]);                |
|     4 | _71:bat[:oid,:lng] <tmp_1102>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",1);
                                                             :
|     8 | _74<tmp_1501>[8000000] :=
algebra.kunion(_70=nil,_71=nil:bat[:oid,:lng]);                |
|     4 | _56:bat[:oid,:int] <tmp_1366>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","summable",0);
                                                               :
|     4 | _58:bat[:oid,:int] <tmp_1065>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","summable",2) |
:       : ;
                       :
|     7 | _59<tmp_1541>[8000000] :=
algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:bat[ |
:       : :oid,:int][0]);
                       :
|     8 | _60<tmp_1524>[8000000] :=
algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]);                |
|     3 | _61:bat[:oid,:int] <tmp_1066>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","summable",1) |
:       : ;
                       :
|     8 | _62<tmp_1541>[8000000] :=
algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]);                |
| 15525 | _35<tmp_1536>[359554] := algebra.semijoin(_20=nil,_34=nil);
                       |
|  2446 | _36<tmp_1534>[139] := algebra.uselect(_35=nil,A0=76:lng);
                       |
|    10 | _37<tmp_1536>[139] := algebra.markT(_36=nil,0@0:oid);
                       |
|     4 | _38<tmpr_1536>[139] := bat.reverse(_37=nil);
                      |
|    33 | _75<tmp_1477>[139] :=
algebra.leftjoin(_38=<tmpr_1536>[139],_74=nil);                    |
|    47 | _51<tmp_1403>[139] :=
algebra.leftjoin(_38=<tmpr_1536>[139],_50=nil);                    |
|     4 | _76<tmp_1477>[139] := algebra.selectNotNil(_75=nil);
                      |
|    47 | (ext61<tmp_1502>[5],grp59<tmp_1476>[139]) :=
group.done(_51=<tmp_1403>[139]);            |
|    19 | _63<tmp_1363>[139] := algebra.leftjoin(_38=nil,_62=nil);
                      |
|     8 | _54<tmp_1541>[5] := bat.mirror(ext61=<tmp_1502>[5]);
                      |
|     3 | _64<tmp_1363>[139] := algebra.selectNotNil(_63=nil);
                      |
|    47 | _55<tmp_1471>[5] := algebra.join(_54=<tmp_1541>[5],_51=nil);
                      |
|    43 | (_77<tmp_1344>[33],grp85) :=
group.derive(ext61=nil,grp59=<tmp_1476>[139],_76=<tmp_1477> |
:       : [139]);
                       :
|     6 | _79<tmp_1527>[33] := bat.mirror(_77=nil);
                       |
|    22 | _80<tmp_1506>[33] := algebra.join(_79=nil,_76=nil);
                       |
|    20 | _81:bat[:oid,:wrd] <tmp_1527>[5] :=
aggr.count(_80=nil,grp59=<tmp_1476>[139],_54=<tmp_15 |
:       : 41>[5]);
                      :
|    21 | _65:bat[:oid,:lng] <tmp_1536>[5] :=
aggr.sum(_64=nil,grp59=nil,_54=nil);                 |
| 63484 | barrier _137 := language.dataflow();
                      |
|     9 | _82 := sql.resultSet(3,1,_55=<tmp_1471>[5]);
                      |
|     7 |
sql.rsColumn(_82=1,"sys.my_test_table","dim5","varchar",50,0,_55=nil);
       |
|     5 |
sql.rsColumn(_82=1,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]);
    |
|     5 |
sql.rsColumn(_82=1,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]);
       |
|     3 | _99 := io.stdout();
                       |
|    41 | sql.exportResult(_99=14711568,_82=1);
                       |
| 83716 | user.s4_1(76:lng,"2010-07-03 00:00:00","2010-07-10 00:00:00");
                      |
+-------+------------------------------------------------------------------------------------------+

SLOW QUERY TIME:
sql>trace select dim5, sum(summable) as summables, count(distinct unique_id)
as unqiue_ids from my_test_table where account_id = 76 and my_time >=
'2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5;
+-----------------------+--------+--------+
| dim1                  | summables | unique_ids |
+=======================+========+========+
| test1                 |    251 |     18 |
| test2                 |     48 |     11 |
| test3                 |      1 |      1 |
| test4                 |      2 |      1 |
| test4                 |    106 |      2 |
+-----------------------+--------+--------+
5 tuples (2.7s)
+---------+----------------------------------------------------------------------------------------+
| ticks   | stmt
                      |
+=========+========================================================================================+
|       3 | _5 := sql.mvc();
                      |
|      20 | _11:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","account_ |
:         : id",2);
                       :
|       5 | _6:bat[:oid,:lng] <tmp_1410>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","acc |
:         : ount_id",0);
                      :
|      25 | _13<tmp_1705>[8000000] :=
algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat |
:         : [:oid,:lng][0]);
                      :
|       4 | _15:bat[:oid,:lng] <tmp_1664>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","account |
:         : _id",1);
                      :
|      20 | _14<tmp_1706>[8000000] :=
algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]);              |
|       4 | _18:bat[:oid,:oid] <tmp_1070>[0] :=
sql.bind_dbat(_5=21685368,"sys","my_test_table",1);  |
|       8 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]);
                       |
|   31627 | _51<tmp_1670>[0] :=
algebra.leftjoin(_38=<tmpr_1665>[0],_50=nil);                      |
|      20 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","my_time",0);
                                                  :
|      10 | _23 := calc.timestamp(A1="2010-07-03 00:00:00");
                      |
|       3 | _24 := calc.timestamp(A2="2010-07-10 00:00:00");
                      |
|    7243 | (ext61<tmp_1710>[0],grp59<tmp_1661>[0]) :=
group.done(_51=<tmp_1670>[0]);              |
|      16 | _54<tmp_1665>[0] := bat.mirror(ext61=<tmp_1710>[0]);
                      |
|   47358 | _63<tmp_1356>[0] := algebra.leftjoin(_38=nil,_62=nil);
                      |
|       6 | _64<tmp_1356>[0] := algebra.selectNotNil(_63=nil);
                      |
|      13 | (_77<tmp_1710>[0],grp85) :=
group.derive(ext61=nil,grp59=<tmp_1661>[0],_76=<tmp_1651>[ |
:         : 0]);
                      :
|      16 | _79<tmp_1674>[0] := bat.mirror(_77=nil);
                      |
|      11 | _80<tmp_1710>[0] := algebra.join(_79=nil,_76=nil);
                      |
|      10 | _81:bat[:oid,:wrd] <tmp_1651>[0] :=
aggr.count(_80=nil,grp59=<tmp_1661>[0],_54=<tmp_16 |
:         : 65>[0]);
                      :
|      20 | _55<tmp_1711>[0] := algebra.join(_54=<tmp_1665>[0],_51=nil);
                      |
|      11 | _65:bat[:oid,:lng] <tmp_1674>[0] :=
aggr.sum(_64=nil,grp59=nil,_54=nil);               |
|      11 | _82 := sql.resultSet(3,1,_55=<tmp_1711>[0]);
                      |
|       9 |
sql.rsColumn(_82=235,"sys.my_test_table","dim5","varchar",50,0,_55=nil);
     |
|       5 |
sql.rsColumn(_82=235,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]);
|
|       4 |
sql.rsColumn(_82=235,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]);
   |
|       2 | _99 := io.stdout();
                       |
|      30 | sql.exportResult(_99=24880032,_82=235);
                       |
|  420626 | _17<tmp_1705>[8000010] :=
algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]);              |
|      17 | _20<tmp_1665>[8000010] :=
algebra.kdifference(_17=nil,_19=<tmpr_1070>[0]);             |
|      13 | _27:bat[:oid,:timestamp] <tmp_1131>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","my |
:         : _time",2);
                      :
|       5 | _31:bat[:oid,:timestamp] <tmp_1704>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","m |
:         : y_time",1);
                       :
|      13 | _32<tmp_1673>[0] :=
algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00: |
:         : 00.000,_24=2010-07-10 00:00:00.000,true,false);
                       :
|       8 | _39:bat[:oid,:str] <tmp_1364>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",0);
                                                  :
|       4 | _44:bat[:oid,:str] <tmp_1103>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",2);
                                                        :
|       7 | _46<tmp_1661>[8000000] :=
algebra.kdifference(_39=nil:bat[:oid,:str],_44=<tmp_1103>:ba |
:         : t[:oid,:str][0]);
                       :
|      16 | _47<tmp_1706>[8000000] :=
algebra.kunion(_46=nil,_44=nil:bat[:oid,:str]);              |
|       3 | _5 := sql.mvc();
                      |
|       4 | _48:bat[:oid,:str] <tmp_1631>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",1);
                                                       :
|      21 | _11:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","account_ |
:         : id",2);
                       :
|       5 | _15:bat[:oid,:lng] <tmp_1637>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","account |
:         : _id",1);
                      :
|       4 | _18:bat[:oid,:oid] <tmp_1070>[0] :=
sql.bind_dbat(_5=25090760,"sys","my_test_table",1);  |
|       3 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]);
                       |
|       5 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","my_time",0);
                                                  :
|       8 | _23 := calc.timestamp(A1="2010-07-27 00:00:00");
                      |
|       3 | _24 := calc.timestamp(A2="2010-08-03 00:00:00");
                      |
|   31883 | _25<tmp_1712>[391659] :=
algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-27 0 |
:         : 0:00:00.000,_24=2010-08-03 00:00:00.000,true,false);
                      :
|      11 | _27:bat[:oid,:timestamp] <tmp_1131>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","my |
:         : _time",2);
                      :
|      21 | _28<tmp_1674>[391659] :=
algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam |
:         : p][0]);
                       :
|      33 | _29<tmp_1714>[0] :=
algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-27 00:00: |
:         : 00.000,_24=2010-08-03 00:00:00.000,true,false);
                       :
|      11 | _30<tmp_1713>[391659] := algebra.kunion(_28=nil,_29=nil);
                       |
|       4 | _31:bat[:oid,:timestamp] <tmp_1660>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","m |
:         : y_time",1);
                       :
|      12 | _32<tmp_1714>[0] :=
algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-27 00:00: |
:         : 00.000,_24=2010-08-03 00:00:00.000,true,false);
                       :
|      10 | _33<tmp_1674>[391659] := algebra.kunion(_30=nil,_32=nil);
                       |
|       7 | _39:bat[:oid,:lng] <tmp_1413>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",0);
                                                   :
|       4 | _44:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","unqiue_id",2);
                                                         :
|       6 | _46<tmp_1713>[8000000] :=
algebra.kdifference(_39=nil:bat[:oid,:lng],_44=<tmp_1101>:ba |
:         : t[:oid,:lng][0]);
                       :
|      12 | _47<tmp_1715>[8000000] :=
algebra.kunion(_46=nil,_44=nil:bat[:oid,:lng]);              |
|       4 | _48:bat[:oid,:lng] <tmp_1657>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",1);
                                                        :
|  366305 | _50<tmp_1713>[8000010] :=
algebra.kunion(_47=nil,_48=nil:bat[:oid,:lng]);              |
|      14 | _56:bat[:oid,:int] <tmp_1366>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","summable",0);
                                                           :
|       4 | _58:bat[:oid,:int] <tmp_1065>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","summable", |
:         : 2);
                       :
|      17 | _59<tmp_1715>[8000000] :=
algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba |
:         : t[:oid,:int][0]);
                       :
|      16 | _60<tmp_1716>[8000000] :=
algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]);              |
|       4 | _61:bat[:oid,:int] <tmp_1362>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","summable" |
:         : ,1);
                      :
|       5 | _6:bat[:oid,:lng] <tmp_1410>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","acc |
:         : ount_id",0);
                      :
|      10 | _13<tmp_1717>[8000000] :=
algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat |
:         : [:oid,:lng][0]);
                      :
|  298001 | _62<tmp_1715>[8000010] :=
algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]);              |
|      21 | _14<tmp_1716>[8000000] :=
algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]);              |
|  374628 | _17<tmp_1717>[8000010] :=
algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]);              |
|      20 | _20<tmp_1716>[8000010] :=
algebra.kdifference(_17=nil,_19=<tmpr_1070>[0]);             |
|      15 | _34<tmp_1720>[391659] := algebra.kdifference(_33=nil,_19=nil);
                      |
|   65082 | _35<tmp_1674>[391659] := algebra.semijoin(_20=nil,_34=nil);
                       |
|    2960 | _36<tmp_1714>[0] := algebra.uselect(_35=nil,A0=239:lng);
                      |
|      12 | _37<tmp_1674>[0] := algebra.markT(_36=nil,0@0:oid);
                       |
|       5 | _38<tmpr_1674>[0] := bat.reverse(_37=nil);
                      |
|   58011 | _51<tmp_1651>[0] :=
algebra.leftjoin(_38=<tmpr_1674>[0],_50=nil);                      |
|      26 | (ext61<tmp_1710>[0],grp59<tmp_1713>[0]) :=
group.done(_51=<tmp_1651>[0]);              |
|       5 | _54<tmp_1674>[0] := bat.mirror(ext61=<tmp_1710>[0]);
                      |
|   27086 | _63<tmp_1356>[0] := algebra.leftjoin(_38=nil,_62=nil);
                      |
|      24 | _55<tmp_1715>[0] :=
algebra.join(_54=<tmp_1674>[0],_51=<tmp_1651>[0]);                 |
|       3 | _66<tmp_1651>[0] := algebra.selectNotNil(_51=nil);
                      |
|      11 | (_67<tmp_1710>[0],grp75) :=
group.derive(ext61=nil,grp59=<tmp_1713>[0],_66=<tmp_1651>[ |
:         : 0]);
                      :
|      10 | _69<tmp_1712>[0] := bat.mirror(_67=nil);
                      |
|       5 | _70<tmp_1710>[0] := algebra.join(_69=nil,_66=nil);
                      |
|       9 | _71:bat[:oid,:wrd] <tmp_1651>[0] :=
aggr.count(_70=nil,grp59=<tmp_1713>[0],_54=<tmp_16 |
:         : 74>[0]);
                      :
|       3 | _64<tmp_1356>[0] := algebra.selectNotNil(_63=nil);
                      |
|      11 | _65:bat[:oid,:lng] <tmp_1712>[0] :=
aggr.sum(_64=nil,grp59=nil,_54=nil);               |
| 1226556 | barrier _119 := language.dataflow();
                      |
|       9 | _72 := sql.resultSet(3,1,_55=<tmp_1715>[0]);
                      |
|       8 |
sql.rsColumn(_72=236,"sys.my_test_table","unique_id","bigint",64,0,_55=nil);
       |
|       4 |
sql.rsColumn(_72=236,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]);
|
|       3 |
sql.rsColumn(_72=236,"sys.my_test_table","unique_ids","wrd",64,0,_71=nil:bat[:oid,:wrd]);
   |
|       3 | _85 := io.stdout();
                       |
|      37 | sql.exportResult(_85=24880032,_72=236);
                       |
|       2 | _5 := sql.mvc();
                      |
|      18 | _11:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","account_ |
:         : id",2);
                       :
|       4 | _15:bat[:oid,:lng] <tmp_1637>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","account |
:         : _id",1);
                      :
|       3 | _18:bat[:oid,:oid] <tmp_1070>[0] :=
sql.bind_dbat(_5=25090760,"sys","my_test_table",1);  |
|       3 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]);
                       |
|       6 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","my_time",0);
                                                  :
|       8 | _23 := calc.timestamp(A1="2010-06-24 00:00:00");
                      |
|       3 | _24 := calc.timestamp(A2="2010-07-01 00:00:00");
                      |
|      18 | _6:bat[:oid,:lng] <tmp_1410>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","acc |
:         : ount_id",0);
                      :
|     678 | _13<tmp_1713>[8000000] :=
algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat |
:         : [:oid,:lng][0]);
                      :
|      22 | _14<tmp_1674>[8000000] :=
algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]);              |
|   49964 | _25<tmp_1714>[372221] :=
algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-06-24 0 |
:         : 0:00:00.000,_24=2010-07-01 00:00:00.000,true,false);
                      :
|      14 | _27:bat[:oid,:timestamp] <tmp_1131>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","my |
:         : _time",2);
                      :
|      21 | _28<tmp_1713>[372221] :=
algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam |
:         : p][0]);
                       :
|      24 | _29<tmp_1720>[0] :=
algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-06-24 00:00: |
:         : 00.000,_24=2010-07-01 00:00:00.000,true,false);
                       :
|      11 | _30<tmp_1712>[372221] := algebra.kunion(_28=nil,_29=nil);
                       |
|       4 | _31:bat[:oid,:timestamp] <tmp_1660>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","m |
:         : y_time",1);
                       :
|      13 | _32<tmp_1720>[0] :=
algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-06-24 00:00: |
:         : 00.000,_24=2010-07-01 00:00:00.000,true,false);
                       :
|      10 | _33<tmp_1713>[372221] := algebra.kunion(_30=nil,_32=nil);
                       |
|       5 | _39:bat[:oid,:lng] <tmp_1413>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",0);
                                                   :
|       5 | _44:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",2);
                                                         :
|       6 | _46<tmp_1712>[8000000] :=
algebra.kdifference(_39=nil:bat[:oid,:lng],_44=<tmp_1101>:ba |
:         : t[:oid,:lng][0]);
                       :
|       8 | _47<tmp_1717>[8000000] :=
algebra.kunion(_46=nil,_44=nil:bat[:oid,:lng]);              |
|       3 | _48:bat[:oid,:lng] <tmp_1657>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",1);
                                                        :
| 1381664 | _50<tmp_1661>[8000010] :=
algebra.kunion(_47=nil,_48=nil:bat[:oid,:str]);              |
|      17 | _66:bat[:oid,:lng] <tmp_1413>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",0);
                                                   :
|       4 | _68:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",2);
                                                         :
|      19 | _69<tmp_1706>[8000000] :=
algebra.kdifference(_66=nil:bat[:oid,:lng],_68=<tmp_1101>:ba |
:         : t[:oid,:lng][0]);
                       :
|      17 | _70<tmp_1721>[8000000] :=
algebra.kunion(_69=nil,_68=nil:bat[:oid,:lng]);              |
|       4 | _71:bat[:oid,:lng] <tmp_1703>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",1);
                                                        :
|  405606 | _50<tmp_1712>[8000010] :=
algebra.kunion(_47=nil,_48=nil:bat[:oid,:lng]);              |
|      15 | _56:bat[:oid,:int] <tmp_1366>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","summable",0);
                                                           :
|       4 | _58:bat[:oid,:int] <tmp_1065>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","summable", |
:         : 2);
                       :
|      20 | _59<tmp_1717>[8000000] :=
algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba |
:         : t[:oid,:int][0]);
                       :
|      17 | _60<tmp_1722>[8000000] :=
algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]);              |
|       4 | _61:bat[:oid,:int] <tmp_1362>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","summable" |
:         : ,1);
                      :
|  374062 | _74<tmp_1706>[8000010] :=
algebra.kunion(_70=nil,_71=nil:bat[:oid,:lng]);              |
|      16 | _56:bat[:oid,:int] <tmp_1366>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","summable",0);
                                                           :
|       4 | _58:bat[:oid,:int] <tmp_1065>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","summable", |
:         : 2);
                       :
|      18 | _59<tmp_1721>[8000000] :=
algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba |
:         : t[:oid,:int][0]);
                       :
|      16 | _60<tmp_1723>[8000000] :=
algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]);              |
|       4 | _61:bat[:oid,:int] <tmp_1361>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","summable" |
:         : ,1);
                      :
| 1884678 | _25<tmp_1707>[359554] :=
algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-03 0 |
:         : 0:00:00.000,_24=2010-07-10 00:00:00.000,true,false);
                      :
|      28 | _28<tmp_1724>[359554] :=
algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam |
:         : p][0]);
                       :
|      30 | _29<tmp_1726>[0] :=
algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00: |
:         : 00.000,_24=2010-07-10 00:00:00.000,true,false);
                       :
|      11 | _30<tmp_1725>[359554] := algebra.kunion(_28=nil,_29=nil);
                       |
|      10 | _33<tmp_1726>[359554] := algebra.kunion(_30=nil,_32=nil);
                       |
|       7 | _34<tmp_1725>[359554] := algebra.kdifference(_33=nil,_19=nil);
                      |
|   63572 | _35<tmp_1726>[359554] := algebra.semijoin(_20=nil,_34=nil);
                       |
|    2812 | _36<tmp_1673>[139] := algebra.uselect(_35=nil,A0=76:lng);
                       |
|      10 | _37<tmp_1726>[139] := algebra.markT(_36=nil,0@0:oid);
                       |
|       5 | _38<tmpr_1726>[139] := bat.reverse(_37=nil);
                      |
|   58840 | _75<tmp_1715>[139] :=
algebra.leftjoin(_38=<tmpr_1726>[139],_74=nil);                  |
|       6 | _76<tmp_1715>[139] := algebra.selectNotNil(_75=nil);
                      |
|   21903 | _51<tmp_1711>[139] :=
algebra.leftjoin(_38=<tmpr_1726>[139],_50=nil);                  |
|      62 | (ext61<tmp_1344>[5],grp59<tmp_1651>[139]) :=
group.done(_51=<tmp_1711>[139]);          |
|      11 | _54<tmp_1661>[5] := bat.mirror(ext61=<tmp_1344>[5]);
                      |
|      46 | _55<tmp_1570>[5] := algebra.join(_54=<tmp_1661>[5],_51=nil);
                      |
|      34 | (_77<tmp_1500>[33],grp85) :=
group.derive(ext61=nil,grp59=<tmp_1651>[139],_76=<tmp_171 |
:         : 5>[139]);
                       :
|       6 | _79<tmp_1706>[33] := bat.mirror(_77=nil);
                       |
|      24 | _80<tmp_1502>[33] := algebra.join(_79=nil,_76=nil);
                       |
|      22 | _81:bat[:oid,:wrd] <tmp_1706>[5] :=
aggr.count(_80=nil,grp59=<tmp_1651>[139],_54=<tmp_ |
:         : 1661>[5]);
                      :
|  554759 | _62<tmp_1717>[8000010] :=
algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]);              |
|  541977 | _62<tmp_1721>[8000010] :=
algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]);              |
|   41224 | _63<tmp_1356>[139] := algebra.leftjoin(_38=nil,_62=nil);
                      |
|      10 | _64<tmp_1356>[139] := algebra.selectNotNil(_63=nil);
                      |
|      48 | _65:bat[:oid,:lng] <tmp_1721>[5] :=
aggr.sum(_64=nil,grp59=nil,_54=nil);               |
| 2761640 | barrier _137 := language.dataflow();
                      |
|      12 | _82 := sql.resultSet(3,1,_55=<tmp_1570>[5]);
                      |
|       8 |
sql.rsColumn(_82=3,"sys.my_test_table","dim5","varchar",50,0,_55=nil);
     |
|       5 |
sql.rsColumn(_82=3,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]);
  |
|       6 |
sql.rsColumn(_82=3,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]);
     |
|       3 | _99 := io.stdout();
                       |
|      45 | sql.exportResult(_99=14711568,_82=3);
                       |
| 2761973 | user.s4_1(76:lng,"2010-07-03 00:00:00","2010-07-10 00:00:00");
                      |
+---------+----------------------------------------------------------------------------------------+

Re: [MonetDB-users] query slowdown with concurrent write

Bennett Hiles