Stopping queries, deleting tuples and other general use questions
Hi, after a while using MonetDB some general use questions pops up. I've collected some which answers I think will be of great help for any MonetDB user. * * *#1:* In the monetdb manpage we can read: *“The kill command immediately sends a SIGKILL and should only be used as last resort for a database that doesn't respond any more. Killing a database may result in (partial) data loss.”* * * 1.1 If kill is the last resort, what are the other options? * * *#2:* System monitor (Oct 2013)http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/monitor 2.1 Will this be available in October or is there a new estimate? 2.2 Does the current development version include this feature yet so we can test it? I'm interested mainly in the methods sys.pause(qtag), sys.resume(qtag) and sys.stop(qtag), *specially sys.stop(qtag)*. 2.4 Is there some alternative method already available in the Feb2013 release to achieve this action? *#3:* In the monetdb manpage we can read about the *nclients* property: *“Sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged.” * 3.1 What do you consider as a high value? Fabian Groffen stated in a similar question *"Concurrency is always a problem for MonetDB, since in the ideal case it means the processes are fighting for resources with each other." * 3.2 Does this mean (theoretically) that if I can run Query A in 10s in a system with resources R (Ram, Disk, Processor), with Rx2 I can run Query A and B (equivalent in effort to A) in the same 10s? *#4:* www.monetdb.org/Documentation/Manuals/SQLreference/Transactions: *"WARNING. The tuples being deleted are only marked as such. They do not reduce the table size. It calls for a vacuum cleaning algorithm."* * * I've found the /usr/lib/monetdb5/createdb/20_vacuum.sql script and after some googling I found the following post* * http://mail.monetdb.org/pipermail/users-list/2011-August/005051.html without an answer. 4.1 Does the following sequence frees the space of the deleted rows? sql>delete from sys.transfer_ip ; 651950 affected rows (117.620ms) sql>call vacuum('sys','transfer_ip'); Best regards, Ruben Silva
Hi On 9/20/13 12:26 PM, Ruben Silva wrote:
Hi, after a while using MonetDB some general use questions pops up. I've collected some which answers I think will be of great help for any MonetDB user. * * *#1:* In the monetdb manpage we can read: /“The kill command immediately sends a SIGKILL and should only be used as *last resort* for a database that doesn't respond any more. Killing a database may result in (partial) data loss.”/ / / 1.1 If kill is the last resort, what are the other options? * * In the upcoming feature release (~Q1 2014) there will be a mechanism to stop and abort SQL queries safely. It is already documented in http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/monitor *#2:* System monitor (Oct 2013) http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/monitor
2.1 Will this be available in October or is there a new estimate? The default branch is not acceptable yet for distribution, therefore we have just issued a Feb13 bugfix release.
2.2 Does the current development version include this feature yet so we can test it? The default branch code can be downloaded.
I'm interested mainly in the methods sys.pause(qtag), sys.resume(qtag) and sys.stop(qtag), *specially sys.stop(qtag)*. We might back-port if the new feature branch takes too much time.
2.4 Is there some alternative method already available in the Feb2013 release to achieve this action?
Use the monetdb command to take a mserver temporarily out of service. Then you can wait for the (update) queries to finish or use the MAL primitives to stop clients. No new client connection will be allowed .
*#3:* In the monetdb manpage we can read about the *nclients* property:
/“Sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged.” /
3.1 What do you consider as a high value? Depends on your concurrent load and the resources on your machine. No general rule of thumb exist.
Fabian Groffen stated in a similar question /"Concurrency is always a problem for MonetDB, since in the ideal case it means the processes are fighting for resources with each other." /
All processes on your box compete for resources.
3.2 Does this mean (theoretically) that if I can run Query A in 10s in a system with resources R (Ram, Disk, Processor), with Rx2 I can run Query A and B (equivalent in effort to A) in the same 10s?
The theory is flexible. In general, resource bottlenecks may appear at multiple places. Experimentation will show.
*#4:* www.monetdb.org/Documentation/Manuals/SQLreference/Transactions http://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions: /"WARNING. The tuples being deleted are only marked as such. *They do not reduce the table size*. It calls for a vacuum cleaning algorithm."/ / / I've found the /usr/lib/monetdb5/createdb/20_vacuum.sql script and after some googling I found the following post//http://mail.monetdb.org/pipermail/users-list/2011-August/005051.html without an answer.
Vacuum can always be achieved by dump/restore and copy tables. A back-ground thread to vacuum the database remains on our wishlist.
4.1 Does the following sequence frees the space of the deleted rows?
sql>delete from sys.transfer_ip ; 651950 affected rows (117.620ms) sql>call vacuum('sys','transfer_ip'); Vacuum as is available does not respect foreign key references. Therefore, its implementation is incomplete.
regards, Martin
Best regards,
Ruben Silva
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Martin Kersten -
Ruben Silva