Help with rasql queries

[Jesse Bowling]

Hi,

Again I'm trying to follow through some of the APT examples. I'm very
interested in the IP address SQL backing tables to speed searches, as this
is a common search for my purposes.

I've built tables with two days of data like:

rasqlinsert -M time 1d -M rmon -m saddr -R 2013/04/29 -R 2013/04/30 -w
mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d -M cache - ip

This took a while (29 hours) but did build tables:

mysql> use argus_ip;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_argus_ip |
+--------------------+
| ipAddrs_2013_04_29 |
| ipAddrs_2013_04_30 |
+--------------------+
2 rows in set (0.00 sec)

mysql> describe ipAddrs_2013_04_29;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| stime  | double(18,6) unsigned | NO   |     | NULL    |       |
| flgs   | varchar(32)           | YES  |     | NULL    |       |
| proto  | varchar(16)           | NO   |     | NULL    |       |
| saddr  | varchar(64)           | NO   | PRI | NULL    |       |
| sport  | varchar(10)           | NO   |     | NULL    |       |
| dir    | varchar(3)            | YES  |     | NULL    |       |
| daddr  | varchar(64)           | NO   |     | NULL    |       |
| dport  | varchar(10)           | NO   |     | NULL    |       |
| pkts   | bigint(20)            | YES  |     | NULL    |       |
| bytes  | bigint(20)            | YES  |     | NULL    |       |
| state  | varchar(32)           | YES  |     | NULL    |       |
| record | blob                  | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

mysql> select count(*) from ipAddrs_2013_04_29;
+----------+
| count(*) |
+----------+
|   715059 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from ipAddrs_2013_04_30;
+----------+
| count(*) |
+----------+
|  1362479 |
+----------+
1 row in set (0.00 sec)

mysql> select stime,flgs,proto,saddr,sport,dir,daddr,dport,pkts,bytes,state
from ipAddrs_2013_04_29 limit 1;
+-------------------+------+-------+---------------+-------+------+---------+-------+------+---------+-------+
| stime             | flgs | proto | saddr         | sport | dir  | daddr
| dport | pkts | bytes   | state |
+-------------------+------+-------+---------------+-------+------+---------+-------+------+---------+-------+
| 1367208000.000000 |      | ip    | 66.249.76.186 |       | <->  | 0.0.0.0
|       | 2465 | 1964858 | CON   |
+-------------------+------+-------+---------------+-------+------+---------+-------+------+---------+-------+
1 row in set (0.00 sec)

...but now I can't seem to get any data back out using the incantations
I've seen:

# time rasql -t -7d+7d -M time 1d -M sql="saddr=’66.249.76.186’" -r
mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d

real    0m0.073s
user    0m0.008s
sys     0m0.021s

# time rasql -t 2013/04/29 -M time 1d -M sql="saddr=’66.249.76.186’" -r
mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d

real    0m0.045s
user    0m0.010s
sys     0m0.017s

This does work, but much more slowly that I expected:

# time rasql -t 2013/04/29 -M time 1d -r
mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d
- host 66.249.76.186
                     StartTime      Flgs  Proto            SrcAddr  Sport
Dir            DstAddr  Dport  TotPkts   TotBytes State
      04/29/13 00:00:00.000000                0      66.249.76.186
<->            0.0.0.0            2465    1964858   CON

real    0m46.419s
user    0m4.245s
sys     0m0.991s

# time rasql -t 2013/04/29+1d -M time 1d -r
mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d
- host 66.249.76.186
^C
real    26m18.117s
user    0m0.037s
sys     0m0.165s


I'm guessing that the second form (using a filter to rasql) is pulling back
ALL records from mySQL and filtering that, whereas the first format should
be using mySQL to do the filtering (the preferred method). So I suppose my
questions are "Is this last statement correct?" and "What am I doing wrong
with the first form of query?"

Is the query speed for the working format inline with expectations? Seems a
bit slow...

Cheers,

Jesse
-- 
Jesse Bowling
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20130503/245d2241/attachment.html>