- users-list - monetdb.org

[MonetDB-users] Scale
by Benjie Chen 11 Jul '12

11 Jul '12

Hi We are currently setup to use MonetDB to capture data from our mass spec machines. We generate about 30GB of data per day, so looking at a few TB of data per year. We are curious to hear what others who have used MonetDB, handle storage scaling. What kind of hardware do you use? Is there distributed/federated support in MonetDB? Are there map/reduce techniques you've used over the data, etc. Thanks, Benjie

1 0

[MonetDB-users] mserver crash under load -16G data, 10 queries each from 2 remote clients
by Tapomay Dey 10 Jul '12

10 Jul '12

I have a database with Single table. 15 million rows. 16G of data. 58 columns. MonetDB stores it consuming 32G of data. mserver Machine specs: Main memory: 4G Processor: Intel(R) Xeon(R) CPU E5645 @ 2.40GHz. Single core. I am performing a test to see how much time it takes if I fire this query multiple times from different machines. Case 1: I fire an aggregation query 10 times from a remote machine with 5sec interval between each firing. The result hs 17000 rows with around 4 MB of data. Memory usage of mserver process reahes a peak of 73% mclients finish within 43 to 48 seconds. Case 2: I do the same as above but now from 2 remote machines simultaneously. Memory usage of mserver process reahes a peak of 73% again. It keeps fluctuating between 70 and 73%. But after some time mserver restarts and the clients terminate with all the 10 clients on each of both machines saying: ERROR = !Connection terminated MAPI = tapomay@54.251.11.181:3306 ACTION= read_line QUERY = select id1, id2, id3, id4, id5, count(*), sum(metric1), avg(metric2), sum(metric3), sum(metric4), sum(metric5), sum(metric6), sum(metric7), sum(metric8), sum(metric9), sum(metric9), sum(metric10), sum(metric11), sum(metric12), sum(metric13) from tablename group by id1, id2, id3, id4, id5; Each query terminates within 5 to 50 seconds with the above error. In both cases intial state of mserver consumes around 0.5% memory. Also tested the same under other competing columnar DBs. They complete the case 2 in around 350 or more seconds average. They complete case 1 in 200 seconds average. Following is a last few lines of tail of merovingian.log 2012-07-05 06:30:42 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45476 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:42 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:45 MSG merovingian[31153]: proxying client 2.amazonaws.com:46225 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:45 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:47 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45477 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:47 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:50 MSG merovingian[31153]: proxying client 2.amazonaws.com:46226 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:50 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:52 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45478 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:52 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:55 MSG merovingian[31153]: proxying client 2.amazonaws.com:46241 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:55 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:59 MSG merovingian[31153]: database 'tablename' (1048) was killed by signal SIGKILL Is there a config that would ask mserver to not kill itself and keep processing at the cost of query execution time. A few more tens of seconds won't harm much. Thanks and Regards, Tapomay.

4 10

Re: [MonetDB-users] MonetDB Python client
by Alexey Mints 09 Jul '12

09 Jul '12

Hi, Fabian! Yeh, I have shifted try outside of loop. I am not a big expert in this, but does it make any difference? Alexey.

2 1

[MonetDB-users] Expected query to be much faster, but is actually two orders of magnitude slower!
by Shahbaz 05 Jul '12

05 Jul '12

Hi All, I did some experiments with monetdb over the weekend and found some strange and unexpected behavior. My database has a single table which stores stock trades. The table is defined as follows: create table trades( td varchar(2), // just a dummy field date int, time int, ticker varchar(17), price double, qty int, vol int, conditioncode int, exch varchar(8)); This table has a little under 1 Billion rows and takes up around 52 gigabytes. The source data is also around 48 gigabytes. I expected monetdb to aggressively compress the columns and reduce the disk usage by 10 fold or so! In any case, that is not the main issue. [select sum(date), sum(time), count(ticker) from trades where date = 20120501;] This query took 1.1 seconds the first time and around 173 ms the second two times (count(ticker) returned 31,268,285). [select sum(date), sum(time), count(ticker) from trades where date = 20120501 and time > 34200000;] This query took about 250 ms the first, second and third times I ran it. count(ticker) returned 31,176,190. (I doing the same thing as previous query, but asking to ignore all trades before the market opened...obviously very very few trades occur before the market opens). [select sum(date), sum(time), count(ticker) from trades where date = 20120501 and time > 34200000 and time < 37800000;] This query took 1 minute and 15 seconds! The second time I ran it, it took 1 minute and 11 seconds. Notice that count(ticker) in this query only returned 5,969,337. This query is essentially the same as the previous one, but now I am only looking to process trades between 9:30 am and 10:30 am. So this query processes 1 hours' worth of trades while previous queries process the whole day. Notice that all three queries process the same columns. Between the second and third columns, all the columns used in the select clause and where clause are exactly the same. The second query just add an additional filter on the time column. Why is the third query two orders of magnitude slower than the second query? This experiment was done on an ubuntu server: >uname -a Linux abcdefg 3.2.0-25-generic #40-Ubuntu SMP Wed May 23 20:30:51 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux >cat /proc/meminfo |fgrep Mem MemTotal: 8168280 kB MemFree: 3588848 kB >hdparm -t ...disk... Timing buffered disk reads: 510 MB in 3.00 seconds = 169.80 MB/sec Please let me know if you require more information. Thanks, Shahbaz

4 3

[MonetDB-users] MonetDB Python client
by Alexey Mints 05 Jul '12

05 Jul '12

Hello! I had a problem with Python client hanging up if MonetDB connection was closed unexpectedly. This happened for example, when I have passed a request that caused "ERROR = !Connection terminated" in mclient. Such things should not occur in a ideal life, but they do =( The problem was that socket.recv cannot handle this situation properly. The solution is to use select.select statement. I attach the diff to mapi2.py file that solves the problem. Regards, Alexey

2 1

[MonetDB-users] importing data from csv
by Flavio Coelho 30 Jun '12

30 Jun '12

I am trying to import data from a CSV file separated by ';' and with string values surrounded by double quotes: " but I keep getting the following error messages: missing separator ';' line 225387 expecting 8 got 7 fields missing separator ';' line 75129 expecting 8 got 7 fields missing separator ';' line 0 expecting 8 got 7 fields missing separator ';' line 450774 expecting 8 got 7 fields missing separator ';' line 300516 expecting 8 got 7 fields missing separator ';' line 150258 expecting 8 got 7 fields missing separator ';' line 525903 expecting 8 got 7 fields missing separator ';' line 375645 expecting 8 got 7 fields failed to import table I am using the following command: copy 300000000 offset 2 records into mobilidade.ligacoes from '/Users/fccoelho/Documents/dados_mobilidade/bilhetagem.txt' using delimiters ';','\n','"' ; after creating the table like this: sql dump of my table: CREATE TABLE "mobilidade"."ligacoes" ( "id" INTEGER NOT NULL DEFAULT next value for "mobilidade"."seq_5840", "DT_INI_ATIVIDADE_REDE" VARCHAR(10), "HR_INI_ATIVIDADE_REDE" VARCHAR(7), "NU_SETOR_CELULA_ORIG" INTEGER, "NU_AREA_CELULA_ORIG" INTEGER, "NU_DDD_ORIG" INTEGER, "NU_DDD_DESTINO" INTEGER, "CD_TELEFONE_ORIG_ALTER" INTEGER DEFAULT NULL, "CD_TELEFONE_DESTINO_ALTER" VARCHAR(16) DEFAULT NULL, CONSTRAINT "ligacoes_id_pkey" PRIMARY KEY ("id") ); Naturally, I checked line 0 and it does have 8 fields. What am I doing wrong? -- Flávio Codeço Coelho

2 2

[MonetDB-users] COPY INTO
by DADOU 29 Jun '12

29 Jun '12

COPY INTO test from '/tmp/bla'; COPY INTO: insufficient privileges: COPY INTO from file(s) requires administrator rights, use 'COPY INTO "test" FROM STDIN' instead can you help me -- View this message in context: http://old.nabble.com/COPY-INTO-tp34088907p34088907.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] COPY INTO
by DADOU 29 Jun '12

29 Jun '12

I have a Problem with copy into copy into "LINEORDER" from ('E:\TEST.tbl'); COPY INTO: insufficient privileges: binary COPY INTO requires administrator rights TEST.tbl is a file with 6 000 000 rows. these a format for two rows in file TEST.tbl 1|1|7381|155190|4137|19960102|TRUCK| 1|2|7381|67310|815|19960102|MAIL| can help me please -- View this message in context: http://old.nabble.com/COPY-INTO-tp34088847p34088847.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Basic sum aggregation returning incorrect value
by Tapomay Dey 28 Jun '12

28 Jun '12

I performed the following operations sequentially. Please look at the result when I use SUM. Sum values are incorrect. They should be 60/600/6000/60000.AVG returns correct values though. What could be going wrong? Is it something I can fix? sql> sql>create table table1 (id int, value int); operation successful (64.427ms) sql>create table table2 (id int, value int); operation successful (78.679ms) sql>insert into table1 values(1, 10); 1 affected row (25.389ms) sql>insert into table1 values(1, 20); 1 affected row (28.254ms) sql>insert into table1 values(1, 30); 1 affected row (27.512ms) sql>insert into table1 values(2, 300); 1 affected row (28.255ms) sql>insert into table1 values(2, 200); 1 affected row (28.658ms) sql>insert into table1 values(2, 100); 1 affected row (29.606ms) sql>insert into table2 values(1, 1000); 1 affected row (25.144ms) sql>insert into table2 values(1, 2000); 1 affected row (26.130ms) sql>insert into table2 values(1, 3000); 1 affected row (27.490ms) sql>insert into table2 values(2, 30000); 1 affected row (18.623ms) sql>insert into table2 values(2, 20000); 1 affected row (40.441ms) sql>insert into table2 values(2, 20000); 1 affected row (29.716ms) sql>insert into table2 values(2, 10000); 1 affected row (25.907ms) sql>select t1.id, sum(t1.value) as v1, sum(t2.value) as v2 from table1 t1, table2 t2 where t1.id=t2.id group by t1.id; +------+------+--------+ | id | v1 | v2 | +======+======+========+ | 1 | 180 | 18000 | | 2 | 2400 | 240000 | +------+------+--------+ 2 tuples (1.769ms) sql>select t1.id, avg(t1.value) as v1, avg(t2.value) as v2 from table1 t1, table2 t2 where t1.id=t2.id group by t1.id; +------+--------------------------+--------------------------+ | id | v1 | v2 | +======+==========================+==========================+ | 1 | 20 | 2000 | | 2 | 200 | 20000 | +------+--------------------------+--------------------------+ 2 tuples (2.275ms) sql> Regards, Tapomay.

2 1

[MonetDB-users] last inserted id
by Tomas Mikula 26 Jun '12

26 Jun '12

Hi, how can I get the ID (value of an AUTO_INCREMENT column) of the last inserted row, from within an SQL script? I am able to get it through the Python API, so I suppose there is a way to get it inside an SQL script, too. Thanks, Tomas

2 1