Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (athena.apache.org: domain of tmarki@gmail.com designates
 209.85.161.44 as permitted sender)
MIME-Version: 1.0
From: Tamas Marki <tmarki@gmail.com>
Date: Tue, 1 Nov 2011 17:17:36 +0100
Message-ID: 
 <CAL768UcbY0oCpMf3-MSms6L8prT_1pY=xXxqzhdBm+W4_f4mmQ@mail.gmail.com>
Subject: Storing and querying IP ranges in Cassandra
To: user@cassandra.apache.org
Content-Type: multipart/alternative; boundary=000e0ce0f7b4f9d66a04b0aeb368

--000e0ce0f7b4f9d66a04b0aeb368
Content-Type: text/plain; charset=UTF-8

Hello,

I'm new to the list and also to Cassandra. I found it when I was searching
for something to replace our busy mysql server.

One of the things we use the server for is filtering IPs based on a list of
IP ranges. These ranges can be small and big, and there are about 50k of
them in the database.

In mysql this is pretty quick: they are stored as integers, and the query
basically looks like (say ip is the ip we want to find the all the ranges
for):

select range from rangelist where ip_start<=ip and ip_end>=ip;

I tried to move this schema to Cassandra, but it turned out to be very
slow, even with indexes on both columns. Since I also had to have an EQ
expression in the query, I added an indexed text field which was the same
for all rows, so the query in cassandra was something like this:

select range from rangelist where type='ip' and ip_start<=ip and ip_end>=ip;

This was very slow, and I imagine it is because it has to scan through all
the rows, making the index useless.

The second thing I tried was to just expand the ranges and store individual
IPs as the keys to a column family. This is very fast to query, but the
problem is that I now have over 2.7 million rows, because some of the
ranges are quite large.

As the number of ranges could change, this method could be a problem -
imagine we add a whole A-class range, it would explode into millions of
rows.

My question is, is there a more sane way to store this information, while
still being able to find all the IP ranges that have the given IP in them?

I've been only dealing with Cassandra for a week or two, so I don't know
about the inner details of what can be done, but I do have programming
experience and am not afraid to get my hands dirty, in case it can be
solved by writing some extension to Cassandra.

Looking forward to any suggestions.

Thanks,
Tamas

--000e0ce0f7b4f9d66a04b0aeb368
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hello,<br><br>I&#39;m new to the list and also to Cassandra. I found it whe=
n I was searching for something to replace our busy mysql server.<br><br>On=
e of the things we use the server for is filtering IPs based on a list of I=
P ranges. These ranges can be small and big, and there are about 50k of the=
m in the database.<br>

<br>In mysql this is pretty quick: they are stored as integers, and the que=
ry basically looks like (say ip is the ip we want to find the all the range=
s for):<br><br>select range from rangelist where ip_start&lt;=3Dip and ip_e=
nd&gt;=3Dip;<br>

<br>I tried to move this schema to Cassandra, but it turned out to be very =
slow, even with indexes on both columns. Since I also had to have an EQ exp=
ression in the query, I added an indexed text field which was the same for =
all rows, so the query in cassandra was something like this:<br>

<br>select range from rangelist where type=3D&#39;ip&#39; and ip_start&lt;=
=3Dip and ip_end&gt;=3Dip;<br><br>This was very slow, and I imagine it is b=
ecause it has to scan through all the rows, making the index useless.<br><b=
r>
The second thing I tried was to just expand the ranges and store individual=
 IPs as the keys to a column family. This is very fast to query, but the pr=
oblem is that I now have over 2.7 million rows, because some of the ranges =
are quite large.<br>

<br>As the number of ranges could change, this method could be a problem - =
imagine we add a whole A-class range, it would explode into millions of row=
s.<br><br>My question is, is there a more sane way to store this informatio=
n, while still being able to find all the IP ranges that have the given IP =
in them?<br>

<br>I&#39;ve been only dealing with Cassandra for a week or two, so I don&#=
39;t know about the inner details of what can be done, but I do have progra=
mming experience and am not afraid to get my hands dirty, in case it can be=
 solved by writing some extension to Cassandra.<br>

<br>Looking forward to any suggestions.<br><br>Thanks,<br>Tamas<br><br>

--000e0ce0f7b4f9d66a04b0aeb368--