mysql database changes - new patch
CS Lee
geek00l at gmail.com
Sat Oct 13 11:54:17 EDT 2012
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/51ba4dba/attachment.html>
More information about the argus
mailing list