NetXMS Support Forum

Database query planning is a separate mystery on its own. Anyways, developers restored additional index as it was before v. 6.0, that will be included in next patch release.

Hi Filipp,

thanks for creating an issue.

I checked the Query again today and noticed that for some reason, the exact same sql-query is now perfoming as expected by using the primary-key.

I didn't do any updates since creating this ticket. Maybe some chache issue from mysql.


mysql> EXPLAIN SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' FROM idata_1177 d JOIN items i ON i.item_id = d.item_id WHERE i.item_id IN (38416,38421,38422) AND idata_timestamp BETWEEN 1776340969454 AND 1776427369454 ORDER BY idata_timestamp;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref              | rows | filtered | Extra                      |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+
|  1 | SIMPLE      | d    | NULL      | range  | PRIMARY      | PRIMARY | 12      | NULL            | 4318 |  100.00 | Using where; Using filesort |
|  1 | SIMPLE      | i    | NULL      | eq_ref | PRIMARY      | PRIMARY | 4      | netxms.d.item_id |    1 |  100.00 | NULL                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+

mysql> EXPLAIN SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' FROM idata_921 d JOIN items i ON i.item_id = d.item_id WHERE i.item_id IN (83253,83254) AND idata_timestamp BETWEEN 1776340969454 AND 1776427369454 ORDER BY idata_timestamp;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref              | rows | filtered | Extra                      |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+
|  1 | SIMPLE      | d    | NULL      | range  | PRIMARY      | PRIMARY | 12      | NULL            | 2880 |  100.00 | Using where; Using filesort |
|  1 | SIMPLE      | i    | NULL      | eq_ref | PRIMARY      | PRIMARY | 4      | netxms.d.item_id |    1 |  100.00 | NULL                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+


Hello Filip!
Thanks for the fast reply.
Here's the screenshot as requested:

My NetXMS user language is set to "English".

Еще одной штуки не хватает, когда этот тикет закроется тогда можно будет прикручивать:
https://github.com/netxms/netxms/issues/3199

А как сконфигурированы прокси - они прописаны в свойствах ноды, или ноды в удаленных офисах выделены в зоны? Если второе, то прокси тоже нужно поставить тоже в эти зоны.

Да, сейчас мы читаем только нормализованное значение, а нужны raw. Сделал тикет: https://github.com/netxms/netxms/issues/3199

raw_dci_values table is just a cache of most recent DCI values. You can just truncate it and proceed with the upgrade:

TRUNCATE TABLE raw_dci_values;

Hello Alex!
Thank you for your fast reply.
The documentation states that there's a specific MariaDB driver and the "netxms-dbdrv-mariadb" package for Debian: https://www.netxms.org/documentation/adminguide/installation.html#mariadb
So it didn't exist on Docker image and I should use MySQL instead even after DB init? Because DB initialization still fails when using MariaDB as DBDriver on Docker, I think that there's some issue that the code is not sharing the same code init template with MySQL for some reason.

Possible case could be a locking of some server thread. You can capture server threads when you see delays in working with the server from the GUI, here's the script: https://github.com/netxms/netxms/blob/master/tools/capture_netxmsd_threads.sh
Script should be executed 3 times with 20-30 second interval. It needs gdb and netxms-dbg packages installed on the system.

Also to check - on netxms server node there should be Server: Number of Processed Events (based on internal Server.TotalEventsProcessed metric) - just to verify that there we no sudden spikes in event quantity.
In overall there's quite a few metrics there, e.g. Server QueueSize ones, it's worth checking them for any big spikes.
If you don't have Server.ImportConfigurationOnStartup set to Always, you might not have the most recent template with above mentioned DCIs, you may want to import them from /usr/share/netxms/templates/netxms_server.xml

Hi,

Please show value of
DefaultNotificationChannel.SMTP.Text
from Configuration->Server Configuration.

If above is set to "SMTP-Text", pls show configuration of SMTP-Text notification channel. If above is set to something else, pls show configuration of that notification channel.