mysql database changes - new patch
Carter Bullard
carter at qosient.com
Sat Oct 13 12:35:36 EDT 2012
OK, I'll add %d support for time fields, regardless of whether the %f is in the RA_TIME_FORMAT string.
the racluster bug is fixed, and should be in argus-clients-3.0.7.3 ?
Carter
On Oct 13, 2012, at 11:54 AM, CS Lee <geek00l at gmail.com> wrote:
> hi Carter,
>
> Actually I know the double is to use for floating, but since I have dur field so I can easily see the duration of the flow for fraction of second as well.
>
> It doesn't complain if I change the stime data type to integer, just ignore it automatically.
>
> I also do encounter the racluster issue that Rafael mentioned, however you are working on it with Rafael to iron out the issue so I think it's better to keep it that way instead of getting more emails bugging on the similar issue until you release the fix, and I will test it out later.
>
> I will try out for the -M time 1M option and see if it goes well, thank you.
>
> On Sat, Oct 13, 2012 at 9:47 PM, Carter Bullard <carter at qosient.com> wrote:
> Hey CS Lee,
> The double is to handle the "%f" extension in the RA_TIME_FORMAT.
>
> If you don't have that specified, we could modify the time fields to integer.
> I'll look to see what I need to do to make that change.
>
> Does it complain or silently ignore your integer specification on the field?
>
> Carter
>
>
> On Oct 13, 2012, at 3:44 AM, CS Lee <geek00l at gmail.com> wrote:
>
>> hi Carter,
>>
>> On the other hand, is that possible to change stime data type from double to integer during the auto table generation from rasqlinsert, currently I can only change for saddr sport daddr dport which you have coded in.
>>
>> On Mon, Jun 25, 2012 at 11:04 PM, CS Lee <geek00l at gmail.com> wrote:
>> hi Carter,
>>
>> It's a trade off because I'm not keeping argus primitive data, I'm trying to minimize the fields I need to keep already ;)
>>
>> This is just the starting work of handling 10G with mysql, my plan is to have mysql handling first hand data, then sync them with hadoop/hive to perform big data analysis. Nothing is final, and I would like to hear some thoughts from other argus users in the list as well.
>>
>> By the way Carter, do you have plan to make argus client multi-threaded as well to have better performance in term of analysis, I know this is not trivial but just asking.
>>
>> Cheers!
>>
>>
>> On Mon, Jun 25, 2012 at 10:51 PM, CS Lee <geek00l at gmail.com> wrote:
>> hi Carter,
>>
>> Yeah initially when I thought of using stime(it's double now and I can alter it to integer anyway) for mysql partition and I may lose fraction of second, then we have dur field in argus which is more useful to know(is it short or long flow) and that makes me think of changing the strategy.
>>
>> Mysql partition doesn't play well with string data type, and i want to check how well it performs compare to default schema that we currently have(providing that the changes of sadddr,daddr,sport and dport changes applied). I'm also looking into have autoid field as the key as well since it is sequential incremental field.
>>
>> I did hit the problem of storing argus primitive data so -record is helpful. however one thing I found is in argus client if we run
>>
>> -s -record
>>
>> it will work, but if we run
>>
>> -s saddr daddr -record
>>
>> This won't work as argus client will take it as -r ecord and said ecord not found. However the workaround is simple just store the field I want in rarc with RA_FIELD_SPEFICIER and then run argus client with -s -record instead of stating the field I want in command line.
>>
>> Cheers!
>>
>> On Mon, Jun 25, 2012 at 10:10 PM, Carter Bullard <carter at qosient.com> wrote:
>> Time is currently defined as a double, so that it can get the fractional part,
>> so you may not need to change that representation, unless double is not
>> a good performer?
>>
>> Please keep sending your preferences for data formats to the list so I can make
>> appropriate changes. The Stanford guys started this effort by asking for addresses
>> to be in decimal, and with your list, we should be able to do a good job on
>> preliminary database work. They are using some very high performance
>> commercial databases for their work, and seem rather happy, but that doesn't
>> cover Stanford's 10G monitoring efforts.
>>
>> I will put in the database schema creation support for these formats next, but
>> that will be in argus-clients-3.0.7.1, which i should put up later this week.
>>
>> You will find that the db will not be able to keep up with stuffing primitive data
>> into the database, as the network and argus can generate more data than the
>> db can accept. But maintaining derived data, like an ethernet/ip address table
>> with rasqlinsert() is no problem, or an ethernet matrix table is a no brainer.
>>
>> I'd be interested in any results that you discover !!!!!
>>
>> Carter
>>
>>
>> On Jun 25, 2012, at 10:00 AM, CS Lee wrote:
>>
>>> hi Carter,
>>>
>>> After applying latest patch from you, here's the result -
>>>
>>> mysql> SELECT stime, saddr, sport, daddr, dport, proto, state FROM tbl_test;
>>> +-------------------+------------+-------+------------+-------+-------+-------+
>>> | stime | saddr | sport | daddr | dport | proto | state |
>>> +-------------------+------------+-------+------------+-------+-------+-------+
>>> | 1340623505.000000 | 3232235779 | 8 | 3512053643 | 5923 | icmp | ECO |
>>> +-------------------+------------+-------+------------+-------+-------+-------+
>>>
>>> I will run more testing on other data later. My other plan is to change stime to be integer as well so that it will be very efficient for partition strategy. String data type can't be used for mysql partition. Currently my tweaking and fine tuning is gearing toward high speed mysql db for argus and see if my strategy works better than default schema.
>>>
>>> Thank you!
>>>
>>> On Mon, Jun 25, 2012 at 9:48 PM, CS Lee <geek00l at gmail.com> wrote:
>>> hi Carter,
>>>
>>> I tested the previous patch and it works, so I'm testing this one now.
>>>
>>>
>>> On Mon, Jun 25, 2012 at 9:27 PM, Carter Bullard <carter at qosient.com> wrote:
>>> Hey CS Lee,
>>> My bad, I needed to change a few lines, as the conditional shouldn't define the scope !!!!!
>>> Here is a new patch that should work a bit better:
>>>
>>> ==== //depot/argus-3.0.6/clients/common/argus_util.c#3 - /Volumes/Users/carter/argus/release/argus-3.0.6/clients/common/argus_util.c ====
>>> 7200c7200
>>> < char upbuf[32], *upstr = upbuf;
>>> ---
>>> > char upbuf[32], *upstr = upbuf, *format = NULL;
>>> 7202,7205c7202,7203
>>> < switch (proto) {
>>> < case IPPROTO_ICMP:
>>> < sprintf(upstr, "0x%4.4x", port);
>>> < break;
>>> ---
>>> > if (parser->RaPrintAlgorithmList[parser->RaPrintIndex] != NULL)
>>> > format = parser->RaPrintAlgorithmList[parser->RaPrintIndex]->format;
>>> 7207,7209c7205,7209
>>> < default:
>>> < sprintf(upstr, "%d", port);
>>> < break;
>>> ---
>>> > if ((format == NULL) || (strlen(format) == 0)) {
>>> > switch (proto) {
>>> > case IPPROTO_ICMP: format = "0x%4.4x"; break;
>>> > default: format = "%d"; break;
>>> > }
>>> 7211a7212,7213
>>> > snprintf (upstr, 128, format, port);
>>> >
>>>
>>> Carter
>>>
>>> On Jun 25, 2012, at 9:16 AM, Carter Bullard wrote:
>>>
>>>> Hey CS Lee,
>>>> Hmmmmm, well that is not how I thought it would go. This patch will enable the print format
>>>> extension for all port values. So, " sport::%d dport::%d" should work for icmp as well.
>>>>
>>>> I'm adding this to the main thread of code, if it works for you, so it will be in any new
>>>> clients tarfiles that we release. And it will be in argus-clients-3.0.7.1.
>>>>
>>>> Carter
>>>>
>>>> ==== //depot/argus-3.0.6/clients/common/argus_util.c#3 - /Volumes/Users/carter/argus/release/argus-3.0.6/clients/common/argus_util.c ====
>>>> 7202,7205c7202,7203
>>>> < switch (proto) {
>>>> < case IPPROTO_ICMP:
>>>> < sprintf(upstr, "0x%4.4x", port);
>>>> < break;
>>>> ---
>>>> > if (parser->RaPrintAlgorithmList[parser->RaPrintIndex] != NULL) {
>>>> > char *format = parser->RaPrintAlgorithmList[parser->RaPrintIndex]->format;
>>>> 7207,7209c7205,7211
>>>> < default:
>>>> < sprintf(upstr, "%d", port);
>>>> < break;
>>>> ---
>>>> > if ((format == NULL) || (strlen(format) == 0)) {
>>>> > switch (proto) {
>>>> > case IPPROTO_ICMP: format = "0x%4.4x"; break;
>>>> > default: format = "%d"; break;
>>>> > }
>>>> > }
>>>> > snprintf (upstr, 128, format, port);
>>>>
>>>> Carter
>>>>
>>>> On Jun 25, 2012, at 12:27 AM, CS Lee wrote:
>>>>
>>>>> hi Carter,
>>>>>
>>>>> While the changes work correctly for tcp and udp, it seems the hex value doesn't get to convert to decimal for icmp in sport and dport.
>>>>>
>>>>> Default
>>>>> mysql> SELECT stime, saddr, sport, daddr, dport, spkts, dpkts, proto, state FROM tbl_argus;
>>>>> +-------------------+-------------+--------+----------------+--------+-------+-------+-------+-------+
>>>>> | stime | saddr | sport | daddr | dport | spkts | dpkts | proto | state |
>>>>> +-------------------+-------------+--------+----------------+--------+-------+-------+-------+-------+
>>>>> | 1340623505.000000 | 192.168.1.3 | 0x0008 | 209.85.175.139 | 0x1723 | 8 | 1 | icmp | ECO |
>>>>> +-------------------+-------------+--------+----------------+--------+-------+-------+-------+-------+
>>>>>
>>>>> After drop the table, and apply the patch
>>>>>
>>>>> mysql> SELECT stime, saddr, sport, daddr, dport, spkts, dpkts, proto, state FROM tbl_argus;
>>>>> +-------------------+------------+-------+------------+-------+-------+-------+-------+-------+
>>>>> | stime | saddr | sport | daddr | dport | spkts | dpkts | proto | state |
>>>>> +-------------------+------------+-------+------------+-------+-------+-------+-------+-------+
>>>>> | 1340623505.000000 | 3232235779 | 0 | 3512053643 | 0 | 8 | 1 | icmp | ECO |
>>>>> +-------------------+------------+-------+------------+-------+-------+-------+-------+-------+
>>>>> 1 row in set (0.00 sec)
>>>>>
>>>>> If hex conversion is right, then 0x0008 will be 8 and 0x1723 will be 5923 in decimal. Here's my testing table -
>>>>>
>>>>> mysql> desc tbl_argus;
>>>>> +-----------+-----------------------+------+-----+---------+----------------+
>>>>> | Field | Type | Null | Key | Default | Extra |
>>>>> +-----------+-----------------------+------+-----+---------+----------------+
>>>>> | autoid | int(11) | NO | PRI | NULL | auto_increment |
>>>>> | stime | double(18,6) unsigned | NO | | NULL | |
>>>>> | ltime | double(18,6) unsigned | NO | | NULL | |
>>>>> | dur | double(18,6) | NO | | NULL | |
>>>>> | srcid | varchar(64) | YES | | NULL | |
>>>>> | flgs | varchar(32) | YES | | NULL | |
>>>>> | proto | varchar(16) | NO | | NULL | |
>>>>> | saddr | varchar(64) | NO | | NULL | |
>>>>> | sport | smallint(5) unsigned | YES | | NULL | |
>>>>> | dir | varchar(3) | YES | | NULL | |
>>>>> | daddr | varchar(64) | NO | | NULL | |
>>>>> | dport | smallint(5) unsigned | YES | | NULL | |
>>>>> | pkts | bigint(20) | YES | | NULL | |
>>>>> | bytes | bigint(20) | YES | | NULL | |
>>>>> | appbytes | bigint(20) | YES | | NULL | |
>>>>> | state | varchar(32) | YES | | NULL | |
>>>>> | spkts | bigint(20) | YES | | NULL | |
>>>>> | dpkts | bigint(20) | YES | | NULL | |
>>>>> | sbytes | bigint(20) | YES | | NULL | |
>>>>> | dbytes | bigint(20) | YES | | NULL | |
>>>>> | sappbytes | bigint(20) | YES | | NULL | |
>>>>> | dappbytes | bigint(20) | YES | | NULL | |
>>>>> | smaxsz | smallint(5) unsigned | YES | | NULL | |
>>>>> | dmaxsz | smallint(5) unsigned | YES | | NULL | |
>>>>> | sminsz | smallint(5) unsigned | YES | | NULL | |
>>>>> | dminsz | smallint(5) unsigned | YES | | NULL | |
>>>>> | smac | varchar(24) | YES | | NULL | |
>>>>> | dmac | varchar(24) | YES | | NULL | |
>>>>> | sco | varchar(2) | YES | | NULL | |
>>>>> | dco | varchar(2) | YES | | NULL | |
>>>>> | sas | int(10) unsigned | YES | | NULL | |
>>>>> | das | int(10) unsigned | YES | | NULL | |
>>>>> | record | blob | YES | | NULL | |
>>>>> +-----------+-----------------------+------+-----+---------+----------------+
>>>>> 33 rows in set (0.01 sec)
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>>
>>>>> CS Lee<geek00L[at]gmail.com>
>>>>>
>>>>> http://geek00l.blogspot.com
>>>>> http://defcraft.net
>>>>
>>>
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>>
>>> CS Lee<geek00L[at]gmail.com>
>>>
>>> http://geek00l.blogspot.com
>>> http://defcraft.net
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>>
>>> CS Lee<geek00L[at]gmail.com>
>>>
>>> http://geek00l.blogspot.com
>>> http://defcraft.net
>>
>>
>>
>>
>> --
>> Best Regards,
>>
>> CS Lee<geek00L[at]gmail.com>
>>
>> http://geek00l.blogspot.com
>> http://defcraft.net
>>
>>
>>
>> --
>> Best Regards,
>>
>> CS Lee<geek00L[at]gmail.com>
>>
>> http://geek00l.blogspot.com
>> http://defcraft.net
>>
>>
>>
>> --
>> Best Regards,
>>
>> CS Lee<geek00L[at]gmail.com>
>>
>> http://geek00l.blogspot.com
>> http://defcraft.net
>
>
>
>
> --
> Best Regards,
>
> CS Lee<geek00L[at]gmail.com>
>
> http://geek00l.blogspot.com
> http://defcraft.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20121013/893c7244/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2589 bytes
Desc: not available
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20121013/893c7244/attachment.bin>
More information about the argus
mailing list