16 Jul
2020
16 Jul
'20
9:21 a.m.
hi,
>Thursday, July 16, 2020 9:33 AM +03:00 from Martin Kersten <martin.kersten@cwi.nl>:
>
>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 </compose?To=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> </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> </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 </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
--
- -