When one first explores the world of MonetDB, one would immediately encounter names such as monetdbd, monetdb, mserver5, mclient, dbfarm, dbpath and dbname. Their meaning and roles may have been explained individually in their manual pages, but they can be confusing without an overview. In this article, I’ll try to show how they work together.
At its highest user-interface level, MonetDB uses the concept of dbfarm, which is a collection of databases. There is no theoretical limit to the number of databases one can have in a dbfarm, but in practice, some file systems get slower if too many items are in a directory.
A dbfarm is identified by its absolute path, e.g.
/tmp/mydbfarm. Each subdirectory in a dbfarm is a complete database of the same name (also referred to as dbname by some MonetDB tools or options). A database can also be directly addressed by its absolute path, referred to as dbpath.
monetdb are the command-line server and client tools to manage a dbfarm. At the same time,
mclient are tools to serve and query individual databases.
monetdb are not available for Windows, so there, we don’t have the notion of dbfarm but only databases.
The drawing above shows the interplay between the MonetDB concepts and components. To illustrate how they work together, we use the following running example on a macOS Monterey with MonetDB v11.43.9 (Jan2022-SP1).
First, we use the MonetDB daemon
monetdbd to create, start and stop a new dbfarm:
$ monetdbd create /tmp/mydbfarm $ monetdbd start /tmp/mydbfarm $ monetdbd stop /tmp/mydbfarm
We can query the properties of the dbfarm with
monetdbd get. The output below shows that some properties are set to specific values only when the dbfarm is running:
$ monetdbd get all /tmp/mydbfarm property value hostname Styx dbfarm /tmp/mydbfarm status no monetdbd is serving this dbfarm mserver unknown (monetdbd not running) logfile /tmp/mydbfarm/merovingian.log pidfile /tmp/mydbfarm/merovingian.pid sockdir /tmp listenaddr localhost port 50000 exittimeout 60 forward proxy discovery yes discoveryttl 600 control no passphrase <unknown> snapshotdir <unknown> snapshotcompression .tar.lz4 mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000 $ monetdbd start /tmp/mydbfarm $ monetdbd get all /tmp/mydbfarm property value hostname Styx dbfarm /tmp/mydbfarm status monetdbd 11.43.9 (Jan2022-SP1) is serving this dbfarm mserver /usr/local/Cellar/monetdb/11.43.9/bin/mserver5 logfile /tmp/mydbfarm/merovingian.log pidfile /tmp/mydbfarm/merovingian.pid sockdir /tmp listenaddr localhost port 50000 exittimeout 60 forward proxy discovery true discoveryttl 600 control no passphrase <unknown> snapshotdir <unknown> snapshotcompression .tar.lz4 mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000
We can change the value of some properties using
monetdbd set. For instance, in a production environment or if one wants to run multiple
monetdbd-s to serve multiple
dbfarm-s, one would want to change the default port to which
$ monetdbd stop /tmp/mydbfarm $ monetdbd set port=60000 /tmp/mydbfarm $ cat /tmp/mydbfarm/.merovingian_properties # DO NOT EDIT THIS FILE - use monetdb(1) and monetdbd(1) to set properties # This file is used by monetdbd logfile=merovingian.log pidfile=merovingian.pid sockdir=/tmp listenaddr=localhost port=60000 exittimeout=60 forward=proxy discovery=yes discoveryttl=600 control=no snapshotcompression=.tar.lz4 $ monetdbd start /tmp/mydbfarm $ monetdbd get all /tmp/mydbfarm property value hostname Styx dbfarm /tmp/mydbfarm status monetdbd 11.43.9 (Jan2022-SP1) is serving this dbfarm mserver /usr/local/Cellar/monetdb/11.43.9/bin/mserver5 logfile /tmp/mydbfarm/merovingian.log pidfile /tmp/mydbfarm/merovingian.pid sockdir /tmp listenaddr localhost port 60000 …
You can change the property values of a running dbfarm and the change will take effect after a restart. However, to avoid confusion or errors, we recommend stopping the dbfarm before changing its properties because
monetdbd set simply writes the new values into
.merovingian_properties, from which the
monetdbd get commands read values. Hence, without the restart, the property values returned by
monetdbd get can be different than those used by the still-running dbfarm.
monetdbd was called
merovingian after the Matrix character. We renamed it to make the spelling easier. But internally, the original name was mostly preserved, e.g.
merovingian.log. So, when you search for information inside
monetdbd, also search for
monetdb is the client tool of
monetdbd. Now that the dbfarm is up-and-running, we can use
monetdb to create, release, start, stop and destroy databases:
$ monetdb create db1 db2 created database in maintenance mode: db1 created database in maintenance mode: db2 $ monetdb release db1 db2 taken database out of maintenance mode: db1 taken database out of maintenance mode: db2 $ monetdb start db1 db2 starting database 'db1'... done starting database 'db2'... done $ monetdb stop db1 db2 stopping database 'db1'... done stopping database 'db2'... done $ monetdb destroy db1 db2 you are about to remove databases 'db1', 'db2' ALL data in these databases will be lost, are you sure? [y/N] y destroyed database: db1 destroyed database: db2
During this writing, I discovered that you could pass multiple databases to all these commands. Convenient! If I’d known about this feature, it would have saved me quite some time when I was playing with distributed MonetDB. The
destroy command is a bit dramatic, though. With a single
y, all your databases are destroyed. So, enjoy the convenience with care!
I didn’t try what the maximal number of databases is that I can pass to these commands. There is even a super convenient shorthand for these commands, e.g.
monetdb start *, where
* is substituted by all filenames in the current directory. So, DO NOT try this at home ;-)
When a database is in the
maintenance mode, you can start the database only on the localhost with an explicit
monetdb start command (see “Use a database” below, outside the
maintenance mode, explicitly starting a database is not needed), and only the database admin user
monetdb can log-in into the database. In this way, the database admin can safely initialise the database, users, and permissions between the
monetdb create and
monetdb release commands above.
To do maintenance on an existing database, we recommend the following sequence (i.e. lock and stop the database, instead of stop and lock the database):
$ monetdb lock db1 put database under maintenance: db1 $ monetdb stop db1 # a no-op if the database was already stopped stopping database 'db1'... done $ # do all the maintenance work $ monetdb release db1 taken database out of maintenance mode: db1 $ monetdb start db1 starting database 'db1'... done
Locking the database before stopping it ensures that no new normal database user connections can be established after
monetdb lock, and the database can’t be automatically restarted by a user connection request after
monetdb stop (which can happen if one first stops and then locks the database).
monetdb has more features to manage your databases, which by itself can be a story for another day. For now, please check out the
monetdb help message or its documentation.
Working with a database
mclient is the command-line tool one can use to connect to a MonetDB database and run queries. Once a database has been created, start using it is as simply passing the
mclient (I use the
.monetdb file to type less):
$ monetdb create db1 created database in maintenance mode: db1 $ monetdb release db1 taken database out of maintenance mode: db1 $ cat ~/.monetdb user=monetdb password=monetdb save_history=true $ mclient -d db1 password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2022-SP1) Database: MonetDB v11.43.9 (Jan2022-SP1), 'mapi:monetdb://Styx:60000/db1' FOLLOW US on https://twitter.com/MonetDB or https://github.com/MonetDB/MonetDB Type \q to quit, \? for a list of available commands auto commit mode: on sql>
Voilà! Now you can start typing your first query.
What happened behind the scenes is:
mclientcontacts the running
monetdbdto connect to the database
db1is a database in its dbfarm.
db1since it hasn’t been started and it’s not in the maintenance mode.
mclientto continue the communication directly with the
mserver5processing now serving
Each database is served by one
mserver5 and vice versa. When
monetdbd is in action, the involvement of
mserver5 is barely noticeable to users. However, sometimes, you might want to manually start a database, e.g., for debugging, then you can find the exact command which
monetdbd has used to start the
mserver5 in the
$ grep mserver5 /tmp/mydbfarm/merovingian.log … 2022-03-16 18:11:07 MSG db1: arguments: /usr/local/Cellar/monetdb/11.43.9/bin/mserver5 --dbpath=/tmp/mydbfarm/db1 --set merovingian_uri=mapi:monetdb://Styx:60000/db1 --set mapi_listenaddr=none --set mapi_usock=/tmp/mydbfarm/db1/.mapi.sock --set monet_vault_key=/tmp/mydbfarm/db1/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe
To start this database manually,
monet_vault_key are the must-haves, while
mapi_usock should not be used. The remaining options are optional, and you should only change their default values if you know exactly what you’re doing.
Ok, folks, that’s all for today. Until the next blog,
March 22, 2022