[MonetDB-users] MonetDB aborts when importing over 10 million rows
Hi,
I'm still trying to evaluate MonetDB but I keep getting errors when trying to import over 10 million rows to a table. The server simply aborts and closes. I'm using MonetDB on Windows XP. Whenener I restart after the server had aborted it shows messages like this:
#GDKmmap(71827456) fails, try to free up space [memory in use=18552560,virtual m emory in use=1837957120] #GDKmmap(71827456) result [mem=18552560,vm=1837957120] #GDKmmap(36175872) fails, try to free up space [memory in use=16499872,virtual m emory in use=1844903936] #GDKmmap(36175872) result [mem=16499872,vm=1844903936] #GDKmmap(143130624) fails, try to free up space [memory in use=15697728,virtual memory in use=1744764928] #GDKmmap(143130624) result [mem=15697728,vm=1744764928] #GDKmmap(143130624) fails, try to free up space [memory in use=13554656,virtual memory in use=1654456320] #GDKmmap(143130624) result [mem=13554656,vm=1654456320] #GDKmmap(143130624) fails, try to free up space [memory in use=8747960,virtual m emory in use=1650917376] #GDKmmap(143130624) result [mem=8747960,vm=1650917376] #GDKmmap(143130624) fails, try to free up space [memory in use=7946840,virtual m emory in use=1531052032] #GDKmmap(143130624) result [mem=7946840,vm=1531052032] !mvc_init: unable to create system tables !SQLException:SQLinit:Catalogue initialization failed !ERROR: HEAPextend: failed to extend to 71722596 for 14\1475tail !ERROR: HEAPextend: failed to extend to 35861298 for 15\1537tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1543tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1550tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1556tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1557tail #function user.main():void; # sql.prelude(); #end main;
First I was trying to import files with about 3 million rows each but it started giving errors after the 3rd file. Then I tried to import through files with about 1 million rows each and I was able to import about 14 million rows, but after about 10 million rows I was only able to import one or two files in a row without MonetDB resetting. Is this a bug or a size limit for the available memory. Anything I could try?
With regards,
Luis Fernando
Hi Luis,
Which version of MonetDB are you using?
Is your XP system 32-bit or 64-bit?
If the latter, is your MonetDB 32-bit or 64-bit?
How large is your dataset (.csv file) in total and what is the exact schema of your table?
How much main memory (RAM) does your system have?
How much free disk space does you system have on the partition (drive) where your MonetDB database (dbfarm) is located?
Regarding memory usage of MonetDB, you might also find the following thread interesting and helpful:
http://sourceforge.net/mailarchive/message.php?msg_name=20100319145945.GA916...
Stefan
On Fri, Jun 25, 2010 at 12:18:11PM -0700, Luis Fernando Kauer wrote:
Hi,
I'm still trying to evaluate MonetDB but I keep getting errors when trying to import over 10 million rows to a table. The server simply aborts and closes. I'm using MonetDB on Windows XP. Whenener I restart after the server had aborted it shows messages like this:
#GDKmmap(71827456) fails, try to free up space [memory in use=18552560,virtual m emory in use=1837957120] #GDKmmap(71827456) result [mem=18552560,vm=1837957120] #GDKmmap(36175872) fails, try to free up space [memory in use=16499872,virtual m emory in use=1844903936] #GDKmmap(36175872) result [mem=16499872,vm=1844903936] #GDKmmap(143130624) fails, try to free up space [memory in use=15697728,virtual memory in use=1744764928] #GDKmmap(143130624) result [mem=15697728,vm=1744764928] #GDKmmap(143130624) fails, try to free up space [memory in use=13554656,virtual memory in use=1654456320] #GDKmmap(143130624) result [mem=13554656,vm=1654456320] #GDKmmap(143130624) fails, try to free up space [memory in use=8747960,virtual m emory in use=1650917376] #GDKmmap(143130624) result [mem=8747960,vm=1650917376] #GDKmmap(143130624) fails, try to free up space [memory in use=7946840,virtual m emory in use=1531052032] #GDKmmap(143130624) result [mem=7946840,vm=1531052032] !mvc_init: unable to create system tables !SQLException:SQLinit:Catalogue initialization failed !ERROR: HEAPextend: failed to extend to 71722596 for 14\1475tail !ERROR: HEAPextend: failed to extend to 35861298 for 15\1537tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1543tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1550tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1556tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1557tail #function user.main():void; # sql.prelude(); #end main;
First I was trying to import files with about 3 million rows each but it started giving errors after the 3rd file. Then I tried to import through files with about 1 million rows each and I was able to import about 14 million rows, but after about 10 million rows I was only able to import one or two files in a row without MonetDB resetting. Is this a bug or a size limit for the available memory. Anything I could try?
With regards,
Luis Fernando
ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi,
Thanks for the quick reply.
It's a 32-bit XP. The machine has 4GB. Windows reports 3.49 GB. The drive (partition) has 180 GB free and 250 GB used. The data was split in files by year. I'm trying to import files from 2003 through 2010, one at a time. Each one has over 3 million rows (between 3 and 5) and are over 300 MB (between 300 and 650 MB) each. I'm getting error when it gets over 10 million rows. I've tried breaking the files in smaller ones but the same things happens. The schema is as follows (replaced the table and column names) create table t1 ( c1 varchar(21), c2 smallint, c3 smallint, c4 smallint, c5 smallint, c6 smallint, c7 smallint, c8 char, c9 smallint, c10 smallint, c11 smallint, c12 smallint, c13 numeric(17,2), c14 char, c15 char, c16 smallint, c17 numeric(17,2), c18 numeric(17,2), c19 numeric(17,2), c20 numeric(17,2), c21 numeric(17,2), c22 numeric(17,2), c23 numeric(17,2), c24 numeric(17,2), c25 numeric(17,2), c26 numeric(17,2), c27 date, c28 date, c29 date, c30 date);
First I had a primary key but after the crashes I started trying without it.
Regards,
Luis Fernando.
________________________________ De: Stefan Manegold Stefan.Manegold@cwi.nl Para: Communication channel for MonetDB users monetdb-users@lists.sourceforge.net Enviadas: Sexta-feira, 25 de Junho de 2010 16:42:19 Assunto: Re: [MonetDB-users] MonetDB aborts when importing over 10 million rows
Hi Luis,
Which version of MonetDB are you using?
Is your XP system 32-bit or 64-bit?
If the latter, is your MonetDB 32-bit or 64-bit?
How large is your dataset (.csv file) in total and what is the exact schema of your table?
How much main memory (RAM) does your system have?
How much free disk space does you system have on the partition (drive) where your MonetDB database (dbfarm) is located?
Regarding memory usage of MonetDB, you might also find the following thread interesting and helpful:
http://sourceforge.net/mailarchive/message.php?msg_name=20100319145945.GA916...
Stefan
On Fri, Jun 25, 2010 at 12:18:11PM -0700, Luis Fernando Kauer wrote:
Hi,
I'm still trying to evaluate MonetDB but I keep getting errors when trying to import over 10 million rows to a table. The server simply aborts and closes. I'm using MonetDB on Windows XP. Whenener I restart after the server had aborted it shows messages like this:
#GDKmmap(71827456) fails, try to free up space [memory in use=18552560,virtual m emory in use=1837957120] #GDKmmap(71827456) result [mem=18552560,vm=1837957120] #GDKmmap(36175872) fails, try to free up space [memory in use=16499872,virtual m emory in use=1844903936] #GDKmmap(36175872) result [mem=16499872,vm=1844903936] #GDKmmap(143130624) fails, try to free up space [memory in use=15697728,virtual memory in use=1744764928] #GDKmmap(143130624) result [mem=15697728,vm=1744764928] #GDKmmap(143130624) fails, try to free up space [memory in use=13554656,virtual memory in use=1654456320] #GDKmmap(143130624) result [mem=13554656,vm=1654456320] #GDKmmap(143130624) fails, try to free up space [memory in use=8747960,virtual m emory in use=1650917376] #GDKmmap(143130624) result [mem=8747960,vm=1650917376] #GDKmmap(143130624) fails, try to free up space [memory in use=7946840,virtual m emory in use=1531052032] #GDKmmap(143130624) result [mem=7946840,vm=1531052032] !mvc_init: unable to create system tables !SQLException:SQLinit:Catalogue initialization failed !ERROR: HEAPextend: failed to extend to 71722596 for 14\1475tail !ERROR: HEAPextend: failed to extend to 35861298 for 15\1537tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1543tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1550tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1556tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1557tail #function user.main():void; # sql.prelude(); #end main;
First I was trying to import files with about 3 million rows each but it started giving errors after the 3rd file. Then I tried to import through files with about 1 million rows each and I was able to import about 14 million rows, but after about 10 million rows I was only able to import one or two files in a row without MonetDB resetting. Is this a bug or a size limit for the available memory. Anything I could try?
With regards,
Luis Fernando
ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi Luis,
on a 32-bit system, MonetDB is limited to database of at most 4 GB; on Windows, the limit is usually at most 3 GB or even only 2 GB, as Windows does not allow larger processes on a 32-bit system; see also http://zone.ni.com/reference/en-XX/help/371361D-01/lvhowto/enable_lrg_ad_awa...
In your case, with 8 files (years) of >300 MB you have >2.4 GB of raw data, and I'm afraid you will not be able to load this amount into MonetDB on a 32-bit Windows system; surely not in the dfault configuration that does not allow processes to grow beyond 2 GB, but evenn if you configure it to allow 3 GB (cf., http://zone.ni.com/reference/en-XX/help/371361D-01/lvhowto/enable_lrg_ad_awa...) it might not work.
To load all the data into MonetDB, you'd need to consider a 64-bit system (and 64-bit MonetDB).
BTW, does the server actually crash, or does it "merely" report an error, aborting the load, but the server keeps running?
Stefan
On Fri, Jun 25, 2010 at 01:39:15PM -0700, Luis Fernando Kauer wrote:
Hi,
Thanks for the quick reply.
It's a 32-bit XP. The machine has 4GB. Windows reports 3.49 GB. The drive (partition) has 180 GB free and 250 GB used. The data was split in files by year. I'm trying to import files from 2003 through 2010, one at a time. Each one has over 3 million rows (between 3 and 5) and are over 300 MB (between 300 and 650 MB) each. I'm getting error when it gets over 10 million rows. I've tried breaking the files in smaller ones but the same things happens. The schema is as follows (replaced the table and column names) create table t1 ( c1 varchar(21), c2 smallint, c3 smallint, c4 smallint, c5 smallint, c6 smallint, c7 smallint, c8 char, c9 smallint, c10 smallint, c11 smallint, c12 smallint, c13 numeric(17,2), c14 char, c15 char, c16 smallint, c17 numeric(17,2), c18 numeric(17,2), c19 numeric(17,2), c20 numeric(17,2), c21 numeric(17,2), c22 numeric(17,2), c23 numeric(17,2), c24 numeric(17,2), c25 numeric(17,2), c26 numeric(17,2), c27 date, c28 date, c29 date, c30 date);
First I had a primary key but after the crashes I started trying without it.
Regards,
Luis Fernando.
De: Stefan Manegold Stefan.Manegold@cwi.nl Para: Communication channel for MonetDB users monetdb-users@lists.sourceforge.net Enviadas: Sexta-feira, 25 de Junho de 2010 16:42:19 Assunto: Re: [MonetDB-users] MonetDB aborts when importing over 10 million rows
Hi Luis,
Which version of MonetDB are you using?
Is your XP system 32-bit or 64-bit?
If the latter, is your MonetDB 32-bit or 64-bit?
How large is your dataset (.csv file) in total and what is the exact schema of your table?
How much main memory (RAM) does your system have?
How much free disk space does you system have on the partition (drive) where your MonetDB database (dbfarm) is located?
Regarding memory usage of MonetDB, you might also find the following thread interesting and helpful:
http://sourceforge.net/mailarchive/message.php?msg_name=20100319145945.GA916...
Stefan
On Fri, Jun 25, 2010 at 12:18:11PM -0700, Luis Fernando Kauer wrote:
Hi,
I'm still trying to evaluate MonetDB but I keep getting errors when trying to import over 10 million rows to a table. The server simply aborts and closes. I'm using MonetDB on Windows XP. Whenener I restart after the server had aborted it shows messages like this:
#GDKmmap(71827456) fails, try to free up space [memory in use=18552560,virtual m emory in use=1837957120] #GDKmmap(71827456) result [mem=18552560,vm=1837957120] #GDKmmap(36175872) fails, try to free up space [memory in use=16499872,virtual m emory in use=1844903936] #GDKmmap(36175872) result [mem=16499872,vm=1844903936] #GDKmmap(143130624) fails, try to free up space [memory in use=15697728,virtual memory in use=1744764928] #GDKmmap(143130624) result [mem=15697728,vm=1744764928] #GDKmmap(143130624) fails, try to free up space [memory in use=13554656,virtual memory in use=1654456320] #GDKmmap(143130624) result [mem=13554656,vm=1654456320] #GDKmmap(143130624) fails, try to free up space [memory in use=8747960,virtual m emory in use=1650917376] #GDKmmap(143130624) result [mem=8747960,vm=1650917376] #GDKmmap(143130624) fails, try to free up space [memory in use=7946840,virtual m emory in use=1531052032] #GDKmmap(143130624) result [mem=7946840,vm=1531052032] !mvc_init: unable to create system tables !SQLException:SQLinit:Catalogue initialization failed !ERROR: HEAPextend: failed to extend to 71722596 for 14\1475tail !ERROR: HEAPextend: failed to extend to 35861298 for 15\1537tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1543tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1550tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1556tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1557tail #function user.main():void; # sql.prelude(); #end main;
First I was trying to import files with about 3 million rows each but it started giving errors after the 3rd file. Then I tried to import through files with about 1 million rows each and I was able to import about 14 million rows, but after about 10 million rows I was only able to import one or two files in a row without MonetDB resetting. Is this a bug or a size limit for the available memory. Anything I could try?
With regards,
Luis Fernando
ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | 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-4199 |
ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi Stefan.
Thanks again for aswering so quickly. I had intented to use it on user machines and so I'm stuck to Windows XP 32-bit.
It crashes and closes the server. Usually when I restart the server after the crash it shows messages about the memory problem but it's able to start. But sometimes it would not start anymore and I had to reinstall the server, since I don't know what I could do to make it start again.
Regards, Luis Fernando
________________________________ De: Stefan Manegold Stefan.Manegold@cwi.nl Para: Communication channel for MonetDB users monetdb-users@lists.sourceforge.net Enviadas: Sexta-feira, 25 de Junho de 2010 18:20:30 Assunto: Re: [MonetDB-users] Res: MonetDB aborts when importing over 10 million rows
Hi Luis,
on a 32-bit system, MonetDB is limited to database of at most 4 GB; on Windows, the limit is usually at most 3 GB or even only 2 GB, as Windows does not allow larger processes on a 32-bit system; see also http://zone.ni.com/reference/en-XX/help/371361D-01/lvhowto/enable_lrg_ad_awa...
In your case, with 8 files (years) of >300 MB you have >2.4 GB of raw data, and I'm afraid you will not be able to load this amount into MonetDB on a 32-bit Windows system; surely not in the dfault configuration that does not allow processes to grow beyond 2 GB, but evenn if you configure it to allow 3 GB (cf., http://zone.ni.com/reference/en-XX/help/371361D-01/lvhowto/enable_lrg_ad_awa...) it might not work.
To load all the data into MonetDB, you'd need to consider a 64-bit system (and 64-bit MonetDB).
BTW, does the server actually crash, or does it "merely" report an error, aborting the load, but the server keeps running?
Stefan orge.net/lists/listinfo/monetdb-users
On Sat, Jun 26, 2010 at 07:15:46AM -0700, Luis Fernando Kauer wrote:
Hi Stefan.
Thanks again for aswering so quickly. I had intented to use it on user machines and so I'm stuck to Windows XP 32-bit.
You might want to consider trying the following:
- load the data of each year (each file) into an individual table;
- create a view that unions all tables into one (views are not materialized in MonetDB)
- phrase your queries using this view as if it was a single large table containing all data
If (a) each query uses only a subset of all columns or (b) your queries contain rather selective predicates and our optimizer successfully manages to automatically push them beneath the view, i.e., before the data from the different tables is actually combined, this *might* work on 32-bit systems.
It crashes and closes the server. Usually when I restart the server after the crash it shows messages about the memory problem but it's able to start. But sometimes it would not start anymore and I had to reinstall the server, since I don't know what I could do to make it start again.
It would be helpful for us (and hence eventually also for you ;-)) if you could report such crashes as bugs via our bugtracker: http://bugs.monetdb.org/
Stefan
Regards, Luis Fernando
De: Stefan Manegold Stefan.Manegold@cwi.nl Para: Communication channel for MonetDB users monetdb-users@lists.sourceforge.net Enviadas: Sexta-feira, 25 de Junho de 2010 18:20:30 Assunto: Re: [MonetDB-users] Res: MonetDB aborts when importing over 10 million rows
Hi Luis,
on a 32-bit system, MonetDB is limited to database of at most 4 GB; on Windows, the limit is usually at most 3 GB or even only 2 GB, as Windows does not allow larger processes on a 32-bit system; see also http://zone.ni.com/reference/en-XX/help/371361D-01/lvhowto/enable_lrg_ad_awa...
In your case, with 8 files (years) of >300 MB you have >2.4 GB of raw data, and I'm afraid you will not be able to load this amount into MonetDB on a 32-bit Windows system; surely not in the dfault configuration that does not allow processes to grow beyond 2 GB, but evenn if you configure it to allow 3 GB (cf., http://zone.ni.com/reference/en-XX/help/371361D-01/lvhowto/enable_lrg_ad_awa...) it might not work.
To load all the data into MonetDB, you'd need to consider a 64-bit system (and 64-bit MonetDB).
BTW, does the server actually crash, or does it "merely" report an error, aborting the load, but the server keeps running?
Stefan orge.net/lists/listinfo/monetdb-users
This SF.net email is sponsored by Sprint What will you do first with EVO, the first 4G phone? Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (2)
-
Luis Fernando Kauer -
Stefan Manegold