I’ve noticed some odd behaviour on the Device Ports page (e.g. /device/1/ports) where there is a considerable delay on page load. I’ve done some digging and it happens regardless of the number of interfaces - it looks like this delay is caused by a slow database query when pulling the ports_vlans for the connected interfaces:
# Time: 240930 13:32:26
# User@Host: librenms[librenms] @ [127.0.0.1]
# Thread_id: 7317 Schema: librenms QC_hit: No
# Query_time: 3.805417 Lock_time: 0.000009 Rows_sent: 0 Rows_examined: 6832053
# Rows_affected: 0 Bytes_sent: 0
SET timestamp=1727703146;
select * from `ports_vlans` where `ports_vlans`.`port_id` in (47546, 11533011, 11533808, 11564043, 11564044);
The explain output shows that it’s doing a full table scan instead of using the ports_vlans index:
MariaDB [librenms]> explain select * from `ports_vlans` where `ports_vlans`.`port_id` in (47546, 11533011, 11533808, 11564043, 11564044);
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | ports_vlans | ALL | NULL | NULL | NULL | NULL | 6805422 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
I’m not sure where in the app that this query is generated, but just wondering if anyone has noticed something similar? I have around 77k ports across ~1500 devices so I guess this is something related to the size of install?
Thanks for coming back to me. I’ve ran an update and now on LibreNMS 24.9.1-4-g2531c8d (2024-09-30T23:39:15+00:00) - I think that’s me pretty much up to date?
Would it make sense to limit the query to search by device_id as well as port_ids? That appears to use the indexes already in place (ports_vlans_device_id_port_id_vlan_unique) and returns faster on my install compared to the plain port_id search.
Looking at an explain it changes it from scanning 7m rows to 5:
MariaDB [librenms]> explain select * from `ports_vlans` where `ports_vlans`.`port_id` in (47546, 11533011, 11533808, 11564043, 11564044) and `ports_vlans`.`device_id` =
1;
+------+-------------+-------------+-------+-------------------------------------------+-------------------------------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------+-------+-------------------------------------------+-------------------------------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | ports_vlans | range | ports_vlans_device_id_port_id_vlan_unique | ports_vlans_device_id_port_id_vlan_unique | 8 | NULL | 5 | Using index condition |
+------+-------------+-------------+-------+-------------------------------------------+-------------------------------------------+---------+------+------+-----------------------+
1 row in set (0.000 sec)
I’ve tried to have a look in the code base to see where this query is generated but I’m not that familiar with Laravel so it’s not entirely obvious to me.
I’ll need to take a look at the new global vlan page.
Following up on my own question: I added an index for the port_id column on the ports_vlans table as a test and that appears to have helped massively - dropping the average query time from seconds to milliseconds.
Hi Tony, thanks for the explanation and pointers - appreciated.
I can also see that the table schema updated overnight and added in 2024_10_06_002633_ports_vlans_table_add_port_id_index - that’s really helpful, thanks!