mysql database changes - new patch
CS Lee
geek00l at gmail.com
Sat Oct 13 03:44:01 EDT 2012
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20121013/e36ea96b/attachment.html>
More information about the argus
mailing list