Tuesday, April 18, 2006

Dealing With Sguil Partition Issue

I operate several Sguil sensors in production environments for clients. At one location I have a single box deployment where the Sguil sensor, server, and database occupy a single FreeBSD platform. This wasn't the original configuration, but I am making do with what I was given.

Here is the current df -h output.
# df -h
Filesystem Size Used Avail Capacity Mounted on
/dev/aacd0s2a 989M 76M 834M 8% /
devfs 1.0K 1.0K 0B 100% /dev
/dev/aacd0s2f 989M 106K 910M 0% /home
/dev/aacd0s2h 436G 363G 38G 90% /nsm
/dev/aacd0s2e 989M 562M 348M 62% /tmp
/dev/aacd0s2d 5.9G 986M 4.4G 18% /usr
/dev/aacd0s2g 4.8G 3.8G 639M 86% /var

As you can see, /var is approaching the 90% mark. /nsm is already there, but Sguil's log_packets.sh script rotates the full content files stored there. Notice I keep all /nsm data in its own partition, to avoid catastrophe if a runaway program were to begin filling the drive. I've found myself on many Red Hat boxes that have only a / (root) and swap partition, which is an invitation to disaster.

Because everything runs on this system, it stores the sguildb MySQL database in the /var partition. You'll see /var is at 86%. What's taking up all the space?

/var/db/mysql# du -ch .
214K ./mysql
2.0K ./test
3.7G ./sguildb
3.8G .
3.8G total

Let's see the 20 biggest files in the sguildb directory.

/var/db/mysql/sguildb# ls -alhS | head -n 20
total 3929694
-rw-rw---- 1 mysql wheel 77M Apr 4 19:17 sancp_scn-sensor-colo_20060324.MYI
-rw-rw---- 1 mysql wheel 69M Apr 4 19:17 sancp_scn-sensor-colo_20060323.MYI
-rw-rw---- 1 mysql wheel 63M Apr 4 19:17 sancp_scn-sensor-colo_20060330.MYI
-rw-rw---- 1 mysql wheel 62M Mar 9 14:00 data_scn-sensor-colo_20060309.MYD
-rw-rw---- 1 mysql wheel 61M Apr 16 22:59 sancp_scn-sensor-colo_20060322.MYI
-rw-rw---- 1 mysql wheel 59M Apr 4 19:17 sancp_scn-sensor-colo_20060331.MYI
-rw-rw---- 1 mysql wheel 59M Mar 22 19:00 sancp_scn-sensor-colo_20060314.MYI
-rw-rw---- 1 mysql wheel 58M Apr 16 19:57 sancp_scn-sensor-colo_20060404.MYI
-rw-rw---- 1 mysql wheel 58M Mar 12 07:02 data_scn-sensor-colo_20060310.MYD
-rw-rw---- 1 mysql wheel 57M Mar 22 19:00 sancp_scn-sensor-colo_20060309.MYI
-rw-rw---- 1 mysql wheel 57M Apr 18 18:53 sancp_scn-sensor-colo_20060412.MYI
-rw-rw---- 1 mysql wheel 57M Apr 18 18:53 sancp_scn-sensor-colo_20060410.MYI
-rw-rw---- 1 mysql wheel 57M Apr 4 19:17 sancp_scn-sensor-colo_20060403.MYI
-rw-rw---- 1 mysql wheel 56M Mar 22 19:00 sancp_scn-sensor-colo_20060317.MYI
-rw-rw---- 1 mysql wheel 56M Apr 4 19:17 sancp_scn-sensor-colo_20060328.MYI
-rw-rw---- 1 mysql wheel 56M Apr 18 18:53 sancp_scn-sensor-colo_20060411.MYI
-rw-rw---- 1 mysql wheel 55M Apr 4 19:17 sancp_scn-sensor-colo_20060329.MYI
-rw-rw---- 1 mysql wheel 55M Apr 4 19:17 sancp_scn-sensor-colo_20060327.MYI
-rw-rw---- 1 mysql wheel 55M Apr 18 18:53 sancp_scn-sensor-colo_20060407.MYI

The sancp tables are session data.

# mysql -u sguil -p sguildb -e "describe sancp"
Enter password:
+------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| sid | int(10) unsigned | NO | MUL | | |
| sancpid | bigint(20) unsigned | NO | | | |
| start_time | datetime | NO | MUL | | |
| end_time | datetime | NO | | | |
| duration | int(10) unsigned | NO | | | |
| ip_proto | tinyint(3) unsigned | NO | | | |
| src_ip | int(10) unsigned | YES | MUL | NULL | |
| src_port | smallint(5) unsigned | YES | MUL | NULL | |
| dst_ip | int(10) unsigned | YES | MUL | NULL | |
| dst_port | smallint(5) unsigned | YES | MUL | NULL | |
| src_pkts | int(10) unsigned | NO | | | |
| src_bytes | int(10) unsigned | NO | | | |
| dst_pkts | int(10) unsigned | NO | | | |
| dst_bytes | int(10) unsigned | NO | | | |
| src_flags | tinyint(3) unsigned | NO | | | |
| dst_flags | tinyint(3) unsigned | NO | | | |
+------------+----------------------+------+-----+---------+-------+

The data tables contain packet payloads.

# mysql -u sguil -p sguildb -e "describe data"
Enter password:
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| sid | int(10) unsigned | NO | MUL | | |
| cid | int(10) unsigned | NO | | | |
| data_payload | text | YES | | NULL | |
+--------------+------------------+------+-----+---------+-------+

At this point it looks like I should back up some of the session data elsewhere, as it is taking up the most space.

First I make sure sensor_agent.tcl, sguild, and Barnyard are not running. I leave Snort and SANCP running, so the sensor continues to collect alert data, session data, and full content data.

Next I shut down MySQL.

# /usr/local/etc/rc.d/mysql-server.sh stop
Stopping mysql.
Waiting for PIDS: 76074, 76074.

I create a directory in the partition with the most free space that isn't going to be used any time soon.

# mkdir /usr/db_backups

Now I see how much space the SANCP tables occupy.

/var/db/mysql/sguildb# du -ch *sancp*
...edited...
3.3G total

Yes, they are the culprit. I have files reaching back to 8 March. Let's see how much space I can reclaim if I move all of March's entries.

/var/db/mysql/sguildb# du -ch *sancp*200603*
...edited...
1.9G total

That will work fine. Now to tar up the files elsewhere.

/var/db/mysql/sguildb# tar -czvf /usr/db_backups/sancp_200603.tar.gz *sancp*200603*
a sancp_scn-sensor-colo_20060308.MYD
a sancp_scn-sensor-colo_20060308.MYI
a sancp_scn-sensor-colo_20060308.frm
...edited...
a sancp_scn-sensor-colo_20060331.MYD
a sancp_scn-sensor-colo_20060331.MYI
a sancp_scn-sensor-colo_20060331.frm

/var/db/mysql/sguildb# ls -alh /usr/db_backups/
total 434788
drwxr-xr-x 2 root wheel 512B Apr 18 19:16 .
drwxr-xr-x 20 root wheel 512B Apr 18 19:11 ..
-rw-r--r-- 1 root wheel 424M Apr 18 19:20 sancp_200603.tar.gz

Now to delete the files, along with the sancp.MRG and sancp.frm files. They will be rebuilt automatically.

/var/db/mysql/sguildb# rm *sancp*200603*
/var/db/mysql/sguildb# rm sancp.MRG
/var/db/mysql/sguildb# rm sancp.frm

# df -h
Filesystem Size Used Avail Capacity Mounted on
/dev/aacd0s2a 989M 76M 834M 8% /
devfs 1.0K 1.0K 0B 100% /dev
/dev/aacd0s2f 989M 106K 910M 0% /home
/dev/aacd0s2h 436G 363G 38G 91% /nsm
/dev/aacd0s2e 989M 562M 348M 62% /tmp
/dev/aacd0s2d 5.9G 1.4G 4.0G 26% /usr
/dev/aacd0s2g 4.8G 1.9G 2.5G 43% /var

Great -- /var has plenty of room to grow now. However, I don't have ready access to session data from March. If I need it, though, I can restore it.

Now restart MySQL, then the Sguil components.

# /usr/local/etc/rc.d/mysql-server start
Starting mysql.
# su - sguil
sguil@scn-sensor-colo$ ./sguild_start.sh
pid(77008) Loading access list: ./sguild.access
pid(77008) Sensor access list set to ALLOW ANY.
pid(77008) Client access list set to ALLOW ANY.
pid(77008) Email Configuration:
pid(77008) Config file: ./sguild.email
pid(77008) Enabled: No
sguil@scn-sensor-colo$ ./sensor_agent_start.sh
sguil@scn-sensor-colo$ ./barnyard_start.sh
Barnyard Version 0.2.0 (Build 32)

A quick log in to Sguil shows that everything is working.

4 comments:

Jeremiah Johnson said...

You mentioned RedHat systems having a / and a swap partition. While I agree, this is a recipe for disaster you're missing something that helps greatly in this situation. Redhat has used LVM for all partitions (except /boot) for a while now. LVM allows you to resize partitions on the fly, so if at some point you are finding that /var just isn't big enough you can shrink another partition that has extra space and add it to your /var partition. I'm sure something like this is available for FreeBSD, but I couldn't tell you what its called.

Jonathan said...

How about just moving the mysql directory to /usr ?
shutdown mysql,
mkdir /usr/db
mv /var/db/mysql /usr/db/
ln -s /usr/db/mysql /var/db/mysql
start mysql,
rejoice.

Richard Bejtlich said...

So now what do I do with all the space in the real /var partition? :)

Jeremiah Johnson said...

While you could move things to /usr its really bad practice. Please go read the FHS (Filesystem Hiearchy Standard). Its written to cover Linux, but most of it can be applied to other OS's. Think about it like this, if you leave, what other admin is going to think that mysql db's are stored in /usr? /var is for variable data, like logs, databases, state files, its where the database should be, so resizing the /var partition to fit your database is the best practice.