Managing security server message logs

If your X-tee V6 or message log database volume and disc space usage have grown larger than you initially planned and the disc space volume keeps on growing, the problem probably lies in the default settings of archiving message logs not being suitable for your security server. The X-tee v6 security server provides an option for automating message logs archiving and cleaning, and, should you wish so, also transferring them to another machine to keep disc space under control.

Archiving message logs consists of 4 consecutive processes that are launched by configured intervals:

1. Archiving entries from the database table into zip packages
2. Cleaning or deleting entries from the database table
3. Transferring archive packages (if requested)
4. Freeing up empty space in the database that has not been freed up

The first step is to gather entries from the message log table of the database with a time stamp and packing them up into a zip file, and adding a note to every archived entry in the database table that the entry has been archived.
After archiving, table cleaning is activated to delete all entries that are older than 30 days (by default) and have a note that they have already been archived from the table.
If you wish to transfer the archive packages into some other machine or drive, you can use an assisting script that uses a HTTP/HTTPS protocol to transfer the packages.
The last step is freeing up empty space in the database that has not been freed up (VACUUM), which is carried out by the database itself on the background. Freeing up empty space is the last step that frees up the space used for storing data.

Message log configurations and parameters

The default configurations of archiving the message log are located in the file /etc/xroad/conf.d/addons/message-log.ini.
If the size of your database has grown too big, usually the first reason is that the default settings are not suitable.
If you wish or need to change the default settings, you can do it in the file /etc/xroad/conf.d/local.ini, which ignores default settings.

Message log archiving, cleaning, and transferring archive packages, should you wish to do so, is launched by the Cron tool.
By default, message logs that are 30 days old are kept in the database.
By default, the archiving interval is 0 0 0/6 1/1 * ? *, which means that archiving is launched every 6 hours, starting from the beginning of the day or 00.00 (12 a.m.).
By default, the cleaning interval is 0 0 0/12 1/1 * ? *, which means that cleaning is launched every 12 hours, starting from the beginning of the day or 00.00 (12 a.m.).

There are different parameters for configuring the message log archiving, with which it is possible to configure archiving in accordance with the needs of your security server.

keep-records-for – period in days for how long the archived message logs are kept in the security server. By default, it is 30 days.

archive-max-filesize – the maximum size of a message log archive pack in bytes. By default, it is 32 MB or 33,554,432 bytes.

archive-interval – archiving interval used by the Cron tool to launch the message logs archiving process. By default, it is every 6 hours.

archive-path – catalogue into which the archived message log packages are placed. By default, this is /var/lib/xroad/.

clean-interval – database cleaning interval used by the Cron tool to launch cleaning message logs in the database table logrecords. By default, it is every 12 hours.

archive-transfer-command – command for transferring archived message logs launched at the end of the archiving process. By default, this functionality is not turned on.

archive-transaction-batch – maximum quantity of message logs timestamped at once. By default, this is 10,000.

More information - https://github.com/nordic-institute/X-Road/blob/develop/doc/Manuals/ug-syspar_x-road_v6_system_parameters.md#37-message-log-add-on-parameters-message-log

As the message log is one of the attachments to xroad-proxy, it is necessary to relaunch it after making changes in the /etc/xroad/conf.d/local.ini, for example, using the command

# starting from the X-Road v7.0:
sudo service xroad-addon-messagelog restart

# all older versions:
sudo service xroad-proxy restart

Example of the contents of the /etc/xroad/conf.d/local.ini file that enables to archive message logs that are older than 1 day. The message log is archived every 15 minutes and cleaned every 30 minutes.

$ cat /etc/xroad/conf.d/local.ini
[message-log]
keep-records-for=1
archive-interval=0 0/15 * 1/1 * ? *
clean-interval=0 0/30 * 1/1 * ? *

Transferring archived packages

There is a script that helps to transfer archived packages into another machine or disc.

/usr/share/xroad/scripts/archive-http-transporter.sh

For that, the following parameter needs to be added to the message log configuration file /etc/xroad/conf.d/local.ini:

archive-transfer-command=/usr/share/xroad/scripts/archive-http-transporter.sh -r http://my-archiving-server/cgi-bin/upload

This script uses the HTTP/HTTPS protocol and enables to transfer archived packages, for example, into a separate archiving machine. By using the -r command, the message log archive packages that have already been transferred from the security server are deleted to free up disc space.

You can find more information about transferring archive packages and using flags here:
https://www.x-tee.ee/docs/live/xroad/ug-ss_x-road_7_security_server_user_guide.html#112-transferring-the-archive-files-from-the-security-server

Time-stamping message logs and studying them in the database

All message logs are placed in the security server into the Postgres database messagelog. By default, a time stamp is added to up to 10,000 message logs every 48 minutes. If the message logs have a time stamp, they can be archived. If there are more than 10,000 new message logs without a time stamp, these will remain without a time stamp, and a time stamp will be added to them during the next time-stamping in priority order.

All message log entries in the security server are located in the database table logrecord.
In order to access the table, it is necessary to log into the Postgres database with the user messagelog.
The default messagelog user password is located in the file /etc/xroad/db.properties 

$ psql -h 127.0.0.1 -U messagelog messagelog

To view all columns of the logrecord table: 

messagelog=> \d logrecord

For every entry, the table logrecord has an archived parameter, the default value of which is ‘false’. When the entry is timestamped and is archived, it will be changed to ‘true’. All entries, the archive parameter value of which is ‘true’ will be removed during the cleaning process.

In order to count the message logs that are not timestamped, the following order can be used. If the returned number is more than 10,000, there is a problem with time-stamping, i.e. there is a lot of traffic in the security server and more than 10,000 message logs are created every 48 minutes. As only timestamped message logs can be archived, these will remain in the security server. In a longer period of time, this may cause the disc space of the database to grow.
As a solution, we wish to increase the parameter timestamp-records-limit in the message log in the configuration file /etc/xroad/conf.d/local.ini; by default, this is 10,000. In such a case, more than 10,000 message logs are timestamped every 48 minutes.

messagelog=> select count(1) from logrecord where discriminator::text = 'm'::text and signaturehash is not null;
count        
-------   
275 
(1 row)

In order to find out the last message log without a time stamp, the following command can be used:

messagelog=> select to_timestamp(min(time)::float/1000) from logrecord where discriminator::text = 'm'::text and signaturehash is not null;  
to_timestamp               
----------------------------       
2018-05-18 09:14:45.825+03
(1 row)

In order to count message logs that are time-stamped, but have not yet been archived, the following command can be used:

messagelog=> select count(1) from logrecord where timestamprecord in (select id from logrecord where discriminator::text = 't'::text and archived = false);       
count        
-------       
802      
(1 row)

In order to find out the last time-stamped, but not yet archived message log, the following command can be used. If the returned date is older than, for example, 30 days (default parameter keep-records-for), it means that there is something wrong with archiving. It is highly probable that the archiving interval should be reviewed and increased, if necessary.

messagelog=> select to_timestamp(min(time)::float/1000) from logrecord where timestamprecord in (select id from logrecord where discriminator::text = 't'::text and archived = false);
to_timestamp              
----------------------------        
2018-05-18 06:50:45.854+03        
(1 row)

Empty space in the database that has not been freed up

If archiving message logs works, but the volume of the database is still too big, the problem may be in the empty space of the database that has not been freed up. By default, Postgres databases implement AUTOVACUUM or freeing up empty space that fully deletes removed rows from the database tables and thereby also frees up the space that was used for the data. Unfortunately, AUTOVACUUM has many errors and systems with a high number of data requests may experience problems with automatically freeing up empty space. If there is a lot of empty space that has not been freed up, it is useful to carry out a VACUUM FULL in the database. A big problem with this operation is that the tables will be locked and so it is impossible to use the database table at the same time. For the functioning of the security server, this means an interruption.

In order to find out how much empty space that has not been freed up there is, you firstly need to log into the database. 

# su - postgres

Then select the database messagelog and create the extension pgstattuple.

$ psql -d messagelog

messagelog=# create extension pgstattuple;

A good idea is to check the current situation of the message log table logrecord at first.

messagelog=# SELECT * FROM pgstattuple('logrecord');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 

-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------

 106192896 | 53390 | 73798005 | 69.49 | 3009 | 4335848 | 4.08 | 27163284 | 25.58

(1 row)

Then you should check the current situation of the toast table (the toast table is used for keeping large entries).

messagelog=# SELECT * FROM pgstattuple('pg_toast.'||(select relname from pg_class where oid = (select reltoastrelid from pg_class where relname = 'logrecord')));
    
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 

------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------   

 3872366592 | 1743879 | 3103020219 | 80.13 | 259348 | 465236384 | 12.01 | 280865112 | 7.25

(1 row)

The results of the first inquiry show that there is 27 MB or 25.58% of empty space that has not been freed up in the logrecords table. The results of the second inquiry show that there is 280 MB or 7.25% of empty space that has not been freed up in the toast table.
In this case, it is not reasonable to carry out VACUUM FULL. If the level of empty space that has not been freed up is critical, e.g. 80%, you should consider using VACUUM FULL.

Using VACUUM FULL

Before using VACUUM FULL, you certainly need to consider whether it is necessary and when to do it as this process locks the database table, which means that the table cannot be used until the process is complete. For the functioning of the security server, this means an interruption.

At first, you need to log into the messagelog database as a messagelog user

# psql -h 127.0.0.1 -U messagelog messagelog

Launching VACUUM FULL on the logrecords table.

messagelog=# VACUUM FULL logrecord;

This process can take several hours. During that period, changes in the volume of the database can be monitored, for example, with the command:

$ df -h

The database processes can also be monitored with the command: 

$  sudo -i -u postgres psql -c "select * from pg_stat_activity;"

All different parameters and more detailed information regarding the message log can be found in chapter 11 of the security server user guide that describes the message log and its usage options.
https://www.x-tee.ee/docs/live/xroad/ug-ss_x-road_7_security_server_user_guide.html#11-message-log