Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 7.2 \(1874\))
Subject: Re: IP addresses, NetBlocks, and ASNs
From: Steve Atkins <steve@blighty.com>
In-Reply-To: <2037300270.1029291.1397959928677.JavaMail.root@cis.uab.edu>
Date: Sun, 20 Apr 2014 07:57:48 -0700
Cc: Gary Warner <gar@cis.uab.edu>
Content-Transfer-Encoding: quoted-printable
Message-Id: <EB35CF80-096F-426F-9D64-0DEDF2777CB1@blighty.com>
References: <2037300270.1029291.1397959928677.JavaMail.root@cis.uab.edu>
To: Postgres Performance <pgsql-performance@postgresql.org>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On Apr 19, 2014, at 7:12 PM, Gary Warner <gar@cis.uab.edu> wrote:

> Does anyone have some good tricks for mapping IP addresses to ASN numbers=
 in very large volumes?
>=20
> This is probably more a "how would you approach this problem?" than "can =
you help me tweak this query"=20
>=20
> I have a very large number of IP addresses (many millions) that are found=
 in some security related logs.  I need a fast way to categorize these back=
 to their ASN numbers and Country codes.  I have a table that was generated=
 from BGP routing files to create a list of netblocks and their correspondi=
ng ASNs.  For small groups of IP addresses (hundreds or thousands) it has a=
lways been fast enough to easily just do something like:
>=20
> select count(*), asn_number from logaddr a, ipv4 b where a.ip <<=3D b.net=
_block=20

For decent performance, you might want to look at http://ip4r.projects.pgfo=
undry.org. That'll let you use indexes for "is this ip address in any of th=
e ranges in this table.".

(I see hints that there'll be similar support for inet in 9.5, but I've not=
 looked at the code).

> where logaddr has information about IP addresses encountered and
> where "ipv4" has a list of mappings of which netblocks belong to which AS=
Ns (yeah, it is 525,000+ Netblocks ...)
>=20
> I'm trying to figure out if there is a better way to use the "cidr" netbl=
ock to speed up the look up and matching.
>=20
> I've tried playing with going from ASN to the matching IP addresses, or t=
he other way around ... for example, here is the Explain  for looking at al=
l the IP addresses in the logaddr (currently about 9 million records) that =
use ASN =3D 2119:
>=20
>=20
> explain select count(distinct ip), country_code, asn_number from ipv4 a, =
logaddr b=20
> where b.ip <<=3D a.net_block and a.asn_number =3D 2119=20
> group by country_code, asn_number;
>=20
>=20
> "GroupAggregate  (cost=3D36851922.32..38215407.51 rows=3D4 width=3D18)"
> "  ->  Sort  (cost=3D36851922.32..37192793.61 rows=3D136348515 width=3D18=
)"
> "        Sort Key: a.country_code, a.asn_number"
> "        ->  Nested Loop  (cost=3D0.00..4448316.05 rows=3D136348515 width=
=3D18)"
> "              Join Filter: ((b.ip)::inet <<=3D (a.net_block)::inet)"
> "              ->  Seq Scan on logaddr b  (cost=3D0.00..347394.01 rows=3D=
9089901 width=3D7)"
> "              ->  Materialize  (cost=3D0.00..10466.66 rows=3D30 width=3D=
18)"
> "                    ->  Seq Scan on ipv4 a  (cost=3D0.00..10466.51 rows=
=3D30 width=3D18)"
> "                          Filter: (asn_number =3D 2119)"
>=20
>=20
> Just the first netblock from that ASN (2.148.0.0/14) occurs around 7,300 =
times, and does ok if I was just looking for that ... but I need a way to d=
o "big aggregate queries" like "what were the most common ASNs in this whol=
e log file?"  or "how often does country XX occur in this log file?"

ip4r is pretty good (and does support v6 too, despite the name).

If you need yet more performance (and you probably don't) the approach I've=
 moved to using is to tag the records with ASN on import, using an in-core =
patricia trie kept in sync with the address ranges listed in the database t=
o do the work. It's faster still, at the cost of a fair bit more work durin=
g import. (It's also a little more accurate in some cases, as the mapping o=
f IP address to list of ASNs is dynamic, and you usually want the ASN at th=
e time of an incident, not the one now.)

Cheers,
  Steve


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance