mysql oddness

Jeremy Hoel

unread,

May 10, 2013, 12:56:24 PM5/10/13

to barnyar...@googlegroups.com

So, after the questions about the duplicates from earlier in the week,
I went through and cleared out the sig_reference table and the one
sensor with the upgrade was working fine, suppressing alerts and doing
great.

I deployed the new version of BY2 to another 3 sensors and made sure
it was good overnight and it was. So then I deployed it to the other
45 yesterday and not I see that the mysql server that they all take
too is very unhappy.

the mysql service is taking 3 of the 4 cores, nonstop and all the
sensors seem to still be doing 'SELECT sig_id FROM signature WHERE'
events to the DB.. if I look at network traffic it seems like they
are all polling put not putting up events.

All the sensors have the same output line with
disable_signature_reference_table at the end.. and looking at hte
server logs, it seems they never get to the point where barnyard
outputs the datanbase: lines (ie:

May 7 09:27:33 st001 barnyard2[996]: database: compiled support for (mysql)
May 7 09:27:33 st001 barnyard2[996]: database: configured to use mysql
May 7 09:27:33 st001 barnyard2[996]: database: schema version = 107
May 7 09:27:33 st001 barnyard2[996]: database: host = 10.10.189.133
May 7 09:27:33 st001 barnyard2[996]: database: user = snorbyuser
May 7 09:27:33 st001 barnyard2[996]: database: database name = snorby
May 7 09:27:33 st001 barnyard2[996]: database: sensor name = st001:eth1
May 7 09:27:33 st001 barnyard2[996]: database: sensor id = 52
May 7 09:27:33 st001 barnyard2[996]: database: sensor cid = 83642
May 7 09:27:33 st001 barnyard2[996]: database: data encoding = hex
May 7 09:27:33 st001 barnyard2[996]: database: detail level = full
May 7 09:27:33 st001 barnyard2[996]: database: ignore_bpf = no
May 7 09:27:33 st001 barnyard2[996]: database: using the "alert" facility

.. I assume that comes after it finishes "signauture cache
synchronization process" but that seems to be taking a long time.

The DB is using InnoDB for the tables.. there's plenty of diskspace.
Current top is:

top - 16:55:41 up 30 min, 1 user, load average: 0.12, 0.30, 0.23
Tasks: 184 total, 1 running, 183 sleeping, 0 stopped, 0 zombie
Cpu(s): 76.6%us, 4.7%sy, 0.0%ni, 18.2%id, 0.1%wa, 0.0%hi, 0.0%si, 0.3%st
Mem: 4054784k total, 807200k used, 3247584k free, 69396k buffers
Swap: 1999992k total, 0k used, 1999992k free, 269012k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1543 mysql 20 0 2174m 39m 6492 S 334.1 1.0 94:59.90 mysqld
1881 root 20 0 445m 92m 2756 S 0.3 2.3 0:05.37 ruby
1 root 20 0 19356 1504 1188 S 0.0 0.0 0:00.75 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd

So I need to figure out why mysql is very very sluggish and by2
doesn't finish starting up.

beenph

unread,

May 10, 2013, 1:17:51 PM5/10/13

to barnyar...@googlegroups.com

On Fri, May 10, 2013 at 12:56 PM, Jeremy Hoel <jth...@gmail.com> wrote:
> So, after the questions about the duplicates from earlier in the week,
> I went through and cleared out the sig_reference table and the one
> sensor with the upgrade was working fine, suppressing alerts and doing
> great.
>
> I deployed the new version of BY2 to another 3 sensors and made sure
> it was good overnight and it was. So then I deployed it to the other
> 45 yesterday and not I see that the mysql server that they all take
> too is very unhappy.

You know that 2-1.13 from github (depending on when you pulled the
source is still beta Jeremy?)
Today sent some last minutes updates that should make 2-1.13 get out of beta.

You upgraded from 2-1.10 right?

>
> the mysql service is taking 3 of the 4 cores, nonstop and all the
> sensors seem to still be doing 'SELECT sig_id FROM signature WHERE'
> events to the DB.. if I look at network traffic it seems like they
> are all polling put not putting up events.
>

I am not sure i fully understand the above statement can you expand in
more details please?

> All the sensors have the same output line with
> disable_signature_reference_table at the end.. and looking at hte
> server logs, it seems they never get to the point where barnyard
> outputs the datanbase: lines (ie:
>
> May 7 09:27:33 st001 barnyard2[996]: database: compiled support for (mysql)
> May 7 09:27:33 st001 barnyard2[996]: database: configured to use mysql
> May 7 09:27:33 st001 barnyard2[996]: database: schema version = 107
> May 7 09:27:33 st001 barnyard2[996]: database: host = 10.10.189.133
> May 7 09:27:33 st001 barnyard2[996]: database: user = snorbyuser
> May 7 09:27:33 st001 barnyard2[996]: database: database name = snorby
> May 7 09:27:33 st001 barnyard2[996]: database: sensor name = st001:eth1
> May 7 09:27:33 st001 barnyard2[996]: database: sensor id = 52
> May 7 09:27:33 st001 barnyard2[996]: database: sensor cid = 83642
> May 7 09:27:33 st001 barnyard2[996]: database: data encoding = hex
> May 7 09:27:33 st001 barnyard2[996]: database: detail level = full
> May 7 09:27:33 st001 barnyard2[996]: database: ignore_bpf = no
> May 7 09:27:33 st001 barnyard2[996]: database: using the "alert" facility
>
> .. I assume that comes after it finishes "signauture cache
> synchronization process" but that seems to be taking a long time.
>

It can take a long time depending on the amount of signature you have enabled.
How mutch time are we speaking here, (also the metrics described below
are important
in the equation, and you have to multiply that by the number of
instance your trying to start/restart at the same time)

If you have 15k signature per instance (in map files) and a few K of
signature in the table its normal it will take some time to startup.
2-1.13 shouldn't be to different from 2-1.10 in the manner, beside
that if you uses disable_signature_reference_table
it will startup faster since it will completly ignore the generation
of that cache, the comparaison of that table and the cache,
and the possible insertion of missing value).

> The DB is using InnoDB for the tables.. there's plenty of diskspace.
> Current top is:
>
> top - 16:55:41 up 30 min, 1 user, load average: 0.12, 0.30, 0.23
> Tasks: 184 total, 1 running, 183 sleeping, 0 stopped, 0 zombie
> Cpu(s): 76.6%us, 4.7%sy, 0.0%ni, 18.2%id, 0.1%wa, 0.0%hi, 0.0%si, 0.3%st
> Mem: 4054784k total, 807200k used, 3247584k free, 69396k buffers
> Swap: 1999992k total, 0k used, 1999992k free, 269012k cached
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 1543 mysql 20 0 2174m 39m 6492 S 334.1 1.0 94:59.90 mysqld
> 1881 root 20 0 445m 92m 2756 S 0.3 2.3 0:05.37 ruby
> 1 root 20 0 19356 1504 1188 S 0.0 0.0 0:00.75 init
> 2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
>

Did you build mysql from source? Did you tune your mysql server using
different performance enchancement
techniques availble? What type of file system do you use? what type of
storage media do you use etc...

Database performance to query can be something to look at especialy
when the number of connection to it scale.

If you never have tunned it and let it mostly stock and you scaled on
that you could have reached a point where you need to
tune it to get a bang for the buck.

-elz

Jeremy Hoel

unread,

May 10, 2013, 1:28:42 PM5/10/13

to barnyar...@googlegroups.com

On Fri, May 10, 2013 at 5:17 PM, beenph <bee...@gmail.com> wrote:
> On Fri, May 10, 2013 at 12:56 PM, Jeremy Hoel <jth...@gmail.com> wrote:
>> So, after the questions about the duplicates from earlier in the week,
>> I went through and cleared out the sig_reference table and the one
>> sensor with the upgrade was working fine, suppressing alerts and doing
>> great.
>>
>> I deployed the new version of BY2 to another 3 sensors and made sure
>> it was good overnight and it was. So then I deployed it to the other
>> 45 yesterday and not I see that the mysql server that they all take
>> too is very unhappy.
>
> You know that 2-1.13 from github (depending on when you pulled the
> source is still beta Jeremy?)
> Today sent some last minutes updates that should make 2-1.13 get out of beta.

Yes.. I know. But without someone doinng a big test case, we'll never
know if it breaks or not, right? :-)

besides. you're support is awesome.. and worse case, I roll back. But
I would like to see the problems, if they pop up, get fixed earlier
then later.

> You upgraded from 2-1.10 right?

We came from BY2 2.1.11 (317)

>>
>> the mysql service is taking 3 of the 4 cores, nonstop and all the
>> sensors seem to still be doing 'SELECT sig_id FROM signature WHERE'
>> events to the DB.. if I look at network traffic it seems like they
>> are all polling put not putting up events.
>>
>
> I am not sure i fully understand the above statement can you expand in
> more details please?

Normally.. when BY2 is running, it just sends alerts right? But
before it gets to that spot it does this caching phase (I assume for
the client side).. and it seems all the sensors are stuck in this
caching phase. And we are talking about hours.. but it couold be
that it's rebuilding the signature table for each sensor

The amount of signatures hasn't changes between 2.1.11 and 2.1.13 -
we are at 15.5K active rules.
[jhoel@LNXJH ~]$ cat /etc/snort/rules/*.rules |grep -v \# |wc -l
15564

mysql> select count(*) from signature;
+----------+
| count(*) |
+----------+
| 295695 |
+----------+
1 row in set (9.56 sec)

Each sensor shouldn't be trying to write all it's rules to the DB should it?

That number is going up. I did a count 30 minutes earlier and it was 292851

I did not however have a count from before I wiped it before the upgrade.

>> The DB is using InnoDB for the tables.. there's plenty of diskspace.
>> Current top is:
>>
>> top - 16:55:41 up 30 min, 1 user, load average: 0.12, 0.30, 0.23
>> Tasks: 184 total, 1 running, 183 sleeping, 0 stopped, 0 zombie
>> Cpu(s): 76.6%us, 4.7%sy, 0.0%ni, 18.2%id, 0.1%wa, 0.0%hi, 0.0%si, 0.3%st
>> Mem: 4054784k total, 807200k used, 3247584k free, 69396k buffers
>> Swap: 1999992k total, 0k used, 1999992k free, 269012k cached
>>
>> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>> 1543 mysql 20 0 2174m 39m 6492 S 334.1 1.0 94:59.90 mysqld
>> 1881 root 20 0 445m 92m 2756 S 0.3 2.3 0:05.37 ruby
>> 1 root 20 0 19356 1504 1188 S 0.0 0.0 0:00.75 init
>> 2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
>>
> Did you build mysql from source? Did you tune your mysql server using
> different performance enchancement
> techniques availble? What type of file system do you use? what type of
> storage media do you use etc...
>
> Database performance to query can be something to look at especialy
> when the number of connection to it scale.
>
> If you never have tunned it and let it mostly stock and you scaled on
> that you could have reached a point where you need to
> tune it to get a bang for the buck.

The only thing I can think of that's changed is moving to by 2.1.13

Up until the testing, it's been running fine under 2 cores.. since
it's gone a bit crazy, I bumped it to 4, hoping that would go away.
Other then that.. it's a VM box, shared SAS disks, RAID 5 array that
normally doesn't peak under 50% of of core load.. with little IO.. And
it's been running BY2 and the database for 5 months with no problem,
with the same number of servers.

Jeremy Hoel

unread,

May 10, 2013, 1:39:21 PM5/10/13

to barnyar...@googlegroups.com

beenph

unread,

May 10, 2013, 6:05:25 PM5/10/13

to barnyar...@googlegroups.com

On Fri, May 10, 2013 at 1:28 PM, Jeremy Hoel <jth...@gmail.com> wrote:
>
>> You upgraded from 2-1.10 right?
>
> We came from BY2 2.1.11 (317)
>
>>>
>>> the mysql service is taking 3 of the 4 cores, nonstop and all the
>>> sensors seem to still be doing 'SELECT sig_id FROM signature WHERE'
>>> events to the DB.. if I look at network traffic it seems like they
>>> are all polling put not putting up events.
>>>
>>
>> I am not sure i fully understand the above statement can you expand in
>> more details please?
>
> Normally.. when BY2 is running, it just sends alerts right? But
> before it gets to that spot it does this caching phase (I assume for
> the client side).. and it seems all the sensors are stuck in this
> caching phase. And we are talking about hours.. but it couold be
> that it's rebuilding the signature table for each sensor
>

Not since 2-1.10.

In 2-1.10 we introduced the revamped database output that use cache
instead of processing signature lookup in the database and then insert
the signature
if it does not exist each time it read an event. (imagine that in your context).

The caching of signature metadata happen at startup and synchronization follows.
Technically there is a slight timer difference that should prevent
race condition
mabey this is what your hitting but i would doubt that i think its
something else
related to 2-1.13-BETA (see below).

That shoudn't happen. (mentioned above)

If i was you right now i would stop all barnyard2 instances.

BEFORE doing the delete could you run the following query and send me
the result:
SELECT * FROM (SELECT count(*) AS a,sig_sid,sig_gid FROM signature
GROUP BY sig_sid,sig_gid) AS tq WHERE tq.a>1;

Do the following (you already deleted all your signature previously so
..once more shouldn't be a problem)
DELETE FROM signature;
DELETE FROM sig_reference;
DELETE FROM reference;

Or create a new virgin db aka snort-2and make instance point to it so
you do not have unresolved
signature in your UI.

Fetch the following code:
https://github.com/binf/barnyard2/tree/fix-signals

build and restart your instances.

>> Did you build mysql from source? Did you tune your mysql server using
>> different performance enchancement
>> techniques availble? What type of file system do you use? what type of
>> storage media do you use etc...
>>
>> Database performance to query can be something to look at especialy
>> when the number of connection to it scale.
>>
>> If you never have tunned it and let it mostly stock and you scaled on
>> that you could have reached a point where you need to
>> tune it to get a bang for the buck.
>
> The only thing I can think of that's changed is moving to by 2.1.13

2-1.13-BETA (wink wink)

> Up until the testing, it's been running fine under 2 cores.. since
> it's gone a bit crazy, I bumped it to 4, hoping that would go away.
> Other then that.. it's a VM box, shared SAS disks, RAID 5 array that
> normally doesn't peak under 50% of of core load.. with little IO.. And
> it's been running BY2 and the database for 5 months with no problem,
> with the same number of servers.
>

Not because it seem's to run fine that there is no optimisation possible.
Database are monsters of their own it offent does not scale horizontaly without
a little help from a few tweaks.

Now in this case i would say its probably getting a kick in the butt
from the duplicate preprocessor signature insertion bug and this could
lead to the crazyness.

Just the fact that the database is running inside a vm with shared disk I/O
could make a few people hair falls ;)

But lets rule this out for now since i honestly think its something else.

Could you give go to the branch proposed above and give us feedback.

(the branch had 3 fixes 1 for signal reload, 1 for suppression (your
1st issue) and an other issue regarding preprocessor signature that
Bill green reported earlier and has also been fixed).

Hope this can help out.

-elz

Jeremy Hoel

unread,

May 10, 2013, 6:34:44 PM5/10/13

to barnyar...@googlegroups.com

Just a quick update..

When I got back from lunch I went through and set all the sensors to
go back and rebuild barnyard using the older 2.1.11 build. Once they
finished building and starting up, after caching was complete and I
got the Database: output in the logs.. once all of them caught up,
mysql usage dropped back down to it's normal sub 5% usage. Before
that, hours in, none of the 40+ sensors had gotten that far. This
only took about 10 minutes a box, once the startup started

I was going to try and use the fix-signals branch first, but I
couldn't see it in the firnsy tree.. i see now it's in binf. That was
going to be my first test. So, right now I'll let this stabilize over
the weekend.. make sure all the alerts catch up and come in and then
look about trying the upgrade again next week.

This seems to be constant now.. not moving up or anything..

mysql> select count(*) from signature;
+----------+
| count(*) |
+----------+

| 302805 |
+----------+
1 row in set (0.06 sec)

Here's the query you asked about. I'm going to hold on on deleteing
anything until you have a look and We get a good idea where we might
need to go. Like I said, right now it's stable, alerts are showing up
and they have the correct information in them.

mysql> SELECT * FROM (SELECT count(*) AS a,sig_sid,sig_gid FROM

signature GROUP BY sig_sid,sig_gid) AS tq WHERE tq.a>1;

+-----+---------+---------+
| a | sig_sid | sig_gid |
+-----+---------+---------+
| 638 | 1 | 2 |
| 638 | 1 | 100 |
| 638 | 1 | 101 |
| 638 | 1 | 102 |
| 638 | 1 | 103 |
| 638 | 1 | 104 |
| 638 | 1 | 105 |
| 638 | 1 | 106 |
| 638 | 1 | 110 |
| 638 | 1 | 111 |
| 638 | 1 | 112 |
| 638 | 1 | 113 |
| 638 | 1 | 114 |
| 638 | 1 | 115 |
| 638 | 1 | 116 |
| 638 | 1 | 117 |
| 638 | 1 | 118 |
| 638 | 1 | 119 |
| 638 | 1 | 120 |
| 638 | 1 | 121 |
| 639 | 1 | 122 |
| 638 | 1 | 123 |
| 638 | 1 | 124 |
| 638 | 1 | 125 |
| 638 | 1 | 126 |
| 638 | 1 | 128 |
| 638 | 1 | 129 |
| 638 | 1 | 130 |
| 638 | 1 | 131 |
| 638 | 1 | 133 |
| 638 | 1 | 134 |
| 638 | 1 | 135 |
| 638 | 1 | 136 |
| 638 | 1 | 137 |
| 638 | 1 | 139 |
| 638 | 1 | 140 |
| 638 | 1 | 141 |
| 638 | 1 | 142 |
| 638 | 1 | 143 |
| 638 | 1 | 144 |
| 638 | 1 | 145 |
| 638 | 2 | 100 |
| 638 | 2 | 102 |
| 638 | 2 | 103 |
| 638 | 2 | 104 |
| 638 | 2 | 105 |
| 638 | 2 | 106 |
| 638 | 2 | 110 |
| 638 | 2 | 111 |
| 638 | 2 | 112 |
| 638 | 2 | 113 |
| 638 | 2 | 114 |
| 638 | 2 | 115 |
| 638 | 2 | 116 |
| 638 | 2 | 119 |
| 638 | 2 | 120 |
| 638 | 2 | 121 |
| 638 | 2 | 122 |
| 639 | 2 | 123 |
| 638 | 2 | 124 |
| 638 | 2 | 125 |
| 638 | 2 | 126 |
| 638 | 2 | 128 |
| 638 | 2 | 129 |
| 638 | 2 | 131 |
| 638 | 2 | 133 |
| 638 | 2 | 134 |
| 638 | 2 | 135 |
| 638 | 2 | 136 |
| 638 | 2 | 137 |
| 638 | 2 | 138 |
| 638 | 2 | 140 |
| 638 | 2 | 141 |
| 638 | 2 | 142 |
| 638 | 2 | 143 |
| 638 | 2 | 144 |
| 638 | 2 | 145 |
| 638 | 3 | 100 |
| 638 | 3 | 102 |
| 638 | 3 | 105 |
| 638 | 3 | 106 |
| 638 | 3 | 110 |
| 638 | 3 | 111 |
| 638 | 3 | 112 |
| 638 | 3 | 113 |
| 638 | 3 | 114 |
| 638 | 3 | 115 |
| 638 | 3 | 116 |
| 639 | 3 | 119 |
| 638 | 3 | 120 |
| 638 | 3 | 121 |
| 639 | 3 | 122 |
| 638 | 3 | 123 |
| 638 | 3 | 124 |
| 638 | 3 | 125 |
| 639 | 3 | 126 |
| 639 | 3 | 128 |
| 638 | 3 | 129 |
| 638 | 3 | 131 |
| 638 | 3 | 133 |
| 638 | 3 | 135 |
| 638 | 3 | 138 |
| 638 | 3 | 140 |
| 638 | 3 | 141 |
| 638 | 3 | 142 |
| 638 | 3 | 143 |
| 638 | 3 | 144 |
| 638 | 3 | 145 |
| 638 | 4 | 102 |
| 638 | 4 | 105 |
| 638 | 4 | 106 |
| 638 | 4 | 110 |
| 638 | 4 | 111 |
| 638 | 4 | 112 |
| 638 | 4 | 113 |
| 638 | 4 | 114 |
| 638 | 4 | 115 |
| 638 | 4 | 116 |
| 638 | 4 | 119 |
| 638 | 4 | 120 |
| 638 | 4 | 121 |
| 639 | 4 | 122 |
| 638 | 4 | 123 |
| 638 | 4 | 124 |
| 638 | 4 | 125 |
| 640 | 4 | 128 |
| 638 | 4 | 129 |
| 638 | 4 | 133 |
| 638 | 4 | 138 |
| 638 | 4 | 140 |
| 638 | 4 | 141 |
| 638 | 4 | 142 |
| 638 | 4 | 145 |
| 638 | 5 | 102 |
| 638 | 5 | 106 |
| 638 | 5 | 111 |
| 638 | 5 | 113 |
| 638 | 5 | 115 |
| 638 | 5 | 116 |
| 638 | 5 | 119 |
| 638 | 5 | 120 |
| 638 | 5 | 122 |
| 638 | 5 | 123 |
| 638 | 5 | 124 |
| 638 | 5 | 125 |
| 638 | 5 | 128 |
| 638 | 5 | 129 |
| 638 | 5 | 133 |
| 638 | 5 | 138 |
| 638 | 5 | 140 |
| 638 | 5 | 141 |
| 638 | 5 | 142 |
| 638 | 5 | 145 |
| 638 | 6 | 102 |
| 638 | 6 | 111 |
| 638 | 6 | 113 |
| 638 | 6 | 116 |
| 638 | 6 | 119 |
| 638 | 6 | 120 |
| 638 | 6 | 122 |
| 638 | 6 | 123 |
| 638 | 6 | 124 |
| 638 | 6 | 125 |
| 638 | 6 | 128 |
| 638 | 6 | 129 |
| 638 | 6 | 133 |
| 638 | 6 | 138 |
| 638 | 6 | 140 |
| 638 | 6 | 141 |
| 638 | 6 | 142 |
| 638 | 6 | 145 |
| 638 | 7 | 102 |
| 638 | 7 | 111 |
| 638 | 7 | 113 |
| 638 | 7 | 119 |
| 638 | 7 | 120 |
| 638 | 7 | 122 |
| 638 | 7 | 123 |
| 638 | 7 | 124 |
| 638 | 7 | 125 |
| 638 | 7 | 128 |
| 638 | 7 | 129 |
| 638 | 7 | 133 |
| 638 | 7 | 140 |
| 638 | 7 | 141 |
| 638 | 7 | 142 |
| 638 | 8 | 111 |
| 638 | 8 | 113 |
| 638 | 8 | 119 |
| 638 | 8 | 120 |
| 638 | 8 | 122 |
| 638 | 8 | 123 |
| 638 | 8 | 124 |
| 638 | 8 | 125 |
| 638 | 8 | 129 |
| 638 | 8 | 133 |
| 638 | 8 | 140 |
| 638 | 9 | 111 |
| 638 | 9 | 113 |
| 638 | 9 | 119 |
| 638 | 9 | 120 |
| 638 | 9 | 122 |
| 638 | 9 | 123 |
| 638 | 9 | 124 |
| 638 | 9 | 125 |
| 638 | 9 | 129 |
| 638 | 9 | 133 |
| 638 | 9 | 140 |
| 638 | 10 | 111 |
| 638 | 10 | 113 |
| 638 | 10 | 119 |
| 638 | 10 | 120 |
| 638 | 10 | 122 |
| 638 | 10 | 123 |
| 638 | 10 | 124 |
| 638 | 10 | 129 |
| 638 | 10 | 133 |
| 638 | 10 | 140 |
| 638 | 11 | 111 |
| 638 | 11 | 119 |
| 638 | 11 | 120 |
| 638 | 11 | 122 |
| 638 | 11 | 123 |
| 638 | 11 | 124 |
| 638 | 11 | 129 |
| 638 | 11 | 133 |
| 638 | 11 | 140 |
| 638 | 12 | 111 |
| 638 | 12 | 119 |
| 638 | 12 | 122 |
| 638 | 12 | 123 |
| 638 | 12 | 124 |
| 638 | 12 | 129 |
| 638 | 12 | 133 |
| 638 | 12 | 140 |
| 638 | 13 | 111 |
| 638 | 13 | 119 |
| 638 | 13 | 122 |
| 639 | 13 | 123 |
| 638 | 13 | 124 |
| 638 | 13 | 129 |
| 638 | 13 | 133 |
| 638 | 13 | 140 |
| 638 | 14 | 111 |
| 638 | 14 | 119 |
| 638 | 14 | 122 |
| 638 | 14 | 129 |
| 638 | 14 | 133 |
| 638 | 14 | 140 |
| 638 | 15 | 111 |
| 638 | 15 | 119 |
| 638 | 15 | 122 |
| 638 | 15 | 129 |
| 638 | 15 | 133 |
| 638 | 15 | 140 |
| 638 | 16 | 111 |
| 638 | 16 | 119 |
| 638 | 16 | 122 |
| 638 | 16 | 129 |
| 638 | 16 | 133 |
| 638 | 16 | 140 |
| 638 | 17 | 111 |
| 638 | 17 | 119 |
| 639 | 17 | 122 |
| 638 | 17 | 129 |
| 638 | 17 | 133 |
| 638 | 17 | 140 |
| 638 | 18 | 111 |
| 638 | 18 | 119 |
| 638 | 18 | 122 |
| 638 | 18 | 129 |
| 638 | 18 | 133 |
| 638 | 18 | 140 |
| 638 | 19 | 111 |
| 638 | 19 | 119 |
| 639 | 19 | 122 |
| 638 | 19 | 129 |
| 638 | 19 | 133 |
| 638 | 19 | 140 |
| 638 | 20 | 111 |
| 638 | 20 | 119 |
| 638 | 20 | 122 |
| 638 | 20 | 133 |
| 638 | 20 | 140 |
| 638 | 21 | 111 |
| 639 | 21 | 119 |
| 638 | 21 | 122 |
| 638 | 21 | 133 |
| 638 | 21 | 140 |
| 638 | 22 | 111 |
| 638 | 22 | 119 |
| 638 | 22 | 122 |
| 638 | 22 | 133 |
| 638 | 22 | 140 |
| 638 | 23 | 111 |
| 638 | 23 | 119 |
| 638 | 23 | 122 |
| 638 | 23 | 133 |
| 638 | 23 | 140 |
| 638 | 24 | 111 |
| 639 | 24 | 119 |
| 638 | 24 | 122 |
| 638 | 24 | 133 |
| 638 | 24 | 140 |
| 638 | 25 | 111 |
| 638 | 25 | 119 |
| 639 | 25 | 122 |
| 638 | 25 | 133 |
| 638 | 25 | 140 |
| 638 | 26 | 119 |
| 638 | 26 | 122 |
| 638 | 26 | 133 |
| 638 | 26 | 140 |
| 638 | 27 | 119 |
| 638 | 27 | 122 |
| 638 | 27 | 133 |
| 639 | 28 | 119 |
| 638 | 28 | 133 |
| 638 | 29 | 119 |
| 638 | 29 | 133 |
| 638 | 30 | 119 |
| 638 | 30 | 133 |
| 638 | 31 | 119 |
| 638 | 31 | 133 |
| 638 | 32 | 119 |
| 638 | 32 | 133 |
| 638 | 33 | 133 |
| 638 | 34 | 133 |
| 638 | 35 | 133 |
| 638 | 36 | 133 |
| 638 | 37 | 133 |
| 638 | 38 | 133 |
| 638 | 39 | 133 |
| 638 | 40 | 133 |
| 638 | 41 | 133 |
| 638 | 42 | 133 |
| 638 | 43 | 133 |
| 638 | 45 | 116 |
| 638 | 46 | 116 |
| 638 | 47 | 116 |
| 638 | 54 | 116 |
| 638 | 55 | 116 |
| 638 | 56 | 116 |
| 638 | 57 | 116 |
| 638 | 58 | 116 |
| 638 | 59 | 116 |
| 638 | 95 | 116 |
| 638 | 96 | 116 |
| 638 | 97 | 116 |
| 638 | 98 | 116 |
| 638 | 105 | 116 |
| 638 | 106 | 116 |
| 638 | 107 | 116 |
| 638 | 108 | 116 |
| 638 | 109 | 116 |
| 638 | 110 | 116 |
| 638 | 111 | 116 |
| 638 | 112 | 116 |
| 638 | 120 | 116 |
| 638 | 130 | 116 |
| 638 | 131 | 116 |
| 638 | 132 | 116 |
| 638 | 133 | 116 |
| 638 | 134 | 116 |
| 638 | 140 | 116 |
| 638 | 141 | 116 |
| 638 | 142 | 116 |
| 638 | 143 | 116 |
| 638 | 150 | 116 |
| 638 | 151 | 116 |
| 638 | 160 | 116 |
| 638 | 161 | 116 |
| 638 | 162 | 116 |
| 638 | 163 | 116 |
| 638 | 164 | 116 |
| 638 | 165 | 116 |
| 638 | 170 | 116 |
| 638 | 171 | 116 |
| 638 | 172 | 116 |
| 638 | 173 | 116 |
| 638 | 174 | 116 |
| 638 | 175 | 116 |
| 638 | 176 | 116 |
| 638 | 250 | 116 |
| 638 | 251 | 116 |
| 638 | 252 | 116 |
| 638 | 253 | 116 |
| 638 | 254 | 116 |
| 638 | 255 | 116 |
| 638 | 270 | 116 |
| 638 | 271 | 116 |
| 638 | 272 | 116 |
| 638 | 273 | 116 |
| 638 | 274 | 116 |
| 638 | 275 | 116 |
| 638 | 276 | 116 |
| 638 | 277 | 116 |
| 638 | 278 | 116 |
| 638 | 279 | 116 |
| 638 | 280 | 116 |
| 638 | 281 | 116 |
| 638 | 282 | 116 |
| 638 | 283 | 116 |
| 638 | 285 | 116 |
| 638 | 286 | 116 |
| 638 | 287 | 116 |
| 638 | 288 | 116 |
| 638 | 289 | 116 |
| 638 | 290 | 116 |
| 638 | 291 | 116 |
| 638 | 292 | 116 |
| 638 | 293 | 116 |
| 638 | 294 | 116 |
| 638 | 295 | 116 |
| 638 | 296 | 116 |
| 638 | 297 | 116 |
| 638 | 298 | 116 |
| 638 | 400 | 116 |
| 638 | 401 | 116 |
| 638 | 402 | 116 |
| 638 | 403 | 116 |
| 638 | 404 | 116 |
| 638 | 405 | 116 |
| 638 | 406 | 116 |
| 638 | 407 | 116 |
| 638 | 408 | 116 |
| 638 | 409 | 116 |
| 638 | 410 | 116 |
| 638 | 411 | 116 |
| 638 | 412 | 116 |
| 638 | 413 | 116 |
| 638 | 414 | 116 |
| 638 | 415 | 116 |
| 638 | 416 | 116 |
| 638 | 417 | 116 |
| 638 | 418 | 116 |
| 638 | 419 | 116 |
| 638 | 420 | 116 |
| 638 | 421 | 116 |
| 638 | 422 | 116 |
| 638 | 423 | 116 |
| 638 | 424 | 116 |
| 638 | 425 | 116 |
| 638 | 426 | 116 |
| 638 | 427 | 116 |
| 638 | 428 | 116 |
| 638 | 429 | 116 |
| 638 | 430 | 116 |
| 638 | 431 | 116 |
| 638 | 432 | 116 |
| 638 | 433 | 116 |
| 638 | 434 | 116 |
| 638 | 435 | 116 |
| 638 | 436 | 116 |
| 638 | 437 | 116 |
| 638 | 438 | 116 |
| 638 | 439 | 116 |
| 638 | 440 | 116 |
| 638 | 441 | 116 |
| 638 | 442 | 116 |
| 638 | 443 | 116 |
| 638 | 444 | 116 |
| 638 | 445 | 116 |
| 638 | 446 | 116 |
| 638 | 447 | 116 |
| 638 | 448 | 116 |
| 638 | 449 | 116 |
| 638 | 450 | 116 |
| 638 | 451 | 116 |
| 638 | 452 | 116 |
| 638 | 453 | 116 |
| 638 | 454 | 116 |
| 638 | 455 | 116 |
| 638 | 456 | 116 |
| 2 | 21845 | 1 |
| 2 | 21846 | 1 |
| 2 | 22088 | 1 |
| 2 | 1000057 | 1 |
| 2 | 1000080 | 1 |
| 3 | 1000081 | 1 |
| 2 | 1000103 | 1 |
| 7 | 1000149 | 1 |
| 5 | 2011582 | 1 |
| 8 | 2014297 | 1 |
| 2 | 2015877 | 1 |
| 2 | 2016058 | 1 |
| 2 | 2016059 | 1 |
| 2 | 2404043 | 1 |
| 5 | 2500041 | 1 |
| 4 | 2500043 | 1 |
| 3 | 2500045 | 1 |
+-----+---------+---------+
491 rows in set (0.34 sec)

And I hear you about the DB on the VM.. but again, it's become fairly
common, depending on how used it is. We have about 300 events a day..
so it's not getting super taxed.

> --
>
> ---
> You received this message because you are subscribed to the Google Groups "barnyard2-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to barnyard2-use...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

beenph

unread,

May 10, 2013, 7:41:08 PM5/10/13

to barnyar...@googlegroups.com

On Fri, May 10, 2013 at 6:34 PM, Jeremy Hoel <jth...@gmail.com> wrote:
> Just a quick update..
>
> When I got back from lunch I went through and set all the sensors to
> go back and rebuild barnyard using the older 2.1.11 build. Once they
> finished building and starting up, after caching was complete and I
> got the Database: output in the logs.. once all of them caught up,
> mysql usage dropped back down to it's normal sub 5% usage. Before
> that, hours in, none of the 40+ sensors had gotten that far. This
> only took about 10 minutes a box, once the startup started
>

> I was going to try and use the fix-signals branch first, but I
> couldn't see it in the firnsy tree.. i see now it's in binf. That was
> going to be my first test. So, right now I'll let this stabilize over
> the weekend.. make sure all the alerts catch up and come in and then
> look about trying the upgrade again next week.
>

Fix-signals will fix whats the query executed below 638 occurences
of the same signature,generator couple, there must have been also some restart
in there. (automatic rule update script (start/stop mabey??)

It will be upstreamed soon an 2-1.13 will be tagged soon enough,
firnsy lives in oceania and i lives in north america
so there is a slight delay in communication inherently derived from that ;).

Yesturday i worked with Bill green to fix exactly whats i think is
happening below and is a regression issue due to integration
of support for sid-msg.mapv2 format in barnyard2.

> This seems to be constant now.. not moving up or anything..
>
> mysql> select count(*) from signature;
> +----------+
> | count(*) |
> +----------+
> | 302805 |
> +----------+
> 1 row in set (0.06 sec)
>

Constant but obviously rediculous if you have ~15k possible sigs and you end
up having 300k entries in the signature table (see above)

Now the problem there is with the schema is that events logged are
logged with database
sid_id and not real gid,sid,rev. If you let it run over the weekend
then the recovery process can become harder
if you do not archive your unified2 files.

So, if you can and trust me since my fix at the time of this writing
has not been yet upstreamed, i would greatly suggest
to use the branch link i provided you as source.

If you want to wait, then you should make sure that you keep unified2
files, delete waldo and re-process old unified2 file when you make the
move.

If you want more clarifications do not hesitate i will try to express
it better, and your testing is appreciated, hopefully we do not want
to
hinder your detection capapbility, and those steps are also
considering this. (in any cases people should archive unified2 file in
uncompressed or compressed form,
for a while in case they hit a DRP senario.)

> Here's the query you asked about. I'm going to hold on on deleteing
> anything until you have a look and We get a good idea where we might
> need to go. Like I said, right now it's stable, alerts are showing up
> and they have the correct information in them.
>
>
>

<SNIP>

> mysql> SELECT * FROM (SELECT count(*) AS a,sig_sid,sig_gid FROM
> signature GROUP BY sig_sid,sig_gid) AS tq WHERE tq.a>1;
> +-----+---------+---------+
> | a | sig_sid | sig_gid |
> +-----+---------+---------+
> | 638 | 1 | 2 |
> | 638 | 1 | 100 |
> | 638 | 1 | 101 |
> | 638 | 1 | 102 |
>

</SNIP>

>
>

>
> And I hear you about the DB on the VM.. but again, it's become fairly
> common, depending on how used it is. We have about 300 events a day..
> so it's not getting super taxed.
>

Well it depends, if its for "local" uses lets say a org with a few
sensors (below hundreds) it should be technically fine,
allways depends on the hypervison and how the ressources are splited
betwen guests. But ultimately if you are at one point
wondering why some operations could be slow (query) this could be a
root issue, especialy in a heavy transactional environement.

If you are only consulting/querying the information and depending on
the ressources assigned/reserved
to the guest it could be fine but its a complete different topic in it
self that i would currently
discard because of the resultset you returned from the query i asked
you for "above".

Lets hope this helps clarify some stuff, its friday.
-elz

Jeremy Hoel

unread,

May 10, 2013, 7:57:37 PM5/10/13

to barnyar...@googlegroups.com

On Fri, May 10, 2013 at 11:41 PM, beenph <bee...@gmail.com> wrote:
> On Fri, May 10, 2013 at 6:34 PM, Jeremy Hoel <jth...@gmail.com> wrote:
>> Just a quick update..
>>
>> When I got back from lunch I went through and set all the sensors to
>> go back and rebuild barnyard using the older 2.1.11 build. Once they
>> finished building and starting up, after caching was complete and I
>> got the Database: output in the logs.. once all of them caught up,
>> mysql usage dropped back down to it's normal sub 5% usage. Before
>> that, hours in, none of the 40+ sensors had gotten that far. This
>> only took about 10 minutes a box, once the startup started
>>
>
>
>> I was going to try and use the fix-signals branch first, but I
>> couldn't see it in the firnsy tree.. i see now it's in binf. That was
>> going to be my first test. So, right now I'll let this stabilize over
>> the weekend.. make sure all the alerts catch up and come in and then
>> look about trying the upgrade again next week.
>>
>
> Fix-signals will fix whats the query executed below 638 occurences
> of the same signature,generator couple, there must have been also some restart
> in there. (automatic rule update script (start/stop mabey??)

Yeah.. good catch. we update snort 4 times a day (new rules) and we
restart BY2 with the new map. I forgot about that too. ha!

>
> It will be upstreamed soon an 2-1.13 will be tagged soon enough,
> firnsy lives in oceania and i lives in north america
> so there is a slight delay in communication inherently derived from that ;).
>
> Yesturday i worked with Bill green to fix exactly whats i think is
> happening below and is a regression issue due to integration
> of support for sid-msg.mapv2 format in barnyard2.

We've always been at war with Oceania.. We've always been at war with Eastasia
hahahah

I was watching that thread.. good stuff there.

>> This seems to be constant now.. not moving up or anything..
>>
>> mysql> select count(*) from signature;
>> +----------+
>> | count(*) |
>> +----------+
>> | 302805 |
>> +----------+
>> 1 row in set (0.06 sec)
>>
>
> Constant but obviously rediculous if you have ~15k possible sigs and you end
> up having 300k entries in the signature table (see above)

I agree, but I wasn't sure I was right. I haven't looked at the code much.

> Now the problem there is with the schema is that events logged are
> logged with database
> sid_id and not real gid,sid,rev. If you let it run over the weekend
> then the recovery process can become harder
> if you do not archive your unified2 files.
>
> So, if you can and trust me since my fix at the time of this writing
> has not been yet upstreamed, i would greatly suggest
> to use the branch link i provided you as source.
>
> If you want to wait, then you should make sure that you keep unified2
> files, delete waldo and re-process old unified2 file when you make the
> move.
>
> If you want more clarifications do not hesitate i will try to express
> it better, and your testing is appreciated, hopefully we do not want
> to
> hinder your detection capapbility, and those steps are also
> considering this. (in any cases people should archive unified2 file in
> uncompressed or compressed form,
> for a while in case they hit a DRP senario.)

I don't mind working through this now.. but now then next week.. I
just wanted to see where things are at.

We do keep the U2 files, so playing them back is not an issue (we keep
90 days worth)..

so stop barnyard access to the mysql, stop mysql,

DELETE FROM signature;
DELETE FROM sig_reference;
DELETE FROM reference;

Then, reimport the U2 files from each sensor, how far back; just since
I started having the problem, yesterday, or all of them?. All the
events are in the DB, so now they will be in there twice.. even if
they have the wrong or erased sig/gids, right?

Thanks for helping out and I'm glad we got it figured out.

beenph

unread,

May 10, 2013, 8:11:58 PM5/10/13

to barnyar...@googlegroups.com

On Fri, May 10, 2013 at 7:57 PM, Jeremy Hoel <jth...@gmail.com> wrote:

> We've always been at war with Oceania.. We've always been at war with Eastasia
> hahahah
>

> I was watching that thread.. good stuff there.
>

>>

>> Constant but obviously rediculous if you have ~15k possible sigs and you end
>> up having 300k entries in the signature table (see above)
>
> I agree, but I wasn't sure I was right. I haven't looked at the code much.
>

Np, its a slight mistakae/regression testing from my part and i am ready to take
blame. But i really think that if you want to test 2-1.13-BETA rel you should
test the fix-signals (for now since last change has not been upstreamed yet).

>
> I don't mind working through this now.. but now then next week.. I
> just wanted to see where things are at.
>
> We do keep the U2 files, so playing them back is not an issue (we keep
> 90 days worth)..
>
> so stop barnyard access to the mysql, stop mysql,
>

You do not need to "stop" mysql. But if you want your UI to react kind,
you should either completly clean the schema or use a alternative schema
and replay unified2 file, replaying the unified2 file shouldn't be
such of a hasle
if your signature base didin't chance alot (aka if signature that
where up X days ago
are still present in sid-msg.map file)

> DELETE FROM signature;
> DELETE FROM sig_reference;
> DELETE FROM reference;
>

If you only do this step, your ui might still display unreliable
signature information
since signature that are not found will probably lead to a display
error for a givien event where its
signature information would be now missing.

By signature that are not found i mean sig_id what is "database
assigned signature id" and has
nothing to do with the triplet [gid,sid,rev].

So the best way using the current schema to make sure your UI react as
expected is either to clean every
table and replay or use a new db and replay unified2 file imho both
are equivalent, but require different step.
And if you use MySQL, the 1st step (clearing the schema completly) is
the easiest way.

> Then, reimport the U2 files from each sensor, how far back; just since
> I started having the problem, yesterday, or all of them?. All the
> events are in the DB, so now they will be in there twice.. even if
> they have the wrong or erased sig/gids, right?
>
> Thanks for helping out and I'm glad we got it figured out.
>

Note, before re-importing data, make sure you copy back archived
unified2 files to the log directory and delete the waldo file, before
restarting barnyard2 processes.

NP, let us know whats up.

Cheers,
-elz

Jeremy Hoel

unread,

May 10, 2013, 10:16:43 PM5/10/13

to barnyar...@googlegroups.com

Ok.. I grabbed your branch and installed it on one of the sensors
that feeds a test DB.

I assume when you say "completely clean the schema" you mean basically
start the DB over?
The only problem with that idea is that all the notes and
classifications from the events (Snorby is the UI) would be lost and
everything would come back in for reclassification.

I tried just cleaning those three tables from earlier (sig, sig_ref
and ref) and the test box didn't like that at all. It's looking for
SIG's that don't exist.

So.. for right now I'll leave the ugly DB as is and just upgrade the
BY2.. and then figure out a good time to cut over to a new DB.

>> Then, reimport the U2 files from each sensor, how far back; just since
>> I started having the problem, yesterday, or all of them?. All the
>> events are in the DB, so now they will be in there twice.. even if
>> they have the wrong or erased sig/gids, right?
>>
>> Thanks for helping out and I'm glad we got it figured out.
>>
>
> Note, before re-importing data, make sure you copy back archived
> unified2 files to the log directory and delete the waldo file, before
> restarting barnyard2 processes.

and yes.. i copied all the U2 files back in and removed waldo and
restarted.. it inserted all the events again.

beenph

unread,

May 11, 2013, 11:31:56 AM5/11/13

to barnyar...@googlegroups.com

On Fri, May 10, 2013 at 10:16 PM, Jeremy Hoel <jth...@gmail.com> wrote:
>
> Ok.. I grabbed your branch and installed it on one of the sensors
> that feeds a test DB.
>
> I assume when you say "completely clean the schema" you mean basically
> start the DB over?

> The only problem with that idea is that all the notes and
> classifications from the events (Snorby is the UI) would be lost and
> everything would come back in for reclassification.
>
> I tried just cleaning those three tables from earlier (sig, sig_ref
> and ref) and the test box didn't like that at all. It's looking for
> SIG's that don't exist.
>

Well there is a few way you can do this, you can also probably do it
directly from
snorby without completly loosig the information that is specific to snorby.
As for "reclassification" i guess it depends on how snorby does it.
If its uses database signature id, your pretty mutch f*****. If it
uses gid,sid,rev
then you can probably flush eveything from the logging schema, without an issue.

> So.. for right now I'll leave the ugly DB as is and just upgrade the
> BY2.. and then figure out a good time to cut over to a new DB.
>

Your call.

But here is the query list you can execute to clean events and stuff.

DELETE FROM data;
DELETE FROM reference;
DELETE FROM reference_system;
DELETE FROM sig_class;
DELETE FROM sig_reference;
DELETE FROM signature;
DELETE FROM event;
DELETE FROM icmphdr;
DELETE FROM iphdr;
DELETE FROM tcphdr;
DELETE FROM udphdr;
DELETE FROM detail;
DELETE FROM opt;
UPDATE sensor set last_cid=1;
-elz

Reply all

Reply to author

Forward