While Monetdbd is a tool that works with DBfarm, Monetdb is a tool that works with the database. Monetdb can be used to create, publish, start, stop, and destroy databases. In reality, Monetdb will just delegate all the work to Monetdbd, and Monetdbd will be the one that will perform all the operations.
Before we learn something about Monetdb, we first have to start DBfarm. We have created DBfarm in the previous post, about monetdbd, "link". Inside of this farm we already have database DBfarmDB.
monetdbd start DBfarm
Database Commands
I will create new database in DBfarm.
monetdb create dbA
Database will be created in the maintenance mode.
Database that is in the maintenance mode can only be started by admin on localhost with this command: monetdb start dbA
I will login to this database and I will create a new user: mclient -u monetdb -d dbA #password monetb
CREATE USER "userA" WITH PASSWORD '123' NAME 'Geriel Damdin';
I will quit the session, and then I will login as a newly created user.
quit #session mclient -u userA -d dbA
While the database is in the maintenance mode, only admin can login to it.
I will now release the database. That will pull database out of the maintenance mode. monetdb release dbA mclient -u userA -d dbA #user will now be able to login
Currently database is out of the maintenance mode. How can we place our database to maintenance mode again? For that we just have to lock the database before we stop it.
quit #session monetdb lock dbA monetdb stop dbA
If database is locked before it is closed, then only the administrator will be able to work with it.
Sample Databases
For further examples, I will create three more databases. monetdb create dbBdbCdbD
I will then get them out of the maintenance mode. monetdb release dbBdbCdbD
Most of the commands can work on severaldatabases at once. We just have to name all of the databases in a row.
Notice that after the release command we didn't get any confirmation message. That is because we have used "--quiet" option. This option will always suppress confirmation messages. It will not suppress error messages, for example if we run it again. =>
Database Status
I will show you how to get status of a databases. I will change the status of the dbA and dbB for this example.
monetdb start dbA monetdb lock dbB
monetdb status If we type the status command, we will get some information about DBfarm databases. Database dbA is "R" running for 1 minute. This database never crashed, so its health is 100%. On average, database dbA was running for 56 minutes.
In the state column we can see that our database can be in one of these states. We can also see for how long is database in that state.
B – booting
R – running.
S – stopped.
C – crashed.
LS – locked and stopped.
LR – locked and running.
In the health column we can see ratio "( successful starts/stops ) / ( all starts/stops )". 100% means that we never had unsuccessful start/stop.
In the health column we can see the times (56 min and 24 min). This is the average time the database has been up and running.
monetdb status -c dbA dbC
We can specify a database name (or several database names) to get the status for only them.
monetdb status -l dbA
If we use options "-c" and "-l" with the status command, we would get several more pieces of information for our databases.
monetdb status -s rl With the "-s" option we can limit the displayed databases to only those that are running and/or locked ("rl"). The options are b (booting), r (running), s (stopped), c (crashed), l (locked).
Monetdb Information
Option "-v" will return the version of a server. monetdb -v
We can also use the long syntax. monetdb --version
This is how we can get help. We can only use long syntax for help. monetdb --help
Snapshots
Snapshot to Stdout
This command would write snapshot of a database to stdout. monetdb snapshot write dbA
Our stdout is terminal, so we can not send binary data to stdout.
We can redirect stdout to a file. That way we can use "write" to get snapshot. monetdb snapshot write dbA > /home/fffovde/Desktop/dbA.tar
Snapshot to File
We can not create a snapshot in a file if property "snapshotdir" is not set. We do it with monetdbd. monetdbd set snapshotdir=/home/fffovde/Desktop /home/fffovde/Desktop/DBfarm
For "snapshot create" we have to provide full path, and that location must be inside of the "snapshotdir". I will try to backup database dbA. monetdb snapshot create -t /home/fffovde/dbAsnap dbA
I will try with this full path, that is inside of the "snapshotdir". This will work. monetdb snapshot create -t /home/fffovde/Desktop/ZZZ/dbAsnap dbA
With "-t" option we can create a snapshot file of one database, with the custom name. Folder "ZZZ" must be made in advance.
Without "-t" option, snapshot will be created with default settings. monetdb snapshot create dbB
We can not snapshot "tbB" because it is locked.
After we release the file, we will create the snapshot. monetdb release dbB monetdb snapshot create dbB
dbB_20250827T180139UTC.tar.lz4
Default name is made of the database name and the timestamp.
We can use wildcards for the name of a database. In this way we can create snapshots of several databases at once.
monetdb snapshot create db*
Restore a Snapshot
monetdb snapshot list
This is how we can get a list of all of the possible snapshots. For database dbB, we have two snapshots. Only default named snapshots are listed. Snapshot "dbAsnap" is missing because it has custom name.
We can restore second snapshot of the "dbB".
monetdb snapshot restore dbB@2
We can restore database under some other name.
monetdb snapshot restore dbB@2 dbBBB
Inside of the "DBfarm" we now have this database twice.
We can also reference snapshot by its full path. monetdb snapshot restore -f /home/fffovde/Desktop/dbD_20250827T180619UTC.tar.lz4 dbD
In this case, we have to provide under which name we are restoring.
Thanks to "-f" switch, we don't have to confirm overwrite of the current file.
Delete a Snapshot
This is how we can delete some snapshot. monetdb snapshot destroy dbD@1
We can delete all but the last N snapshots for some database. monetdb snapshot destroy -f-r 1 dbB
Monetdb will delete the oldest dbB snapshot. We stil have one snapshot more.
We can apply this command with the wild card, on all of the matched databases. I will leave zero snapshots, so we will delete them all. monetdb snapshot destroy -f -r 0 db*
We have no more snapshots that have default name. monetdb snapshot list
Database Settings
Read Database Settings
This is how we can read properties of some database. monetdb get all DBfarmDB We can select what property we want to see: monetdb get optpipe DBfarmDB
For each property, we will see its name, value, and whether that value is the default value.
name prop source value DBfarmDB name - DBfarmDB DBfarmDB type default database DBfarmDB shared default yes DBfarmDB nthreads default 10 DBfarmDB ncopyintothreads default 10 DBfarmDB optpipe default default_pipe DBfarmDB readonly default no DBfarmDB embedr default no DBfarmDB embedpy3 default no DBfarmDB embedc default no DBfarmDB listenaddr default <unset> DBfarmDB nclients default 64 DBfarmDB dbextra default <unset> DBfarmDB memmaxsize default <unset> DBfarmDB vmmaxsize default <unset> DBfarmDB raw_strings default <unset> DBfarmDB loadmodules default <unset>
Database Performance Settings
For most of the settings above, before we change them, we have to stop our database. Database "DBfarmDB" is currently stopped.
monetdb set nthreads=4 DBfarmDB monetdb set ncopyintothreads=6 DBfarmDB monetdb set optpipe=default_pipe DBfarmDB
Number of threads that database will use. By default, it is equal to number od CPU cores. Number of threads used for COPY INTO FROM. Default is equal to number of columns copied. We can find the list of possible optimizers in the system table "sys.optimizers".
After we change some property, the column "source" will be "local", instead of the "default".
I will now login to this DBfarmDB database. mclient -u monetdb -d DBfarmDB I will run this statement: select * from env() where name in ('gdk_mem_maxsize','gdk_vm_maxsize');
This is where we can see current values for properties "memmaxsize" and "vmmaxsize".
These values are in bytes, so maximal virtual memory size is about 4.000 terabytes, and maximal memory size is about 10.000 gigabytes. I will now exit and I will stop the database. quit monetdb stop DBfarmDB
#monetdb set memmaxsize=107374182400 DBfarmDB
This would limit RAM consumption of this database to 100GB. This is not hard limit; it is just suggestion to memory allocator.
#monetdb set vmmaxsize=107374182400 DBfarmDB
This is hard limit. Limiting virtual memory will limit how much RAM+Swap space this database can use.
Restriction Settings
monetdb set readonly=yes DBfarmDB monetdb set nclients=10 DBfarmDB monetdb set idletimeout=3600 DBfarmDB
We can set our database to be read only. We can limit number of the clients that can connect to our database. Connection will be closed if idle for this many seconds.
This property "idletimeout" is not listed when we use "get all" command, but inside of the file "DBfarmDB > .merovingian_properties" , that property is listed. It is also in documentation.
Extension Settings
#monetdb set embedpy3=true DBfarmDB #monetdb set embedr=true DBfarmDB #monetdb set embedc=true DBfarmDB
If we want to make UDF functions in python or "C" or "R" language, we have to set these settings to TRUE for specific databae. We used this for python in the blog post "link". For values we can use true/false or yes/no.
In this blog post "link", we have installed library "monetdb-odbc-loader". For such libraries to work in a database, we have to load them. This is done with "loadmodules" property.
#monetdb set loadmodules=odbc_loader DBfarmDB
Discovery Setting
I will now open green server. We have created that server in this blog post "link". monetdbd start /home/sima/monetdb/DBfarmG#I will start daemon In the previous blog post "link", about monetdbd, we turned on discovery on the voc server for "DBfarm". I will again use discover command on the green server. monetdb discover
Discover command will show us all of the databases on the "voc" server, that are inside of the "DBfarm", and one local database on the green server.
We have turned on discovery for the whole "DBfarm" and for that we have used monetdbd. Now, I want to hide only one of those databases from discovery. I will hide database "DBfarmDB". monetdb set shared=no DBfarmDB
Discovery for "DBfarmDB" is turned off. Discover command will show us other databases from the "DBfarm", but not "DBfarmDB".
Other Settings
I will skip settings "type, dbextra, listenaddr". I don't understand them. I will talk about "raw_strings" setting.
I will login into DBfarmDB with the default password: mclient -u monetdb -d DBfarmDB
By default, the raw strings are turned OFF. I have talked about raw strings in this blog post "link". I will run some queries to see the default behaviour.
SELECT 'Bob\'s file';
SELECT 'a\nb';
SELECT 'C:\\temp\\new\\file.txt';
I will exit the session, and I will stop the database. quit monetdb stop DBfarmDB
I will change "raw_strings" property. monetdb set raw_strings=true DBfarmDB#false is default Then, we will again login, and run those queries from above.
SELECT 'Bob''s file'; Backslash will not work.
SELECT 'a\nb';
SELECT 'C:\\temp\\new\\file.txt';
Off course, we can get the same results as before if we place prefix "E" in front of the strings. That will make them NOT raw.
Set Property to Default
Currently database can connect to the most 10 clients. We can change that back to the default value (64). monetdb get nclients DBfarmDB
monetdb inherit nclients DBfarmDB
For some properties we can unset them: monetdb set loadmodules= DBfarmDB
Something More About Database Commands
We can provide password for monetdb user during database creation. monetdb create -p password123 dbE
This database will not be created in the maintenance mode. It has non-standard password, so there is no need for that. We can start it directly.
monetdb start dbE
Now, that our database is running, we can stop it with the "stop" command. This command will send SIGTERM signal, so it will kindly ask database to close, although the database can refuse that. Instead of the "stop" command, it is possible to us "kill" command. This command will send SIGKILL signal and the database will be violently killed.
I can kill "dbE" database by its name "monetdb kill dbE", or I can use "-a" switch to kill all of the open databases. monetdb kill -a
"-a" switch can be used with the commands "lock, release, start, stop and kill".
Remote Control
Remote control is explained in the blog post about monetdbd, "link". Take a look there to find out about switches "-h,-p,-P".
dbB_20250827T180139UTC.tar.lz4
Backslash will not work.
