Tools for managing MonetDB

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.

monetdbd and monetdb are the command-line server and client tools to manage a dbfarm. At the same time, mserver5 and mclient are tools to serve and query individual databases. monetdbd and monetdb are not available for Windows, so there, we don’t have the notion of dbfarm but only databases.

Tools for managing MonetDB

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).

Managing dbfarms

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[46605] 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 listens:

$ 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[46960] 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.

Previously, 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_properties and merovingian.log. So, when you search for information inside monetdbd, also search for merovingian.

Managing databases

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 dbname to 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:

mclient contacts the running monetdbd to connect to the database db1.
monetdbd checks that db1 is a database in its dbfarm.
monetdbd starts db1 since it hasn’t been started and it’s not in the maintenance mode.
monetdbd redirects mclient to continue the communication directly with the mserver5 processing now serving db1.

Managing a database DIY

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 merovingian.log:

$ grep mserver5 /tmp/mydbfarm/merovingian.log
…
2022-03-16 18:11:07 MSG db1[47863]: 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, dbpath and monet_vault_key are the must-haves, while merovingian_uri, 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,