MonetDB Server

MonetDB Server mk Mon, 03/21/2011 - 00:01

Starting a MonetDB database server is as simple as starting the deamon program monetdbd.  At the time of this writing, monetdbd is only available on UNIX-like platforms. The MonetDB daemon program encapsulates an arbitrary number of database servers, and takes care of their configuration.  The general concept of the MonetDB daemon program is that it builds upon a given directory, often referred to as the dbfarm.  There can be at most one instance of monetdbd monitoring a dbfarm, but multiple monetdbd processes can be running on the same system.

The next (optional) step is to create a .monetdb configuration file with information to ease using the Monetdb client interface, called mclient.

shell> cat ~/.monetdb
language=<"sql" or "mal">

In the simple case, monetdbd is started by the system through some init script, and databases are created by the local DBA, and assigned to users.  A more flexible approach for users, however, is the case where a user runs monetdbd itself.  Because a dbfarm can be anywhere in the filesystem, and needs nothing more than just being an ordinary directory, a user can use monetdbd to initialise his/her own dbfarm in an accessible place.  For example:

% monetdbd create ~/my-dbfarm
% monetdbd get all ~/my-dbfarm
property         value
dbfarm           /Users/fabian/my-dbfarm
status           no monetdbd is serving this dbfarm
mserver          unknown (monetdbd not running)
logfile          merovingian.log
sockdir          /tmp
port             50000
exittimeout      60
forward          proxy
discovery        true
discoveryttl     600
control          no
passphrase       <unknown>
mapisock         /tmp/.s.monetdb.50000
controlsock      /tmp/.s.merovingian.50000

After creation of the personal dbfarm directory, the get command can be used to inspect the dbfarm-wide settings of the newly created environment.  Note that the above output differs per system, per user and per dbfarm.  Important setting is the port to use to open up services for clients.  In particular for a user installation, these often may conflict with another running monetdbd, hence it is wise to change these to a private port in situations where such conflicts may arise:

% monetdbd set port=54321 ~/my-dbfarm
% monetdbd get all ~/my-dbfarm
property         value
dbfarm           /Users/fabian/my-dbfarm
status           no monetdbd is serving this dbfarm
mserver          unknown (monetdbd not running)

logfile          merovingian.log
sockdir          /tmp
port             54321
exittimeout      60
forward          proxy
discovery        true
discoveryttl     600
control          no
passphrase       <unknown>
mapisock         /tmp/.s.monetdb.54321
controlsock      /tmp/.s.merovingian.54321

Note that control defaults to no.  For remote control connections, control has to be set to yes, and a passphrase has to be set as well.  When set, it will show as a hash in get's output.

When the settings look ok, the next step is to simply start monetdbd:

% monetdbd start ~/my-dbfarm

If the above command returned without any message, the monetdbd process was successfully started.  The logfile (default merovingian.log) should show that the daemon has started, bound some ports, and discovered itself.

From this part on, monetdbd can be given commands through the monetdb command (without the trailing 'd').  The monetdb client utility knows commands much like monetdbd, but it is much more focussed towards giving information about what is going on inside monetdbd.  The monetdb client utility is the main tool to use to manage the dbfarm.

The dbfarm we created is empty, as such there are no databases inside it.  With monetdb we can create a new database:

% monetdb create my-first-db
created database in maintenance mode: my-first-db

This simple command created the necessary bits inside the dbfarm for a database called my-first-db.  It is created in maintenance mode, since otherwise anyone could access it before it is properly setup.  If the command gives an access-denied error, most probably another monetdbd is running on the system.  In that case try adding -p54321(the port) as arguments to monetdb in order to tell it which monetdbd to contact as in the status example.  In the status view of monetdb the newly created database will show up as a locked database:

% monetdb -p54321 status
     name        state     uptime       health
my-first-db     locked             

Albeit the database is locked, it can be started, such that the monetdb superuser can access the database:

% monetdb start my-first-db
starting database 'my-first-db'... done

% monetdb status
     name        state     uptime       health
my-first-db     locked           18s  100%,  0s

The database remains locked, but the uptime field indicates the database is actuallty running.  If setting up the database is ready, the database can be made available for other users (if any, see MonetDB SQL tutorial) to connect to it using the release command:

% monetdb release my-first-db
taken database out of maintenance mode: my-first-db

% monetdb status
     name        state     uptime       health
my-first-db     running       2m 48s  100%,  0s

Next thing, making a connection to the database using mclient requires the -d argument to specify which database to connect to.  It will ask for username and password when none given or provided via a .monetdb file.  The default username/password is monetdb/monetdb:

% mclient -dmy-first-db
Welcome to mclient, the MonetDB/SQL interactive terminal (Dec2011-SP1)
Database: MonetDB v11.7.7 (Dec2011-SP1), 'mapi:monetdb://'
Type \q to quit, \? for a list of available commands
auto commit mode: on

Like for the monetdb command, if this fails with some database does not exist error, try giving the port (not the controlport!) to mclient such that it finds the correct monetdbd, e.g.:

% mclient -p54321 -dmy-first-db

It may be necessary to stop individual databases, or even make them unavailable.  The monetdb commands stopand lockcan perform these tasks.  To completely stop the monetdbd daemon process for a dbfarm, stop it using the stop command of monetdbd:

% monetdbd stop ~/my-dbfarm

This should stop the monetdbd and all the databases that are started inside it.  To verify that monetdbd has indeed stopped, review the end of the merovingian.log file.  It should report all databases being shut down, and the deamon shutting down.

mclient man-page

mclient man-page sjoerd Tue, 04/26/2011 - 12:34


mclient − the MonetDB command-line tool


mclient [ options ] [ file or database [ file ... ] ]
mclient −−help


MonetDB is a database management system that is developed from a main-memory perspective with use of a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, and a SQL frontend.

Mclient is the command-line interface to the MonetDB server.

If the −−statement=query (−s query) option is given, the query is executed. If any files are listed after the options, queries are read from the files and executed. The special filename refers to standard input. Note that if there is both a −−statement option and filename arguments, the query given with −−statement is executed first. If no −−statement option is given and no files are specified on the command line, mclient reads queries from standard input.

When reading from standard input, if standard input is a terminal or if the −−interactive (−i) option is given, mclient interprets lines starting with \ (backslash) specially. See the section BACKSLASH COMMANDS below.

Before mclient starts parsing command line options, it reads a .monetdb file. If the environment variable DOTMONETDBFILE is set, it reads the file pointed to by that variable instead. When unset, mclient searches for a .monetdb file in the current working directory, and if that doesn’t exist, in the current user’s home directory. This file can contain defaults for the flags user, password, language, database, save_history, format, and width. For example, an entry in a .monetdb file that sets the default language for mclient to mal looks like this: language=mal. To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment.


General Options

Print usage information and exit.

−−version (−v)

Print version information and exit.

−−encoding=encoding (−E encoding)

Specify the character encoding of the input. The option applies to both the standard input of mclient and to the argument of the −−statement (−s) option but not to the contents of files specified on the command line (except for which refers to standard input) or files specified using the \< command (those must be encoded using UTF-8). The default encoding is taken from the locale.

−−language=language (−l language)

Specify the query language. The following languages are recognized: mal and sql. A unique prefix suffices. When the −−language option is omitted, the default of sql is assumed.

−−database=database (−d database)

Specify the name or URI of the database to connect to. The −d can be omitted if an equally named file does not exist in the current directory. As such, the first non-option argument will be interpreted as database to connect to if the argument does not exist as file. Valid URIs are as returned by ‘monetdb discover‘, see monetdb(1), and look like mapi:monetdb://hostname:port/database.

−−host=hostname (−h hostname)

Specify the name of the host on which the server runs (default: localhost). When the argument starts with a forward slash (/), host is assumed to be the directory where the UNIX sockets are stored for platforms where these are supported.

−−port=portnr (−p portnr)

Specify the portnumber of the server (default: 50000).

−−interactive (−i)

When reading from standard input, interpret lines starting with \ (backslash) specially. See the section BACKSLASH COMMANDS below.

−−timer=timermode (−t timermode)

The timer command controls the format of the time reported for queries. The default mode is none which turns off timing reporting. The timer mode clock reports the client-side wall-clock time ("clk") in a human-friendly format. The timer mode performance reports client-side wall-clock time ("clk") as well as detailed server-side timings, all in milliseconds (ms): the time to parse the SQL query, optimize the logical relational plan and create the initial physical (MAL) plan ("sql"); the time to optimize the physical (MAL) plan ("opt"); the time to execute the physical (MAL) plan ("run"). All timings are reported on stderr.
that the client-measured wall-clock time is reported per query only when options −−interactive or −−echo are used, because only then does mclient send individual lines (statements) of the SQL script to the server. Otherwise, when mclient sends the SQL script in large(r) batch(es), only the total wall-clock time per batch is measured and reported. The server-measured detailed performance timings are always measured and reported per query.

−−user=user (−u user)

Specify the user to connect as. If this flag is absent, the client will ask for a user name, unless a default was found in .monetdb file.

−−format=format (−f format)

Specify the output format. The possible values are sql, expanded, x, csv, tab, raw, xml, and trash. csv is comma-separated values, tab is tab-separated values, raw is no special formatting (data is dumped the way the server sends it to the client), sql is a pretty format which is meant for human consumption where columns are clearly shown, expanded and x are synonyms and are another pretty format meant for human consumption where column values are printed in full and below each other, xml is a valid (in the XML sense) document, and trash does not render any output, enabling performance measurements free of any output rendering/serialization costs. In addition to plain csv, two other forms are possible. csv=c uses c as column separator; csv+c uses c as column separator and produces a single header line in addition to the data.

−−echo (−e)

Echo the query. Note that using this option slows down processing.

−−history (−H)

Load and save the command line history (default off).

−−log=logfile (−L logfile)

Save client/server interaction in the specified file.

−−statement=stmt (−s stmt)

Execute the specified query. The query is run before any queries from files specified on the command line are run.

−−timezone (−z)

Do not tell the client’s timezone to the server.

−−Xdebug (−X)

Trace network interaction between mclient and the server.

−−pager=cmd (−| cmd)

Send query output through the specified cmd. One cmd is started for each query. Note that the | will have to be quoted or else the shell will interpret it.

SQL Options
nullstr (−n nullstr)

Set the string to be used as NULL representation when using the sql, csv, or tab output formats. If not used, NULL values are represented by the string "null" in the sql output format, and as the empty string in the csv and tab output formats. Note that an argument is required, so in order to use the empty string, use −n "" (with the space) or −−null=.

−−autocommit (−a)

Switch autocommit mode off. By default, autocommit mode is on.

−−allow−remote (−R)

Allow remote content (URLs) in the COPY INTO table FROM file ON CLIENT ... query. Remote content is retrieved by mclient.

−−rows=nr (−r nr)

If specified, query results will be paged by an internal pager at the specified number of lines.

−−width=nr (−w nr)

Specify the width of the screen. The default is the (initial) width of the terminal.

−−dump (−D)

Create an SQL dump.

−−inserts (−N)

Use INSERT INTO statements instead of COPY INTO + CSV values when dumping the data of a table. This option can be used when trying to load data from MonetDB into another database, or when e.g. JDBC applications are used to reload the dump.


General Commands


Show a help message explaining the backslash commands.


Exit mclient.

\< file

Read input from the named file.

\> file

Write output to the named file. If no file is specified, write to standard output.

\| command

Pipe output to the given command. Each query is piped to a new invocation of the command. If no command is given, revert to writing output to standard output.


Show the readline(3) history.

\L file

Log client/server interaction in the given file. If no file is specified, stop logging information.


Trace what mclient is doing. This is mostly for debugging purposes.


Echo the query in SQL formatting mode.

\f format

Use the specified format mode to format the output. Possible modes the same as for the −−format (−f) option.

\w width

Set the maximum page width for rendering in the sql formatting mode. If width is −1, the page width is unlimited, when width is 0, use the terminal width. If width is greater than 0, use the given width.

\r rows

Use an internal pager using rows per page. If rows is −1, stop using the internal pager.

SQL Commands


Dump the complete database. This is equivalent to using the program msqldump(1).

\D table

Dump the given table.


Alias for \dvt.


List database objects of the given type. Multiple type specifiers can be used at the same time. The specifiers S, t, v, s, f and n stand for System, table, view, sequence, function and schema respectively. Note that S simply switches on viewing system catalog objects, which is orthogonal to the other specifiers.

\d[Stvsfn]+ object

Describe the given object in the database using SQL statements that reconstruct the object. The same specifiers as above can be used, following the same rules. When no specifiers are given, vt is assumed. The object can be given with or without a schema, separated by a dot. The object name can contain the wildcard characters * and _ that represent zero or more, and exactly one character respectively. An object name is converted to lowercase, unless the object name is quoted by double quotes ("). Examples of this, are e.g. *.mytable, tabletype*, or "myschema.FOO". Note that wildcard characters do not work in quoted objects. Quoting follows SQL quoting rules. Arbitrary parts can be quoted, and two quotes following each other in a quoted string represent the quote itself.


Enable auto commit mode.


Disable auto commit mode.


Efficiently import data from a CSV (comma-separated values) file into a table. The file must be readable by the server. $file is the absolute path name of the file, $table is the name of the table, $db is the name of the database.

mclient −d $db −s "COPY INTO $table FROM ’$file’ USING DELIMITERS ’,’,’\\n’,’\"’"

Efficiently import data from a CSV file into a table when the file is to be read by mclient (e.g. the server has no access to the file). $file is the (absolute or relative) path name of the file, $table is the name of the table, $db is the name of the database.

mclient −d $db −s "COPY INTO $table FROM STDIN USING DELIMITERS ’,’,’\\n’,’\"’" − < $file

Note that in this latter case, if a count of records is supplied, it should be at least as large as the number of records actually present in the CSV file. This, because otherwise the remainder of the file will be interpreted as SQL queries.

Another, easier method to have the client read the file content is as follows.

mclient −d $db −s "COPY INTO $table FROM ’$file’ ON CLIENT USING DELIMITERS ’,’,’\\n’,\"’"

If, in addition, the option −−allow−remote is passed to mclient, the $file in the above query can also be a URL. It then has to have the form schema://string.

See for more information about the COPY INTO query.


msqldump(1), mserver5(1)

monetdb man-page

monetdb man-page sjoerd Tue, 04/26/2011 - 12:33


monetdb − control a MonetDB Database Server instance


monetdb [ monetdb_options ] command [ command_options ] [ command_args ]


Monetdb allows an administrator of the MonetDB Database Server to perform various operations on the databases in the server. It relies on monetdbd(1) running in the background for all operations.


Monetdb_options affect all commands and control the general behavior of monetdb.


Suppresses all standard progress messages, only writing output to stderr if an error occurred.

−h hostname

Connect to hostname instead of attempting a connection over the local UNIX socket. This allows monetdb to connect to a remote monetdbd(1). The use of this option requires −P (see below).

−p port

Connects to the given portnumber instead of the default (50000). Requires −h to be given as option too.

−P passphrase

Specifies the passphrase necessary to login to a remote monetdbd(1). This option requires −h to be given as well. A bad passphrase causes monetdb to fail to login, and hence fail to perform any remote action.


Show version, equal to monetdb version.


The commands for the monetdb utility are create, destroy, lock, release, status, start, stop, kill, profilerstart, profilerstop, set, get, inherit, discover, help, and version. The commands facilitate adding, removing, maintaining, starting and stopping a database inside the MonetDB Database Server.

For all commands, database arguments can be glob-like expressions. This allows to do wildcard matches. For details on the syntax, see EXPRESSIONS.
[−m pattern] [−p password] database [database ...]

Initializes a new database in the MonetDB Database Server. A database created with this command makes it available under its database name, but not yet for use by clients, as the database is put into maintenance mode. This allows the database administrator to perform initialization steps before releasing it to users, unless the −p argument is supplied. See also monetdb lock. The name of the database must match the expression [A−Za−z0−9−_]+.

With the −m flag, instead of creating a database, a multiplex-funnel is created. See section MULTIPLEX-FUNNEL in monetdbd(1). The pattern argument is not fully the same as a pattern for connecting or discovery. Each parallel target for the multiplex-funnel is given as username+password@pattern sequence, separated by commas. Here the pattern is an ordinary pattern as would be used for connecting to a database, and can hence also be just the name of a database.

−p password

The −p flag allows to create a database with the given password for the monetdb user. Since this protects the database from being accessed via well-known credentials, the created database is not locked after creation. This way, a new database can be created and used right away using the password supplied.

destroy [−f] database [database ...]

Removes the given database, including all its data and logfiles. Once destroy has completed, all data is lost. Be careful when using this command.


By default, a confirmation question is asked, however the −f option, when provided, suppresses this question and removal is executed right away. Note that without this option you cannot destroy a running database, bring it down first using the stop command.

lock database [database ...]

Puts the given database in maintenance mode. A database under maintenance can only be connected to by an administrator account (by default the monetdb account). A database which is under maintenance is not started automatically by monetdbd(1), the MonetDB Database Server, when clients request for it. Use the release command to bring the database back for normal usage. To start a database which is under maintenance for administrator access, the start command can be used.

release database [database ...]

Brings back a database from maintenance mode. A released database is available again for normal use by any client, and is started on demand. Use the lock command to take a database under maintenance.

status [−lc] [−s states] [database ...]

Shows the state of the given database, or, when none given, all known databases. Three modes control the level of detail in the displayed output. By default a condensed one-line output per database format is used. This output resembles pretty much the output of various xxxstat programs, and is ideal for quickly gaining an overview of the system state. The output is divided into four columns, name, state, health, and remarks. The state column contains two characters that identify the state of the database, based on Booting (starting up), Running, Stopped, Crashed and Locked (under maintenance). This is followed by the uptime when running. The health column contains the percentage of successful starts and stops, followed by the average uptime. The remarks column can contain arbitrary information about the database state, but usually contains the URI the database can be connected to.


The −c flag shows the most used properties of a database. This includes the state of the database (running, crashed, stopped), whether it is under maintenance or not, the crash averages and uptime statistics. The crash average is the number of times the database has crashed over the last 1, 15 or 30 starts. The lower the average, the healthier the database is.


Triggered by the −l flag, a long listing is used. This listing spans many rows with on each row one property and its value separated by a colon (:). The long listing includes all information that is available.


The −s flag controls which databases are being shown, matching their state. The required argument to this flag can be a combination of any of the following characters. Note that the order in which they are put also controls the order in which the databases are printed. b, r, s, c, and l are used to print a starting up (booting), started (running), stopped, crashed and locked database respectively. The default order which is used when the −s flag is absent, is rbscl.

start [−a] database [database ...]
[−a] database [database ...]
[−a] database [database ...]

Starts, stops or kills the given database, or, when −a is supplied, all known databases. 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. It is more common to use the stop command to stop a database. It will first attempt to stop the database, waiting for mero_exittimeout seconds and if that fails, kill the database. When using the start command, monetdb(1) will output diagnostic messages if the requested action failed. When encountering an error, one should always consult the logfile of monetdbd(1) for more details. For the kill command a diagnostic message indicating the database has crashed is always emitted, due to the nature of that command. Note that in combination with −a the return code of monetdb(1) indicates failure if one of the databases had a failure, even though the operation on other databases was successful.

profilerstart database [database ...]
database [database ...]

Starts or stops the collection of profiling logs for the given database. The property profilerlogpath must be set for the given database, and it should point to a directory where the logs will be gathered. The filenames of the logs have the format: proflog_<database>_YYYY-MM-DD_HH-MM-SS.json where the last part is the date and time when the collection started. Please note that a file recording the pid of the profiler is written in the log directory, therefore each database needs to have a different profilerlogpath value.

get <all | property[,property[,..]]> [database ...]

Prints the requested properties, or all known properties, for the given database. For each property its source and value are printed. Source indicates where the current value comes from, e.g. the configuration file, or a local override.

set property=value database [database ...]

Sets property to value for the given database. For a list of properties, run monetdb get all. Most properties require the database to be stopped when set.

Defines if and how the database is being announced to other monetdbds or not. If not set to yes or no the database is simply announced or not. Using a string, called tag the database is shared using that tag, allowing for more sophisticated usage. For information about the tag format and use, see section REMOTE DATABASES in the monetdbd(1) manpage. Note that this property can be set for a running database, and that a change takes immediate effect in the network.


Defines how many worker threads the server should use to perform main processing. Normally, this number equals the number of available CPU cores in the system. Reducing this number forces the server to use less parallelism when executing queries, or none at all if set to 1.


Each server operates with a given optimizer pipeline. While the default usually is the best setting, for some experimental uses the pipeline can be changed. See the mserver5(1) manpage for available pipelines. Changing this setting is discouraged at all times.


Defines if the database has to be started in readonly mode. Updates are rejected in this mode, and the server employs some read-only optimizations that can lead to improved performance.


Sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged. A multiplex-funnel may be more performant, see MULTIPLEX-FUNNEL below.

inherit property database [database ...]

Like set, but unsets the database-local value, and reverts to inherit from the default again.

discover [expression]

Returns a list of remote monetdbds and database URIs that were discovered by monetdbd(1). All databases listed can be connected to via the local MonetDB Database Server as if it were local databases using their database name. The connection is redirected or proxied based on configuration settings. If expression is given, only those discovered databases are returned for which their URI matches the expression. The expression syntax is described in the section EXPRESSIONS. Next to database URIs the hostnames and ports for monetdbds that allow to be controlled remotely can be found in the discover list masked with an asterisk. These entries can easily be filtered out using an expression (e.g. "mapi:monetdb:*") if desired. The control entries come in handy when one wants to get an overview of available monetdbds in e.g. a local cluster. Note that for monetdbd to announce its control port, the mero_controlport setting for that monetdbd must be enabled in the configuration file.


help [command]

Shows general help, or short help for a given command.



Shows the version of the monetdb utility.


For various options, typically database names, expressions can be used. These expressions are limited shell-globbing like, where the * in any position is expanded to an arbitrary string. The * can occur multiple times in the expression, allowing for more advanced matches. Note that the empty string also matches the *, hence "de*mo" can return "demo" as match. To match the literal ’*’ character, one has to escape it using a backslash, e.g. "\*".


The monetdb utility returns exit code 0 if it successfully performed the requested command. An error caused by user input or database state is indicated by exit code 1. If an internal error in the utility occurs, exit code 2 is returned.


monetdbd(1), mserver5(1)

monetdbd man-page

monetdbd man-page sjoerd Tue, 04/26/2011 - 12:31


monetdbd − the MonetDB Database Server daemon


monetdbd command [ command_args ] dbfarm


Monetdbd is the MonetDB Database Server daemon. The program is mainly meant to be used as daemon, but it also allows to setup and change the configuration of a dbfarm. The use of monetdbd is either as user-oriented way to configure, start and stop a database farm, or to be started from a startup script, such as from /etc/init.d/ on Linux systems or smf(5) on Solaris systems, as part of a system startup.

Monetdbd is the system formerly known as merovingian. It was renamed to monetdbd since the name merovingian proved to be confusing to most regular end-users. Internally, monetdbd uses the name merovingian at many places for historical reasons.

A monetdbd instance manages one local cluster based, which is a directory in the system, referred to as the dbfarm. Nowadays, the dbfarm location always has to be given as argument to monetdbd.

Within its local cluster monetdbd takes care of starting up databases when necessary, and stopping them either upon request via monetdb(1) or when being shut down. Client database connections are made against monetdbd initially which redirects or proxies the client to the appropriate database process, started on the fly when necessary.

When started, monetdbd runs by default in the background, sending log messages to merovingian.log, until being sent a stop, terminate or interrupt signal, possibly using the stop command of monetdbd.

monetdbd uses a neighbor discovery scheme to detect other monetdbd processes running in the local network. Databases from those remote instances are made available to a locally connecting client. Remote databases never override local databases, and their availability is controlled by the remote monetdbd process. See also the sharing capabilities of monetdb(1) and the REMOTE DATABASES section below.


The commands for monetdbd are create, start, stop, get, set, version, and help. The commands facilitate initializing a dbfarm, starting and stopping the MonetDB Database Server, and retrieving or setting options.
create dbfarm

Initializes a new database farm, such that a MonetDB Database Server can be started on that location. All necessary directories are attempted to be created, and an initial properties file is created in the directory itself. dbfarm must be a location addressable in the local filesystem hierarchy.

start [−n] dbfarm

Starts monetdbd, the MonetDB Database Server, on the given dbfarm. When the −n flag is given, monetdbd will not fork into the background, but instead remain attached to the calling environment, until given a stop signal.

stop dbfarm

Sends a stop signal to the monetdbd process responsible for the given dbfarm.

get <all | property[,property[,..]]> dbfarm

Prints the requested properties, or all known properties, for the given dbfarm. For each property, its value is printed. Some properties are virtual, and given for information purposes only, they cannot be modified using the set command.

set property=value dbfarm

Sets property to value for the given database. For a list of properties, run monetdbd get all. Some properties require a restart of the MonetDB Database Server in order to take effect. The set command, will however always write the property, and tell the running monetdbd to reload the properties file (if running). For an explanation of the properties, see the CONFIGURATION section below.


Monetdbd reads its properties from the .merovingian_properties file inside the dbfarm. This file is created by the create command. This file is not meant to be edited manually, instead it should be updated using the set command. The following properties can be set:

This property points to the file where all log messages are written to. It is relative to the dbfarm directory, but can be absolute to point to e.g. another medium. Changing this property takes effect immediately at runtime.


Monetdbd stores the process ID of the background server in the file pointed to by this property. The same rules apply as for the logfile property.


For faster access, monetdbd uses UNIX domain sockets for its control mechanism and regular database connections. The sockets are placed as files in the filesystem hierarchy. The sockdir property controls in which directory they are placed. In general this setting should not be changed.


This property specifies which TCP port monetdbd should listen to for connection requests. Defaults to 50000.


This property specifies an address that is allowed to connect to the server. The user can specify one IP(v6) address, or use the notation to allow connections from everywhere. Defaults to localhost (


For remote management of monetdbd, the control property specifies whether or not to enable remote management. Note that for remote management, a passphrase is required, see below. It defaults to false for security reasons. Changing this property takes effect immediately at runtime.


To control monetdbd from a remote machine, a passphrase is necessary, to be given to monetdb(1). The passphrase can be either given as hashed value prefixed by the hash type in curly braces (e.g. {SHA512}xxx...) or as plain text value which will be hashed automatically. Note that the only hash accepted is the one specified at configure time, which is SHA512. Changing this property takes effect immediately at runtime.


Specifies whether neighbor discovery is to be enabled using UDP broadcasts or not. The broadcasts are done on the same portnumber as the port setting.


Monetdbd publishes locally available databases to others periodically. The interval used here, defined in seconds, depends on the time-to-live of the databases before they need to get refreshed. The default is 600 seconds (10 minutes), which should keep traffic in your network fairly low. Additions and removals are processed immediately regardless of this timeout. If you are in a network environment where physical network links disappear often, you may want to decrease this value to more quickly remove no longer reachable databases.


mservers that were started by the MonetDB Database Server are shut down when monetdbd is shut down. Setting the exittimeout property to a positive non-zero value will shut down each running mserver with the given time-out in seconds. If the time-out expires, the mserver process is killed using the SIGKILL signal. A time-out value of 0 means no mservers will be shut down, and hence they will continue to run after monetdbd has shut down. Note that this particular configuration is extremely inconvenient. The default time-out is 60 seconds. If your databases are rather large and find your databases consistently being killed by monetdbd upon shutdown, you may want to increase this time-out. Changing this property takes effect immediately at runtime.


monetdbd has two ways in which it can "attach" a connecting client to the target database. The first method, redirect, uses a redirect sent to the client with the responsible mserver process. The second method, proxy, proxies the client to the mserver over monetdbd. While redirect is more efficient, it requires the connecting client to be able to connect to the mserver. In many settings this may be undesirable or even impossible, since a wide range of open ports and routing are necessary for this. In such case the proxy technique of monetdbd is a good solution, which also allows a monetdbd instance on the border of a network to serve requests to nodes in the local (unreachable) network. Note that for local databases, the proxy method uses a UNIX domain socket feature to pass file-descriptors to the local mserver. This effectively is as efficient as the redirect approach, but still hides away the mservers properly behind monetdbd. Hence, in practice it is only relevant for connections to remote databases to use redirects instead of proxies. Changing this property takes effect immediately at runtime.


The neighbor discovery capabilities of monetdbd allow a user to contact a remote database transparently, as if it were a local database. By default, all local databases are announced in the network, such that neighbors can pick them up to make them available for their local users. This feature can be disabled globally, or on database level. For the latter, the monetdb(1) utility can be used to change the share property of a database.

While neighbor discovery in itself is sufficient to locate a database in a cluster, it is limited in expressiveness. For instance, database names are assumed to be unique throughout the entire system. This means local databases overshadow remote ones, and duplicate remote entries cannot be distinguished. To compensate for this, monetdbd allows to adds a tag to each database that is being shared. This tag is sent in addition to the database name, and only understood by other monetdbds.

Tags are arbitrary ASCII-strings matching the pattern [A−Za−z0−9./]+. There are no assumed semantics in the tag, which allows for multiple approaches when using the tag. The tag is always used in combination with the database name. For this, the ‘/’ character is used as separator, which hence suggests the user to use that character as separator for multilevel tags. Monetdbd allows common path globbing using ‘*’ on tags, which allows for many use-cases. Consider for instance the following three databases with their tag:


A default match has implicit ‘/*’ added to the search, making more generic search strings match more specific ones. Hence, a connect with database dbX is the same as dbX/* and hence matches dbX/master/tableQ. Similar, a database connect for */master matches the same database as before. Note that the implicit ‘/*’ is not added if that would cause no matches, such as for */master/tableQ which would return all masters for tableQ, which in our hypothetical example is only dbX. In contrast, a database connect for */slave/tableQ matches with either dbY or dbZ. Monetdbd returns the two options to the client in a round-robin fashion, such that subsequent connects for the same pattern result in a load-balanced connect to either of both databases.

With tags in use, one can possibly make distinction between databases, if setup like that. The previous example could hence also be setup like this:


Connecting to tableQ/slave would now return either of both databases even though they are not unique (apart from the host they are located on, which is not shown in the example). While being confusing for humans, for monetdbd it is the same situation as in the previous example. However, because globbing allows to make things easier to understand, tags for both slaves could be changed to slaveX or slave/X and use the necessary pattern to match them. It is up to the user to decide how to use the tags.


Monetdbd implements multiplex-funnel capabilities. As the name suggests two techniques are combined, the multiplexer and the funnel.

The funnel capability limits the access to the database to one client at a time. That is, if multiple clients connect to the funnel, their queries will be serialized such that they are executed one after the other. An effect of this approach is that clients no longer have an exclusive channel to the database, which means that individual queries from one client may have been interleaved with queries from others. This most notably makes SQL transaction blocks unreliable with a funnel. The funnel, hence, is meant to scale down a large amount of clients that perform short-running (read-only) queries, as typically seen in web-based query loads.

When a funnel is defined to use multiple databases, the funnel adds a multiplexer to its query channel. A multiplex-funnel sends each query to all of the defined databases. This behavior can be quite confusing at first, but proves to be useful in typical sharding configurations, where in particular simple selection queries have to be performed on each of the shards. The multiplexer combines the answers from all defined databases in one single answer that it sends back to the client. However, this combining is without any smart logic, that is, the multiplexer does not evaluate the query it is running, but just combines all answers it receives from the databases. This results in e.g. as many return tuples for a SELECT COUNT(*) query, as there are databases defined.

Due to the two above mentioned characteristics, a multiplex-funnel has some limitations. As mentioned before, transactions over multiple queries are likely not to result in the desired behavior. This is due to each query to the funnel is required to be self-contained. Further, since for each query, the results from multiple servers have to be combined into one, that query must only return a single response, i.e. multi-statement queries are most likely causing the funnel to respond with an error, or return garbled results. Last, the size of each query is limited to currently about 80K. While this size should be sufficient for most queries, it is likely not enough for e.g. COPY INTO statements. Apart from the data transfer implications, such statements should not be used with the funnel, as the results will be undefined due to the limited query buffer. Applications using the funnel should aim for short and single-statement queries that require no transactions.

See the create command in the monetdb(1) man-page for details on how to setup a multiplex-funnel.


Monetdbd acts upon a number of signals as is common for a daemon.

Any of these signals make monetdbd enter the shutdown sequence. This sequence involves cleanly shutting down listener sockets, shutting down all started databases and finally terminating itself.


When this signal is received by monetdbd it will reopen the logfile as pointed to by the logfile setting. Before it reopens the logfile, it will re-read the properties file from the dbfarm, which might result in opening a different file to continue logging.


Monetdbd returns exit code 0 if it was able to successfully perform the requested action, e.g. start, stop, etc. When an error occurs during the action, that prevents monetdbd from successfully performing the action, the exit code 1 is returned.


monetdb(1), mserver5(1)

mserver5 man-page

mserver5 man-page sjoerd Tue, 04/26/2011 - 12:35


mserver5 − the MonetDB server version 5


mserver5 [ options ]


Mserver5 is the current MonetDB server that performs all processing on request of clients for a certain database.

Note that while mserver5 is the process that does the actual work, it is usually more common to start, monitor and connect to the mserver5 process through monetdbd(1).

This man-page describes the options that mserver5 understands. It is intended for people who really need to work with mserver5 itself. In regular cases, the programs monetdbd(1) and monetdb(1) control the many options, and allow to adjust them to appropriate values where sensible. For normal usage, it is preferred to apply any configuration through these programs.


When the build-time configuration did not disable this, a mserver5 process presents the user with a console prompt. On this prompt, MAL commands can be executed. The architecture is setup to handle multiple streams of requests. The first thread started represents the server, which is the console prompt, reading from standard input and writing to standard output.

The server thread started remains in existence until all other threads die. The server is stopped by Ctrl-D on its console, typing quit() or by sending it a termination signal (SIGINT, SIGTERM, SIGQUIT).


Mserver5 can be started with options and scripts as arguments. The MAL scripts will be executed directly after startup on the console, which eases e.g. testing of MAL scripts directly, without starting a client.

Path where mserver5 should find a database. Shorthand for option gdk_dbpath. Default value: ${prefix}/var/monetdb5/dbfarm/demo.


Path where mserver5 should store transient data. Default value is the value of the −−dbpath option.


Config file to read options from. This file can contain all options as can be set with the --set flag. See CONFIG FILE FORMAT.


Disable the console prompt, do not read commands from standard input. Default: no.

−−set option=value

Set individual configuration option. For possible options, see PARAMETERS sections.


Print list of options.


Print version and compile configuration.


Print information about MAL instructions as they are executed.


Set or increase verbosity level. If no value is specified, the verbosity level is increased by 1. If a value is specified, the verbosity level is set to the given value.


Set debug level. This is mostly for debugging purposes. The value is an integer, which can be (a bit-wise OR of):
= THRDMASK = thread-specific debug output
= CHECKMASK = property enforcing on new BATs
= MEMMASK = memory allocation
= PROPMASK = property checking on all values:
tells about wrongly set properties

= IOMASK = major IO activity
= BATMASK = BAT handling
= PARMASK = Thread management
= TMMASK = Transaction management
= TEMMASK = Locks and Triggers
= PERFMASK = BBP Performance (?)
= DELTAMASK = Delta debugging (?)
= LOADMASK = Module loading
= ALGOMASK = show join/select algorithm chosen
= ESTIMASK = show result size estimations
(for join, select)

= NOSYNCMASK = disable forcefully synchronizing files
to disk. If this flag is set, there is
no guarantee that the database remains
consistent after a crash. DO NOT USE
(unless you really don’t care about your

= DEADBEEFMASK = disable "cleaning" of freed memory
in GDKfree() (e.g., for performance

= ALLOCMASK = exhaustive GDK malloc & free tracing
for debugging (GDK developers, only)

= OPTMASK = trace the actions, decisions and
effects of MAL optimizers

= HEAPMASK = trace/debug HEAPextend;
used only for development & debugging

= FORCEMITOMASK = forcefully activate mitosis even on
small tables, i.e., split small tables
in as many (tiny) pieces as there are
cores (threads) available;
this allows us to test mitosis
functionality without requiring large
data sets (— at the expense of a
potentially significant interpretation
overhead for unnecessarily large plans);
used only for development & testing;
set automatically by

The value together with the = sign is optional. If not specified, it defaults to 1. In the short form −d, the value, if present, must immediately (i.e. without space) follow the option. The values of multiple instances of this flag are OR-ed together.


Equivalent to −−debug=(THRDMASK | PARMASK).


Equivalent to −−debug=(MEMMASK | ALLOCMASK).


Equivalent to −−debug=(CHECKMASK | PROPMASK | BATMASK).


Equivalent to −−debug=(IOMASK | PERFMASK).


Equivalent to −−debug=(HEAPMASK).


Equivalent to −−debug=(TMMASK | DELTAMASK | TEMMASK).


Equivalent to −−debug=(LOADMASK).


Equivalent to −−debug=(ALGOMASK | ESTIMASK).


Equivalent to −−debug=(DEADBEEFMASK).


Equivalent to −−debug=(OPTMASK).


Equivalent to −−debug=(FORCEMITOMASK | NOSYNCMASK).


Mserver5 instructs the GDK kernel through the MAL (MonetDB Assembler Language) language. MonetDB 5 contains an extensive optimiser framework to transform MAL plans into more optimal or functional (e.g. distributed) plans. These parameters control behaviour on the MAL level.

The authorisation tables inside mserver5 can be encrypted with a key, such that reading the BATs does not directly disclose any credentials. The monet_vault_key setting points to a file that stores a secret key to unlock the password vault. It can contain anything. The file is read up to the first null-byte (’ ’), hence it can be padded to any length with trailing null-bytes to obfuscate the key length. Generating a key can be done for example by using a tool such as pwgen and adding a few of the passwords generated. Make sure not to choose a too small key. Note that on absence of a vault key file, some default key is used to encrypt the authorisation tables. Changing this setting (effectively changing the key) for an existing database makes that database unusable as noone is any longer able to login. If you use monetdbd(1), a per-database vault key is set.


Controls how many client slots are allocated for clients to connect. This settings limits the maximum number of connected clients at the same time. Note that MonetDB is not designed to handle massive amounts of connected clients. The funnel capability from monetdbd(1) might be a more suitable solution for such workloads.

Default 64.


The SQL component of MonetDB 5 runs on top of the MAL environment. It has its own SQL-level specific settings.

Enable debugging using a mask. This option should normally be disabled (0). Default: 0.


The default SQL optimizer pipeline can be set per server. See the optpipe setting in monetdb(1) when using monetdbd. During SQL initialization, the optimizer pipeline is checked against the dependency information maintained in the optimizer library to ensure there are no conflicts and at least the pre-requisite optimizers are used. The setting of sql_optimizer can be either the list of optimizers to run. Default: default_pipe.
The following are possible pipes to use:

The minimal pipeline necessary by the server to operate correctly. minimal_pipe=inline,remap,deadcode,multiplex,generator,profiler,candidates,garbageCollector


The default pipeline contains the mitosis-mergetable-reorder optimizers, aimed at large tables and improved access locality. default_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,pushselect,aliases,mitosis,mergetable,deadcode,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,dataflow,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector


The no_mitosis pipeline is identical to the default pipeline, except that optimizer mitosis is omitted. It is used mainly to make some tests work deterministically, and to check/debug whether "unexpected" problems are related to mitosis (and/or mergetable). no_mitosis_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,pushselect,aliases,mergetable,deadcode,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,dataflow,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector


The sequential pipeline is identical to the default pipeline, except that optimizers mitosis & dataflow are omitted. It is use mainly to make some tests work deterministically, i.e., avoid ambigious output, by avoiding parallelism. sequential_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,pushselect,aliases,mergetable,deadcode,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector


Enable embedded Python. This means Python code can be called from SQL. The value is true or 2 for embedded Python 2, or 3 for embedded Python 3. Note that by enabling embedded Python, users of the server are allowed to execute arbitrary Python code, and are therefore able to read and modify all data that the server process has access to. Also note that it is not possible to enable both Python 2 and 3 at the same time.


Enable embedded R. This means R code can be called from SQL. Note that by enabling embedded R, users of the server are allowed to execute arbitrary R code, and are therefore able to read and modify all data that the server process has access to.


Enable embedded C. This means C code can be called from SQL. The C code will first be compiled and then executed. This means a C compiler must be available. Note also that by enabling embedded C, users of the server are allowed to execute arbitrary C code, and are therefore able to read and modify all data that the server process has access to. In addition, if the C code causes a crash, all bets are off.


The configuration file readable by mserver5 consists of parameters of the form "name=value". The file is line-based, each newline-terminated line represents either a comment or a parameter. Only the first equals sign in a parameter is significant. Whitespace before or after the first equals sign is not stripped. Trailing whitespace in a parameter value is retained verbatim. Any line beginning with a hash (#) is ignored, as are lines containing only whitespace. The values following the equals sign in parameters are all a string where quotes are not needed, and if written be part of the string.


monetdbd(1), monetdb(1), mclient(1)

msqldump man-page

msqldump man-page sjoerd Tue, 09/20/2011 - 14:45


msqldump − dump a MonetDB/SQL database


msqldump [ options ] [ dbname ]


MonetDB is a database management system that is developed from a main-memory perspective with use of a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, SQL- and XML- frontends.

Msqldump is the program to dump an MonetDB/SQL database. The dump can be used to populate a new MonetDB/SQL database.

Before msqldump starts parsing command line options, it reads a .monetdb file. If the environment variable DOTMONETDBFILE is set, it reads the file pointed to by that variable instead. When unset, msqldump searches for a .monetdb file in the current working directory, and if that doesn’t exist, in the current user’s home directory. This file can contain defaults for the flags user and password. To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment.


−−help (−?)

Print usage information and exit.

−−database=database (−d database)

Specify the name of the database to connect to. The −d can be omitted if it is the last option.

−−host=hostname (−h hostname)

Specify the name of the host on which the server runs (default: localhost).

−−port=portnr (−p portnr)

Specify the portnumber of the server (default: 50000).

−−user=user (−u user)

Specify the user to connect as. If this flag is absent, the client will ask for a user name.

−−describe (−D)

Only dump the database schema.

−−inserts (−N)

When dumping the table data, use INSERT INTO statements, rather than COPY INTO + CSV values. INSERT INTO statements are more portable, and necessary when the load of the dump is processed by e.g. a JDBC application.

−−functions (−f)

Only dump functions definitions.

−−table=table (−t table)

Only dump the specified table.

−−quiet (−q)

Don’t print the welcome message.

−−Xdebug (−X)

Trace network interaction between mclient and the server.


mclient(1), mserver5(1)

Readonly database

Readonly database mk Sun, 03/20/2011 - 23:53

When working in a regular mode, the query processing spans the basic column storage and the data changed by transactions (inserts, updates, and deletes). For instance, the inserted data, kept in a separate delta structure, are added to the column before other operations in the plan. In a 'read-only mode' modifying transactions are not allowed and the query processing spans only over the basic column storage. One of the consequences is that query execution may become faster.

The readonly mode is administered with a boolean variable, which  can be set by the system administrator using the monetdb tool:

shell> monetdb set readonly=yes <mydatabasename>

It ensures that all tables are accessed in read only mode.  Moreover, the database is protected against any catalog change, the user can not even create a temporary table for keeping an intermediate result around.

The alternative route is to deploy SQL schemas in combination with user authentication and access control grants to selectively allow users access to the database. See the corresponding description in the SQL manual.