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. -- - -
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 https://www.monetdb.org/mailman/listinfo/users-list
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 <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
>> 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 ???? What is the link to the public accessible script. 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 <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
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 <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
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-da... ) 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
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;
On 13/07/2020 08:52, - - wrote: 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 <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
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 <harharharishwar@gmail.com>:
>
>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 <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
>_______________________________________________
>users-list mailing list
>users-list@monetdb.org
>https://www.monetdb.org/mailman/listinfo/users-list
>
--
- -
Hi, Just to add: 1. Would it be fair to compare a columnar database with a row based database for row inserts? 2. We would also initially need the following: (a) Hardware details : RAM, CPU, etc (b) Load size, nature, (c) data structure, number of columns to be loaded (d) network or local, etc Well this looks interesting and I shall be exploring monetDB bulk loads. Just FYI: more details of SAP IQ record: https://news.sap.com/2013/06/sap-sybase-iq-software-smashes-previous-results... Thanks, Sincerely, Rajendra Singh Negi Mobile: 7709112263 email: harharharishwar@gmail.com Chase the Dream and not the competition On Mon, Jul 13, 2020 at 9:56 PM - - <bindto@bk.ru> wrote:
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 < harharharishwar@gmail.com>:
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-da... ) 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
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;
On 13/07/2020 08:52, - - wrote: 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 <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
_______________________________________________ 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 <martin.kersten@cwi.nl>:
>
>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 <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
--
- -
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 <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
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
--
- -
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 <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/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 <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
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
--
- -
participants (4)
-
- - -
Martin Kersten -
Martin Kersten -
Rajendra Singh Negi