Re: PHP insert performance

Hi the storage footprint is 4 * 3200 *10000 ~= 128MB 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 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 : 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/BulkInpu... 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 >: > 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