Help with rasql queries
Jesse Bowling
jessebowling at gmail.com
Fri May 3 16:03:30 EDT 2013
On Fri, May 3, 2013 at 2:23 PM, Carter Bullard <carter at qosient.com> wrote:
> Hey Jesse,
> Your tables took too long to build, What version are you using ???
>
RaSqlInsert Version 3.0.7.3
RaSql Version 3.0.7.3
> I suspect, because you used the -R option, that you tickled a bug
> where we end up using huge amounts of memory, and you started
> to page. The bug was the -R option seemed to not want to deallocate
> the primitive flow records between files, but the -r worked fine. That
> bug should have been fixed.
>
> If you had used:
> > 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 - ip
>
> I believe it would have gone much quicker. Could you give that a try ?
> I removed the " -M cache " option, as that is really intended for real
> time data
> processing, using the " -S argus.source " rather than files.
>
> I'll give that a shot with some new data and let you know how that
goes...It should be noted that this is an over-the-wire insert; thus
network comes into play... :)
> OK, to best understand what a particular query is doing, use the " -D3 "
> debug option. It should printout the actual mysql calls, so you can
> figure out
> what is up.
>
> Try your query:
> rasql -t -7d+7d -M time 1d -M sql="saddr=’66.249.76.186’" -r
> mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d -D3
>
> What did you get?
>
Unfortunately I get....nothing. The prompt simply returns with no messages.
I also tried with -D9 with the same results...
I ensured that there is a .debug file in the source directory and
re-comiled, but it's still not giving a response...
> Carter
>
>
>
> On May 3, 2013, at 12:16 PM, Jesse Bowling <jessebowling at gmail.com> wrote:
>
> > 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
> >
>
>
--
Jesse Bowling
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20130503/204372ba/attachment.html>
More information about the argus
mailing list