Hi,
since upgrading to v6 one of my SQL-queries is slower than usual.
Similar queries are still fast like before the update.
I don't know why the DB should threat them differently.
Slow Query:
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 <starttime> AND <endtime>
-> ORDER BY idata_timestamp;
describe idata_1177;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| item_id | int | NO | PRI | NULL | |
| idata_timestamp | bigint | NO | PRI | NULL | |
| idata_value | varchar(255) | YES | | NULL | |
| raw_value | varchar(255) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
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 <starttime> AND <endtime>
-> ORDER BY idata_timestamp;
+----+-------------+-------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | i | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | d | NULL | ref | PRIMARY | PRIMARY | 4 | netxms.i.item_id | 1355 | 11.11 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0,00 sec)
EXPLAIN ANALYZE 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 <starttime> AND <endtime> ORDER BY idata_timestamp;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: d.idata_timestamp (actual time=2041.314..2041.572 rows=4318 loops=1)
-> Stream results (cost=418.24 rows=452) (actual time=978.482..2039.624 rows=4318 loops=1)
-> Nested loop inner join (cost=418.24 rows=452) (actual time=978.464..2037.612 rows=4318 loops=1)
-> Filter: (i.item_id in (38416,38421,38422)) (cost=3.63 rows=3) (actual time=0.086..0.144 rows=3 loops=1)
-> Index range scan on i using PRIMARY over (item_id = 38416) OR (item_id = 38421) OR (item_id = 38422) (cost=3.63 rows=3) (actual time=0.084..0.137 rows=3 loops=1)
-> Filter: (d.idata_timestamp between 1776340969454 and 1776427369454) (cost=7.70 rows=151) (actual time=678.666..679.068 rows=1439 loops=3)
-> Index lookup on d using PRIMARY (item_id=i.item_id) (cost=7.70 rows=1355) (actual time=0.046..659.711 rows=525672 loops=3)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (2,05 sec)
Fast Query:
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 <starttime> AND <endtime>
-> ORDER BY idata_timestamp;
describe idata_921;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| item_id | int | NO | PRI | NULL | |
| idata_timestamp | bigint | NO | PRI | NULL | |
| idata_value | varchar(255) | YES | | NULL | |
| raw_value | varchar(255) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
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 <starttime> AND <endtime>
-> 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 |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+
2 rows in set, 1 warning (0,00 sec)
EXPLAIN ANALYZE 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 <starttime> AND <endtime> ORDER BY idata_timestamp;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=3749.56 rows=2880) (actual time=1.607..5.474 rows=2880 loops=1)
-> Sort: d.idata_timestamp (cost=581.56 rows=2880) (actual time=1.563..1.708 rows=2880 loops=1)
-> Filter: ((d.item_id in (83253,83254)) and (d.idata_timestamp between 1776340969454 and 1776427369454)) (cost=581.56 rows=2880) (actual time=0.021..1.171 rows=2880 loops=1)
-> Index range scan on d using PRIMARY over (item_id = 83253 AND 1776340969454 <= idata_timestamp <= 1776427369454) OR (item_id = 83254 AND 1776340969454 <= idata_timestamp <= 1776427369454) (cost=581.56 rows=2880) (actual time=0.018..0.876 rows=2880 loops=1)
-> Single-row index lookup on i using PRIMARY (item_id=d.item_id) (cost=1.00 rows=1) (actual time=0.001..0.001 rows=1 loops=2880)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)
I've created a ticket for this: https://github.com/netxms/netxms/issues/3195