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 <who am i talking to?>
>
> 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 </compose?To=users%2dlist@monetdb.org>
> > https://www.monetdb.org/mailman/listinfo/users-list
> >
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org </compose?To=users%2dlist@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
 
 
--
- -