On Tue, Apr 20, 2010 at 11:14:54PM +0200, Martin Kersten wrote:

Nozhup wrote:

...

Since the FEB2010+SP1 release we're encountering loading issues.

With the FEB2010 release we were able to load records until the HDD was full (500+ million records) on PCs with 2,3 or 24 GB ram memory without going to the SWAP. This was done by loading CSV-files with 1 million records each through the COPY INTO command (in the mclient and through a c-script).

Since the FEB2010+SP1 release the system starts to SWAP after loading the first couple of million records. This results in increased loadtimes per loaded CSV-file:

1th million: 14 sec 2nd million: 26 sec 3rd million: 44 sec 4th million: 50 sec 5th million: 69 sec etc.

We have tested this on several PCs and all PCs encounter the same problem. Test files used are the same for both releases as is the OS (Ubuntu 9.10). The only variable that has changed is MonetDB.

To analyse the situation, the schema should be known, in particular if there are any constraints defined over it.

This problem could not been reproduced on the ontime benchmark database (120Mrecords in batches of 0.5M)

Investigation still going...

I also looked at loading the AirTraffic OnTime benchmark (single table with 90+ columns, loaded from 260 .csv files, each consisting of ~0.5M record, i.e., almost 130M record in total). This is the closed we have / can do to simulate the originally reported problem. I tested the loading with Feb2010, Feb2010-SP1, Feb2010-SP2 preview; all compiled from the respective super source tarball, on my 64-bit Fedora 12 desktop with 8 GB RAM. With Feb2010, loading times vary between 10 and 15 sec per .csv file for the first files and then slowly increase towards 15 to 25 sec per .csv file at the end of the series. Both the variation and the slight increase can be explaint by the fact that the files vary in size and number of records, with the first files starting at just over 0.4M records, while the last ones hold almost 0.6M records. With Feb2010-SP1 & Feb2010-SP2, I see roughtly the same pattern as baseline. However, "once in a while" loading one .csv file takes much longer, namely between 100 & 400(!) sec. The frequency of such outliers is rather low in the beginning (atmost one in 10), but becomes eevry other file at the end of the series. Moreover, I noticed that as of loading the second file into the same table, extra *.new files are created / left behind for most of the *.tail & *.theap files in the dbfarm, making the dbfarm about twice a large as with Feb2010.

From the differences between the Feb2010 codebase and the Feb2010-SP1 codebase (respectively the log messages of the cahnges that happened in between) I could not yet conclude which change(s) trigger(s) this misbehaviour of Feb2010 & Feb2010-SP1 with multiple "COPY INTO" into the same table. --- More invetigation is pending ...

Stefan

Martin

------------------------------------------------------------------------------ _______________________________________________ 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 |

In order to fix this I had to dump all of my tables and restore them. Now memory usage has gone back to normal. 73, Matthew W. Jones (KI4ZIB) http://matburt.net On Tue, May 4, 2010 at 2:19 PM, Matthew Jones wrote:

I just upgraded from Nov2009 SP1 and noticed that COPY INTO commands loading data into the database use a lot more memory... significantly more memory than prior to the upgrade but only for databases that existed before the upgrade and whose tables contain data, if I deploy a new schema then memory usage seems okay.

73, Matthew W. Jones (KI4ZIB) http://matburt.net

On Mon, Apr 26, 2010 at 3:37 AM, Stefan Manegold wrote:

...

...

Nozhup wrote:

...

Since the FEB2010+SP1 release we're encountering loading issues.

With the FEB2010 release we were able to load records until the HDD was full (500+ million records) on PCs with 2,3 or 24 GB ram memory without going to the SWAP. This was done by loading CSV-files with 1 million records each through the COPY INTO command (in the mclient and through a c-script).

Since the FEB2010+SP1 release the system starts to SWAP after loading

...

...

first couple of million records. This results in increased loadtimes

...

...

loaded CSV-file:

1th million: 14 sec 2nd million: 26 sec 3rd million: 44 sec 4th million: 50 sec 5th million: 69 sec etc.

We have tested this on several PCs and all PCs encounter the same

...

...

Test files used are the same for both releases as is the OS (Ubuntu 9.10). The only variable that has changed is MonetDB.

To analyse the situation, the schema should be known, in particular if

On Tue, Apr 20, 2010 at 11:14:54PM +0200, Martin Kersten wrote: the per problem. there

...

are any constraints defined over it.

This problem could not been reproduced on the ontime benchmark database (120Mrecords in batches of 0.5M)

Investigation still going...

I also looked at loading the AirTraffic OnTime benchmark (single table with 90+ columns, loaded from 260 .csv files, each consisting of ~0.5M record, i.e., almost 130M record in total). This is the closed we have / can do to simulate the originally reported problem.

I tested the loading with Feb2010, Feb2010-SP1, Feb2010-SP2 preview; all compiled from the respective super source tarball, on my 64-bit Fedora 12 desktop with 8 GB RAM.

With Feb2010, loading times vary between 10 and 15 sec per .csv file for the first files and then slowly increase towards 15 to 25 sec per .csv file at the end of the series. Both the variation and the slight increase can be explaint by the fact that the files vary in size and number of records, with the first files starting at just over 0.4M records, while the last ones hold almost 0.6M records.

With Feb2010-SP1 & Feb2010-SP2, I see roughtly the same pattern as baseline. However, "once in a while" loading one .csv file takes much longer, namely between 100 & 400(!) sec. The frequency of such outliers is rather low in the beginning (atmost one in 10), but becomes eevry other file at the end of the series.

Moreover, I noticed that as of loading the second file into the same table, extra *.new files are created / left behind for most of the *.tail & *.theap files in the dbfarm, making the dbfarm about twice a large as with Feb2010.

...

From the differences between the Feb2010 codebase and the Feb2010-SP1 codebase (respectively the log messages of the cahnges that happened in between) I could not yet conclude which change(s) trigger(s) this misbehaviour of Feb2010 & Feb2010-SP1 with multiple "COPY INTO" into the same table. --- More invetigation is pending ...

Stefan

...

Martin

------------------------------------------------------------------------------

...

_______________________________________________ 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 |

------------------------------------------------------------------------------ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

I have done some new tests. Test 5 can probably give you more insight on where the problem lies. Apparently loading with FEB2010+SP2 works fine when there already is data present that is loaded with FEB2010. TEST: 1 Create Database and Table with Feb2010 Load 50 million with Feb2010 Results: Load: 1-1 --> 1.910726 SECONDS Load: 1-2 --> 1.933725 SECONDS Load: 1-3 --> 2.593301 SECONDS Load: 1-4 --> 2.641776 SECONDS Load: 1-5 --> 2.577344 SECONDS : : Load: 1-45 --> 2.536684 SECONDS Load: 1-46 --> 1.759640 SECONDS Load: 1-47 --> 1.763326 SECONDS Load: 1-48 --> 1.699384 SECONDS Load: 1-49 --> 1.740449 SECONDS Load: 1-50 --> 1.759094 SECONDS TEST: 2 Create Database and Table with Feb2010+SP2 Load 50 million with Feb2010+SP2 Results: Load: 1-1 --> 1.168731 SECONDS Load: 1-2 --> 1.863967 SECONDS Load: 1-3 --> 1.971184 SECONDS Load: 1-4 --> 2.815388 SECONDS Load: 1-5 --> 2.294919 SECONDS Load: 1-6 --> 2.264362 SECONDS Load: 1-7 --> 2.336258 SECONDS Load: 1-8 --> 3.933792 SECONDS Load: 1-9 --> 4.250140 SECONDS Load: 1-10 --> 2.269240 SECONDS Load: 1-11 --> 3.963424 SECONDS Load: 1-12 --> 2.696158 SECONDS Load: 1-13 --> 4.462025 SECONDS Load: 1-14 --> 1.701432 SECONDS Load: 1-15 --> 6.067436 SECONDS Load: 1-16 --> 3.494509 SECONDS Load: 1-17 --> 1.871878 SECONDS Load: 1-18 --> 5.816339 SECONDS Load: 1-19 --> 1.827136 SECONDS Load: 1-20 --> 1.764564 SECONDS Load: 1-21 --> 1.740637 SECONDS Load: 1-22 --> 6.289675 SECONDS Load: 1-23 --> 1.797130 SECONDS Load: 1-24 --> 1.787667 SECONDS Load: 1-25 --> 2.133380 SECONDS Load: 1-26 --> 11.274407 SECONDS Load: 1-27 --> 1.798045 SECONDS Load: 1-28 --> 1.668344 SECONDS Load: 1-29 --> 1.635813 SECONDS Load: 1-30 --> 1.654823 SECONDS Load: 1-31 --> 10.366146 SECONDS Load: 1-32 --> 3.870185 SECONDS Load: 1-33 --> 1.729740 SECONDS Load: 1-34 --> 1.659759 SECONDS Load: 1-35 --> 2.474685 SECONDS Load: 1-36 --> 1.807413 SECONDS Load: 1-37 --> 1.667068 SECONDS Load: 1-38 --> 11.853121 SECONDS Load: 1-39 --> 20.454700 SECONDS Load: 1-40 --> 1.723229 SECONDS Load: 1-41 --> 2.541948 SECONDS Load: 1-42 --> 1.773245 SECONDS Load: 1-43 --> 1.775079 SECONDS Load: 1-44 --> 1.675748 SECONDS Load: 1-45 --> 22.696733 SECONDS Load: 1-46 --> 22.960610 SECONDS Load: 1-47 --> 1.868287 SECONDS Load: 1-48 --> 1.658702 SECONDS Load: 1-49 --> 1.674087 SECONDS Load: 1-50 --> 1.824218 SECONDS TEST: 3 Create Database and Table with Feb2010 Stop/Start Merovingian Load 50 million with Feb2010+SP2 Results: Load: 1-1 --> 1.259857 SECONDS Load: 1-2 --> 1.704675 SECONDS Load: 1-3 --> 1.855676 SECONDS Load: 1-4 --> 2.117738 SECONDS Load: 1-5 --> 2.232621 SECONDS Load: 1-6 --> 2.368525 SECONDS Load: 1-7 --> 2.624660 SECONDS Load: 1-8 --> 4.555418 SECONDS Load: 1-9 --> 2.958973 SECONDS Load: 1-10 --> 2.175845 SECONDS Load: 1-11 --> 3.898645 SECONDS Load: 1-12 --> 1.841802 SECONDS Load: 1-13 --> 4.220988 SECONDS Load: 1-14 --> 3.364434 SECONDS Load: 1-15 --> 4.442505 SECONDS Load: 1-16 --> 2.706044 SECONDS Load: 1-17 --> 1.875754 SECONDS Load: 1-18 --> 6.241370 SECONDS Load: 1-19 --> 2.232179 SECONDS Load: 1-20 --> 1.652533 SECONDS Load: 1-21 --> 1.725189 SECONDS Load: 1-22 --> 6.973820 SECONDS Load: 1-23 --> 10.112246 SECONDS Load: 1-24 --> 1.762699 SECONDS Load: 1-25 --> 1.685413 SECONDS Load: 1-26 --> 9.765358 SECONDS Load: 1-27 --> 1.789590 SECONDS Load: 1-28 --> 1.714047 SECONDS Load: 1-29 --> 1.719277 SECONDS Load: 1-30 --> 3.506406 SECONDS Load: 1-31 --> 10.179752 SECONDS Load: 1-32 --> 15.672030 SECONDS Load: 1-33 --> 1.839481 SECONDS Load: 1-34 --> 1.805844 SECONDS Load: 1-35 --> 1.771690 SECONDS Load: 1-36 --> 1.812470 SECONDS Load: 1-37 --> 1.765272 SECONDS Load: 1-38 --> 14.832091 SECONDS Load: 1-39 --> 6.387477 SECONDS Load: 1-40 --> 3.481112 SECONDS Load: 1-41 --> 2.441015 SECONDS Load: 1-42 --> 2.024243 SECONDS Load: 1-43 --> 1.675132 SECONDS Load: 1-44 --> 1.666789 SECONDS Load: 1-45 --> 22.490218 SECONDS Load: 1-46 --> 25.359638 SECONDS Load: 1-47 --> 1.711532 SECONDS Load: 1-48 --> 1.949842 SECONDS Load: 1-49 --> 1.807465 SECONDS Load: 1-50 --> 1.808858 SECONDS TEST: 4 Create Database and Table with Feb2010+SP2 Stop/Start Merovingian Load 50 million with Feb2010 Results: Load: 1-1 --> 1.216096 SECONDS Load: 1-2 --> 1.845383 SECONDS Load: 1-3 --> 2.120632 SECONDS Load: 1-4 --> 2.077262 SECONDS Load: 1-5 --> 2.150625 SECONDS Load: 1-6 --> 2.126977 SECONDS Load: 1-7 --> 2.551506 SECONDS Load: 1-8 --> 4.019961 SECONDS Load: 1-9 --> 4.196475 SECONDS Load: 1-10 --> 2.345779 SECONDS Load: 1-11 --> 3.904973 SECONDS Load: 1-12 --> 3.153936 SECONDS Load: 1-13 --> 3.323880 SECONDS Load: 1-14 --> 1.717809 SECONDS Load: 1-15 --> 4.696059 SECONDS Load: 1-16 --> 3.997302 SECONDS Load: 1-17 --> 1.779581 SECONDS Load: 1-18 --> 5.239965 SECONDS Load: 1-19 --> 2.220652 SECONDS Load: 1-20 --> 1.676368 SECONDS Load: 1-21 --> 1.868592 SECONDS Load: 1-22 --> 6.509640 SECONDS Load: 1-23 --> 12.516192 SECONDS Load: 1-24 --> 1.670079 SECONDS Load: 1-25 --> 1.675120 SECONDS Load: 1-26 --> 7.649243 SECONDS Load: 1-27 --> 1.661681 SECONDS Load: 1-28 --> 1.648923 SECONDS Load: 1-29 --> 1.650329 SECONDS Load: 1-30 --> 1.682180 SECONDS Load: 1-31 --> 12.297468 SECONDS Load: 1-32 --> 14.463934 SECONDS Load: 1-33 --> 1.907643 SECONDS Load: 1-34 --> 1.930773 SECONDS Load: 1-35 --> 3.259206 SECONDS Load: 1-36 --> 1.729549 SECONDS Load: 1-37 --> 1.652379 SECONDS Load: 1-38 --> 20.633551 SECONDS Load: 1-39 --> 18.258972 SECONDS Load: 1-40 --> 2.227979 SECONDS Load: 1-41 --> 1.742363 SECONDS Load: 1-42 --> 1.777535 SECONDS Load: 1-43 --> 1.671454 SECONDS Load: 1-44 --> 1.735362 SECONDS Load: 1-45 --> 22.877463 SECONDS Load: 1-46 --> 21.912744 SECONDS Load: 1-47 --> 1.812405 SECONDS Load: 1-48 --> 1.681236 SECONDS Load: 1-49 --> 1.801676 SECONDS Load: 1-50 --> 1.745948 SECONDS TEST: 5 Create Database and Table with Feb2010 Load 1 million with Feb2010 Stop/Start Merovingian Load 50 million with Feb2010+SP2 Results: Load: 1-1 --> 1.767545 SECONDS Load: 2-1 --> 1.694455 SECONDS Load: 2-2 --> 2.546381 SECONDS Load: 2-3 --> 2.391589 SECONDS Load: 2-4 --> 2.427970 SECONDS Load: 2-5 --> 2.524295 SECONDS Load: 2-6 --> 2.536293 SECONDS Load: 2-7 --> 2.551000 SECONDS Load: 2-8 --> 2.437870 SECONDS Load: 2-9 --> 1.709934 SECONDS Load: 2-10 --> 2.982058 SECONDS Load: 2-11 --> 1.660244 SECONDS Load: 2-12 --> 2.891418 SECONDS Load: 2-13 --> 1.809969 SECONDS Load: 2-14 --> 2.912382 SECONDS Load: 2-15 --> 1.650690 SECONDS Load: 2-16 --> 1.647589 SECONDS Load: 2-17 --> 3.467065 SECONDS Load: 2-18 --> 1.753329 SECONDS Load: 2-19 --> 3.029100 SECONDS Load: 2-20 --> 1.871252 SECONDS Load: 2-21 --> 2.974907 SECONDS Load: 2-22 --> 1.675388 SECONDS Load: 2-23 --> 1.898563 SECONDS Load: 2-24 --> 1.847560 SECONDS Load: 2-25 --> 3.694222 SECONDS Load: 2-26 --> 1.805643 SECONDS Load: 2-27 --> 1.707622 SECONDS Load: 2-28 --> 1.746381 SECONDS Load: 2-29 --> 1.740738 SECONDS Load: 2-30 --> 4.185549 SECONDS Load: 2-31 --> 1.810812 SECONDS Load: 2-32 --> 1.731268 SECONDS Load: 2-33 --> 1.731836 SECONDS Load: 2-34 --> 1.649722 SECONDS Load: 2-35 --> 4.395486 SECONDS Load: 2-36 --> 1.840713 SECONDS Load: 2-37 --> 3.084581 SECONDS Load: 2-38 --> 1.701720 SECONDS Load: 2-39 --> 1.647938 SECONDS Load: 2-40 --> 1.676998 SECONDS Load: 2-41 --> 1.848505 SECONDS Load: 2-42 --> 3.900773 SECONDS Load: 2-43 --> 1.690402 SECONDS Load: 2-44 --> 2.980633 SECONDS Load: 2-45 --> 1.673819 SECONDS Load: 2-46 --> 1.789264 SECONDS Load: 2-47 --> 1.658723 SECONDS Load: 2-48 --> 1.784668 SECONDS Load: 2-49 --> 4.326350 SECONDS Load: 2-50 --> 1.858123 SECONDS TEST: 6 Create Database and Table with Feb2010+SP2 Load 1 million with Feb2010+SP2 Stop/Start Merovingian Load 50 million with Feb2010 Results: Load: 1-1 --> 1.199446 SECONDS Load: 2-1 --> 1.953970 SECONDS Load: 2-2 --> 1.853001 SECONDS Load: 2-3 --> 2.082067 SECONDS Load: 2-4 --> 2.344586 SECONDS Load: 2-5 --> 2.009283 SECONDS Load: 2-6 --> 2.442688 SECONDS Load: 2-7 --> 4.325844 SECONDS Load: 2-8 --> 4.699312 SECONDS Load: 2-9 --> 2.444890 SECONDS Load: 2-10 --> 4.978190 SECONDS Load: 2-11 --> 2.062113 SECONDS Load: 2-12 --> 4.015930 SECONDS Load: 2-13 --> 1.817437 SECONDS Load: 2-14 --> 5.612018 SECONDS Load: 2-15 --> 2.775419 SECONDS Load: 2-16 --> 1.870272 SECONDS Load: 2-17 --> 5.834528 SECONDS Load: 2-18 --> 1.807582 SECONDS Load: 2-19 --> 1.759486 SECONDS Load: 2-20 --> 1.658563 SECONDS Load: 2-21 --> 6.366512 SECONDS Load: 2-22 --> 14.240177 SECONDS Load: 2-23 --> 1.865211 SECONDS Load: 2-24 --> 1.808078 SECONDS Load: 2-25 --> 7.312290 SECONDS Load: 2-26 --> 4.003556 SECONDS Load: 2-27 --> 1.785553 SECONDS Load: 2-28 --> 1.638819 SECONDS Load: 2-29 --> 1.658940 SECONDS Load: 2-30 --> 12.879252 SECONDS Load: 2-31 --> 10.528374 SECONDS Load: 2-32 --> 2.029107 SECONDS Load: 2-33 --> 2.077801 SECONDS Load: 2-34 --> 3.781921 SECONDS Load: 2-35 --> 1.714947 SECONDS Load: 2-36 --> 1.901108 SECONDS Load: 2-37 --> 14.873591 SECONDS Load: 2-38 --> 3.529517 SECONDS Load: 2-39 --> 19.615659 SECONDS Load: 2-40 --> 1.802141 SECONDS Load: 2-41 --> 1.750894 SECONDS Load: 2-42 --> 1.682143 SECONDS Load: 2-43 --> 1.677087 SECONDS Load: 2-44 --> 22.066439 SECONDS Load: 2-45 --> 32.562102 SECONDS Load: 2-46 --> 1.728281 SECONDS Load: 2-47 --> 1.695364 SECONDS Load: 2-48 --> 1.728679 SECONDS Load: 2-49 --> 1.788980 SECONDS Load: 2-50 --> 1.692497 SECONDS -- View this message in context: http://old.nabble.com/Loading-problems-since-FEB2010%2BSP1-release-tp2828765... Sent from the monetdb-users mailing list archive at Nabble.com.