Send Netdot-users mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
https://osl.uoregon.edu/mailman/listinfo/netdot-users
or, via email, send a message with subject or body 'help' to
[email protected]
You can reach the person managing the list at
[email protected]
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Netdot-users digest..."
Today's Topics:
1. slow DB query? (Vincent Magnin)
2. Re: slow DB query? (Carlos Vicente)
3. Re: slow DB query? (Vincent Magnin)
----------------------------------------------------------------------
Message: 1
Date: Fri, 5 Feb 2016 10:28:58 +0000
From: Vincent Magnin <[email protected]>
Subject: [Netdot-users] slow DB query?
To: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset="iso-8859-1"
Greetings,
I am experiencing slow behavior during device polling in 1.0.7.
While debugging, I've observed that the related SQL query is related to
following query:
Ipblock->search_devipsbyaddr($dev).
By example:
> SELECT device.id, interface.id, interface.name, interface.device, ipblock.id,
> ipblock.interface, ipblock.address
> FROM ipblock, interface, device
> WHERE interface.id = ipblock.interface AND
> device.id = interface.device AND
> device.id = '656'
> ORDER BY ipblock.address
>
> Result is displayed after 2 minutes.
If I remove the 'ORDER BY' clause, the result is shown immediately.
I know that my ipblock table is huge (about 3'400'000 records ... thanx to ipv6
polling), but, is it really needed to sort the result by IP during a
Device->get_ips() query?
Regards,
Vincent
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://osl.uoregon.edu/pipermail/netdot-users/attachments/20160205/489fb648/attachment-0001.html
------------------------------
Message: 2
Date: Fri, 5 Feb 2016 09:09:38 -0500
From: Carlos Vicente <[email protected]>
Subject: Re: [Netdot-users] slow DB query?
To: Vincent Magnin <[email protected]>
Cc: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=windows-1252
Hi Vincent,
We could modify Device::get_ips to not do any sorting by default. I agree that
it?s probably not necessary to sort during polling. Throw me a ticket on
Redmine (even better with a patch).
That said, I?m curious why MySQL would take so long to sort the somewhat small
subset of addresses belonging to a single device. From what you say it sounds
like it?s sorting before doing the joins.
cv
On Feb 5, 2016, at 5:28 AM, Vincent Magnin <[email protected]> wrote:
> Greetings,
>
> I am experiencing slow behavior during device polling in 1.0.7.
>
> While debugging, I've observed that the related SQL query is related to
> following query:
>
> Ipblock->search_devipsbyaddr($dev).
>
> By example:
> > SELECT device.id, interface.id, interface.name, interface.device,
> > ipblock.id, ipblock.interface, ipblock.address
> > FROM ipblock, interface, device
> > WHERE interface.id = ipblock.interface AND
> > device.id = interface.device AND
> > device.id = '656'
> > ORDER BY ipblock.address
> >
> > Result is displayed after 2 minutes.
>
> If I remove the 'ORDER BY' clause, the result is shown immediately.
>
> I know that my ipblock table is huge (about 3'400'000 records ... thanx to
> ipv6 polling), but, is it really needed to sort the result by IP during a
> Device->get_ips() query?
>
>
> Regards,
>
>
> Vincent
> _______________________________________________
> Netdot-users mailing list
> [email protected]
> https://osl.uoregon.edu/mailman/listinfo/netdot-users
------------------------------
Message: 3
Date: Fri, 5 Feb 2016 14:24:22 +0000
From: Vincent Magnin <[email protected]>
Subject: Re: [Netdot-users] slow DB query?
To: Carlos Vicente <[email protected]>
Cc: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset="Windows-1252"
Hi Carlos,
It seems these 2 minutes comes from phpmyadmin web interface. Same query from
mysql command prompt is much faster:
mysql> SELECT device.id, interface.id, interface.name, interface.device,
ipblock.id, ipblock.interface, ipblock.address FROM ipblock, interface, device
WHERE interface.id = ipblock.interface AND device.id = interface.device AND
device.id = '656' ORDER BY ipblock.address;
+-----+-------+------+--------+-------+-----------+------------+
| id | id | name | device | id | interface | address |
+-----+-------+------+--------+-------+-----------+------------+
| 656 | 40275 | br0 | 656 | 14565 | 40275 | ******* |
+-----+-------+------+--------+-------+-----------+------------+
1 row in set (13.27 sec)
mysql> SELECT device.id, interface.id, interface.name, interface.device,
ipblock.id, ipblock.interface, ipblock.address FROM ipblock, interface, device
WHERE interface.id = ipblock.interface AND device.id = interface.device AND
device.id =
'656';+-----+-------+------+--------+-------+-----------+------------+
| id | id | name | device | id | interface | address |
+-----+-------+------+--------+-------+-----------+------------+
| 656 | 40275 | br0 | 656 | 14565 | 40275 | ******* |
+-----+-------+------+--------+-------+-----------+------------+
1 row in set (0.01 sec)
I've already writen a patch. I'll send it on redmine.
Regards,
Vincent
________________________________________
De : Carlos Vicente <[email protected]>
Envoy? : vendredi 5 f?vrier 2016 15:09
? : Vincent Magnin
Cc : [email protected]
Objet : Re: [Netdot-users] slow DB query?
Hi Vincent,
We could modify Device::get_ips to not do any sorting by default. I agree that
it?s probably not necessary to sort during polling. Throw me a ticket on
Redmine (even better with a patch).
That said, I?m curious why MySQL would take so long to sort the somewhat small
subset of addresses belonging to a single device. From what you say it sounds
like it?s sorting before doing the joins.
cv
------------------------------
_______________________________________________
Netdot-users mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-users
End of Netdot-users Digest, Vol 83, Issue 2
*******************************************
