0390 Unlogged tables in MonetDB

Write Ahead Log

START TRANSACTION; INSERT INTO Tab1 ( Num ) VALUES ( 5 ); UPDATE Tab1 SET Num = 6 WHERE Num = 5; DELETE FROM Tab1 WHERE Num = 6; COMMIT;

When a transaction occurs, we want to record all changes made by that transaction as quickly as possible. 1) We write to files that are called "Write Ahead Log" (WAL).      These files are just temporary notes. 2) We write down only essential data. 3) We write sequentially onto disk. That is faster. 4) We buffer these notes in memory and we write them to disk in batches. 5) We do not recalculate indexes of any table.

After we have collected all the necessary data, we have analyzed it and are now ready to write it to the table. We will transfer temporary notes from the WAL to the permanent record. Changes are going to be applied to the database in one fast move. This brings us three advantages: a) The chances of something bad happening during the write, are minimized, because we are really fast and prepared. b) We can be sure that our transaction will be completed in full, or will not be completed at all. c) Until the end, no final decision is made, so If transaction has to be rollbacked, we just have to declare WAL notes invalid. We don't have to delete anything from the database.

But what if the system becomes corrupted and crash? We don't have to worry. We still have WAL. After we fix the system, we can read from the WAL about: a) Transactions that are complete and are not written to a table. We can write them after the system is repaired, from the WAL  notes. This is how we provide durability. These transactions will not be lost. b) Transactions that are incomplete in the WAL will be discarded. We don't want incomplete transactions.

Unlogged Tables

Unlogged Tables are just like normal database tables, except they are not using WAL.

Normal tables are written like:

Unlogged tables are skipping WAL:

Unlogged tables are almost like normal tables:
1) They are written to disk.
2) After normal shutdown of a system, content of unlogged tables will be preserved.
3) They have transactions, but their transactions are not using WAL. Their transactions exist only in RAM memory.
4) Content of these tables is available to all of the users, just like for normal tables.

The only difference is in the case of the system crush. After the crush, content of normal tables will be restored to a consistent state by using WAL. Unlogged table will be truncated during the recovery. Server can not guarantee consistency of unlogged tables without WAL, so it will delete their content. This is why unlogged tables should be used only for temporary and re-creatable data.

Writing to unlogged tables can be several times faster then writing to normal tables. Without WAL, we can write much faster. We are sacrificing reliability of unlogged tables for better performance.

Unlogged Tables in MonetDB

I will login as administrator. I will change current schema to "voc".

monetdbd start /home/fffovde/DBfarm1 mclient -u monetdb -d voc   –password monetdb SET SCHEMA voc;

This is how we create unlogged table in MonetDB. CREATE UNLOGGED TABLE UnLogTab ( Number INTEGER ); INSERT INTO UnLogTab ( Number ) VALUES ( 1 ); SELECT * FROM UnLogTab;

If we quit our session, and we log in again, we will still be able to use our unlogged table. quit mclient -u monetdb -d voc   SELECT * FROM voc.UnLogTab;

Both the data and the table structure will be preserved. Unlogged table can last through several sessions.

I will create another session as a "voc" user.	mclient -u voc -d voc   –password voc
This other session will also be able to read and modify UnLogTab table. UPDATE UnLogTab SET Number = 2; SELECT * FROM UnLogTab;

Sample Table

I will create one normal sample table in the "voc" schema, from the admin session: CREATE TABLE voc.Tab1 ( Letter VARCHAR(10) ); INSERT INTO voc.Tab1 VALUES ( 'A' ); SELECT * FROM voc.Tab1;

What Happens After the Crash?

I will exit both sessions from above. We are now in the shell. The number of mserver5 process is 1335. pgrep mserver5        –process 1335 I will kill this process. kill -9 1335

We have now violently closed our server.

I will again start my "voc" database and I will log in as administrator. monetdb start voc mclient -u monetdb -d voc

If we now read from our table, our table will be empty. Because of crush, MonetDB truncated this table. SELECT * FROM voc.UnLogTab;

But, if we read from the sample table, everything will be fine:   SELECT * FROM voc.Tab1;

Difference Between MonetDB and Some Other Servers

I will again add some data to UnLogTab: INSERT INTO voc.UnLogTab ( Number ) VALUES ( 1 ); SELECT * FROM voc.UnLogTab;

This time I will restart my server gracefully. quit monetdb stop voc monetdb start voc mcient -u monetdb -d voc    –password monetdb

Our UnLogTab will be empty. This is opposite from some other servers, where regular restart doesn't remove the content of the unlogged tables. In MonetDB, the content will be lost. SELECT * FROM voc.UnLogTab;

ALTER TABLE Tab1 SET UNLOGGED; ALTER TABLE UnLogTab SET LOGGED;

Unlike some other databases, MonetDB doesn't have ability to transform unlogged tables to logged tables and vice versa. These statements will not work.

System Tables

We can find information about unlogged tables in the sys.tables and sys.statistics.

SELECT * FROM sys.tables WHERE name = 'unlogtab';

SELECT * FROM sys.statistics WHERE table = 'unlogtab';

In the system table sys.table_types we can see that unlogged tables are of the type 7. SELECT * FROM sys.table_types;