Send netdisco-users mailing list submissions to
netdisco-users@lists.sourceforge.net
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.sourceforge.net/lists/listinfo/netdisco-users
or, via email, send a message with subject or body 'help' to
netdisco-users-requ...@lists.sourceforge.net
You can reach the person managing the list at
netdisco-users-ow...@lists.sourceforge.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of netdisco-users digest..."
Today's Topics:
1. Re: Slow Search Performance (Christian Ramseyer)
--- Begin Message ---
On 17.09.20 10:06, Pavel Skovajsa wrote:
> Anybody give me a hint?
>
> -pavel
> ////////
> SELECT me.mac, me.ip FROM ( SELECT ip, mac FROM device where mac =
> any ($1::macaddr[])
> UNION
> SELECT ip, mac FROM device_port dp where mac = any
> ($2::macaddr[])
> ) me GROUP BY mac, ip
> 2020-08-09 00:00:46.997 EDT [537360] netdisco@netdisco DETAIL:
> parameters: $1 = '{00:05:9b:cf:1c:1a}', $2 = '{00:05:9b:cf:1c:1a}'
>
> //////////
Hi Pavel
To fill in the array parameters, paste the whole string including the
"'{". Then run it with "explain analyze", e.g:
explain analyze SELECT me.mac, me.ip FROM (
SELECT ip, mac FROM device where mac = any
('{00:05:9b:cf:1c:1a}'::macaddr[])
UNION
SELECT ip, mac FROM device_port dp where mac = any
('{00:05:9b:cf:1c:1a}'::macaddr[])
) me GROUP BY mac, ip
Can you paste the output into https://explain.depesz.com/ and share the
link? This will nicely show where the slowness occurs. For comparison, I
have 450k records in device_port and device combined, and it finishes in
6ms.
Usually slowness over time occurs because tables and indexes can become
"bloated" by having many pages (blocks on the file system) with mostly
deleted rows in it. Postgres does not consolidate these on its own.
Vacuum and reindexing help, check the "Things are getting really slow"
section here: https://github.com/netdisco/netdisco-legacy/blob/master/README
It's for Netdisco 1.x but the essential advice is still correct, stop
all netdisco processes (-web and -backend) then run these:
REINDEX TABLE node;
REINDEX TABLE node_ip;
REINDEX TABLE device;
REINDEX TABLE device_port;
REINDEX TABLE device_port_log;
VACUUM FULL ANALYZE VERBOSE;
Cheers
Christian
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
