Help with rasql queries
Carter Bullard
carter at qosient.com
Sat May 4 10:14:49 EDT 2013
Hey Jesse,
Excellent, however 2m:38s is too long for me !!!
So is that the rasql() query time, or is that the total time for insertion, and query?
Hope all is most excellent !!!!
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 10:05 PM, Jesse Bowling <jessebowling at gmail.com> wrote:
> Hi Carter,
>
> Well that's just sillly. I copy pasted from example text and didn't notice that it was using backticks rather than single quotes. After correcting the quotes it's all good...
>
> 2m:38s to check 2.5 million IP's over three days...Not bad! :)
>
> Cheers,
>
> Jesse
>
>
> On Fri, May 3, 2013 at 9:02 PM, Carter Bullard <carter at qosient.com> wrote:
>> Hey Jesse,
>> Use mysql() to try the same queries rasql() is using, to see why they aren't giving you a return.
>>
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.029297 SQL Query SELECT record from ipAddrs_2013_04_29 WHERE saddr=’66.249.76.186’
>>
>> Try that literally, but change record to something like saddr.
>>
>> Carter
>>
>>
>> On May 3, 2013, at 7:52 PM, Jesse Bowling <jessebowling at gmail.com> wrote:
>>
>>> Thanks Carter; I missed the configure step after making .debug...
>>>
>>> # 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
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:30.971502 ArgusParseInit(0x120ee010, NULL)
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.009019 RaMySQLInit () RaSource (null) RaArchive (null) RaFormat (null)
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.019209 SQL Query SELECT record from ipAddrs_2013_04_27 WHERE saddr=’66.249.76.186’
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.023303 SQL Query SELECT record from ipAddrs_2013_04_28 WHERE saddr=’66.249.76.186’
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.029297 SQL Query SELECT record from ipAddrs_2013_04_29 WHERE saddr=’66.249.76.186’
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.032263 SQL Query SELECT record from ipAddrs_2013_04_30 WHERE saddr=’66.249.76.186’
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.036089 SQL Query SELECT record from ipAddrs_2013_05_01 WHERE saddr=’66.249.76.186’
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.039221 SQL Query SELECT record from ipAddrs_2013_05_02 WHERE saddr=’66.249.76.186’
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.043198 SQL Query SELECT record from ipAddrs_2013_05_03 WHERE saddr=’66.249.76.186’
>>> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.048043 ArgusShutDown (2)
>>>
>>> What next? I see that 3.0.7.7 is available; should I upgrade to those?
>>>
>>> Thanks for the assistance,
>>>
>>> Jesse
>>>
>>>
>>> On Fri, May 3, 2013 at 7:35 PM, Carter Bullard <carter at qosient.com> wrote:
>>>> 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
>>>
>>>
>>>
>>> --
>>> Jesse Bowling
>
>
>
> --
> Jesse Bowling
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20130504/754f60f9/attachment.html>
More information about the argus
mailing list