[MonetDB-users] MonetDB performance
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.
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
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
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
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
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 dariuszs@svnp.com 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
-----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
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 stefan@konink.de 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
-----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
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 stefan@konink.de 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
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 stefan@konink.de 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
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
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.
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 Fabian.Groffen@cwi.nlwrote:
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.
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (7)
-
dariuszs -
Fabian Groffen -
Isidor Zeuner -
Matthew Jones -
Roberto Cornacchia -
Stefan de Konink -
Stefan Manegold