7.2 Monitoring and Tuning

A backup is a full or partial copy of the information in a database, held in a physically separate location. You should make regular backups of the ZENworks database. If the database becomes unavailable, you can restore it from the backup.

Backing up a running database provides a snapshot of the database where the data is in a consistent state, even though other users are modifying the database.

If the operating system or database server fails, or if the database server does not shut down properly, then the database must be recovered. On database startup, the database server checks to see if the database was shut down cleanly at the end of the previous session. If it was not, the database server runs an automatic recovery process to restore all changes up to the most recently committed transaction.

For detailed information, see Embedded Database Maintenance in the ZENworks Database Management Reference.

The rest of this section describes the different types of backups and how to back up the database:

PostgreSQL does not include built-in commands for database validation. However, to help maintain the validity of the database, we recommend that active production databases be vacuumed frequently (at least nightly) in order to remove dead rows.

After adding or deleting a large number of rows, it is a good idea to issue a VACUUM ANALYZE command for the affected table. This command updates the system catalogs with the results of all recent changes and allows the PostgreSQL query planner to make better choices in planning queries.

For information about using the VACUUM commands, see Table Fragmentation.

The amcheck module provides functions that allow you to verify the logical consistency of the structure of relations. If the structure appears to be valid, no error is raised.

The pageinspect module provides functions that allow you to inspect the contents of database pages at a low level, which is useful for debugging purposes. All of these functions may be used only by superusers.

pg_resetwal resets the write-ahead log and other control information of a PostgreSQL database cluster.For more information, see, amcheck on the PostgreSQL website.

Tuning the database parameters listed below can improve the performance of ZENworks. The postgresql.conf file contains the parameters and is located in the following locations:

Restarting the PostgreSQL service is necessary after changing some parameters. If a restart is necessary, it is called out in the parameter description.

PostgreSQL file logging is enabled and customized via postgressql.conf parameters. If you are using an external PostgreSQL database, file logging is turned on by default. If you are using the embedded PostgreSQL database, logging is turned off.

The following sections explain how to enable\disable file logging and customize the logging parameters. For complete information about logging parameters, see Error Reporting and Logging on the PostgreSQL website.

log_filename = log.%a
log_rotation_age = 1d
log_truncate_on_rotation = on

On a Linux server, if you need to move the database files, such as to a new separate physical disk, this operation is as simple as a folder copy. When the folder has been moved, take a backup, and then modify the zenpostgresql file’s POSTGRES_DATADIR parameter to point to the new location. The file is located in /opt/novell/zenworks/share/pgsql/sysconfig/

On a Windows server, unregister the PostgreSQL service by using following command:

"C:\Program Files (x86)\Novell\ZENworks\share\postgres\bin\pg_ctl.exe" unregister -N "Novell ZENworks Embedded Datastore - PostgreSQL"

Re-registrer the service again with the new data folder location:

"C:\Program Files (x86)\Novell\ZENworks\share\postgres\bin\pg_ctl.exe" register -N "Novell ZENworks Embedded Datastore - PostgreSQL" -U "NT AUTHORITY\NetworkService" -D "C:\Program Files

The primary tool for monitoring database activity and analyzing performance is the PostgreSQL statistics collector. The statistics collector provides a rich set of views and functions for collecting and reporting information about server activity. This information falls into two main categories:

This section lists a few of the ways you can use the statistics collector. However, for detailed information, you should refer to Monitoring Database Activity on the PostgreSQL website. The website also explains how to use regular Linux monitoring programs such as ps, top, iostat, and vmstat. In addition, you can use the pg_stat_statements module to track executation statistics for all SQL statements executed by the server.

On Windows, you can use performance monitoring tools such as Process Monitor, Process Explorer, and FileMon. For more information about both Windows and Linux tools, see Performance Analysis Tools on the PostgreSQL Wiki site.

SELECT
     p.pid,
     now() - a.xact_start AS duration,
     coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
     CASE
       WHEN a.query ~ '^autovacuum.*to prevent wraparound' THEN 'wraparound'
       WHEN a.query ~ '^vacuum' THEN 'user'
       ELSE 'regular'
     END AS mode,
     round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
     round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
     p.index_vacuum_count,
     round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;

Fragmented and bloated indexes are a top reason for performance degradation of the ZENworks database. A bloated index contains many empty or nearly-empty pages. This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns.

Micro Focus recommends that you check the indexes and rebuild in the following scenarios:

You can use the REINDEX command to rebuild the indexes. For detailed information, see REINDEX on the PostgreSQL website.

PostgreSQL uses Multi-Version Concurrency Control to manage concurrent access to data. With this approach, reads don’t block writes because INSERT and UPDATE operations create a new version of the row every time. But these operations don’t immediately remove the old version of the row. Instead, old versions of rows are eventually removed by the VACUUM operation.

To check for table fragmentation that can be caused by this process, use the following query:

SELECT * FROM pgstattuple('public.zzenobject');

For more information about the pgstattuple command, see pgstattuple on the PostgreSQL website.

When fragmentation exists, you can use the VACUUM command to reclaim space still used by data that had been updated. In PostgreSQL, updated key-value tuples are not removed from the tables when rows are changed, so the VACUUM command should be run occasionally to do this.

VACUUM can be run on its own, or with ANALYZE. Common commands and examples are:

For more information about the VACUUM command, see VACUUM on the PostgreSQL website.

At its basic level, PostgreSQL is one giant append-only log. When you insert a new record, the new record is appended. When you delete a record, the record is simply flagged as invisible, it’s not actually removed from disk immediately. When you update a record, the old record is flagged as invisible and a new record is written.

If fragmentation exists on numerous tables and the database is large, a faster method for fixing fragmentation is to rebuild the entire database by using the following PostgreSQL applications:

A few tips when rebuilding the database:

If you encounter problems with your database, collect the following information before engaging with Micro Focus Support:

PostgreSQL supports database mirroring as a method to implement high availability and fault tolerance. This technique requires multiple database servers and is therefore not set up by default with ZENworks.

For more information, see High Availability, Load Balancing, and Replication on the PostgreSQL website.