[Monetdb-developers] Re : Re : COPY, terminating connection
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
participants (1)
-
sylver_b