- users-list - monetdb.org

[MonetDB-users] Change schema for already existing tables
by Travis Brady 22 Jan '08

22 Jan '08

Hello, Is it possible to change the schema for tables that have already been created and populated? I'd like to avoid having to fully recreate everything as the load process will likely take a few days at least. thank you, Travis Brady

5 5

[MonetDB-users] Problems with COPY INTO and CHAR(N) columns containing empty strings or all spaces
by Bob Ippolito 22 Jan '08

22 Jan '08

We've had problems with MonetDB SR5 using COPY INTO with data that may be the empty string or all spaces. We've been able to work around this by surrounding all of the values in our copy files with "quotes" (I read tablet.c to try and diagnose the issue and found that as a workaround). I'm not familiar enough with the source and build process yet to submit a patch. We are using '\t' as the separator and the fields that we've had problem with are CHAR(2) or CHAR(16) if that matters. -bob

2 3

[MonetDB-users] Optimal filesystem/platform choices for MonetDB?
by Bob Ippolito 22 Jan '08

22 Jan '08

We've started doing a big import of data into SR5 and I've noticed that it's creating *A LOT* of files. So far we've got about 620k files for 6.7 GB of bat. I expect that a full import should have at least 20 times what's imported so far. It seems that loading data is relatively slow compared to PostgreSQL (we're using COPY) and I'm guessing it's due to all of these files. Right now we're using ext3 on amd64 ubuntu linux. Should we be looking at other platforms? -bob

2 3

[MonetDB-users] Core dump on first connect by mclient in BUNinplace
by Bob Ippolito 22 Jan '08

22 Jan '08

So we've been testing out a big ETL and it crashed again, and now every time we start mserver5 it will seemingly start up ok but as soon as mclient connects it will crash. I've generated a core dump. The db farm is 3.6GB and the core dump is 15MB. I can send either to someone if they need it to look further into the issue. The platform is 2.6.20-15-generic (root@yellow) (gcc version 4.1.2 (Ubuntu 4.1.2-0ubuntu4)) #2 SMP on amd64. It's running an unmodified SR5 superball that we compiled ourselves. I tried looking at strace and the --debug option with all of the flags on but it didn't produce anything that looked very interesting. The backtrace is here: Core was generated by `//mochi/bin/mserver5 --dbname=mochiads_stats --dbinit=include sql; --dbfarm=/da'. Program terminated with signal 11, Segmentation fault. #0 0x00002b88a35bf285 in BUNinplace (b=0x12458e8, p=0x0, h=<value optimized out>, t=0x1246eb0, force=1 '\001') at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB/src/gdk/gdk_bat.mx:1555 1555 ATOMreplace(b->ttype, b->theap, BUNtloc(b, p), t); (gdb) bt #0 0x00002b88a35bf285 in BUNinplace (b=0x12458e8, p=0x0, h=<value optimized out>, t=0x1246eb0, force=1 '\001') at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB/src/gdk/gdk_bat.mx:1555 #1 0x00002b88a35bfb75 in void_inplace (b=0x12458e8, id=1, val=0x1245a20, force=1 '\001') at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB/src/gdk/gdk_bat.mx:1671 #2 0x00002b88a35bfc33 in void_replace_bat (b=0x12458e8, u=0x1246d08, force=<value optimized out>) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB/src/gdk/gdk_bat.mx:1688 #3 0x00002aaab26c9a5a in load_bat (sname=<value optimized out>, tname=<value optimized out>, bname=<value optimized out>, type=<value optimized out>, sz=<value optimized out>, cnt=0x1214400) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/bat/bat_storage.mx:364 #4 0x00002aaab26c9b83 in create_col (tr=<value optimized out>, c=0x1245878) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/bat/bat_storage.mx:456 #5 0x00002aaab26c44a9 in load_table (tr=0xd3dec8, s=0xd3df38, rid=149) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/store.mx:511 #6 0x00002aaab26c5523 in load_trans (tr=0xd3dec8, id=1128) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/store.mx:845 #7 0x00002aaab26c6ff4 in store_init (debug=<value optimized out>, store=<value optimized out>, logdir=<value optimized out>, dbname=<value optimized out>, stk=0) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/storage/store.mx:1318 #8 0x00002aaab26aab79 in mvc_init (debug=0, store=store_bat, stk=0) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/server/sql_mvc.mx:246 #9 0x00002aaab2665c97 in SQLinit (c=<value optimized out>) at /ufs/sjoerd/src/MonetDB/virgin-stable/sql/src/backends/monet5/sql_scenario.mx:225 #10 0x00002b88a2c7d42a in initScenario (c=0x604980, s=0x2b88a2e934d0) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB5/src/mal/mal_scenario.mx:264 #11 0x00002b88a2c58d82 in MSscheduleClient (command=0xb0a1b8 "LIT", challenge=0x41000fd0 "KdhtXMmr29n", fin=0xb10270, fout=0xb03030) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB5/src/mal/mal_session.mx:342 #12 0x00002aaab0e78247 in doChallenge (cmd=<value optimized out>, in=<value optimized out>, out=<value optimized out>) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB5/src/modules/mal/mserver.mx:482 #13 0x00002aaab0e79036 in SERVERlistenThread (Sock=<value optimized out>) at /ufs/sjoerd/src/MonetDB/virgin-stable/MonetDB5/src/modules/mal/mserver.mx:564 #14 0x00002b88a43122a5 in start_thread () from /lib/libpthread.so.0 #15 0x00002b88a4ff261d in clone () from /lib/libc.so.6 #16 0x0000000000000000 in ?? () -bob

2 2

Re: [MonetDB-users] MonetDB/SQL 64-bit
by Martin Kersten 22 Jan '08

22 Jan '08

Stefan Manegold wrote: > Hi Peter, > > unfortunately, this still does not help us much --- at least not me (but I'm > everything but a Windows expert) ... > the 4 tutorial sessions on MonetDB website have been designed to uncover installation errors quickly and to get a first working session. Is tutorial 1 running? > I know, though, that on Windows both server & client need to be started via > the proper batch scripts (or by clicking on the respective icons) to ensure > evironment and paths are set correctly. > yes, the commands execute a script, which sets paths correctly. locate them to see what is required to properly run both. Especially, if you have manually moved folders around or changed permissions. > To help you any further, (sorry for repeating myself), we'd need to know > the exact error message, or at least the paths/names of DLLs that are not > found. > > Furthermoe, could you please continue mailing to neten(a)cwi.nl (as requested > monet(a)cwi.nl > by the "Rply-To:" in the mail header of bot my first and this reply; or even > better to Monetdb-users(a)lists.sourceforge.net)? --- thus, more people than > just me get to see your problem and can help answering and solving it... > > Stefan > > On Tue, Jan 22, 2008 at 12:51:19AM -0600, Peter Haluska wrote: > >> Hi Stefan, >> >> installation has worked. It looks like that it is having problems with >> system paths while trying to run server file(can not find dlls) or server >> batch file. Thanks. >> >> -Peter >> >> >> >> On Jan 21, 2008 6:24 PM, Stefan Manegold <Stefan.Manegold(a)cwi.nl> wrote: >> >> >>> Dear Peter, >>> >>> thank you very much for your interest in MonetDB! >>> >>> To be of any help for you, it is necessary for us to know exactly what >>> does >>> (not) work for you. >>> >>> Does it install? >>> Can you start a server? >>> Can you start a client and connect to the server? >>> Can you load data? >>> Can you run a query? >>> >>> Hence, please let us know exactly which steps you took to download, >>> install, >>> start, etc. MonetDB/SQL, where you get stuck, whether you get any error >>> message(s), and if so, which one(s)... >>> >>> Kind regards, >>> Stefan >>> >>> >>> On Mon, Jan 21, 2008 at 06:02:05PM -0600, Peter Haluska wrote: >>> >>>> Hi, we are testing your software MonetDB/SQL >>>> 64-bit< >>>> >>> http://monetdb.cwi.nl/projects/monetdb//download.php?target=http://prdownlo… >>> >>> > on >>> >>>> our new system 'Windows Server 2008 RC1 Enterprise Edition' with 64-bit >>>> support server and we can not make it to work. >>>> Any suggestions? Thanks. >>>> >>>> -Peter >>>> > >

1 0

[MonetDB-users] Unexpected results with COALESCE and aggregates
by Bob Ippolito 21 Jan '08

21 Jan '08

MonetDB SR5 behaves unexpectedly when we've tried to use COALESCE with aggregates, here's an example: sql>CREATE TABLE tmp(a int); sql>SELECT SUM(a) FROM tmp; +-------+ | sum_a | +=======+ | 0 | +-------+ sql>SELECT MIN(a) FROM tmp; +-------------+ | min_a | +=============+ | null | +-------------+ sql>SELECT COALESCE(MIN(a), 0) FROM tmp; sql> For the first SELECT I would expect NULL, the second SELECT is okay, but I expect a result for the third! Here's what PosgreSQL does: bob=# CREATE TABLE tmp(a int); CREATE TABLE bob=# SELECT SUM(A) FROM tmp; sum ----- (1 row) bob=# SELECT MIN(a) FROM tmp; min ----- (1 row) bob=# SELECT COALESCE(MIN(a), 0) FROM tmp; coalesce ---------- 0 -bob

2 2

[MonetDB-users] timestamptz issues with non-GMT
by Bob Ippolito 21 Jan '08

21 Jan '08

We've been playing around with MonetDB-Mars-SuperBall-SR5 for the past couple weeks and we've been having some issues with timestamps. We unfortunately don't do all of our calculations on GMT so we've hit a couple walls because it seems that the MonetDB 5 mtime module doesn't consider timezones: (Yes I know the compiler seems to optimize out the original type if you look at the EXPLAIN output, but this happens for timestamptz coming from tables too) sql>set time zone interval '-08:00' hour to minute; sql>SELECT CAST(TIMESTAMPTZ '2008-01-01 00:00+00:00' AS DATE); +--------------+ | single_value | +==============+ | 2008-01-01 | +--------------+ PostgreSQL has the expected behavior, which accounts for the local time zone when converting timestamps to dates. bob=# SET TIMEZONE TO 'PST8PST8'; SET bob=# SELECT '2008-01-01 00:00+00:00'::timestamptz::date; date ------------ 2007-12-31 (1 row) This is the best I've been able to do, but it's of course ugly and it seems like it could break if the implementation gets fixed: sql>SELECT CAST(TIMESTAMPTZ '2008-01-01 00:00+00' + CURRENT_TIMEZONE AS DATE); +----------------------+ | sql_add_single_value | +======================+ | 2007-12-31 | +----------------------+ Is the "best" solution to just use the GMT timezone (or the TIMESTAMP type) and hardcode our timezone offset everywhere? sql>SELECT CAST(TIMESTAMP '2008-01-01 00:00' - SEC_INTERVAL '28800' AS DATE); +----------------------+ | sql_sub_single_value | +======================+ | 2007-12-31 | +----------------------+ -bob

1 0

[MonetDB-users] "bpm_storage.h" missing when trying to install "sql" module
by xxiao 19 Jan '08

19 Jan '08

Hi, I have sucessfully installed the following modules of MonetDB monetdb version 1.21.0 monetdb-clients version 1.21.0 monetdb4 version 4.21.0 When I continue to install "sql" module, the "make" complains missing file "bpm_storage.h". The platform is Solaris10/SPARC. Anyone here has a hint what has gone wrong? Thank you so much for your attention. Xiang -- View this message in context: http://www.nabble.com/%22bpm_storage.h%22--missing-when-trying-to-install-%… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] mserver5 question
by Claude Monet 18 Jan '08

18 Jan '08

OS: CentOS 4.6 I am only able to initiate mserver5 as su. What should I do to be able to start the server in a user account? Thanks

2 4

Re: [MonetDB-users] tuning advice for TPC-H
by Rahul Chopra 18 Jan '08

18 Jan '08

I thought I would try and ask these questions again, since there was no response. - How does how MonetDB handles its virtual memory? Does it swap out entire BATs at a time? Or just smaller blocks? What type of caching algorithms does it use? - If I configure a large swap, MonetDB is not using it. I guess it allocates based on actual physical memory. If I want to run a test to let the OS handle virtual memory and have MonetDB try and use more of system virtual memory, how do I do that? I am interested in seeing how this affects performance when the database is too large to fit in memory. - Are there any other tuning settings I should set when using a database that is much larger than memory? Thanks in advance, Rahul > ----- Original Message ----- > From: "Rahul Chopra" <rchopra(a)linuxmail.org> > To: "Communication channel for MonetDB users" <monetdb-users(a)lists.sourceforge.net> > Subject: Re: [MonetDB-users] tuning advice for TPC-H > Date: Thu, 10 Jan 2008 03:14:54 +0800 > > > > I am still trying to get better performance when doing TPC-H tests > for a database more than twice the size of physical memory. > > I am not sure how MonetDB handles its virtual memory, but it sure > seems to be going to disk an awful lot. Does it swap out entire > BATs? Or just smaller blocks? What type of caching algorithms does > it use? > > Also, one of the things I wanted to try is to setup a really big > swap partition on a separate device with write-back cache enabled > and have MonetDB use it (Basically, see if Linux will virtual > memory better than MonetDB does, and take advantage of putting swap > on a separate device for speed). When I try and do that though, > MonetDB seems to just be trying to stay within the confines of > physical memory available. Is there something I can configure to > try and get it to use system virtual memory? Has anyone tried out > anything like that? > > -Rahul > > > > > > > > > > > > > > I am trying to run some TPC-H queries to get a sense for how > > > > large of databases MonetDB can handle. I am currently trying > > a > > factor 20 database. I notice that on query 9, it is taking > > over > > two hours (still running). In "top" it claims that > > virtual > memory > for the process started at 27 GB or so, and is > > now down > to 10 GB. > What is noteworthy is that CPU usage is > > pretty small, > at 2%. I > previously observed 80-100% with a > > factor 1 database. > > > > > > Does this mean you got the sf-20 loaded? What fixed your problem with > > > loading? > > > > Yes, I got it loaded. Since I had broken up the file into smaller > > chunks, I just restarted on the last chunk that it failed on > > before the crash. Eventually, it went through all of the tables. > > > > > > > > The low cpu usage indicates that MonetDB is waiting for IO. I'll later > > > this week try to repeat your experiment on a similar system. Then I'll > > > have more input on how to improve things. > > > > After writing the email I noticed that system CPU wait time was > > at 25%, with MonetDB at 2% and idle at 73% or so. So, yes, it is > > waiting on IO. Perhaps it is just my hardware. > > > > I did disable the 2 GB swap partition and rerun. After doing so, > > execution time did improve somewhat. I am not sure if this means > > one should always disable swap on a MonetDB system, since the > > system drive is slower than the data drives. I will try another > > experiment and let you know the results. > > > > > = > College For Financial Planning > Nation's most successful CFP® Program with 35+ years of experience. > http://a8-asy.a8ww.net/a8-ads/adftrclick?redirectid=0badaaf35b41b0f3ca7504b… > > > -- > Powered by Outblaze > > ------------------------------------------------------------------------- > Check out the new SourceForge.net Marketplace. > It's the best place to buy or sell services for > just about anything Open Source. > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace > _______________________________________________ > MonetDB-users mailing list > MonetDB-users(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users > = Las Vegas, Nevada Home for Sale Dave Radcliffe will help you find your Las Vegas new home for sale, dream condo, townhome, or real estate investment property. Search here for all the homes for sale in Las Vegas, Nevada. http://a8-asy.a8ww.net/a8-ads/adftrclick?redirectid=95c6cb18131e4c352f8c920… -- Powered by Outblaze

2 1