Server Administration

Server Administration zhang Mon, 02/17/2020 - 17:25

Once you have a running MonetDB setup, you might want to customize it according to your specific needs.

In this section we are going to give details on how to administrate a MonetDB instance.

Installation

Installation zhang Mon, 02/17/2020 - 17:42

Although there are pre-built images for MonetDB, it is also possible to download and build it from source code.

In this section we are going to give a guide for binary installers, source code build, Windows and Mac OS setup.

Install from Source

Install from Source giulia Tue, 02/25/2020 - 16:36

Installing MonetDB from sources is often considered a tedious job.  Compilation of MonetDB on the Linux platforms is based on the bootstrap-configure-make pattern. It can be compiled in any location on your file system and it does not require special (root) permissions.

While the core task of compiling MonetDB itself is extremely simple (on UNIX based platforms), inexperienced users often run into problems caused by many effects of the build environment. The catch is the differences between the target platforms, because not all libraries needed may have been installed by your system administrator.  Typical missing components on a binary distribution such as Fedora or Ubuntu are the xxx-dev packages necessary for MonetDB to compile against the package itself.

MonetDB is a rather large C-based program, and it sometimes closely interacts with the host system it runs on to achieve the best end results for the user.  This makes the code not always as generic as we ultimately would like it to be.  Because we understand that these parts of the code are likely suspects for malfunctioning, we try to test our sources on a nightly basis on as many different platforms that we can access.  This mainly focuses on different architectures, kernels and libcs, but we also test a couple of Linux distributions, for example (See Nightly testing).

To allow doing the right thing on your system, we use autoconf, automake and libtool to build the software.  We aim at being very standard in this respect.   In a nutshell, building the software works with the usual ritual:

% configure

% make

% make install

This process often fails for users in the configure step, but the make step may also give problems.  We will discuss some of the frequent issues people find here in detail.

For Mac OS X instruction please check this page.

Configure

Calling the configure script will check the system for required files and packages, and determine the capabilities of the system, such that the right approach can be taken in certain parts of the code.

Before calling configure, it is wise to examine all available options this script has by using

% configure --help

In particular the --prefix argument will be necessary to be set in most cases.  If you don't know what value to use for the prefix argument, a location in your homedir will typically do, e.g. --prefix=$HOME/MonetDB.

If configure finishes, it gives a conclusion at the end of its output.  Be sure to read this carefully, if a component is disabled this means it will not be built and installed.  At the time of this writing, you will need at least the monetdb5 and sql components.  Unless if you know what you're doing, you should not continue as long as configure reports these components to be disabled.

When a component is disabled, it is usually due to a missing dependency.  A dependency is often a library that the component in question relies on for its functionality.  The following two dependencies are known trouble makers on most systems that aren't fairly recent: openssl, libxml2 and pcre or libpcre.  The former is usually available, but a not recent enough version.  The latter two often lack the development headers on systems.  You have to install missing dependencies either through the native package management system of your host system, or by compiling and installing them manually.  Since this in general differs from package to package, we cannot provide generalized help on this topic.  Make sure you install the xxx-dev versions on binary distributions of the necessary packages as well!

Make

After configure has successfully found all it is looking for, make usually churns away for some time, producing quite some output.  It can happen that make aborts:

make[3]: *** [something] Error 1

The real error is usually right above this message.  If you're looking for help, or want to report a bug, always copy at least about 20 lines above this message, as it gives the necessary context to investigate on the problem.

If you passed --enable-strict to configure, then try to configure with --disable-strict and try make again.  This can allow certain type of problems to be ignored, since they are typically non-critical.

Make can fail on a compilation or linking step.  The former usually manifests itself by a message from the compiler like

somefile.c:12: 'rl' may be used uninitialized in this function

the latter typically manifest itself by "undefined references".  For compilation issues Googling is usually the best action to get a clue on what's going on.  The undefined references during linking are often missing libraries, due to missing -L/path/to/dir flags in LDFLAGS.  If you have libraries in use that are in a non-standard location (for example because you installed them yourself), make sure you have the proper includes (-I/path/to/include/dir) in your CPPFLAGS environment variable, and the library search paths (-L/path/to/lib/dir and -rpath=/path/to/lib/dir on ELF-based systems e.g. Linux) in your LDFLAGS such that they can be properly found by configure, libtool, the compiler and the linker.

OS X Install

OS X Install giulia Tue, 02/25/2020 - 16:39

Install Xcode

To be able to build (almost) any software package on OS X, one needs to have Apple Xcode installed, or at least the Xcode command-line tools. Xcode comes with a C/C++ compiler toolset, which (as of the more recent versions) is based on Clang/LLVM and not GCC.

If you want the most recent version of Xcode you can only obtain it from the Apple Developer Website (if you are a registered Apple developer) or directly from the Apple Mac App Store here.

Run the following command in Terminal in order to install the Xcode command-line tools:

xcode-select --install

Beware, OS X usually ships with a BSD toolset, unlike most Linux distribution that make use of the GNU toolset. This affects not only the compiler, but also tools (such as make, pkgconfig, aws, sed) used in the build scripts of MonetDB.

Installing required packages

There are two community supported package managers that one can use to install the dependencies for building MonetDB on OS X: Homebrew or MacPorts. At this point you would need to pick which package manager to use and install only one of them.

Using Homebrew

To install Homebrew please follow the up-to-date instructions for your OS X version. You can find these on their website found here.

Minimal modules build-set

After Homebrew is installed, you should install the required packages for building MonetDB absolute minimal set of modules. Run the command below to install the required packages (and their dependencies in the brackets): pkg-config; pcre

brew install pkg-config pcre openssl

Default modules build-set

Keep reading/installing if you want to build the other MonetDB modules included in the default build-set.

* JDBC & control
Install JDK 7, which you can get from the Oracle website. Note: You need the JDK, not the JRE.
After JDK in installed, set the JAVA_HOME variable and add the JDK bin directory to your shell's PATH. To do this, add the lines below to your .profile, .bashrc or .bash_profile (or the corresponding config file for your shell).

export JAVA_HOME=$(/usr/libexec/java_home) export PATH=${JAVA_HOME}/bin:$PATH

Install Apache Ant

brew install ant

* ODBC
Install unixODBC

brew install unixodbc

* GEOM
Install geos

brew install geos

Other

* sphinxclient
Install libsphinxclient

brew install libsphinxclient

* GSL
Install gsl

brew install gsl

* FITS
Install cfitsio

brew install cfitsio

Before building

You most likely need to install automake, autoconf, libtool, gettext and readline  from Homebrew as well. These will also be required to run the bootstrap script (see below).

brew install autoconf automake libtool gettext readline

Putting it all together

brew install autoconf automake libtool gettext readline pkg-config pcre openssl unixodbc geos gsl cfitsio

Using MacPorts

As an alternative to Homebrew, you can also use MacPorts to obtain the required packages. To install MacPorts please follow the up-to-date instructions for your OS X version. You can find these on their website here. Do not forget to restart your command prompt shell (since MacPorts will add new extensions to your PATH) and run:

sudo port -v selfupdate

Minimal modules build-set

After MacPorts is installed, you should install the required packages for building MonetDB absolute minimal set of modules. Run the command below to install the required packages (and their dependencies in the brackets): pkgconfig (libiconv); openssl (zlib); pcre (bzip2, ncurses, libedit); libxml2 (expat, gettext, xz).

sudo port install pkgconfig openssl pcre libxml2

Default modules build-set

Keep reading/installing if you want to build the other MonetDB modules included in the default build-set.

* JDBC & control
Install JDK 7, which you can get from the Oracle website. Note: You need the JDK, not the JRE.
After JDK in installed, set the JAVA_HOME variable and add the JDK bin directory to your shell's PATH. To do this, add the lines below to your .profile, .bashrc or .bash_profile (or the corresponding config file for your shell).

export JAVA_HOME=$(/usr/libexec/java_home) export PATH=${JAVA_HOME}/bin:$PATH

Install Apache Ant, which can be downloaded its website [https://ant.apache.org/bindownload.cgi]. Unpack the package in a directory in your home space, e.g. in /Users/<username>/tools/apache-ant. After that set the ANT_HOME variables and update the PATH like this:

export ANT_HOME=/Users/<username>/tools/apache-ant export PATH=${ANT_HOME}/bin:$PATH

* ODBC
Install unixODBC (libtool, readline)

sudo port install unixodbc

Add the line below when configuring the build to point the tool to the library (see Configuring below).

--with-unixodbc =/opt/local --with-readline=/opt/local

* GEOM
Install geos

sudo port install geos

Other

* curl
Install curl (curl-ca-bundle, libind)

sudo port install curl

* libmicrohttpd
Install libmicrohttpd (gmp, libtasn1, nettle, libffi, glib2, popt, desktop-file-utils, libxslt, p11-kit, gnutls, libgpg-error, libgcrypt)

sudo port install libmicrohttpd

* liburiparser
Install uriparser

sudo port install uriparser

* sphinxclient

Install libsphinxclient

sudo port install libsphinxclient

Add the line below when configuring the build to point the tool to the library (see Configuring below).

--with-sphinxclient=/opt/local

* GSL
Install gsl

sudo port install gsl

* FITS
Install cfitsio

sudo port install cfitsio

Before building

You will probably need to install automake (gdbm, perl5.16, perl5) and autoconf from MacPorts as well. These will also be required to run the bootstrap script (see below).

sudo port install automake autoconf

Putting it all together

sudo port install automake autoconf pkgconfig openssl pcre libxml2 unixodbc bison geos gsl cfitsio curl libmicrohttpd uriparser libsphinxclient

Obtaining sources and bootstrapping

Sources of the latest released and testing versions on MonetDB can be obtained from the repository: released or testing. Download the selected file and unpack it in a directory.

Cloning MonetDB Mercurial repository

Optionally, bleeding egde sources can be directly obtained from the MonetDB Mercurial repository. To do that make sure you have Mercurial installed on your system first - latest version available here. The clone the MonetDB repository. Note this can take some time and a lot of data will be transferred.

hg clone http://dev.monetdb.org/hg/MonetDB/

Bootstrapping

Sources check-out from Mercurial need to be bootstrapped first, before configure can be run. To do that, go in the directory you in check you cloned the repository and run:

./bootstrap

* If you are using Homebrew, you will also need to set M4DIRS to the directory where the Homebrew gettext M4 macros are in. The same holds for the OpenSSL libs and includes:

export M4DIRS=/usr/local/opt/gettext/share/aclocal
export LDFLAGS=-L/usr/local/opt/openssl/lib
export CPPFLAGS=-I/usr/local/opt/openssl/include

Configuring build

With Homebrew

Before starting the configure tool, go in the directory where the MonetDB sources are, and create a subdirectory BUILD. Go in the BUILD directory and run the following command, where prefix is the location where you would like MonetDB installed.

../configure --prefix=<full-path>

With MacPorts

Before starting the configure tool, go in the directory where the MonetDB sources are, and create a subdirectory BUILD. Go in the BUILD directory and run the following command, where prefix is the location where you would like MonetDB installed.

../configure --prefix=<full-path> --with-libiconv-prefix=/opt/local

* If you want to build the ODBC driver or enable sphinxclient support, do not forget to add the appropriate arguments, e.g.:

../configure --prefix=<full-path> --with-libiconv-prefix=/opt/local --with-sphinxclient=/opt/local --with-unixodbc =/opt/local --with-readline=/opt/local

Note: If you don't have root/admin permission for your system you will not be able to deploy the RubyGem. If you don't need the RubyGem simply disable with with the option --without-rubygem

Building

To build the configured modules, simply run from the <tt>BUILD</tt> directory, using the -j<number> argument for a parallel build. E.g.

make -j4

Once the build process finishes, install MonetDB in the prefix location set during the configure process.

make install

Note: If you have built the RubyGem, on OS X you will need to use sudo make install to deploy it.

Windows Install

Windows Install giulia Tue, 02/25/2020 - 16:37

On Windows the first step is to initialize a MonetDB server by simply opening: 'Start -> Programs -> MonetDB -> Start server'. 

Subsequently you can start the textual interface (mclient) by opening: 'Start -> Programs -> MonetDB -> SQL client'.  The commands entered there are identical to those found on other platforms. To stop the server, you can simply close the MonetDB SQL Server window. 

Note the server by default only accepts connections originating from the local host. If you need other machines to access your database, change the configuration file by setting mapi_open=yes.

Exploring the wealth of functionality offered by MonetDB/SQL is best started using a toy database. For this we use the VOC database which provides a peephole view into the administrative system of an early multi-national company, the VOC (or Dutch East India Company).

Download the VOC dataset voc_dump.zip (542K) gz (519K) bz2 (371K) which is a compressed file with SQL statements (SQL database dump). After the file has been extracted, load its contents into MonetDB using the mclient.exe file. From within mclient run:

sql> \< voc_dump.sql

Since the SQL interface is the same on all platforms, from here on you can follow the later part of the regular SQL tutorial.

Changing database location

If you need (or want) to change the location of the database then editing of the monetdb script is required. Change the mclient.bat and M5server.bat files in 'C:\Program Files\MonetDB\MonetDB5'. In the M5server.bat file you would need to change the setting of the MONETDBFARM variable, and in mclient.bat you would need to add a -d option to the call of mclient.exe.  Note: you upgrade such changed files will be overwritten.

About the VOC dataset

The data for the tutorial is published in the book J.R. Bruijn, F.S. Gaastra and I. Schaar Dutch-Asiatic Shipping in the 17th and 18th Centuries, which gives an account of the trips made to the East and ships returned safely (or wrecked on the way) by the VOC (or Dutch East India Company). A total of 8000 records are provided. They include information about ship name and type, captain, the arrival/departure of harbors along the route, personnel accounts, and anecdotal information. You can find more about the VOC on Wikipedia.

Server Setup and Configuration

Server Setup and Configuration mk Tue, 02/18/2020 - 11:03

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
user=<yourname>
password=<yourpassword>
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
hostname         vomac.ins.cwi.nl
dbfarm           /Users/fabian/my-dbfarm
status           no monetdbd is serving this dbfarm
mserver          unknown (monetdbd not running)
logfile          merovingian.log
pidfile          merovingian.pid
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
hostname         vomac.ins.cwi.nl
dbfarm           /Users/fabian/my-dbfarm
status           no monetdbd is serving this dbfarm
mserver          unknown (monetdbd not running)
logfile          merovingian.log
pidfile          merovingian.pid
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
user(fabian):monetdb
password:<monetdb>
Welcome to mclient, the MonetDB/SQL interactive terminal (Dec2011-SP1)
Database: MonetDB v11.7.7 (Dec2011-SP1), 'mapi:monetdb://vomac.ins.cwi.nl:54321/my-first-db'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>

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.

Client Authentication

Client Authentication giulia Tue, 02/18/2020 - 13:50

Under construction

MonetDB supports client authentication based on password.

Database Roles, Privileges, Sessions

Database Roles, Privileges, Sessions giulia Tue, 02/18/2020 - 14:16

The user access grants are organized by authorization groups.

sys.auths
name type references description
"id" INTEGER   The unique authorization identifier.
"name" VARCHAR   The SQL authorization name of the user or role.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization grantor.

 

sys.users   and   sys.db_user_info
name type references description
"name" VARCHAR sys.auths.name The unique user login name.
"fullname" VARCHAR   Full name of user.
"default_schema" INTEGER sys.schemas.id The default schema for this user.

 

sys.roles
name type references description
"id" INTEGER   The unique role identifier.
"name" VARCHAR   The role name.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization grantor.

 

sys.user_role
name type references description
"login_id" INTEGER sys.auths.id The corresponding user identifier.
"role_id" INTEGER sys.roles.id The corresponding role identifier.

 

sys.privileges
name type references description
"obj_id" INTEGER sys.ids.id The identifier of the object for which privilege(s) are given to a certain user or role.
"auth_id" INTEGER sys.auths.id The id of the user or role.
"privileges" INTEGER sys.privilege_codes.privilege_code_id Privileges classification code, see table sys.privilege_codes.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding grantor authorization identifier.
"grantable" INTEGER   Permission to pass privilege rights to other users or roles.
sys.privilege_codes
name type references description
"privilege_code_id" INTEGER   The unique internal code of a privilege or combination of privileges.
"privilege_code_name" VARCHAR   A description of the privilege(s) associated with this code.

 

sys.sessions
name type references description
"user" VARCHAR sys.users.name The user who created the session.
"login" TIMESTAMP   The login start timestamp of the session.
"sessiontimeout" BIGINT   The session timeout time in seconds, 0 means timeout is disabled.
"lastcommand" TIMESTAMP   The start timestamp of the last issued command.
"querytimeout" BIGINT   The query execution timeout time in seconds, 0 means timeout is disabled.
"active" BOOLEAN   Whether the session is active or not.

Localization

Localization giulia Tue, 02/18/2020 - 14:30

Under construction

Database Maintenance Tasks

Database Maintenance Tasks giulia Mon, 02/24/2020 - 10:23

Under construction

Availability, Load Balancing and Replication

Availability, Load Balancing and Replication giulia Mon, 02/24/2020 - 10:40

Under construction

Monitoring

Monitoring giulia Mon, 02/24/2020 - 10:42

 

 

Systems Monitor

Systems Monitor giulia Mon, 05/04/2020 - 17:17

The MonetDB kernel maintains an active queue of all running queries. This queue is available for all users to inspect the status of his own queries. The system administrator can inspect it to overlook the complete workload on the system. The queue is made visible as a table producing function, called sys.queue().

sql>select * from sys.queue();
+------+---------+----------------------------+----------+----------+---------+------------+----------------------------+
| qtag | user    | started                    | estimate | progress | status  | tag        | query                      |
+======+=========+============================+==========+==========+=========+============+============================+
|  902 | monetdb | 2013-03-30 10:23:39.000000 | null     |     null | running | 15168688@0 | select * from sys.queue(); |
+------+---------+----------------------------+----------+----------+---------+------------+----------------------------+
1 tuple (0.446ms)

The schema structure is largely self-explanatory. If the query (template) is ran multiple times, then the system can derive a progress indicator and calculate an estimated time of completion. The 'tag' column references the query log tables, provided this facility has been turned on. The initial column 'qtag' provides a key to each active query .

One can use the SQL functions PAUSE, RESUME and STOP to control the execution of (long) running queries.

NOTE that PAUSE and STOP takes effect at the first safe point within the query plan, which often is after the current MAL instruction has been finished. For complex queries over large database this may take seconds up to minutes.

sql> select * from sys.queue;
+------+---------+----------------------------+----------+----------+---------+-----------+--------------------------+
| qtag | user    | started                    | estimate | progress | status  | tag       | query                    |
+======+=========+============================+==========+==========+=========+===========+==========================+
|   52 | monetdb | 2013-12-23 12:34:50.000000 | null     |     null | running | 1349763@0 | select * from sys.queue; |
+------+---------+----------------------------+----------+----------+---------+-----------+--------------------------+
1 tuple (2.242ms)

--- start a long running query in another window
sql>select * from sys.queue;
+------+---------+----------------------------+----------+------+---------+-----------+-------------------------------------------------------------+
| qtag | user    | started                    | estimate | prog | status  | tag       | query                                                       |
:      :         :                            :          : ress :         :           :                                                             :
+======+=========+============================+==========+======+=========+===========+=============================================================+
|   56 | monetdb | 2013-12-23 12:35:03.000000 | null     | null | running | 1353354@0 | select                                                      |
:      :         :                            :          :      :         :           :  sum(l_extendedprice * l_discount) as revenue               :
:      :         :                            :          :      :         :           : from                                                        :
:      :         :                            :          :      :         :           :  lineitem                                                   :
:      :         :                            :          :      :         :           : where                                                       :
:      :         :                            :          :      :         :           :  l_shipdate >= date \'1994-01-01\'                          :
:      :         :                            :          :      :         :           :  and l_shipdate < date \'1994-01-01\' + interval \'1\' year :
:      :         :                            :          :      :         :           :  and l_discount between 0.06 - 0.01 and 0.06 + 0.01         :
:      :         :                            :          :      :         :           :  and l_quantity < 24;                                       :
|   57 | monetdb | 2013-12-23 12:35:10.000000 | null     | null | running | 1349763@0 | select * from sys.queue;                                    |
+------+---------+----------------------------+----------+------+---------+-----------+-------------------------------------------------------------+
2 tuples (1.404ms)
sql>call sys.pause(56);
sql>select * from sys.queue; 
------+---------+----------------------------+-----------+------+---------+-----------+-------------------------------------------------------------+
| qtag | user    | started                    | estimate | prog | status  | tag       | query                                                       |
:      :         :                            :          : ress :         :           :                                                             :
+======+=========+============================+==========+======+=========+===========+=============================================================+
|   56 | monetdb | 2013-12-23 12:35:03.000000 | null     | null | paused  | 1353354@0 | select                                                      |
:      :         :                            :          :      :         :           :  sum(l_extendedprice * l_discount) as revenue               :
:      :         :                            :          :      :         :           : from                                                        :
:      :         :                            :          :      :         :           :  lineitem                                                   :
:      :         :                            :          :      :         :           : where                                                       :
:      :         :                            :          :      :         :           :  l_shipdate >= date \'1994-01-01\'                          :
:      :         :                            :          :      :         :           :  and l_shipdate < date \'1994-01-01\' + interval \'1\' year :
:      :         :                            :          :      :         :           :  and l_discount between 0.06 - 0.01 and 0.06 + 0.01         :
:      :         :                            :          :      :         :           :  and l_quantity < 24;                                       :
|   65 | monetdb | 2013-12-23 12:36:03.000000 | null     | null | running | 1349763@0 | select * from sys.queue;                                    |
+------+---------+----------------------------+----------+------+---------+-----------+-------------------------------------------------------------+
2 tuples (3.082ms)
sql>call sys.resume(56);
sql>select * from sys.queue; 
------+---------+----------------------------+-----------+------+---------+-----------+-------------------------------------------------------------+
| qtag | user    | started                    | estimate | prog | status  | tag       | query                                                       |
:      :         :                            :          : ress :         :           :                                                             :
+======+=========+============================+==========+======+=========+===========+=============================================================+
|   56 | monetdb | 2013-12-23 12:35:03.000000 | null     | null | running | 1353354@0 | select                                                      |
:      :         :                            :          :      :         :           :  sum(l_extendedprice * l_discount) as revenue               :
:      :         :                            :          :      :         :           : from                                                        :
:      :         :                            :          :      :         :           :  lineitem                                                   :
:      :         :                            :          :      :         :           : where                                                       :
:      :         :                            :          :      :         :           :  l_shipdate >= date \'1994-01-01\'                          :
:      :         :                            :          :      :         :           :  and l_shipdate < date \'1994-01-01\' + interval \'1\' year :
:      :         :                            :          :      :         :           :  and l_discount between 0.06 - 0.01 and 0.06 + 0.01         :
:      :         :                            :          :      :         :           :  and l_quantity < 24;                                       :
|   65 | monetdb | 2013-12-23 12:36:03.000000 | null     | null | running | 1349763@0 | select * from sys.queue;                                    |
+------+---------+----------------------------+----------+------+---------+-----------+-------------------------------------------------------------+
2 tuples (12.320ms)
sql>call sys.stop(56);
sql>select * from sys.queue;
+------+---------+----------------------------+----------+----------+---------+-----------+--------------------------+
| qtag | user    | started                    | estimate | progress | status  | tag       | query                    |
+======+=========+============================+==========+==========+=========+===========+==========================+
|   67 | monetdb | 2013-12-23 12:36:22.000000 | null     |     null | running | 1349763@0 | select * from sys.queue; |
+------+---------+----------------------------+----------+----------+---------+-----------+--------------------------+
1 tuple (2.799ms)

Table Statistics

Table Statistics giulia Mon, 05/04/2020 - 17:15

Statistics gathered over tables in the database can be found in the system table sys.statistics. This table is initially empty and explicitly filled or updated using the ANALYZE command:

ANALYZE schemaname [ '.' tablename [ '('columnname , ...')' ] ]
      [ SAMPLE size ]
      [ MINMAX ]

You can gather statistics for a) all tables in a schema or b) all columns of a specific table or c) a list of specific columns of one table. You can only gather statistics of tables with real physical column data, so not for views.

Since statistics gathering involves accessing and profiling all table columns data, it can take considerable time, especially if the tables are large or you analyze all tables in a schema. You may consider to add an optional MINMAX which directs exclusion of the expensive unique count operation. Likewise, a SAMPLE size can be used to provide a quick, but imprecise impression.

You can remove statistics data via SQL command: DELETE FROM sys.statistics. For example removing statistics for table: my_schema.my_table:

DELETE FROM sys.statistics
 WHERE column_id IN (SELECT c.id FROM sys.columns c
 WHERE c.table_id IN (SELECT t.id FROM sys.tables t
 WHERE t.schema_id IN (SELECT s.id FROM sys.schemas s
 WHERE s.name = 'my_schema') AND t.name = 'my_table'));

Backup

Backup giulia Mon, 05/04/2020 - 14:15

MonetDB offers different options for backup and dump of the database

File System Level Backup

File System Level Backup giulia Mon, 05/04/2020 - 14:18

Aside from the ASCII dumps, one can also take the database server out of production using the following commands. Be careful in using shutdown(), because there may be users running transactions. This can be seen using the sys.sessions table or the system monitor.

shell> mclient -d demo
select * from sys.sessions;
call sys.shutdown(10);
<cntrl-d>
shell> monetdb lock demo
shell> monetdb stop demo

The "lock" step is necessary to prevent monetdbd from automatically restarting the database "demo" upon receiving another client connection request.

After the database has been securely stopped, we can create a copy of the database directory in the dbfarm and put it aside in a safe place. Alternatively, incremental file-system dumps can be used to reduce the time and storage space for a recovery point. Finally, the database is released for production again using:

shell>monetdb release <dbname>

SQL Dump and Restore

SQL Dump and Restore giulia Mon, 05/04/2020 - 14:17

An SQL dump of a database is a common method to safely store away a snapshot of the database for archival purposes or to migrate data between database instances, e.g. between two major system releases. The content of an SQL dump is a large collection of SQL statements as text. Running the script will recreate the database in the same state as it was when the dump was created. Since MonetDB does not provide global locking schemes, a time-consuming dump operation may become invalidated by a concurrent update query.

The primary tool to consider for making a database SQL dump is msqldump, which includes a wide variety of options:.

Usage: msqldump [ options ] [ dbname ]

Options are:
 -h hostname | --host=hostname    host to connect to
 -p portnr   | --port=portnr      port to connect to
 -u user     | --user=user        user id
 -d database | --database=database  database to connect to
 -f          | --functions        dump functions
 -t table    | --table=table      dump a database table
 -D          | --describe         describe database
 -N          | --inserts          use INSERT INTO statements
 -q          | --quiet            don't print welcome message
 -X          | --Xdebug           trace mapi network interaction
 -?          | --help             show this usage message
--functions and --table are mutually exclusive

msqldump program acts as a normal MonetDB client application, e.g. it runs concurrently with all other user sessions, and dumps the generated SQL statements onto standard output. Safe this to a file (by using redirection of stdout) for a later restore session by calling mclient with the saved file as input argument. Details on the various arguments can be found in the manual page for mclient.

For example, consider you have already installed the SQL tutorial database voc on a Linux platform and wishes to transport it to another machine. Then the following command line option generates the dump file.

shell> msqldump --database=voc >/tmp/voc.sql

You can inspect the file /tmp/voc.sql to confirm that indeed a readable SQL database dump is available. If storage space is a problem, you can readily pipe the output of this command to a compression tool, e.g. gzip, or sent it directly to another machine in your environment using conventional Linux tools, e.g. scpy. As a baseline for recovery, move the file over to the new machine. Then the monetdb tool can be used to create the database on the recipient machine. Once done, it suffices to feed the dump file to mclient to populate the database.

Creation of the dump respects your MonetDB server login credentials, which means you only can dump the tables you have access to.

Migration of a database from other database systems follow the same route, but be aware that SQL dialects often differ. A manual or scripted patch of a foreign SQL dump is often needed. An overview of the various SQL dialects can be found in the SQL dialects book and general background on database compatibility on Wikipedia.

shell> monetdb release demo

Windows platforms

On Windows platforms we advise to always dump the old database into a mydbdump.sql file before upgrading to a new MonetDB software release. Subsequently stop the MonetDB server and rename/remove the dbfarm/demo. After upgrade of MonetDB software and restart of MonetDB server, the dump can be restored.

To dump the SQL database, start the MonetDB SQL Client program (mclient.bat) and type the command:

\>...\mydbdump.sql
\D
\>

The path after \> should be an absolute path name (i.e. start with a drive letter) and be in a save location. By default the database is located in %APPDATA%\MonetDB5. After having made a database dump it can be removed. This folder is located inside the dbfarm\demo folder.

Restoring an SQL database can be done using the MonetDB SQL Client program (mclient.bat) with the following command

\<...\mydbdump.sql