Help with rasql queries
Carter Bullard
carter at qosient.com
Fri May 3 19:35:51 EDT 2013
Hey Jesse,
You have to compile in the debug support....in the clients root directory.
% touch .debug
% ./configure
% make clean; make
Carter
Carter Bullard, QoSient, LLC
150 E. 57th Street Suite 12D
New York, New York 10022
+1 212 588-9133 Phone
+1 212 588-9134 Fax
On May 3, 2013, at 4:03 PM, Jesse Bowling <jessebowling at gmail.com> wrote:
> 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/ce814514/attachment.html>
More information about the argus
mailing list