Benchmark Data

For this benchmark we will use sales data from the fictitious company "Contoso". We can download our database from the github. If we go to this address, we will find there compressed CSV files with the data. In total there are 8 files of 500 MB, and one smaller file with 250 MB ( 4250 MB in total ).   https://github.com/sql-bi/Contoso-Data-Generator-V2-Data/releases/tag/ready-to-use-data

When we unzip these files, inside we will find 8 CSV files. This is sales cube with tables that can show us sales and orders per product, customer, date and store. Three big tables are "Orders" ( 88M ), "Sales" ( 211M ) and "OrderRows" ( 211M ). Dimension table "Customer" has 2M rows, and all the other dimension tables are small. Not all of the columns are shown on the image. —————————————————————————————— In CSV file "date.csv" I will change the names of columns month=>month2 and year=>year2, because MonetDB will not accept original names. "Month" and "Year" are reserved words.

Machine Hardware

For this benchmark we will use CPU with 8 cores and 64 GB of RAM. Our operational system is Zorin 18.

Creating Tables and Loading CSV Files

OrderRows Table

Import of this 211M rows table will last only one minute ( 56.241 sec ). Amazing.

Other Tables

I will leave a file for download at the end of this article. That file will have SQL for creation and import of all of the Contoso tables.

Bellow we can see time for import for two other larger files. Smaller dimension tables are imported almost instantly.

Sales (211M) table will need almost 5 minutes to be imported.
Orders (88M) CSV table will be imported in 62 seconds.

Query Benchmarking

Cold Start

I will now restart my computer. I want to make sure to run a cold query. This simple query bellow will run for 10 seconds. MonetDB database becomes faster as more queries are executed. This ability of MonetDB is called "cracking". MonetDB will automatically sort, group and index columns during the SELECT queries. That will make subsequent queries faster.   SELECT * FROM sales LIMIT 1;

If we run this query again, it will be executed in just 1.3 miliseconds. This is not the result of query caching. If we make a query that takes 2 or 3 rows, we will again see these exceptional speeds. SELECT * FROM sales LIMIT 2; SELECT * FROM sales LIMIT 3;

Aggregated Queries in MonetDB

If we aggregate two columns in the "sales" table, that query will touch 211M rows. It will be fast ( 2.268 sec. ), but we can repeat it to get only 94.915 ms. SELECT SUM( quantity ), AVG( unitprice ) FROM sales;

I will run the same query, but this time with a filter. SELECT SUM( quantity ), AVG( unitprice ) FROM sales WHERE orderdate <= '2020-05-25';

We will get the result even faster. This proves that the result is not cached. MonetDB run the query again, but this time "cracking" made our query faster.

It's hard to make a benchmark when execution times are constantly changing. So, from now on I will focus on the fastest times.

How Database Reports Execution Time

Databases only report the time spent to produce the results in the memory. It will not include the time needed to print the result in the shell or any other client. That is why MonetDB is reporting 13 ms, but I can see the result only after 7 seconds. MonetDB has command to suppress printing of the result in the shell. I will use that command ( command explained here ) next, to test reading the whole tables.

This is how long it takes MonetDB to read a large number of rows. Columnar databases are better suited for aggregate queries, but we can see that MonetDB is capable of performing OLTP types of queries quite well.

I will disable my command, so we can again see the results of our queries.

Joins

SELECT brand, SUM( quantity ), AVG( netprice ) FROM sales INNER JOIN product    ON sales.productkey = product.productkey GROUP BY brand;

The query with a join will last more than 1 second. We can speed it up if we create a foreign key constraint.

Query from before is a traditional analytical query. If we look at system monitor, we will see that during the execution of this query the load will be equally distributed between CPU cores. MonetDB is capable to significantly parallelize query execution. That means that our individual queries can be speed up with a CPU that has even more cores.

DISTINCT, LIKE, ROLLUP

From the table "customer" we can list distinct continents and genders in 3.841 ms.                                                               SELECT DISTINCT continent, gender FROM customer;

We can get distinct combinations of storekey and currency code from "sales" table in half of the second.   SELECT DISTINCT storekey, currencycode FROM sales;

If we want to count unique combinations of the orderkey and currencycode, then our query will be slow, it will last almost 11 seconds. But this query has to count 88 million rows. SELECT COUNT( * ) FROM     ( SELECT DISTINCT orderkey, currencycode       FROM sales );

Query like this will also last 11 seconds.                                                                                   SELECT COUNT( * ) FROM  ( SELECT orderkey, currencycode    FROM sales    GROUP BY orderkey, currencycode );

LIKE operator allows usage of the wild cards. Sign "_" will replace one letter.   SELECT currencycode, SUM( quantity ), MAX( unitprice ) FROM sales WHERE currencycode LIKE '_U_' GROUP BY currencycode;

If we use the sign "%" that replaces several characters, then the speed will drop, almost double.   SELECT currencycode, SUM( quantity ), MAX( unitprice ) FROM sales WHERE currencycode LIKE '%U_' GROUP BY currencycode;

Before we test ROLLUP, I will create foreign key constraint between "customer" and "sales" tables.   ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( customerkey ); ALTER TABLE sales ADD CONSTRAINT FKfromCustomer FOREIGN KEY ( customerkey ) REFERENCES customer ( customerkey );

This time we have unusually slow query. It will take full 10 seconds. SELECT continent, title, SUM( quantity ) FROM customer INNER JOIN sales    ON customer.customerkey = sales.customerkey GROUP BY ROLLUP( continent, title ); ——————————————————————————————————————————– Query with union would be much better choice for this. Only 1.837 seconds. SELECT continent, title, SUM( quantity ) FROM sales INNER JOIN customer ON sales.customerkey = customer.customerkey GROUP BY continent, title UNION SELECT continent, null, SUM( quantity ) FROM sales INNER JOIN customer ON sales.customerkey = customer.customerkey GROUP BY continent UNION SELECT null, null, SUM( quantity ) FROM sales;

This show us that there is still room for improvements in MonetDB optimizer.

Window Functions

I will first add foreign key constraint between "sales" and "date" tables.   ALTER TABLE date ADD CONSTRAINT date_pk PRIMARY KEY ( date ); ALTER TABLE sales ADD CONSTRAINT FKfromDate FOREIGN KEY ( orderdate ) REFERENCES date ( date );

We can use LAG function to compare sales for the current and the previous row. Each value from quantity column will have a pair in the BeforeQty column, except in the first row ( no previous ). Quantity in that first row will make a difference. This query will execute in 2 seconds.   SELECT ( SUM( Qty ) - SUM( BeforeQty ) ) AS Difference FROM ( SELECT Quantity AS Qty,   LAG( Quantity, -1 ) OVER ( ORDER BY date ) AS BeforeQty                                     FROM date INNER JOIN sales ON date.date = sales.orderdate );

For each row in the table, we will calculate the average quantity of the previous 6 rows and the current row. At the end will see sums of the quantity and these rolling averages. This query will last 29 seconds. SELECT SUM( Quantity ) AS "SumQty", SUM( AvgBefore7Qty ) AS "SumAvgBefore7Qty" FROM ( SELECT Date, Quantity,      AVG( Quantity ) OVER          ( ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)              AS AvgBefore7Qty FROM date INNER JOIN sales ON date.date = sales.orderdate );

Updates

MonetDB should be slow for updates, but it managed to update 44 milion rows for just 4.19 seconds. UPDATE sales SET currencycode = 'EU' WHERE currencycode = 'EUR';

We can confirm that all of the values 'EUR' are updated to 'EU'.   SELECT DISTINCT currencycode FROM sales;

Problematic Queries

Double Grouping

Double grouping is when we first group our data, and then we group that result. For example, we will total sales quantity per customerkey, and then we will count customers per total quantity. We will count how many customers have the same total quantity.

This query is problematic because while the first grouping can be fast, the second one could be much longer. The result of the first grouping will have 2M rows, because we have so much customers. In the second stage, we have to group these 2M rows, and that is when I expect the performance to become bad.

Second phase: SELECT TotQty, COUNT( customerkey ) FROM ( SELECT customer.customerkey, SUM( quantity ) AS TotQty      FROM customer INNER JOIN sales ON customer.customerkey = sales.customerkey    GROUP BY customer.customerkey ) as FirstPhase GROUP BY TotQty;   We can see on the image that we have 2,663 customers with total quantity of 333 items, and only two with 1,130 items. The time for execution is again 5 seconds. This is something I didn't expect. I am pleasantly surprised. I can tell you that these kinds of queries are problematic for Power BI database ( SSAS ).

Aggregated Query from Two Fact Tables ( Stitch Query )

"Stitch" query is when we aggregate two fact tables per the same dimension and we get two data sets as a result. Then we join those two data sets in the final result. This is how we aggregate values from two fact tables.

I have tried to read two small subqueries into python, and then to join them with pandas. Python reported execution time of just 1.2 seconds. It is strange that we can get the final result faster by combining MonetDB and Pandas, then just by using MonetDB.

WITH S AS ( SELECT productkey, SUM(quantity) AS Sq             FROM sales             GROUP BY productkey ), O AS ( SELECT productkey, SUM(quantlty) AS Oq        FROM orderrows        GROUP BY productkey ) SELECT P.brand, SUM(S.Sq) AS Sq, SUM(O.Oq) AS Oq FROM product P  LEFT JOIN S ON S.productkey = P.productkey  LEFT JOIN O ON O.productkey = P.productkey GROUP BY P.brand;

This would be the fastest version of this query. It would execute in only 3 seconds. In this query, we would use the the last subquery to join reduced sales and orderRows tables, with the product table.

INSERT INTO SELECT

I will use "INSERT INTO SELECT" to make "Sales" table bigger.  Before doing that, I will remove FK constraints. I will also change optimizer. ALTER TABLE sales DROP CONSTRAINT fkfromproduct; ALTER TABLE sales DROP CONSTRAINT fkfromcustomer; ALTER TABLE sales DROP CONSTRAINT fkfromdate; SET sys.optimizer = 'minimal_pipe';

I will now run this statement to make my sales table twice bigger. INSERT INTO sales SELECT * FROM sales; MonetDB needed 5 minutes to do this.

I will do this 1 more time. That will double the number of rows to 844M. That was done in 9:48 minutes. Then, I will again read from the CSV file into this table. That will add another 211M rows, so in total "sales" table will now have one billion rows.

I will run now this query twice. The first time it will end after 28 seconds, and the second time after 5,5 seconds. SELECT brand, SUM( quantity ), AVG( netprice ) FROM sales INNER JOIN product    ON sales.productkey = product.productkey GROUP BY brand;

SELECT color, SUM( quantity ), AVG( netprice ) FROM product INNER JOIN sales    ON sales.productkey = product.productkey GROUP BY color;

Immediately after, I run the same query, by it was grouped by color. The time was again 5 seconds. We can see that performance is good, even with 1B rows.

Conclusions

We can conclude some things: – MonetDB is usually very fast. – Initially, until the database worm up, queries can be slow. – We should always set foreign key constraints to achieve speed boost. – Some kinds of queries are better optimized than others.

– MonetDB does not use much memory. During the import of the "sales" table, the RAM usage increased from 4 to 13 GB. It was the same during this last, 1B rows, query. At other times, the usage was much less.

Bundled Products vs Products in Bulk

Stores like to sell products as a bundle. For example, the store will sell a box with several decorations as one product. Store will benefit from this because: – Stacking and scanning products is easier when the are bundled ( effort- ). – The buyer will spent less time in the store choosing decorations ( speed+ ). – We can sell more decorations by bundling less popular decorations with the more popular ones ( sale+ ).

But bundles make production planning harder. – We have to unbundle data about products to find out how much each individual shape/color was sold ( effort+ ). – We are usually looking at sale for a longer period of time. Because of quantity of data, unbundling will make our database real slow ( speed- ). – We have to read data about all the shapes/colors, although we just want to analyze the sale of one of the shapes/colors ( analysis- ).

Data in Bundle or in Bulk

We can treat transactions as one bundle product. Product code, quantity and price are one bundle. We want to be able to quickly save this data bundle.	We can achieve this by saving our transactions as separate rows in a database. That is how we can quickly save or retrieve individual transaction as a bundle.

For production planning we will differently organize our data. We will organize it into columns. If we want to find out how many products, we have sold that have the price lower than 3 EUR, we just have to scan the column "Price", and then to find specific positions in the column "Qty". Each column can be sorted and compressed for even faster retrieval. We don't even need to read the column "Product".

When we organize data in rows, we use a row-oriented database. When we organize data in columns, we use a columnar database. Data is typically collected in a row-oriented database (Postgres, Oracle, MySQL) and then transferred to a columnar database for analytics ( ClickHouse, DuckDB, Vertica ).

Row Oriented Databases vs Columnar Databases

Row oriented databases: 1) They are great for small writes/reads. Perfect for "insert one order", "update this user", "delete this invoice line" etc. 2) Application code often works with whole objects/rows (User, Order, Invoice), which matches row layout. 3) Great for transaction isolation, replication, ACID complacence, constraints, triggers, foreign keys. 4) We have many users who send queries that touch only one row from a table and expect an immediate response from the database.   They are best for Cash registers, ERP, CRM, banking systems, ticketing.

Columnar databases: 1) We only read columns we need, and columns can be heavily compressed. That improves IO. 2) CPU can process several values of the same type at once. This is called vectorized execution. Queries with lot of data will work faster. 3) Column scans and aggregations are easy to parallelize across cores and nodes. 4) We have smaller number of queries, but they are much heavier, they touch a lot of data, with lot of joins, but the users are expecting results in 3-4 seconds.   They are best for sales reports, dashboards, time-series, data mining.

Row oriented databases are catching current state and providing transactional correctness. Columnar databases are for analysis of the historical data. We can also say that row-oriented databases are better in writing data and providing data consistency and integrity. Columnar databases are better in reading huge quantities of data.

OLTP vs OLAP

If we are preparing our database for everyday operations and transactions, then we are making OLTP database. For analytics and BI, we would prepare OLAP database. OLTP is compatible with row-oriented databases, and OLAP with columnar databases.

In the OLTP databases, data is  organized in a lot of small tables. There is no data duplication and every value is written only once.                                        Small tables make queries more complex so these databases usually don't grow significantly in size.

In the OLAP databases, we merge small tables to reduce their number. Columnar technology have no problem with tall tables that have a lot of columns.   By reducing the number of joins, we can query our data with simpler (faster) queries. That is why we organize our tables in a star schema.

OLTP tables are filled with data by users and applications in small transactions. From time to time ( usually nightly ), data is transformed and transported into OLAP databases in batches. This makes data management different between OLTP and OLAP databases. OLTP databases receive data continuously, but OLAP databases receive data periodically and only after data is well-reformed.

OLTP is acronym for "Online Transaction Processing" and OLAP for "Online Analytical Processing". We saw that the difference is made by:
      – Different purpose and usage.
      – Different database technology.
      – Different data organization inside the database.
      – Different data management.

Data Immutability

Columnar ( OLAP ) databases are filled in batches. That doesn't mean that it is not possible to UPDATE or DELETE rows in tables. There are four strategies that OLAP databases use:

1) Data is immutable. We can only insert data in batches, and afterward we can not change individual rows. If data is immutable and presorted then we can achieve the biggest levels of compression. The example of this technology is the database used for Power BI.

2) MonetDB is an example of the OLAP database that is suited for light updates and deletes. When we delete/update some value, MonetDB will label old record as "dead". That record will become invisible to queries. After some time, garbage collection will remove not-needed records.

3) We can divide our columns into small segments. Whenever we want to change some value, we modify the whole segment. This is good solution when we have a lot of hardware power, like cloud providers do. Google Snowflake is an example of such database.

4) We can have two databases in one. One database is row-oriented, and the other one is columnar. Data is written into row-oriented database, but when we want to read data we read from both databases together. Historical data is read from the columnar database, and today's data is read from the row-oriented database. SAP Hana is using such approach.

This last strategy creates something called "Hybrid Transaction Analytical Processing", or HTAP. HTAP is when a single database can do fast real-time transactions and fast analytics on the same data at the same time.

MonetDB

If you have one server and install MonetDB on it, you will be able to:
      – Enjoy blazing-fast query performance, even if you have a lot of data and your queries are more complex.
      – Avoid thinking about indexing, compression, statistics tuning, partitioning decisions. MonetDB will do it all automatically.
      – Apply the full power of SQL to all your tables, whether they are carefully modeled or just collected in one place.
      – Spend your money on something else because MonetDB is an open source database under the Mozilla Public License.

MonetDB is a fast, complete SQL, easy-to-maintain, open source analytical database server that can process huge amounts of data on a single server machine.

Hardware and Speed Considerations for MonetDB

MonetDB doesn't have a requirement to keep the whole database in a RAM memory. Parts of data that are already processed will be deleted from memory. MonetDB have capacity to deal with data that is much bigger than the amount of memory on your server machine. Only data that is currently processed will have just-in-memory execution pipelines.

MonetDB, will benefit from the fast NVMe disk, that has sustained performance under write load. Such disk should be accompanied with a lot of RAM memory, so that usage of a disk is minimal.

MonetDB is designed to take the full advantage of your CPU. – When executing a query or loading data, MonetDB will spread the load across all processor cores, increasing CPU utilization to over 90%. There is a setting that can limit the number of cores used by MonetDB. – Vectorized query execution means that the processor can process thousands of values ​​with a single instruction. The values ​​must be of the same data type, which is perfect for columnar databases. – Data is processed in small chunks using simple instructions. This reduces RAM visits and uses the processor's fast L1/L2 memory. – Late materialization means that we avoid reading data that does not contribute to our query. We will only read the data needed for the final dataset, and skip everything else. – Partial query results are reused instead of being recomputed.

Zero-Tuning Philosophy

MonetDB will not burden you with indexing strategies, partitioning decisions and vacuuming. MonetDB tries to eliminate complexity. Most workloads run at full speed without manual optimization. Indexes are automatic. Compression is automatic. Memory management is automatic.

Creating indexes manually is problematic because you need to know in advance where to create the indexes. Indexes can slow down updates and deletes because when data is modified, the database must also update the corresponding indexes to reflect these changes. This is something that we especially want to avoid in the columnar databases. If users change their behavior, that can make our indexes ineffective.

Technique that MonetDB use to automatically tune indexes is called database cracking. Cracking means that MonetDB will sort and group data, and create indexes during SELECT queries. If most of the statements on the OLAP databases are SELECT queries, and such queries touch a lot of rows, then it is best to create indexes and tune data during the SELECT queries execution. This is optimal because:

   – The parts of a database that are heavily used will be tuned the best. We will not spend effort on data that no one reads.
   – Instead of heaving one huge indexing job, we will have many tiny, incremental reorganizations.
   – After some time, our data will be optimally indexed and sorted, but MonetDB can change strategy if users change their behavior.
   – Cracking is especially powerful in columnar databases like MonetDB, where each column is stored separately.

Cracking is powerful but not ideal for everything:

   – Heavy transactional workloads (many updates) disrupt adaptive patterns.
   – Distributed cracking across many nodes is still complex.
   – Highly unpredictable workloads (all queries unique) limit its benefits.

Comparison of MonetDB and Power BI

Power BI database ( SSAS Tabular )  is in-memory database, that is using compressed columnar tables, and is optimized for BI models ( dimensions, measures, heirarchies ). Power BI needs the whole model loaded in the memory, but columns are heavily compressed, so Power BI doesn't have huge memory footprint. For many calculations Power BI doesn't even need to decompress columns, so it can do its work while maintaining really fast IO.

Power BI likes table relationships and measures defined in advance, and is not optimized for arbitrary queries.  It is not intended for complex joins outside the model. If we spend time creating optimal model, and we wait for our model to process/refresh during the load, we will get database that is optimized for:

   – measure evaluation
   – filters
   – slice and dice

On the other side MonetDB is optimized for raw analytical SQL on large tables, without modeling overhead.

MonetDB Imperfections

As columnar server, MonetDB suffers from all of the shortcomings that columnar servers have. MonetDB is not good in transactional traffic, and lot of deletes and updates. MonetDB is not perfect for reports that return huge tables with lot of columns. MonetDB is best for short, aggregated analytical queries.

If you have massive amounts of data, you will need a computer cluster, and cloud scalability. This is something where MonetDB doesn't shine. MonetDB is best if you have one powerful server machine, and you want to run complex analytics on it. Distributed databases are better in scaling, but because their setup is more complex and they are spread over many computers, they usually have less rich SQL capabilities and other limitations.

MonetDB does not support replication, but it does support distributed operation across multiple machines via sharding. Sharding will speed up some queries, but not all. If we want to set up a cluster with MonetDB, partitioning and sharding must be done carefully and manually. If possible, it is better to have a single powerful server than to resort to sharding and expensive network equipment.

MonetDB Market Position

MonetDB supports SQL, ODBC, JDBC and many programming languages ( python, java, R, ruby, PHP… ). It can be easily integrated with different software, but the support of 3^rd party software, clients and ORMs is limited. This is the consequence of the MonetDB origin which was research and science oriented. MonetDB was developed on the CWI institute in the Netherland. Development was driven by innovation and curiosity, and not by commercialization and marketing. This is why you can say that MonetDB is the fastest database you've never heard of. But that development lasted for 30 years and today MonetDB is complete and powerful database system.

MonetDB Features

 Here, I will list features of the MonetDB server, and I will direct you toward blog posts about specific feature.

1) Blog posts 0010, 0020 and 0030 will teach you how to install MonetDB, how to create a database, and how to fill database with a sample data. On the other side, installation of MonetDB through docker is explained in the last blog post 0600.

2) MonetDB can be augmented by using python language. There are different ways how that can be achieved. Blog post 0040 will explain how to connect to MonetDB from python.

Blog post 0360 will teach us how to use python to fetch data from different sources into MonetDB.

Beside using python to fetch data, we can use python to create custom functions in MonetDB. That is explained in the blog post 0430.

We can create inline or aggregated Python UDFs.

3) Blog post 0050 is about identifiers and constants. MonetDB supports different data types.

4) How to create and alter a table, and set table and column constraints is explained in blog posts 0110 and 0320.

MonetDB also supports some special kinds of tables.
   – Temporary tables are tables with limited lifetime. They are described in 0370.
   – If we have several small tables with the same structure, we can connect them into one big virtual table ( partitioning ). This is covered in 0380.
   – Unlogged tables are special tables that can be used for fast writes, updates and deletes. We can read about them in 0390.
   – Views are explained in 0300.
   – MERGE and REMOTE tables are special tables that are used for sharding in MonetDB. Sharding is a way to distribute MonetDB work on a cluster of    computers. We can read about them in 0480.

5) SELECT, WHERE, HAVING, LIMIT;OFFSET, GROUP BY, ORDER BY, are described in 0120, 0150.

6) Article 0170 will teach as about ANALYZE, SAMPLE and PREPARE.
   – ANALYZE is used to update MonetDB statistics. That statistic is used by MonetDB to optimize queries.
   – SAMPLE is used to create a sample of rows from some table.
   – PREPARE is a way to prefabricate our statement so that when we execute it, it is running faster.

7) MERGE statement, CTE and GROUPING SETS can be considered as composite SQL statements, that do several things at once.

8) In SQL, running totals and moving averages are calculated by using WINDOW functions. Theory and syntax of window functions are presented in 0200, 0220. Window function can be divided into Aggregate 0230, Ranking 0240 and Offset 0250 window functions.

9) MonetDB is rich with built-in functions. They can be divided into Aggregate 0210, Mathematical 0260,  String 0270, Comparison 0280, functions. Time functions are explained in 0070.

10) Transactions are explained in 0290, Indexes in 0300, Schemas in 0310,

11) Blog posts 0330, 0340, 0350 are about importing and exporting data to/from MonetDB. We can import/export data to/from CSV files or a binary format. It is also possible to load data into MonetDB from different programming languages ( we have covered python and SQL ).

12) Procedural SQL is a way to introduce procedural paradigm into SQL. Procedural paradigm is when we have to explain to database what to do instead of just describing our demands with SQL query. With procedural SQL we can make:

   – Custom functions ( 0400, 0410, 0420 ).
   – Procedures ( 0440 ). Procedures are sets of statements that are executed together.
   – Triggers ( 0450 ). Triggers are procedures that run automatically after some event.

13) Creating users and setting their rights is explained in blog posts 0460, 0470.

14) Articles 0500, 0510 will teach us how to create ODBC and JDBC connections to MonetDB. We will also learn about file_loader and proto_loader functions. With the file_loader and proto_loader functions, we can treat CSV files and tables in other ODBC databases as if they were local MonetDB tables. Article 0530 will show us how to encrypt connection with MonetDB using TLS encryption.

15) Every database need backup. That is explained in 0540 and 0550.

16) For administration we have to learn three console applications:

   – Monetdbd ( 0560 ) is a linux daemon. This is the main application used to start and control MonetDB.
   – Monetdb ( 0570 ) is console application used to work with specific database.
   – Mclient ( 0580 ) is client application that we can use to send queries to MonetDB.

17) Different system and session procedures and commands will help us to monitor and control user sessions and queries. They are explained in 0590.

Notice: In this blog post, we will create a VOC database. This database will be used as the main database for most subsequent blog posts.

Getting the Codename of our Ubuntu Version

First, we need to know the code name of our Ubuntu version. We can find that by reading from the file "os-release". From this file we can read only the line that has words "VERSION_CODENAME" inside of it.

cat /etc/os-release | grep VERSION_CODENAME

Our Ubuntu codename is "focal". It is also possible to use command:

lsb_release -cs

We can see from the command line above that our user account is "fffovde". "FffOvdeKomp" is the name of our computer.

Installing GPG key

GPG key is a key that Ubuntu will use to verify MonetDB packages before installing them.

First, we will create folder /etc/apt/keyrings, if we don't have it. We will use permissions 755 on this folder. Administrator will have full access to this folder, but other users will have read and execute rights. This is necessary because "apt" application will access this GPG key, not only with administrator rights, but also as a special low-privilege user. sudo mkdir -p /etc/apt/keyrings                #option -p means that we will create parent directories, if they do not exist. sudo chmod 0755 /etc/apt/keyrings

We will use wget to download GPG key from the internet. Option "-O" means output. sudo wget -O /etc/apt/keyrings/monetdb.gpg https://dev.monetdb.org/downloads/MonetDB-GPG-KEY.gpg We will also change permissions on this binary ".gpg" file. sudo chmod 0644 /etc/apt/keyrings/monetdb.gpg

If result of this command is equal to " DBCE 5625 94D7 1959 7B54 CE85 3F1A D47F 5521 A603" for MonetDB, then that means that we have installed the correct key. There is also a key "8289 A5F5 75C4 9F50 22F8 EE20 F654 63E2 DF0E 54F3", but that key is for versions of MonetDB older than 11.49.

Adding a Repository Where MonetDB is Stored

Next, in folder "/etc/apt/sources.list.d" we will create a file with the name "monetdb.list".

#jump to that folder cd /etc/apt/sources.list.d  #create new file, you will be asked to provide password sudo touch monetdb.list

We can add this text by running these two lines in our terminal:

sudo sh -c 'echo "deb [signed-by=/etc/apt/keyrings/monetdb.gpg] https://dev.monetdb.org/downloads/deb/ focal monetdb" >> monetdb.list' sudo sh -c 'echo "deb-src [signed-by=/etc/apt/keyrings/monetdb.gpg] https://dev.monetdb.org/downloads/deb/ focal monetdb" >> monetdb.list'

Now our file looks like this:

MonetDB Installation

Then we can install MonetDB server and client: sudo apt install monetdb5-sql monetdb-client

Creating a DBfarm

In MonetDB, databases are located inside a folder usually called "DBfarm". In addition to the databases, in this folder we will find configuration files with settings for the DBfarm.

Monetdbd, linux daemon, is used to initialize DBfarm by placing configuration files inside it. We use this daemon for changing DBfarm settings and for communication with databases. Monetdbd manages DBfarm and its databases.

monetdbd create /home/fffovde/DBfarm1	We will use "monetdbd" to create a DBfarm on our disk.
	Inside of this folder, a new file ".merovingian_properties" will appear. ls -A  /home/fffovde/DBfarm1

The Merovingian dynasty was the ruling family of the Franks from the mid-5th century until 751. This dynasty ruled the Netherlands, the country from which MonetDB originates. MonetDB is using this term for some of its internal files and commands.

If we look inside of this file, we will find only one property. All other properties are using default values. We can read those default values by command: monetdbd get all /home/fffovde/DBfarm1

property hostname dbfarm status mserver logfile pidfile loglevel sockdir listenaddr port exittimeout forward discovery discoveryttl control passphrase snapshotdir snapshotcompression mapisock controlsock

value FffOvdeKomp /home/fffovde/DBfarm1 no monetdbd is serving this dbfarm unknown (monetdbd not running) /home/fffovde/DBfarml/merovingian.log /home/fffovde/DBfarml/merovingian.pid information /tmp localhost /50000 60 proxy true 600 no <unset> <unset> .tar.lz4 /tmp/.s.monetdb.50000 /tmp/.s.merovingian.50000

Now, that DBfarm is created, I will start the daemon. We can use daemon to control DBfarm.	monetdbd start /home/fffovde/DBfarm1
	Inside of the DBfarm1, we now have 4 files: ls -A /home/fffovde/DBfarm1

File ".merovingian_lock" is empty. This file probably just signalizes that this is DBfarm. File "merovingian.pid" has the number 1863. This is the number of monetdbd process. If we use command "sudo ss -tlnp" to show us all listening ports, we will see the name monetdbd beside process 1863, and this process will listen the default port 50000.

We can also read content of the log file. cat /home/fffovde/DBfarm1/merovingian.log

Creation of a Database

While monetdbd is used to manage DBfarm, "monetdb" console application is used to manage individual databases. In the background, "monetdb" will send our commands to monetdbd, and monetdbd will be the one exercising direct control over databases. So, we control databases from "monetdb", but through the power of monetdbd.

This is how we create a database with the name "voc": monetdb create voc

A folder with the name "voc" will appear inside of the DBfarm1 directory.

When database is created, it will only have one default user. That user is administrator "monetdb", and he has default password "monetdb". Database will be created in the maintenance mode. That means that only administrator will be able to start the database, and only on the local computer.

I will change the mode of the database, and I will take it out of the maintenance mode. We can do this to make database available to all the users ( although we currently don't have other users ). monetdb release voc
We can start our database. After the database start, it will be ready for users to log in. monetdb start voc We can check the status of the database with the command: monetdb status

The process with our database server is called "mserver5". This process will run when the database is opened: pgrep mserver5

This command will return process ID of our database server process.

Logging into Database

Now that our database is working and is listening on port 50000, we can try to use it. We will now use another application, with the name "mclient". Let's first recapitulate three console applications used by MonetDB:

"monetdbd" is managing DBfarm.

"monetdb" is managing individual database, with the help of "monetdbd".

Database will run as a "mserver5" process. We'll use "mclient" application to send queries to this process.

We will login to the "voc" database as a "monetdb" user. Password is the default password "monetdb". mclient -u monetdb -d voc
This will be our welcome screen. We will get "sql>" prompt. There we can type our queries.
I can run this query in my database: SELECT 'columnValues' as columnName;

We can exit the "mclient" program by typing the word "quit".

How to Stop our Server?

This is how we can stop "mserver5" process, the process of the Monetdb database. monetdb stop voc
	Log file will tell us what happened. tail -1 /home/fffovde/DBfarm1/merovingian.log

We can put our database in maintenance mode at any time. It doesn't matter if database is opened or closed. We use "lock" command. monetdb lock voc

Next time, only administrator, on the local computer, can start the database with "monetdb start voc" command. He can start the database in exclusive mode, so that he can run some maintenance operations on the database ( he can do backup, or he can make changes in the schema ). We saw previously that database can be taken out of maintenance mode with "monetdb release voc" command. After that, any user can login to a database.

We can also stop "monetdbd" daemon.	monetdbd stop /home/fffovde/DBfarm1
	Log file will show us that daemon has stopped.

Install MonetDB in Alternative Way

This time I will jump to the newer version of the Ubuntu. It is "noble". cat /etc/os-release | grep VERSION_CODENAME

Then, I will go the web page https://www.monetdb.org/downloads/deb/repo/. On this web page we have a list of the newest versions of the Ubuntu and Debian. One of the versions is "noble". Enter that folder, click on "monetdb-repo.deb". Firefox will download this file.

We will install this "monetdb-repo.deb" package. sudo apt install /home/fff/Downloads/monetdb-repo.deb

	This package will add the file "monetdb.sources" inside of the "/etc/apt/sources.list.d". This file has the same content as the file "monetdb.list" that we have created by hand during the original installation.
	This "monetdb-repo.deb" package will also provide GPG keys that can be found on this location "/usr/share/keyrings/monetdb-1.4.gpg". We now understand that we can prepare our computer for monetdb installation by using this package, instead of doing everything by hand.

Uninstalling Of the MonetDB

For uninstallation process, if monetdbd is running, we must stop it.

monetdbd stop /home/fff/DBfarm1

We will now list all the packages that have "monetdb" in their name.   dpkg -l | grep monetdb

We will remove all those packages. When we delete "monetdb-repo", files with repositories and gpg keys will also be deleted. sudo apt purge libmonetdb-client28 libmonetdb-mutils libmonetdb-stream28 libmonetdb30 monetdb-client monetdb-repo monetdb-server monetdb-sql monetdb5-sql

We will clean all the possible remains:   sudo apt autoremove sudo apt autoclean

We still have "monetdb" group and user. cat /etc/passwd | grep monetdb getent group monetdb

We will delete the group and the user.   sudo deluser monetdb sudo delgroup monetdb Actually, when we delete the user, the group will also be deleted, so we don't have to delete it separately.

This will not delete DBfarms, only MonetDB application.

Systemd Unit File

Systemd Unit File is a file with settings that Systemd will use when starting and controlling some daemon. From version 11.53.13, MonetDB is using a new systemd file that allows us to change the default directory ( DBfarm ) for databases that are controlled by Systemd.

Old Systemd Unit File

systemctl status monetdbd This command will show us where is systemd unit file for Monetdbd daemon. /lib/systemd/system/monetdbd.service

cat /lib/systemd/system/monetdbd.service   The old system unit file was hard coded to always use the directory "/var/monetdbd5/dbfarm". Only databases located within this directory could be controlled by systemd. Only such databases could be started automatically after the computer boots.

[Unit] Description=MonetDB database server daemon Documentation=man:monetdbd https://www.monetdb.org/documentation/admin-guide/manpages/monetdbd/ After=network.target                                                                                                                                                              [Service] Type=forking User=monetdb Group=monetdb ExecStart=/usr/bin/monetdbd-11.53.3 start /var/monetdb5/dbfarm ExecStop=/usr/bin/monetdbd-11.53.3 stop /var/monetdb5/dbfarm Restart=on-failure PIDFile=/run/monetdb/merovingian.pid PrivateDevices=no ProtectSystem=full ProtectHome=read-only                                                                                                                [Install] WantedBy=multi-user.target

New Systemd Unit File

From version 11.53.13, we have a new system file that allows us to change DBfarm folder, controlled by Systemd.

systemctl status monetdbd This command will show us where is systemd unit file for Monetdbd daemon. /usr/lib/systemd/system/monetdbd.service

cat /usr/lib/systemd/system/monetdbd.service This is the new look of the Systemd unit file. We can notice that now we have environ variable DBFARM that is directed to directory "DBFARM=/var/monetdb5/dbfarm" by default.   This file is now more complex because it allows us to take some other directory for the DBfarm.

[Unit] Description=MonetDB database server daemon Documentation=man:monetdbd https://www.monetdb.org/documentation/admin-guide/manpages/monetdbd/ After=network.target [Service] Type=forking User=monetdb Group=monetdb UMask=0007 Environment=DBFARM=/var/monetdb5/dbfarm ExecStartPre=/bin/bash -c 'test -d ${DBFARM} || (mkdir -m 770 ${DBFARM}; chcon -u system_u -r object_r -t mserver5_db_t ${DBFARM})' ExecStartPre=/bin/bash -c 'test -f ${DBFARM}/.merovingian_properties || (umask 0007; /usr/bin/monetdbd-11.53.13 create ${DBFARM}; /usr/bin/monetdbd-11.53.13 set pidfile=/run/monetdb/merovingian.pid ${DBFARM}; touch ${DBFARM}/.merovingian_lock; chcon -u system_u -r object_r -t monetdbd_lock_t ${DBFARM}/.merovingian_lock; chcon -u system_u -r object_r -t monetdbd_etc_t ${DBFARM}/.merovingian_properties)' ExecStartPre=/usr/bin/grep -q pidfile=/run/monetdb/merovingian.pid ${DBFARM}/.merovingian_properties ExecStart=/usr/bin/monetdbd-11.53.13 start ${DBFARM} ExecStop=/usr/bin/monetdbd-11.53.13 stop ${DBFARM} Restart=on-failure PIDFile=/run/monetdb/merovingian.pid PrivateDevices=no ProtectSystem=full ProtectHome=read-only     [Install] WantedBy=multi-user.target

Changing The Default Directory

We will not change the original Systemd unit file. Instead of that we will create a "drop-in". That means that we will create another file that will be "amendment" to the original file. "Drop-in" file augments or overrides parts of a unit file without touching the original unit file. We create this "drop-in" file by running the command: sudo systemctl edit monetdbd

An instance of Nano text editor will open. Inside of it we will se the whole original Systemd unit file, but all the lines will be commented out (every line will start with #).   Inside of this file we have to add these lines. [Service] Environment=DBFARM=/var/monetdb5/dbfarm2 This location will override the original "/var/monetdb5/dbfarm" location.

This config snippet will be "amendment" to original Systemd unit file. This snippet must be added above the comment "Edits below this comment will be discarded". We will save this change with "Ctrl+O" ENTER, and then we will exit with the "Ctrl+X". After that we must reload all the systemd unit files.   sudo systemctl daemon-reload

The Changes We Made

This command will show us the new value of the DBFARM environ: systemctl show monetdbd | grep ^Environment=

"drop-in" file is written inside of the: /etc/systemd/system/monetdbd.service.d/override.conf   On the image we use these commands to check override.conf: cd /etc/systemd/system/monetdbd.service.d ls -alh cat override.conf

Testing The Changes

Because we are going to use Systemd, we should add our user to "monetdb" group.

We will now create one database and after that we will restart our computer to see if it will be started automatically.

After the reboot, I will open the terminal, and I will try to log in: mclient -u monetdb -d DBdesktop It will work. We don't have to start the daemon manually.

The last step is to check if DBdesktop is really inside of the dbfarm2 directory. cd /var/monetdb5/dbfarm2 ls -alh

What is Schema

Database is made of tables, views, indices and so on. Inside of Database, each of these objects belongs to some schema. Database is organizationally divided into schemas. There is no overlap between schemas. Some special objects are outside of a schema, like roles. Usage and modification of schema elements is strictly done by the user or the role that owns that schema. During creation of a schema, we should decide who will be the owner, because later it will not be possible to change ownership. If we want several people to maintain one schema then we should set a role as an owner of a schema. Only 'monetdb' user and 'sysadmin' role are allowed to create new schemas.

Schema as a File

We can create a file that will contain all the instructions the server needs to create database objects inside of the schema. This file would tell the database which tables, relations, indexes, views to create. In this way, we can create everything that makes up one schema. That is why we call such a file a "schema file". Although we have not learned all the SQL commands needed to create such a file, we can use the "schema file" that the MonetDB development team has prepared for us.

First, we will download sample database from this location: https://dev.monetdb.org/Assets/VOC/voc_dump.zip   This will give us ZIP file. Inside of it, there is SQL script with the schema for our new database.

Creation of a New User

For our schema we will create a new user. First, we will enter mclient with 'monetdb' privileges.   > monetdbd start /home/fffovde/DBfarm1 > mclient -u monetdb -d voc  --password monetdb

For creation of a user, we need username (USER), password (WITH PASSWORD), user's full name (NAME), and default schema for that user (SCHEMA). The default schema is schema that MonetDB will use as a current schema when the user log in. For tables in the current schema, the user can type "SELECT * FROM Table1", but for tables in NON current schemas, the user must type "SELECT * FROM schema.Table1".

"sys" schema is a built-in schema in MonetDB. We will use it temporarily so we can create a new user. sql> CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys";

As a 'monetdb' administrator we can create a new schema. We will say that previously created "voc" user is the owner of that schema.

sql> CREATE SCHEMA "voc" AUTHORIZATION "voc";

Don't get confused. The name of our database is "voc", but the name of the new schema is also "voc", and the name of the user is "voc".

We will set the new schema as the default schema for our user.   sql> ALTER USER "voc" SET SCHEMA "voc";  sql> \q   -- we can exit mclient with "quit" or "\q"

Since the "voc" schema is the default schema for the "voc" user, this schema will be active when this user logs in to MonetDB. Everything the user does will be reflected in this schema, unless the user explicitly mentions that they want to work in a different schema.

Populating our Schema with Database Objects

Our "voc" schema is currently empty, but we have definitions of all the tables, view, indices … inside of our downloaded SQL script. We will use that script to populate our schema. We type:

> mclient -u voc -d voc ~/Desktop/voc_dump.sql   This code will log "voc" user into "voc" server. It will also execute all the SQL commands from the "voc_dump.sql" file. When the user log in, and his default schema is "voc", that means that he will log in into "voc" schema. All the SQL commands will be executed inside of this schema.

We will log in again: mclient -u voc -d voc     #password is voc We can use mclient command "\d" to list all the tables and views inside of our database.   sql> \d TABLE voc.craftsmen TABLE voc.impotenten TABLE voc.invoices TABLE voc.passengers TABLE voc.seafarers TABLE voc.soldiers TABLE voc.total TABLE voc.voyages

This is what we would get:

DBeaver Database Manager Program

We will install DBeaver database manager program to peruse our database. > sudo snap install dbeaver-ce

This program is GUI program, so we will open it in our desktop environment. In the program we will click on the triangle (1), and then we will select "Other" (2) to open other servers. There we will find MonetDB. We will click on it (3), and a new dialog will open. In this dialog, host and port are already entered. We just need to enter Database/Schema (4), Username and Password (5) (which is "voc").

DBeaver will not have driver for MonetDB database installed, so it will offer you to download it. Just accept that offer.

At the end, objects of our database will appear inside of pane on the left side of a program. There, we should double click on schema name (1). After that we can select tab "ER Diagram" (2). There, after some rearrangement, we will see ER diagram of our database (3). As we can see, tables are organized in star schema with "voyages" table as the only fact table. All tables are connected with foreign key constraints, where foreign key is also primary key inside of dimension tables. The only exception is Invoice table where foreign key columns are not primary key columns, and that is why that relationship is shown with dashed line (4).

Here is download for voc_dump.zip file used in this blog post:

Download

DBeaver is an excellent program. If you want to learn more about it, you can try this linkedin tutorial.

https://www.linkedin.com/learning/dbeaver-essential-training?trk=learning-topics_trending-courses_related-content-card&upsellOrderOrigin=default_guest_learning