Improving ZCM Database Performance by Monitoring and Limiting Table Growth

A key item in maintaining peak database performance is detecting and resolving any excess growth of various database tables.  In general, the ZCM database should not exceed approximately 1GB per 1,000 devices for a basic setup.  If the database significantly exceeds 1GB per 1,000 devices then it should be examined for possible bloat, though in some cases it may significantly exceed this and still be normal but the reasons for the excess size should be readily apparent.  (Note:  The Windows process recording feature of ZAM and Patch Management Dashboard data retention are two features that will cause the 1GB per 1,000 devices to be easily exceeded.)

TID 7006931 - How to get table size information for supported databases provides Details on how to obtain the sizes of the various tables, which will help provide insight to which tables may be excessively large.  The results of these queries can help provide focus when reviewing the various common causes of database growth and their resolutions discussed below.

No attempts to manually clean up any tables should ever be performed without first ensuring a proper backup exists!

Also no attempts to manually clean up any tables should be performed unless the exact procedures have been presented in either a TID or directly from Novell Support as part of a specific Service Request. 

Limiting the number of client log events uploaded to the database can significantly control database size and overhead.  This is controlled in the ZCC under “Local Device Logging” with the setting “Send message to local system log and roll up to Collection Server if severity is…”  This will cause any event logged locally with that level severity or higher to be uploaded to the database and be available for review in the ZCC.  Since ZCM tends to perform extensive logging, it is recommended to limit what is uploaded to the server to prevent information overload in the ZCC, while the information will still be retained locally for troubleshooting purposes if necessary.  

 

The second area involves ensuring that the client events uploaded to the server are then automatically acknowledged and cleared after a reasonable amount of time; otherwise they could compose a preponderance of the database itself.  This process is controlled by the “Automatic Message Cleanup Process”.  

 

The third area will retain the theme of event based logging.  By default, ZCM will store the “System Requirement” evaluations for each bundle on each device for each user on that device.  For a traditional work-place environment that has approximately one user per device, if 100 bundles are assigned to 10,000 users on 10,000  machines it would result in about 1,000,000 entries in the database.  In a school environment where a large number of students may use a large number of different computers the numbers could grow quite significantly.  100 Bundles assigned to 10,000 users on 10,000 machines with 100 different users on each machine would result in 100,000,000 entries in the table.  There are two different ways to minimize the growth of system requirement evaluations in the database.  The first method would be to minimize the system requirements on the bundles themselves.  If there is not a system requirement on the bundle itself, a record will not be stored for that bundle.  In ZCM 11.1 and higher, system requirements can be set on the individual actions inside of the bundle.  Action level system requirement evaluations for bundles are not stored in the database.  The second method involves disabling the storing of system requirement evaluations inside of the database.  School systems in particular, should consider disabling the recording of system requirement evaluations as well as any other organization with a significantly large number of bundles that use system requirements.

TID 7004256 - The zStatusEvent and zObjectInfo tables may grow extremely large over time details how to disable the storing of system requirements in ZCM 10.2.1 or later.  After disabling the storing of system requirements, it is recommended to follow the TID to remove the existing stored evaluations.  After removing the existing system requirement evaluations, consider rebuilding the zStatusEvent and zObjectInfo tables to re-optimize the indexes and remove white space from the tables.

Do note however, that as long as this filter is in effect, the device status in ZCC for Assignment Status will remain as "Pending" for devices rather than move to "Not Effective"

Patch Management Customers should take care to limit the size of the PatchScanAuditLog table.  The sole purpose of this table is for the “Dashboard” report in the ZCC.  It is recommended to not retain the data any longer than 90 days unless absolutely necessary.  Consider retaining for only 30 days or less on systems experiencing database performance constraints.   ZCM 11.x allows the retention period to be configured via the ZCC and the servers will automatically control the growth of this table. 

(ZCC > Configuration > Patch Management > Dashboard and Trending > Days to Store Data in Database.)

ZCM 10.3.x and earlier required the following SQL script to be run on a regular basis to limit the data retention in the PatchScanAuditLog table to 90 days:

delete from PATCHSCANAUDITLOG where createddate < CONVERT(VARCHAR(30), dateadd(d,-90,getDate()), 101)

(Hint: Along with limiting the retention history for patches, set the Dashboard Trending report to run during the off hours to avoid it potentially regenerating itself during a busy period of the day.)

 

The growth of the ZCM inventory tables can also cause unecessary growth.  The NC_CompChanges table in particular may have grown very large due to ZCM incorrectly recording changes to a devices inventory when no change occurs.  TID 7004915 - ZENworks Database grows rapidly due to NC_CompChanges table contains some SQL scripts that can reduce the size if faulty change records exist.  The NC_CompChanges table and index should be rebuilt if it had experienced excessive growth.

Also prior to ZCM 11.2 there was not any way to remove historical inventory data.  This could be of particular concern to ZAM customers who monitor process usage, since process usage can consume a significant amount of database resources.  Section 2.4.3 of the “Assset Inventory Reference Guide” details how to perform this in ZCM 11.2 https://www.novell.com/documentation/zenworks11/zen11_inventory/data/ba5qg48.html.  Again, Rebuilding the related tables and indexes is recommended after a major pruning of historical inventory data.

 

Bundles with an excessive number of individual pieces of content may cause both database overhead and slow bundle delivery.  Depending on how it is configured, a bundle that delivers a large number of files may store each of them individually with separate entries in the ZCM database or it may store all the files as a single package with a single entry in the database.  When stored as a single package, there is reduced database overhead and the time to deliver the files to a manage device tends to be much lower.  MSI bundle install actions starting with ZCM 11.1, will store all of the related files as a single piece of content.  ZCM 11.1 also allows for the “Install Directory” action to optionally upload the directory as a single piece of content.  Rebuilding old bundles that contain a very large number of individual pieces of content so that they only contain a few pieces of content may help lower database overhead for a host of functions ranging from bundle installs to content synchronization.  The following SQL query will generate a list in descending order containing the number of pieces of content for each bundle by GUID.

SELECT containingobjectuid, count(*) FROM zlinkedobject WHERE containingobjectuid IN (SELECT zuid FROM zbundle) group by containingobjectuid order by count(*) desc

Use “zman ogn” for the GUIDs returned in the query above to get the name of the bundles with excessive content to see if it can be recreated so that it has less overhead.