[MonetDB-users] MonetDB performance

newer
[MonetDB-users] crash when doing...

dariuszs

17 May 2010 17 May '10

12:50 p.m.

Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

Show replies by date

Roberto Cornacchia

17 May 17 May

1:23 p.m.

KA-BUM! :-) ..sorry, I couldn't resist this. Seriously, my first reaction would be: well, if you overload the system, you get what you can expect - a slow system. MonetDB is not scared of working out of physical memory, but it can't do magic. It all depends of course on the real DB usage. I guess it is not realistic to think that all users would use the same tables at the same time, which would worsen the situation of course. One of the optimizers under development (recycler) aims at making intermediate results reusable - also across different sessions. With this in place, you could expect massive improvements in a scenario with many users firing similar queries. Roberto On Mon, 2010-05-17 at 08:50 -0400, dariuszs wrote:

...

Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

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

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

-- | M.Sc. Roberto Cornacchia | CWI (Centrum voor Wiskunde en Informatica) | Science Park 123, 1098XG Amsterdam, The Netherlands | tel: +31 20 592 4322 , http://www.cwi.nl/~roberto

dariuszs

1:27 p.m.

Hi, Then how is that different than using MySQL memory tables - it's not. Dariusz. Roberto Cornacchia wrote:

...

KA-BUM! :-)

..sorry, I couldn't resist this. Seriously, my first reaction would be: well, if you overload the system, you get what you can expect - a slow system. MonetDB is not scared of working out of physical memory, but it can't do magic. It all depends of course on the real DB usage. I guess it is not realistic to think that all users would use the same tables at the same time, which would worsen the situation of course. One of the optimizers under development (recycler) aims at making intermediate results reusable - also across different sessions. With this in place, you could expect massive improvements in a scenario with many users firing similar queries.

Roberto

On Mon, 2010-05-17 at 08:50 -0400, dariuszs wrote:

...
Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

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

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

Stefan Manegold

2:22 p.m.

On Mon, May 17, 2010 at 09:27:41AM -0400, dariuszs wrote:

...

Hi, Then how is that different than using MySQL memory tables - it's not.

It's totally different. It's not comparable at all. For details see the related research paper: http://repository.cwi.nl/search/fullrecord.php?publnr=14287 Stefan

...

Dariusz.

Roberto Cornacchia wrote:

...
KA-BUM! :-)

..sorry, I couldn't resist this. Seriously, my first reaction would be: well, if you overload the system, you get what you can expect - a slow system. MonetDB is not scared of working out of physical memory, but it can't do magic. It all depends of course on the real DB usage. I guess it is not realistic to think that all users would use the same tables at the same time, which would worsen the situation of course. One of the optimizers under development (recycler) aims at making intermediate results reusable - also across different sessions. With this in place, you could expect massive improvements in a scenario with many users firing similar queries.

Roberto

On Mon, 2010-05-17 at 08:50 -0400, dariuszs wrote:

...
Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

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

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

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

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

dariuszs

2:39 p.m.

Hi, The design is different, but the results are the same. Dariusz. Stefan Manegold wrote:

...

On Mon, May 17, 2010 at 09:27:41AM -0400, dariuszs wrote:

...
Hi, Then how is that different than using MySQL memory tables - it's not.

It's totally different. It's not comparable at all.

For details see the related research paper: http://repository.cwi.nl/search/fullrecord.php?publnr=14287

Stefan

...
Dariusz.

Roberto Cornacchia wrote:

...
KA-BUM! :-)

..sorry, I couldn't resist this. Seriously, my first reaction would be: well, if you overload the system, you get what you can expect - a slow system. MonetDB is not scared of working out of physical memory, but it can't do magic. It all depends of course on the real DB usage. I guess it is not realistic to think that all users would use the same tables at the same time, which would worsen the situation of course. One of the optimizers under development (recycler) aims at making intermediate results reusable - also across different sessions. With this in place, you could expect massive improvements in a scenario with many users firing similar queries.

Roberto

On Mon, 2010-05-17 at 08:50 -0400, dariuszs wrote:

...
Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

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

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

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

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

Matthew Jones

1:39 p.m.

In my experience this is probably not going to work very well for you... especially if you are continuing to load data at the same time. I usually use MonetDB to load data and process it, then move simplified/aggregated data to another database for user presentation. You're still going to have to be very careful with MonetDB, depending on the volume of incoming data you'll need to restart the database at least a couple of times a day. On Mon, May 17, 2010 at 8:50 AM, dariuszs wrote:

...

Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

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

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

Stefan de Konink

1:54 p.m.

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Op 17-05-10 15:39, Matthew Jones schreef:

...

You're still going to have to be very careful with MonetDB, depending on the volume of incoming data you'll need to restart the database at least a couple of times a day.

One new developer already managed to get 90GB of used memory on a table having only 22MB, consisting of integers and timestamps. I did congratulate this user with this achievement, but want to reproduce it. Interestingly, after a restart (ok a kill) everything went back to acceptable levels: 1191MB (virtual), 289MB (resources), 11MB (shared) it is interesting to see how such things can expand. Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkvxSoEACgkQYH1+F2Rqwn3LdgCdEXJdCs1uGH+1CVFTAIdTt6xs PQcAoI7AYnwTn5dFf8MB4GrCdqAyUU8M =pB2H -----END PGP SIGNATURE-----

Matthew Jones

2:51 p.m.

Another huge issue is that deleting records from a table still won't shrink the size of the table. I like to keep some of my raw data tables small so once a day I delete some already processed records out of them, dump the table, and then reload it. To manage the rest of the database I have a series of scripts that check to see if the database is using a stupid amount of memory (80%+ of physical memory on the machine) or to see if the database is deadlocked (this happens several times a day). If either of these situations exist then I SIGHUP the merovingian and mserver processes... when the database comes back up and gets primed then it will be using a reasonable amount of memory for about 8 hours or so. This is all acceptable at the moment because the database is ridiculously fast. 73, Matthew W. Jones (KI4ZIB) http://matburt.net On Mon, May 17, 2010 at 9:54 AM, Stefan de Konink wrote:

...

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512

Op 17-05-10 15:39, Matthew Jones schreef:

...
You're still going to have to be very careful with MonetDB, depending on the volume of incoming data you'll need to restart the database at least a couple of times a day.

One new developer already managed to get 90GB of used memory on a table having only 22MB, consisting of integers and timestamps. I did congratulate this user with this achievement, but want to reproduce it.

Interestingly, after a restart (ok a kill) everything went back to acceptable levels: 1191MB (virtual), 289MB (resources), 11MB (shared) it is interesting to see how such things can expand.

Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEAREKAAYFAkvxSoEACgkQYH1+F2Rqwn3LdgCdEXJdCs1uGH+1CVFTAIdTt6xs PQcAoI7AYnwTn5dFf8MB4GrCdqAyUU8M =pB2H -----END PGP SIGNATURE-----

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

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

Stefan de Konink

2:57 p.m.

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Op 17-05-10 16:51, Matthew Jones schreef:

...

Another huge issue is that deleting records from a table still won't shrink the size of the table. I like to keep some of my raw data tables small so once a day I delete some already processed records out of them, dump the table, and then reload it.

Another quicky would be: start transaction; drop table activeselecting; create table activeselecting... insert into activeselecting select * from alldata; commit; Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkvxWWsACgkQYH1+F2Rqwn1x7ACfUg0LWJsef3ONbxtw9b9NO7RD Qs0An3EM/jy4E5SftTE+9tvEqnQ0ryMV =629O -----END PGP SIGNATURE-----

Matthew Jones

3:15 p.m.

Unless you want the table to be named the same as it was before... as far as I am aware there is no support for renaming tables. Also... COPY INTO seems to be faster than a SELECT into a new table. 73, Matthew W. Jones (KI4ZIB) http://matburt.net On Mon, May 17, 2010 at 10:57 AM, Stefan de Konink wrote:

...

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512

Op 17-05-10 16:51, Matthew Jones schreef:

...
Another huge issue is that deleting records from a table still won't shrink the size of the table. I like to keep some of my raw data tables small so once a day I delete some already processed records out of them, dump the table, and then reload it.

Another quicky would be:

start transaction; drop table activeselecting; create table activeselecting... insert into activeselecting select * from alldata; commit;

Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEAREKAAYFAkvxWWsACgkQYH1+F2Rqwn1x7ACfUg0LWJsef3ONbxtw9b9NO7RD Qs0An3EM/jy4E5SftTE+9tvEqnQ0ryMV =629O -----END PGP SIGNATURE-----

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

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

Stefan Manegold

3:38 p.m.

On Mon, May 17, 2010 at 11:15:49AM -0400, Matthew Jones wrote:

...

Unless you want the table to be named the same as it was before... as far as I am aware there is no support for renaming tables.

...

Also... COPY INTO seems to be faster than a SELECT into a new table.

Is also DUMP + COPY INTO faster than SELECT into a new table (or, say, CREATE TABLE <new> AS SELECT ... FROM TABLE <old> ... WITH DATA;)? In either case, that's "interesting" and unexpected to me --- I'll try to investigate later this week ... Stefan

...

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

On Mon, May 17, 2010 at 10:57 AM, Stefan de Konink wrote:

...
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512

Op 17-05-10 16:51, Matthew Jones schreef:

...
Another huge issue is that deleting records from a table still won't shrink the size of the table. I like to keep some of my raw data tables small so once a day I delete some already processed records out of them, dump the table, and then reload it.

Another quicky would be:

start transaction; drop table activeselecting; create table activeselecting... insert into activeselecting select * from alldata; commit;

Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEAREKAAYFAkvxWWsACgkQYH1+F2Rqwn1x7ACfUg0LWJsef3ONbxtw9b9NO7RD Qs0An3EM/jy4E5SftTE+9tvEqnQ0ryMV =629O -----END PGP SIGNATURE-----

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

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

...

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

...

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

Isidor Zeuner

18 May 18 May

5:30 a.m.

...

Op 17-05-10 16:51, Matthew Jones schreef:

...
Another huge issue is that deleting records from a table still won't shrink the size of the table. I like to keep some of my raw data tables small so once a day I delete some already processed records out of them, dump the table, and then reload it.

Another quicky would be:

start transaction; drop table activeselecting; create table activeselecting... insert into activeselecting select * from alldata; commit;

Does the wrapping inside a transaction change anything regarding space reclamation? I am currently trying to create a working scenario for tables with changing content but a bounded amount of rows, where also the physical storage requirements remain bounded. So far the only solution that seems to work for me is to regularly create a new database and move the data over. I have filed a bug report ([1]) for that, where you can find two test scripts, one which only deletes the rows and one which also continuously uses new tables to hold the data. Both scripts result in unbounded storage requirements. Best regards, Isidor [1] http://bugs.monetdb.org/show_bug.cgi?id=2539

Fabian Groffen

17 May 17 May

3:34 p.m.

On 17-05-2010 10:51:56 -0400, Matthew Jones wrote:

...

To manage the rest of the database I have a series of scripts that check to see if the database is using a stupid amount of memory (80%+ of physical memory on the machine) or to see if the database is deadlocked (this happens several times a day). If either of these situations exist then I SIGHUP the merovingian and mserver processes... when the database comes back up and gets primed then it will be using a reasonable amount of memory for about 8 hours or so.

Do you really mean SIGHUP here? Does merovingian get stuck/deadlocked too, or just the database? SIGHUP to merovingian will just make it reopen its logfiles.

Matthew Jones

4:01 p.m.

I do really mean SIGHUP I do it to both processes just to make sure everything gets fully cycled. 73, Matthew W. Jones (KI4ZIB) http://matburt.net On Mon, May 17, 2010 at 11:34 AM, Fabian Groffen wrote:

...

...
To manage the rest of the database I have a series of scripts that check to see if the database is using a stupid amount of memory (80%+ of physical memory on the machine) or to see if the database is deadlocked (this happens several times a day). If either of these situations exist then I SIGHUP

On 17-05-2010 10:51:56 -0400, Matthew Jones wrote: the

...
merovingian and mserver processes... when the database comes back up and gets primed then it will be using a reasonable amount of memory for about 8 hours or so.

Do you really mean SIGHUP here? Does merovingian get stuck/deadlocked too, or just the database? SIGHUP to merovingian will just make it reopen its logfiles.

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

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

5338

Age (days ago)

5339

Last active (days ago)

List overview

Download

13 comments

7 participants

participants (7)

dariuszs
Fabian Groffen
Isidor Zeuner
Matthew Jones
Roberto Cornacchia
Stefan de Konink
Stefan Manegold