mysql database changes - new patch
CS Lee
geek00l at gmail.com
Mon Jun 25 11:04:28 EDT 2012
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20120625/33d76e8a/attachment.html>
More information about the argus
mailing list