NXMC database best practices

[jbriseno]

Hi, there. We have been using NXMC system for at least two years. During this period of time, the NXMC  database has increased its tables size. So far on this forum I do not find any clue how to handle this database tables size. Any idea how we can apply NXMC database best practice.Any comment is welcome Thanks in advance.

Jose

[Filipp Sudanov]

First of all, it's a question of your retention times for DCIs and event, alarm, etc log retention times (search for "Retention" in Configuration->Server configuration). At some older version there was also a bug that some of these log tables were not cleared, so make sure that you are on the newest version.

Also, could it be that during the life of your system a lot of new nodes or new DCIs were added - that would also add up to the DB size.

According to retention settings housekeeper should delete records from the db. (It's also worth to check server log for any SQL errors - and there are events generated for such cases - make sure you've configured notifications for these events).

Then it's a question to the database engine that you are using - how effective it is in performing vacuum operation to reclaim space. Normally the database should keep at some size and not grow much. What DB engine are you using?

[jbriseno]

Hi, Filipp. First I appreciate your time to reply my post. Second, we have the following database engine: Server version: 10.3.37-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Finally, the NetXMC installed is:

NetXMS Server Version 4.2.433 Build 4.2-433-g453d89aa43 (UNICODE)
NXCP: 5.60.1.43 (AES-256, Blowfish-256, 3DES, AES-128, Blowfish-128)
Built with: g++ (Ubuntu 9.3.0-10ubuntu2) 9.3.0

My best.

Jose

[Filipp Sudanov]

I'm not an expert in MariaDB, but here's a post that has a query, which should give some information from the DB about used and allocated disk space.

[jbriseno]

Hi, Filipp.  The netXMC database  size.

+--------------------+--------------+
| Database name      | Size in (MB) |
+--------------------+--------------+
| information_schema |        0.19    |
| mysql              |        13.12            |
| netxms            |    51727.00          |
| performance_schema |        0.00 |
+--------------------+--------------+

I hope this helps

My best

Jose
4 rows in set (15.991 sec

[jbriseno]

Hi, Fillip. I followed your DB instructions. Also, I looked  for Retention options information on the NetXMC documentation website. Howerver, I did not find nothing related with this topic.

I checked the mysql error file out. This file is empty.

Then according with Server Configuration tab information. It has the following headling titles.

Name, Value, Default value Restart

Also, I chosen the most relevant retention options of the server configuraiton :

1 WindowsEvents.LogRetentionTime
2 UserAgent.RetentionTime
3 UserAgent.DefaultMessageRetentionTime
4 Syslog.RetentionTime
5 SNMP.Traps.LogRetentionTime
6 Scheduler.TaskRetentionTime
7 NotificationLog.RetentionTime
8 NotificationChannels.MaxRetryCount
9 MaintenanceJournal.RetentionTime
10 DataCollection.InstanceRetentionTime
11 DataCollection.DefaultDCIRetentionTime
12 BusinessServices.History.RetentionTime
13 AuditLog.RetentionTime
14 Alarms.HistoryRetentionTime
15 ActionExecutionLog.RetentionTime

According with the NetXMC Database best practice. What would be the retention options that I need to update to work in proper manner with the netXMC server?

Thanks in Advance

Jose

[gkaudewitz]

There is a perl script called mysqltuner that helps very much identifying unused space within the tables that will lead to large files in the database.
Especially after changing some RetentionTimers and deleting some records via housekeeper you will see spaces there where you can shrink databasesize.
Sometimes it is good identifying unused tabels via nxdbmgr.

[jbriseno]

Hi, gkaudewitz. First I appreciate your time to reply my follow-up message. Second, I am going to follow your comments. Thanks.

My best
Jose