Hi,

Yes it's a 32bit system. 4G of Ram, Quad Xeon. OS: Ubuntu Server 6.10 - Raid10(4x HDs)

Total data size > 4G (but could be a lot more, sometimes i need to restream 1month worth of CDRs).

I was using a postgres/bizgres DB, and loading data with psql -c, + the COPY command - but didnt like the fact that, if a line was rejected the whole process was stopped, so that's why i've created my own perl ETL script able to ignore rejected entries - which might be the best option finally ? MonetDB's COPY command is doing the same. Also can the mclient re-use an active connection ?

Let me know what do you think.

Thanks

----- Message d'origine ----
De : Stefan Manegold <Stefan.Manegold@cwi.nl>
À : sylver_b <sylver_b@yahoo.com>
Cc : monetdb-developers@lists.sourceforge.net
Envoyé le : Samedi, 13 Septembre 2008, 9h12mn 53s
Objet : Re: [Monetdb-developers] Re : COPY, terminating connection

On Sat, Sep 13, 2008 at 12:43:32AM -0700, sylver_b wrote:
> Hi Stephan
>
> >How many (sequential, not concurrent, right?) connections are we talking
> >about, i.e., how many *.CDR files do you have?
> I wanted to stream about 60 to 100 CDR files
>
> >How many rows do these *.CDR files hold in total?
> some files can contain over half a million records - average is 100~200K lines
>
> >Are all attributes of the cdrwholesaler table string attributes?
> some of them are date/time, timestamp, int, double, but majority is varchar

How large is your total data volumne?
Does it fit into a 32-bit address space?

What kind of machine are you running on?
CPU, memory, OS?
Given that you're using a 32-bit MonetDB installation, I suppose you
hardware and OS is 32-bit, too, isn't it?

> It looks like for each call of mclient (probably for each cdr line) a connection is opened.

Obviously, you call mclient once for each of you CBR files, and yes, each of
these mclient calls make a new connection to the server.
For each CBR file (not line!) there should be only one mclient call, and
hence, only one connection.

Stefan

ps: please also note that the vrey fast bulk loading I mentioned only holds
for loading from files with the number of rows to be loaded specified in
the COPY INTO statement.

> SB
>
>
> ----- Message d'origine ----
> De : Stefan Manegold <Stefan.Manegold@cwi.nl>
> À : sylver_b <sylver_b@yahoo.com>
> Cc : monetdb-developers@lists.sourceforge.net
> Envoyé le : Samedi, 13 Septembre 2008, 6h36mn 19s
> Objet : Re: [Monetdb-developers] COPY, terminating connection
>
> On Fri, Sep 12, 2008 at 04:48:43AM -0700, sylver_b wrote:
> > Hi,
> >
> > When i run the COPY command from a batch script and after few minutes of processing (2,3minutes), and few thousand lines inserted in the DB, im getting :
> >
> > Connection terminated
> >
> >
> > merovingian log is showing:
> >
> > MSG demo[5084]: # MonetDB server v5.6.0, based on kernel v1.24.0
> > MSG demo[5084]: # Serving database 'demo'
> > MSG demo[5084]: # Compiled for i686-pc-linux-gnu/32bit with 32bit OIDs dynamically linked
> > MSG demo[5084]: # Copyright (c) 1993-2008 CWI, all rights reserved
> > MSG demo[5084]: # Visit http://monetdb.cwi.nl/ for further information
> > MSG demo[5084]: # Listening for connection requests on mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: proxying client 127.0.0.1:41420 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41420 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41422 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41422 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41424 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41424 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41426 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41426 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41428 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41428 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41430 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41430 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41432 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41434 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41432 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41436 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41434 disconnected from proxy
> > MSG merovingian[3981]: client has 127.0.0.1:41436 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41438 for database 'demo' to mapi:monetdb://ulys:50001/
> > MSG merovingian[3981]: client has 127.0.0.1:41438 disconnected from proxy
> > MSG merovingian[3981]: database 'demo' already running since 2008-09-12 11:44:44, up min/avg/max: 30/37441/213742, crash average: 0.00 0.10 0.03 (8-6=1)
> > MSG merovingian[3981]: proxying client 127.0.0.1:41440 for database 'demo' to mapi:monetdb://ulys:50001/
> > TME 2008-09-12 11:56:46
> > MSG merovingian[3981]: client has 127.0.0.1:41440 disconnected from proxy
> >
> >
> > This is what my script does :
> >
> > #!/bin/sh
> >
> > FILE=$1
> > awk -F "\"*;\"*" '{print ";",$1,";",$2,";",$3,";",$4,";",$5,";",$6,";",$8,";",$9,";",$10,";",$13,";",$15,";",$18,";",$22,";",$24,";",$25,";",$26,";",$27,";",$28,";",$29,";",$30,";",$31,";",$32,";",$33,";",$35,";",$36,";",$38,";",$39,";",$42,";",$47,";",$48,";",$49,";",$53,";",$54,";",$55,";","FILE"}' $1 | sed -e "s/ ; /;/g; s/; /;/g" | mclient -lsql -d demo -i -s "COPY INTO cdrwholesaler FROM STDIN USING DELIMITERS ';' NULL AS ''"
> >
> > and this is how i call it :
> >
> > find /usr/ftp -name '*.CDR' -exec /var/www/consolecdr/batch/test_monetdb/awk_csv_generator.sh {} \;
> >
> >
> > Is there anything im doing wrong ? I have the feeling there must be too many connections crashing the system .. coz after that i can't use mclient anymore, i have to restart the DB.
>
> How many (sequential, not concurrent, right?) connections are we talking
> about, i.e., how many *.CDR files do you have?
>
> How many rows do these *.CDR files hold in total?
>
> Are all attributes of the cdrwholesaler table string attributes?
>
> ... "too many connections" *might* be the problem (though it should of
> course not) --- strange, though, that the merovingian log do not give any
> indication of whether, and if so why the server might have crashed ...
>
> Stefan
>
> > Please advise,
> > Thanks
> > SB
> >
> >
> >
> > -------------------------------------------------------------------------
> > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> > Build the coolest Linux based applications with Moblin SDK & win great prizes
> > Grand prize is a trip for two to an Open Source event anywhere in the world
> > http://moblin-contest.org/redirect.php?banner_id=100&url=/
> > _______________________________________________
> > Monetdb-developers mailing list
> > Monetdb-developers@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/monetdb-developers
>
>
> --
> | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl |
> | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ |
> | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 |
> | The Netherlands | Fax : +31 (20) 592-4312 |
>
>
>
>
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> Build the coolest Linux based applications with Moblin SDK & win great prizes
> Grand prize is a trip for two to an Open Source event anywhere in the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> _______________________________________________
> Monetdb-developers mailing list
> Monetdb-developers@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-developers

--
| Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl |
| CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ |
| 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 |
| The Netherlands | Fax : +31 (20) 592-4312 |