NetXMS Support Forum

Hi All,

The Query "INSERT INTO tdata_122 (item_id,tdata_timestamp,record_id) VALUES (:1,:2,:3)" is very expensive, it causes all sorts of problems with the Database including the creation of deadlocks and waits. Isn't there a way we can optimize it so as to make is less expensive on resources.

It's simple insert with 3 field and single composite index, it should not be expensive.
Please show explain / query plan.
What DB you are using?
How many objects in the system?
How big the database is?
What is retention time for tables DCIs on node 122?

I understand that the tdata_ table holds the DCI value history, my database is big with a lot of objects in the system sitting on oracle 11g database. The problem is the value history are alot and are taking a lot of resources when pushing these history to the database. Is there way on how to tell netxms not to generate a lot of these history?

Quote from: isoftcom on November 19, 2018, 08:12:37 PM
Is there way on how to tell netxms not to generate a lot of these history?

Sure.
1) Increase collection interval for the table DCI (it's once a minute by default)
2) Decrease retention time for the table DCI (1 month by default)

You can change these settings per-DCI.

To find out which DCI consume most of the space, you can query table in question:

select item_id,count(*) c from tdata_122 group by item_id order by c desc;

ID is the same as shown in data collection editor for the node

Hi Alex

Thanks for the Help, let me try to do what you have advised and monitor the difference.