hi Martin,
 
  monetdb version: 11.37.7-20200529
  sql: generic insert — INSERT INTO table(id,v1,v2,v3,v4) VALUES (…);
  test: same environment for both servers: vmbox 8gb, 4 cores, only one server is running on tests, no load of the host server
  php 7.4.5,  SQLAnyhwere pdo extension;  php-monetdb-1.0-1.el8.noarch.rpm
  script: same for both tests except connection, measured only loop execution,   the script uses «random» functions to
  generate  data for insert
 
 
function str_random($length = 3200)
{
    $pool = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    return substr(str_shuffle(str_repeat($pool, $length)), 0, $length);
}
 
 
  the execution time ratio for all tests was almost the same. each test was done on «clean» server.
 
  one of the test:
  monetdb:  Work: 796.90835905075 seconds
  SQLA:      Work: 382.80270600319 seconds
 
 
 
 
 
  /alex
 
 
  
>Понедельник, 13 июля 2020, 8:44 +03:00 от Martin Kersten :
> 
>Hi 
>
>There can be many reasons. Ranging from version used, SQL difference, test running, en PHP? API.
>Based on the information provided this can not be judged.
>
>regards, Martin
>
>On 13/07/2020 02:13, - - wrote:
>> I have run the series of INSERT tests in PHP for monetdb and SAP SQLAnywhere.  The tests show: performance of the monetdb on INSERT operations is almost twice slower against SQLA with the equal conditions (DDL, index, data, autocommit, 10K records),
>> monetdb is in in-memory mode . What can be a reason?
>> p.s. It is not benchmark, I just used the SQLA test server already running.
>> --
>> - -
>>
>> _______________________________________________
>> 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 
 
 
--
- -
 

Hi Could you try out this version https://github.com/bolner/MonetDB-PHP-Deux and report on your findings. regards, Martin On 13/07/2020 08:52, - - wrote:

hi Martin,   monetdb version: 11.37.7-20200529   sql: generic insert — INSERT INTO table(id,v1,v2,v3,v4) VALUES (…);   test: same environment for both servers: vmbox 8gb, 4 cores, only one server is running on tests, no load of the host server   php 7.4.5,  SQLAnyhwere pdo extension;  php-monetdb-1.0-1.el8.noarch.rpm   script: same for both tests except connection, measured only loop execution,   the script uses «random» functions to   generate  data for insert function str_random($length = 3200) {     $pool = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';     return substr(str_shuffle(str_repeat($pool, $length)), 0, $length); }   the execution time ratio for all tests was almost the same. each test was done on «clean» server.   one of the test:   monetdb:  Work: 796.90835905075 seconds   SQLA:      Work: 382.80270600319 seconds   /alex

Понедельник, 13 июля 2020, 8:44 +03:00 от Martin Kersten : Hi

There can be many reasons. Ranging from version used, SQL difference, test running, en PHP? API. Based on the information provided this can not be judged.

regards, Martin

On 13/07/2020 02:13, - - wrote: > I have run the series of INSERT tests in PHP for monetdb and SAP SQLAnywhere.  The tests show: performance of the monetdb on INSERT operations is almost twice slower against SQLA with the equal conditions (DDL, index, data, autocommit, 10K records), > monetdb is in in-memory mode . What can be a reason? > p.s. It is not benchmark, I just used the SQLA test server already running. > -- > - - > > _______________________________________________ > 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

actually yes, the columnar database is slower on insert then row database but not with so significant penalty.
 
in my memory Clickhouse and Exasol had no such big differences against same SQLA. i did tests long time ago, i will
run fresh tests again.
 
monetdb bulk load is acceptable in time on my tests, however i didn’t load really big tables yet.
 
  
>Понедельник, 13 июля 2020, 18:33 +03:00 от Rajendra Singh Negi :
> 
>Hi,
>I have a background in SAP IQ (Columnar DB), SAP ASE(OLTP / row DB), SAP SQL Anywhere(OLTP / row DB).
>I am interested in MonetDB as I think it is a Columnar Database.
> 
>Here are some points: 
>1. Having a background in Columnar DB (SAP IQ), I would assume that the behaviour of MonetDB would be similar.
>   (a) I would expect Columnar Databases to be slow in row inserts.
>   (b) I would expect Columnar databases to be faster in bulk loads (I will explore this in monetDB)
>        For example SAP IQ 16.x is the Fastest Bulk loading database in the world ( https://www.guinnessworldrecords.com/world-records/fastest-loading-of-big-data )
>        I would expect a good performance in monetDB if it has a bulk load capability.
> 
>2. OLTP / row Databases like ASE, SQLA, Oracle, mySQL, mariaDB, etc should be better than Columnar databases in row-inserts  
> 
>Thanks,
> 
>Sincerely,
> 
>Rajendra Singh Negi
>Mobile: 7709112263
>email:  harharharishwar@gmail.com
>Chase the Dream and not the competition  
>On Mon, Jul 13, 2020 at 3:13 PM Martin Kersten < martin.kersten@cwi.nl > wrote:
>>Hi
>>
>>Could you try out this version  https://github.com/bolner/MonetDB-PHP-Deux
>>and report on your findings.
>>
>>regards, Martin
>>
>>On 13/07/2020 08:52, - - wrote:
>>> hi Martin,
>>>    monetdb version: 11.37.7-20200529
>>>    sql: generic insert — INSERT INTO table(id,v1,v2,v3,v4) VALUES (…);
>>>    test: same environment for both servers: vmbox 8gb, 4 cores, only one server is running on tests, no load of the host server
>>>    php 7.4.5,  SQLAnyhwere pdo extension;  php-monetdb-1.0-1.el8.noarch.rpm
>>>    script: same for both tests except connection, measured only loop execution,   the script uses «random» functions to
>>>    generate  data for insert
>>> function str_random($length = 3200)
>>> {
>>>      $pool = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
>>>      return substr(str_shuffle(str_repeat($pool, $length)), 0, $length);
>>> }
>>>    the execution time ratio for all tests was almost the same. each test was done on «clean» server.
>>>    one of the test:
>>>    monetdb:  Work: 796.90835905075 seconds
>>>    SQLA:      Work: 382.80270600319 seconds
>>>    /alex
>>>
>>>     Понедельник, 13 июля 2020, 8:44 +03:00 от Martin Kersten < martin@monetdb.org >:
>>>     Hi 
>>>
>>>     There can be many reasons. Ranging from version used, SQL difference, test running, en PHP? API.
>>>     Based on the information provided this can not be judged.
>>>
>>>     regards, Martin
>>>
>>>     On 13/07/2020 02:13, - - wrote:
>>>      > I have run the series of INSERT tests in PHP for monetdb and SAP SQLAnywhere.  The tests show: performance of the monetdb on INSERT operations is almost twice slower against SQLA with the equal conditions (DDL, index, data, autocommit, 10K records),
>>>      > monetdb is in in-memory mode . What can be a reason?
>>>      > p.s. It is not benchmark, I just used the SQLA test server already running.
>>>      > --
>>>      > - -
>>>      >
>>>      > _______________________________________________
>>>      > 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
>>>
>>_______________________________________________
>>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
>  
 
 
--
- -
 

 
https://github.com/bolner/MonetDB-PHP-Deux
the results are in the same ratio.
 
any suggestions?
 
monetdb
Work: 793.54999995232 seconds
Work: 813.93353414536 seconds
Work: 798.28180193901 seconds
 
SQLA
Work: 381.90039515495 seconds
Work: 380.11742515733 seconds
Work: 384.53782300789 seconds
  
>Понедельник, 13 июля 2020, 12:41 +03:00 от Martin Kersten :
> 
>Hi
>
>Could you try out this version  https://github.com/bolner/MonetDB-PHP-Deux
>and report on your findings.
>
>regards, Martin
>
>On 13/07/2020 08:52, - - wrote:
>> hi Martin,
>>   monetdb version: 11.37.7-20200529
>>   sql: generic insert — INSERT INTO table(id,v1,v2,v3,v4) VALUES (…);
>>   test: same environment for both servers: vmbox 8gb, 4 cores, only one server is running on tests, no load of the host server
>>   php 7.4.5,  SQLAnyhwere pdo extension;  php-monetdb-1.0-1.el8.noarch.rpm
>>   script: same for both tests except connection, measured only loop execution,   the script uses «random» functions to
>>   generate  data for insert
>> function str_random($length = 3200)
>> {
>>     $pool = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
>>     return substr(str_shuffle(str_repeat($pool, $length)), 0, $length);
>> }
>>   the execution time ratio for all tests was almost the same. each test was done on «clean» server.
>>   one of the test:
>>   monetdb:  Work: 796.90835905075 seconds
>>   SQLA:      Work: 382.80270600319 seconds
>>   /alex
>>
>> Понедельник, 13 июля 2020, 8:44 +03:00 от Martin Kersten < martin@monetdb.org >:
>> Hi 
>>
>> There can be many reasons. Ranging from version used, SQL difference, test running, en PHP? API.
>> Based on the information provided this can not be judged.
>>
>> regards, Martin
>>
>> On 13/07/2020 02:13, - - wrote:
>> > I have run the series of INSERT tests in PHP for monetdb and SAP SQLAnywhere.  The tests show: performance of the monetdb on INSERT operations is almost twice slower against SQLA with the equal conditions (DDL, index, data, autocommit, 10K records),
>> > monetdb is in in-memory mode . What can be a reason?
>> > p.s. It is not benchmark, I just used the SQLA test server already running.
>> > --
>> > - -
>> >
>> > _______________________________________________
>> > 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
>> _______________________________________________
>users-list mailing list
>users-list@monetdb.org
>https://www.monetdb.org/mailman/listinfo/users-list 
 
 
--
- -
 

hi Martin,

just for info,

results for clickhouse 

Work: 322.42408704758 seconds
Work: 315.65788292885 seconds

and exasol ( to be honest, it was some activity on host system so perhaps results could be better)

Work: 348.83456234240 seconds
Work: 351.62343298452 seconds

2: Rajendra Singh Negi,  as you see it is quite fair to compare, both columnar servers have beaten SQLA on insert. however,
    some time ago, maybe few years ago, it was vice versa.
   

"A funny routine" was used due to monetdb looks like maps the same data internally on disk/memory.

so,

if static data is used to fill table 

bat]# du -hs
2.1M

if  random

bat]# du -hs
130M


and it could be the reason why monetdb is slower. mapping takes a time.

if it is, it is quite good trick for performance and size but is it configurable? possible to switch it on/off? just for specific tables?


[script]

//static
   $rnd1=str_random(3200);
   $rnd2=str_random(3200);
   $rnd3=str_random(3200);
   $rnd4=str_random(3200);

                     
$time_start = microtime(true);

for ($i=1;$i<10000;$i++){

//dynamic
//   $rnd1=str_random(3200);
//   $rnd2=str_random(3200);
//   $rnd3=str_random(3200);
//   $rnd4=str_random(3200);


 monetdb_query("INSERT INTO test(id,v1,v2,v3,v4) VALUES (".$i.",'".$rnd1."','".$rnd2."','".$rnd3."','".$rnd4."');") or die(monetdb_last_error());

}


pls do not try to find a hidden reason in the query or logic behind. it is merely test.

  
>Вторник, 14 июля 2020, 23:45 +03:00 от Martin Kersten :
> 
>Hi ?????
>
>The information provided is still insufficient to check the numbers independently
>and draw any conclusion.
>
>On 13/07/2020 08:52, - - wrote:
>> hi Martin,
>>   monetdb version: 11.37.7-20200529
>>   sql: generic insert — INSERT INTO table(id,v1,v2,v3,v4) VALUES (…);
>What is the table schema?
>
>>   test: same environment for both servers: vmbox 8gb, 4 cores, only one server is running on tests, no load of the host server
>>   php 7.4.5,  SQLAnyhwere pdo extension;  php-monetdb-1.0-1.el8.noarch.rpm
>Did you looked at the bulk loading advice?
>https://www.monetdb.org/Documentation/SQLReference/DataManipulation/BulkInputOutput
>Which provides bulk insert from the MonetDB client, but whose functionality has not
>been carried over to all APIs.
>
>>   script: same for both tests except connection, measured only loop execution,   the script uses «random» functions to
>>   generate  data for insert
>Please share the script to make the test explicit.
>
>> function str_random($length = 3200)
>> {
>>     $pool = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
>>     return substr(str_shuffle(str_repeat($pool, $length)), 0, $length);
>> }
>A funny routine to create a database with non-sense text permutations of a fixed length.
>This is far away from a real-world application case.
>
>And if the table only contains text columns, I am curious why you would consider a row/column store at all.
>A document or key/value store would probably be more appropriate unless you have specific query set in mind as well,
>e.g. a collection text pattern queries??
>Please, share the query set as well.
>
>regards, Martin
>
>>   the execution time ratio for all tests was almost the same. each test was done on «clean» server.
>>   one of the test:
>>   monetdb:  Work: 796.90835905075 seconds
>>   SQLA:      Work: 382.80270600319 seconds
>>   /alex
>>
>> Понедельник, 13 июля 2020, 8:44 +03:00 от Martin Kersten < martin@monetdb.org >:
>> Hi 
>>
>> There can be many reasons. Ranging from version used, SQL difference, test running, en PHP? API.
>> Based on the information provided this can not be judged.
>>
>> regards, Martin
>>
>> On 13/07/2020 02:13, - - wrote:
>> > I have run the series of INSERT tests in PHP for monetdb and SAP SQLAnywhere.  The tests show: performance of the monetdb on INSERT operations is almost twice slower against SQLA with the equal conditions (DDL, index, data, autocommit, 10K records),
>> > monetdb is in in-memory mode . What can be a reason?
>> > p.s. It is not benchmark, I just used the SQLA test server already running.
>> > --
>> > - -
>> >
>> > _______________________________________________
>> > 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
>>
>_______________________________________________
>users-list mailing list
>users-list@monetdb.org
>https://www.monetdb.org/mailman/listinfo/users-list 
 
 
--
- -
 

hi,
  
>Thursday, July 16, 2020 9:33 AM +03:00 from Martin Kersten :
> 
>Hi
>
>the storage footprint is 4 * 3200 *10000 ~= 128MB
 
     exactly, but if static data was used it is just 2.1M (including internals).
 
 
static data
 
bat]# du -hs
2.1M
 
    sql>truncate table test;
           9999 affected rows
 
bat]# du -hs
1.8M
 
 
random data
 
bat]# du -hs
134M
 
    sql>truncate table test;
           9999 affected rows
 
bat]# du -hs
1.8M
 
   do you see what i meant?
 
 
>One of the issues for a factor two may come from the
>default settings of the transaction managers involved.
>For MonetDB the default is autocommit, which means
 
      sure, but it is not a reason for a slow processing.
      other db were tested with autocommit as well. i mentioned it from the beginning.
 
>that every insert leads to a forced flush to disk
>to update the WAL.The alternative is to wrap the complete
>sequence in one compound transaction using START TRANSACTION, COMMIT
>
>On 15/07/2020 22:27, - - wrote:
>> hi Martin,
>>
>> just for info,
>>
>> results for clickhouse
>>
>> Work: 322.42408704758 seconds
>> Work: 315.65788292885 seconds
>>
>> and exasol ( to be honest, it was some activity on host system so perhaps results could be better)
>>
>> Work: 348.83456234240 seconds
>> Work: 351.62343298452 seconds
>>
>> 2: Rajendra Singh Negi,  as you see it is quite fair to compare, both columnar servers have beaten SQLA on insert. however,
>>     some time ago, maybe few years ago, it was vice versa.
>>
>>
>> "A funny routine" was used due to monetdb looks like maps the same data internally on disk/memory.
>>
>> so,
>>
>> if static data is used to fill table
>>
>> bat]# du -hs
>> 2.1M
>>
>> if  random
>>
>> bat]# du -hs
>> 130M
>>
>>
>> and it could be the reason why monetdb is slower. mapping takes a time.
>>
>> if it is, it is quite good trick for performance and size but is it configurable? possible to switch it on/off? just for specific tables?
>>
>>
>> [script]
>>
>> //static
>>    $rnd1=str_random(3200);
>>    $rnd2=str_random(3200);
>>    $rnd3=str_random(3200);
>>    $rnd4=str_random(3200);
>>
>>
>> $time_start = microtime(true);
>>
>> for ($i=1;$i<10000;$i++){
>>
>> //dynamic
>> //   $rnd1=str_random(3200);
>> //   $rnd2=str_random(3200);
>> //   $rnd3=str_random(3200);
>> //   $rnd4=str_random(3200);
>>
>>
>>  monetdb_query("INSERT INTO test(id,v1,v2,v3,v4) VALUES (".$i.",'".$rnd1."','".$rnd2."','".$rnd3."','".$rnd4."');") or die(monetdb_last_error());
>>
>> }
>>
>>
>> pls do not try to find a hidden reason in the query or logic behind. it is merely test.
>>
>> Вторник, 14 июля 2020, 23:45 +03:00 от Martin Kersten < martin.kersten@cwi.nl >:
>> Hi ?????
>>
>> The information provided is still insufficient to check the numbers independently
>> and draw any conclusion.
>>
>> On 13/07/2020 08:52, - - wrote:
>> > hi Martin,
>> >   monetdb version: 11.37.7-20200529
>> >   sql: generic insert — INSERT INTO table(id,v1,v2,v3,v4) VALUES (…);
>> What is the table schema?
>>
>> >   test: same environment for both servers: vmbox 8gb, 4 cores, only one server is running on tests, no load of the host server
>> >   php 7.4.5,  SQLAnyhwere pdo extension;  php-monetdb-1.0-1.el8.noarch.rpm
>> Did you looked at the bulk loading advice?
>>  https://www.monetdb.org/Documentation/SQLReference/DataManipulation/BulkInputOutput
>> Which provides bulk insert from the MonetDB client, but whose functionality has not
>> been carried over to all APIs.
>>
>> >   script: same for both tests except connection, measured only loop execution,   the script uses «random» functions to
>> >   generate  data for insert
>> Please share the script to make the test explicit.
>>
>> > function str_random($length = 3200)
>> > {
>> >     $pool = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
>> >     return substr(str_shuffle(str_repeat($pool, $length)), 0, $length);
>> > }
>> A funny routine to create a database with non-sense text permutations of a fixed length.
>> This is far away from a real-world application case.
>>
>> And if the table only contains text columns, I am curious why you would consider a row/column store at all.
>> A document or key/value store would probably be more appropriate unless you have specific query set in mind as well,
>> e.g. a collection text pattern queries??
>> Please, share the query set as well.
>>
>> regards, Martin
>>
>> >   the execution time ratio for all tests was almost the same. each test was done on «clean» server.
>> >   one of the test:
>> >   monetdb:  Work: 796.90835905075 seconds
>> >   SQLA:      Work: 382.80270600319 seconds
>> >   /alex
>> >
>> > Понедельник, 13 июля 2020, 8:44 +03:00 от Martin Kersten < martin@monetdb.org >:
>> > Hi 
>> >
>> > There can be many reasons. Ranging from version used, SQL difference, test running, en PHP? API.
>> > Based on the information provided this can not be judged.
>> >
>> > regards, Martin
>> >
>> > On 13/07/2020 02:13, - - wrote:
>> > > I have run the series of INSERT tests in PHP for monetdb and SAP SQLAnywhere.  The tests show: performance of the monetdb on INSERT operations is almost twice slower against SQLA with the equal conditions (DDL, index, data, autocommit, 10K records),
>> > > monetdb is in in-memory mode . What can be a reason?
>> > > p.s. It is not benchmark, I just used the SQLA test server already running.
>> > > --
>> > > - -
>> > >
>> > > _______________________________________________
>> > > 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
>> >
>> _______________________________________________
>> 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 
 
 
--
- -