11 EMM Administration

11.1 EMM JobQueue Monitoring

The EMM JobQueue is a component of the GUI service and executes several cron-job-like background processes of EMM, called JobWorkers. You can see a subset of the JobQueue data in the GUI of EMM: Log into the master tenant (company ID 1) and go to menu Administration → Sub menu System Status → tab Show Jobqueue for details. This view is helpful to check the status of those JobWorkers.

You should make sure that you have configured an email address for error messages (see DB info below), because some JobWorkers like AutoOptimization or WorkflowReactionHandler take care of automated processes and should always be active. For other JobWorkers it makes sense to check their status at least from time to time, because some hanging jobs like cleanup processes will have no immediate effects but can drag down performance of EMM over time.

If you do not want to check the status of EMM jobs in the frontend, you can also access the EMM database directly. For MariaDB, the SQL statement is

     SELECT id, description, lastresult, running, nextstart, laststart
     FROM Job _queue_tbl
     WHERE deleted = 0 AND
     ( (lastresult != 'Ok') -- has errors
       OR (nextstart < CURRENT_TIMESTAMP() - INTERVAL 10 MINUTE) -- overdue
       OR ( (running = 1) AND
       (laststart < CURRENT_TIMESTAMP() - INTERVAL 4 HOUR)) -- (too) long running
     );

     SELECT id, description, lastresult, running, nextstart, laststart
     FROM Job_queue_tbl
     WHERE deleted = 0 AND
     ( (lastresult != 'Ok') -- has errors
       OR (nextstart < sysdate - 0.01) -- overdue
       OR ( (running = 1) AND
       laststart < sysdate - 0.16 )) -- (too) long running
     );

Table job_queue_tbl lists all JobWorkers periodically executed by EMM's JobQueue. You can control the execution times of the cleanup jobs via their entries in job_queue_tbl because the mass deletion of information can place serious strain on your database resources. Here are some information on the data fields of the job_queue_tbl:

• ID: id of job
• CREATED: creation date of job entry
• LASTSTART: last time the job was started
• RUNNING: status of execution (>0 = currently running)
• LASTRESULT: result of last execution (OK or NULL if no errors happend, or error message)
• STARTAFTERERROR: flag for start after error (>0 = yes)
• LASTDURATION: run time of latest execution of the job in milli seconds
• NEXTSTART: next time the job will launch
• RUNCLASS: Java class triggered by the job (example: org.agnitas.util.quartz.DBCleanerJobWorker executes the DB cleanup job)
• EMAILONERROR: email address to notify in case of an error in a job
• DELETED: flag to indicate if this job was deleted (>0 = deleted)
• INTERVAL: schedule for job execution, some format and examples:
• Format: ***M, example: **00 (daily execution on the hour)
• Format: ***0;***M, example: ***0;***5 (execution every 5 minutes)
• Format: HHMM, example: 1200 (daily execution at noon)

• Format: DaDaDa:HHMM, example: Su:1200 (execution on Sundays at noon) or MoTuWeThFr:1800 (execution from Monday to Friday at 18:00)

• If you have any questions regarding the JobQueue settings or if you want to modify settings of certain jobs, please contact the AGNITAS support team at support@agnitas.com.

11.2 Automatic File Cleanup

11.2.1 Frontend GUI Service

The GUI service of EMM stores all files for upload, import and export as well as the resulting report files in the file system in subdirectories of directory /home/console/temp. This directory should be periodically cleaned by shell script tempcleaner.sh. To ensure the daily processing of this cleanup script, it should be executed by a crontab entry like this:

0 1 * * * /home/console/bin/tempcleaner.sh

11.2.2 Backend Service Merger

The following directories of the merger service are cleaned by a daily cronjob (all given paths are relative to the home directory of the service user):

• var/log/: log files of processes of the merger service, will be compressed after 2 days and deleted after 90 or 180 days (depending on the type of data logged)
• log/done/: source files for bounce management, will be deleted after 30 days
• var/spool/META/: XML files for mailings to admin and test recipients, will be moved to var/spool/ADMIN/ after 2 days
• var/spool/ADMIN/: XML files for mailings to admin and test recipients older than 2 days, will be deleted after 30 days
• var/spool/DELETED/: XML files of mailings, which were deleted after generation but before sending, will be deleted after 30 days
• var/spool/ARCHIVE/: XML files successfully transfered to mailer services, sorted into daily sub directories, sub directories will be compressed into tarballs after 7 days and deleted after another 30 days
• var/spool/postfix/store: copies of mails sent to the merger service, will be compressed after 7 days and deleted after 30 days

11.2.3 Backend Service Mailer

The following directories of the merger service are cleaned by a daily cronjob (all given paths are relative to the home directory of the service user):

• ARCHIVE/: XML files processed successfully, sorted into daily sub directories, sub directories will be compressed into tarballs after one day and deleted after another 2 days
• var/log/: log files of processes of this mailer service, will be compressed after 2 days and deleted after 40 or 120 days (depending on the type of data logged)
• var/spool/postfix/store/: copies of mails sent to this mailer service, will be compressed after 2 days and deleted after 4 days

11.2.4 Backend Service Mailloop

The following directories of the mailloop service are cleaned by a daily cronjob (all given paths are relative to the home directory of the service user):

• var/log/: log files of processes of the maillop service, will be compressed after 2 days and deleted after 40 days
• var/spool/filter/: copies of mails sorted by type of filtering, after one day files will be moved to daily sub directory, sub directories will be compressed into tarballs after 30 days and deleted after another 180 days
• var/spool/postfix/store/: copies of mails sent to the mailloop service, will be compressed after 7 days and deleted after 30 days

11.3 Automatic Database Cleanup

Over time the EMM database will continue to grow. To limit the growth of its database, EMM provides an automatic cleanup of some database tables to prevent the EMM database from becoming too big.

This is a list of tables which are cleaned up automatically by EMM:

* Please read the following paragraphs for details of conditions that lead to deletions

The bounce management of EMM stores all bounce related data in the database. However, it is not necessary to store bounce information forever. Therefore, table bounce_tbl is cleaned up daily by the backend merger service from all entries older than 90 days, table bounce_collect_tbl is cleared daily during softbounce processing and information in table softbounce_email_tbl is kept for a maximum of 180 days.

A JobWorker named DBCleaner is responsible for the cleanup of some more tables. The more important ones are:

• access_data_tbl: this table is deleted daily after examination of its content
• customer_<cid>_binding_tbl: all pending bindings (subscribers who did not confirm their double opt-in mail, status "waiting for confirmation") are removed after 30 days so that it is possible for them to start the subscription process again at a later time
• dyn_content_tbl and dyn_name_tbl: all entries which are no longer referenced by a mailing for at least 60 days are removed from these tables
• login_track_tbl: all entries older than 60 days are removed
• onepixellog_<cid>_tbl and onepixellog_device_<cid>_tbl: all entries older than 1,000 days are removed
• undo_component_tbl, undo_dyn_content_tbl and undo_mailing_tbl: all entries older that 60 days are removed from the tables of the undo feature

Expression <cid> is a placeholder for the company ID (usually starting with 2 for the first tenant).

Entries in mailtrack and success tables are removed automatically after 180 days. To change the cleanup period for these (big) tables, use this SQL statement:

INSERT INTO company_info_tbl (company_id, cname, cvalue, description, creation_date,timestamp) VALUES (<cid>, 'expire.success', '<period_in_days>', '<comment>', current_timestamp, current_timestamp);

The cleanup of some tables can be configured by these entries in file emm.properties of the GUI service in directory /home/console/webapps/emm/WEB-INF/classes:

expireStatMax=1000
expireStatDef=1000
expireOnePixelMax=1000
expireOnePixelDef=1000
expireSuccessMax=1000
expireSuccessDef=180

Please be aware: After the retention time for opening tracking is expired, all affected entries in tables onepixellog_<cid>_tbl and onepixellog_device_<cid>_tbl are deleted and all affected entries in success_<cid>_tbl are deleted.

But after the retention time for mailings is expired, mailings are not deleted but only flagged as deleted, i.e. field deleted is set to 1. Additionally, a CSV file containing statistical data of the expired mailings is send to the email address of the account administrator.

The next section provides information on how to really remove mailings from the database.

11.4 Manual Database Cleanup

If, despite the cleanup processes described above, at some point in time your database becomes simply too big (and because of it, too slow!), you should delete old mailings and related statistical data from the database (if you delete a mailing in the GUI it is only set to status “deleted”). We recommend to delete entries from these 10 tables:

Unless otherwise noted use the field mailing_id to identify the entries to be deleted from each table.

If you want to delete mailings not set to status deleted, make sure to not delete date-based, event-based or interval mailings (mailing_type <> 0) without checking first if they are still in use.

If you do not want to delete templates, make sure that field is_template is set to 0. And if you want to make sure to only delete sent mailings (because you might still work on the unsent ones), check field status_field of table mailing_account_tbl for value “W” to identify those mailings. Bringing it all together in one SQL statement:

     SELECT mailing_id FROM mailing_tbl
     WHERE creation_date LIKE '2023%'
     AND mailing_type = 0
     AND (
      is_template = 0
      AND mailing_id IN (
        SELECT mailing_id FROM mailing_account_tbl WHERE status_field = 'W'
      )
      OR deleted = 1
    );

     CREATE TABLE tmp_mailing_delete AS SELECT mailing_id FROM mailing_tbl
     WHERE creation_date LIKE '2023%'
     AND mailing\_type = 0
     AND is\_template = 0;

     DELETE FROM tmp_mailing_delete
     WHERE mailing_id NOT IN (
       SELECT mailing _id FROM mailing _account _tbl WHERE status _field = 'W'
     );

       INSERT INTO tmp_mailing_delete (
       SELECT mailing_id FROM mailing_tbl
       WHERE creation_date LIKE '2023%'
       AND mailing_type = 0
       AND deleted = 1
     );

creation\_date LIKE '2023%'

11.5 Database Backup

For Oracle there exist plenty of strategies for database backups and tons of books and Internet resources on that subject. Therefore, we will not delve into this subject.

The same is valid for backups of MariaDB databases. However, if you run only a medium MariaDB database with a few GByte of data and if you can live with an interruption of services of very few minutes, you may simply use tool mysqldump:

mysqldump -aCceQx --hex-blob --routines --triggers -u root -p -r emm.sql emm

mysql -u root -p emm < emm.sql

If you run a bigger MariaDB database which should not be stopped during backup time, we recommend the use of the tool Percona XtraBackup.

11.6 Generic Database Tuning

80% of all application performance problems are really database performance problems. If you run a big EMM installation and you are not satisfied with the application's performance, here are some database tuning tips you should try.

If certain tenants of your EMM database hold a long list of recipients, you may speed up database operations like calculating statistics significantly with a combined index on several fields of table customer_<tenant>_binding_tbl.

We recommend the following two indices in case they do not exist yet:

CREATE INDEX custbind<tenant>$mlid_user_cuid$idx ON customer_<tenant>_binding_tbl (mailinglist_id, user_status, customer_id);

CREATE INDEX custbind<tenant>$user_mlid_cuid$idx ON customer_<tenant>_binding_tbl (user_status, user_type, mailinglist_id, customer_id);

If you use any other recipient profile field than email for duplicate checks in imports, you should put an index on this field in customer_<tenant>_tbl to significantly speed up file imports:

CREATE INDEX cust<tenant>$<fieldname>$idx ON customer_<tenant>_tbl (<fieldname>);

11.7 MariaDB Database Tuning

InnoDB is the default storage engine of MariaDB. While InnoDB supports row locking and real transactions for better crash protection, the internal data structure is more complex compared to simpler storage engines like MyISAM. This leads to larger table sizes, slower writes, slower full table scans and slower handling of BLOBs and CLOBs. Also, backup and recovery via mysqldump/mysql is slower.

To get the best performance from MariaDB and because the configuration parameters of storage engine InnoDB can make a big difference, you should define at least the following properties in section [mysqld] of MariaDB's configuration file my.cnf (usually found in directory /etc):

• innodb_buffer_pool_size: default value is only 128 MByte, which is way too small for bigger databases with lots of InnoDB tables. If your EMM database runs on a dedicated server, innodb_buffer_pool_size should be set to 50% of the RAM size of this DBMS server if this server has less than 32 GByte RAM. If the DBMS server has 32 GByte RAM or more, set innodb_buffer_pool_size to 75% of the server RAM size.
• innodb_log_file_size: default value is 96 MByte. Its value should be set to 25% of the size of innodb_buffer_pool_size, but not higher than 256 MByte to limit recovery time after a database crash.
• innodb_file_per_table: By default the InnoDB engine saves all table data into system tablespace file ibdata1 in directory /var/lib/mysql/. Set innodb_file_per_table to value 1 to force InnoDB to create a separate file for each database table.
• innodb_lock_wait_timeout: Default value is 50 (seconds). For big databases this value is too small. We recommend to use 1800 (30 minutes) or even 3600 (60 minutes) for this value.

• innodb_rollback_on_timeout: When the timeout defined in innodb_lock_wait_timeout is reached, by default only the blocking statement is rolled back, not the entire transaction. This could lead to data inconsistencies. Therefore, we recommend to set innodb_rollback_on_timeout to value 1 to enforce rollbacks of the entire transactions.

• Restart MariaDB to activate these changes.

You can check if the new values are active with

SHOW VARIABLES LIKE 'innodb_%';

If you want to know more about the InnoDB configuration parameters, we recommend to have a look at https://mariadb.com/kb/en/innodb-system-variables.

11.8 Identifying MariaDB Performance Bottlenecks

Let's assume that a slow SQL statement is blocking access to your database. To find out this statement, define and configure a slow query log in section [mysqld] of MariaDB's configuration file my.cnf (usually found in directory /etc/):

slow_query_log
slow_query_log_file = <path of log file>/slow_query.log
long_query_time = 60

If you do not want to restart MariaDB, you can set the values with

SET global slow_query_log = 1;
SET global slow_query_log_file = '<path of log file>/slow_query.log';
SET global long_query_time = 60;

11.9 EMM Recovery after Database Downtime

If the EMM database was down or the database connection was lost (for whatever reason), please follow these steps to achieve the maximum data integrity:

1. Stop EMM services on all servers. If the database downtime was planned, stop the EMM services before database shutdown, of course.

The best sequence would be to stop EMM servers running redirect services first and then to continue with the server running the GUI service, which also stops statistics service and (optional) webservices. After that you should stop the server running merger service, all mailer services and finally the mailloop service.

On each server the services should be stopped by executing

/root/bin/agnitas.sh stop

1. After the EMM database is up and running, re-start all EMM services on all EMM servers with

/root/bin/agnitas.sh start

11.10 EMM Recovery After Loss of Connection to Mail Servers

If the merger service loses the connection to its mail servers, it can not transfer meta mail packages to the mail servers any longer and, therefore, no mailings can be send out. If this happens in the midst of sending out mailings and re-establishing the connection does not re-activate the sendout, you should restart the backend of EMM:

1. stop the merger service
2. stop all mailer services
3. start all mailer services
4. start the merger service

If even a restart does not help, you have to manipulate the files of the meta mail packages manually:

Any file with extension SYNC in directory /home/mailout/INCOMING/ indicates that the processing of a package has not been completed successfully, because a SYNC file registers records already processed, and it is only deleted after the successful completion of a package processing.

If SYNC files exist, do not delete them, but check out directory /home/mailout/RECOVER/.

su - mailout
cd /home/mailout/RECOVER

for i in `ls`; do touch 00-$i;done

mv /home/mailout/RECOVER/* /home/mailout/INCOMING/

11.11 Stopping the Sending in Case of Emergency

To stop the sending of a certain mailing, it is important to understand the process of its generation, distribution and dispatch:

Meta mail packages are generated by the merger service. These files are located in directory /home/merger/var/spool/META/ and one part of the file name is the ID of the mailing. If files with the ID of the mailing to be stopped, are removed, they are no longer distributed by the merger to the mail server for dispatch. However, to avoid internal conflicts, you have to stop process npickup before deleting the files with

/home/merger/bin/npickup.sh stop

/home/merger/bin/npickup.sh start

ps aux | grep xmlback

/home/mailout/bin/distribute.sh stop

/home/mailout/bin/distribute.sh stop

Please be aware that the statistics for a certain mailing in the GUI of EMM may not be correct if you manually delete meta mail packages of this mailing or email files of this mailing from the MTA queues.

11.12 Out of Memory

If you work with big lists and experience an error message like

Java.lang.OutOfMemoryError: Java heap space

11.13 Log Rotation

To prevent the Tomcat log from filling up the hard disk of the servers running frontend services or redirect services of EMM, you may install a log rotation to get rid of old log files. Create file tomcat-console in directory /etc/logrotate.d/ with this suggested content:

/home/console/logs/catalina.out {
  copytruncate
  daily
  rotate 7
  compress
  dateext
  size 10k
  missingok
  sharedscripts
  postrotate

    # zip files older than 180 min and delete access_logs older than 90 days
    find /home/console/logs/access -name "*.log" -mmin +180 -exec gzip -9 {} \;
    find /home/console/logs/access -name "*.log.gz" -mtime +90 -exec rm {} \;
    find /home/console/logs -name "*.gz" -mtime +10 -exec rm {} \;
    find /home/console/logs -name "*manager*.log" -mmin +180 -exec rm {} \;
    find /home/console/logs -name "localhost*.log" -mmin +180 -exec rm {} \;

    # delete files older than 5 days in logs/webapps/
    find /home/console/logs/webapps -type f -mtime +5 -exec rm {} \;

    # Delete old ARCHIVES > 10 days
    find /home/console/var/spool/ARCHIVE -mindepth 1 -type d -mtime +10 2>/dev/null | xargs -r rm -fr

    # Delete old logfiles from backend
    find /home/console/var/log -name "*.log" -mtime +30 -exec rm {} \;

  endscript
}

11.14 Changing Security-Related Files

EMM passwords are saved in the database not only encrypted but salted as well. The salt file with the file extension salt is located in directory /home/console/conf/keys/ of all servers running frontend services (GUI service, statistics service, webservices and redirect service).

If you want to change the salt file, please do it before you start operating EMM, because otherwise all saved passwords will not work any longer. For generating a new salt just save a string of letters, digits and other characters of the ASCII character set (decimal values 33 to 126) with a maximum length of 32 characters in a simple text file and name the new file like the old salt file. You may change the file name in configuration file emm.properties of the corresponding service.

To prevent access of the statistics service by a random server, the statistics service is accessible via HTTPS protocol only, uses a private key and grants access only to those servers providing the corresponding public key. This public key is provided by the GUI service. The private key in file birt_private.pem is located on the server running the statistics service in directory /home/console/conf/keys/, and the corresponding public key in file birt_public.pem is located on the server running the GUI service in directory /home/console/conf/keys/. Therefore, if both services run on the same server, both files are located in the same directory.

If you think that the keys are not safe (enough) for your purpose, you may replace them and restart the affected services.