Tag Archives: server monitoring

Get Top URLs in SGUIL (with SecurityOnion Example)

Here’s a quick way to get a list of the top URLs used in a system that is monitoring traffic with SGUIL.  Login to the MySQL database and query the “event” table for signatures that have “URL” in them.  The example below is for SecurityOnion.

# In mysql, gets the top urls

# -A turns off "reading table information for completion of table and column names" for faster DB selection

mysql -uroot -A

use securityonion_db;

# Change date in the WHERE clause and number in LIMIT
# This query below retrieves the top 100 URLS after the date specified
SELECT event.signature,
FROM event
WHERE event.timestamp > '2013-03-01'
AND event.signature LIKE '%URL%'
GROUP BY event.signature
ORDER By count(*) desc
LIMIT 100;

This is a good quick way to find out what people are requesting.  However, because so many CDN’s, ad servers and trackers use multiple hostnames, you don’t get the big picture of what is coming from just the domain itself.  This is great if you are retrieving statistics to help you tune performance on your ad blocker or web proxy cache.

Monitoring the Postgres Daemon

Following up on Running Postgres with Daemontools – Shutdown Errors, the Postgres mail list had some advice on monitoring and automatically restarting Postgres.

It seems the danger in automatically restarting the postmaster process from Postgres is a dangerous proposition. As mentioned in the mail list, if a process quits for whatever reason, daemontools will simply restart it. Now, that could theoretically fail and restart 3600 times per hour, every hour, every day without you ever being notified that there is a problem that requires attention. This may be fine for some things, but not Postgres.

Some people indicated they use a custom script to try and connect to postgres, and if the postgres fails they are notified. Some said the program monit will do the same job. I believe monit is quite extensible and will allow you to be notified as well as restart the server.

Still another person said they run the nagios monitoring program and are notified by email and pager of a failure.

Whatever the solution is, it seems only right to me that you should be notified of a failure if you so desire. At the same time, the mechanism that is handling the process should provide a clean shutdown. Apparently monit does that.

While I can’t advocate daemontools for Postgres, I would certainly say it is time to revisit nagios and monit. I’ve used nagio is the past but had resource issues with it. I’ve perused the monit documentation and enjoyed the flexibility. Now I’ll be looking at them both again and reporting back.

PHP’s APC cache and how it relates to Apache Bloat

You just finished your install of Apache, PHP and APC cache on a VPS and you’re beat. You spent about a day going over every configuration and compile option trying to eek performance out of your VPS machine and web server. Then you take a look at the output of top or ps and notice, hey, my Apache process is 150 Megs! Well isn’t that grand. Compiling the Big Apache on a limited resource VPS can be a challenge for those of us who like to tinker. On the last VPS I got I scrapped the packaged stuff and started on compiling an Apache for the machine. The problem is trying to meet your needs with the limited memory you usually get on these machines. Although I like lighttpd, I’m not ready to give it a go on a production server that is being monitored by the media all the time. I need something as guaranteed as I can get. Although lighttpd may be such a beast, I’m not going to test it out on this site at this time!

I sat down and started to go over all configuration option for Apache, PHP and APC to get a nice small package to do the job. I was surprised how much junk PHP has compiled in by default. Compile it once and execute a phpinfo() and you’ll see. Just start with the “–disable” switches for anything you don’t want.

After about a day of thinning out the size of my stack I slipped into TOP and used the SHIFT-A toggle to see the alternate views. I noticed something odd…..

 24140  0.1 78592  69m 7228  504 2124 2952    6    0 S  16   0    0 httpd
 11329  0.1 78408  69m 6908  504 1964 2736    2    0 S  16   0    0 httpd
 24141  0.1 78564  70m 6752  504 2120 2492    0    0 S  16   0    0 httpd
 24142  0.1 78564  70m 6732  504 2120 2476    0    0 S  16   0    0 httpd
 27813  0.1 78560  70m 6684  504 2116 2432    0    0 S  16   0    0 httpd

Doesn’t that seem like a lot of VIRT and SWAP for Apache2? Yes, it is. If you used the suggested 128 for apc.shm_size it will be a lot bigger too! I used 64M and you can see that at 78592 minus the 64M shared mem for the APC cache, the actual size is around 13M for apache with a 6.7M RES. That makes me feel better! I was so worried I had gotten something horribly wrong.

If you happen to run across this kind of thing in testing, simply set your apc.enabled in php.ini to ‘0’ to disable it, restart Apache and check ‘top’ again. Likely nothing to worry about.

The machine I am running is only hosting 4 sites so 64M is a good starting point, but what you should do is copy the file ‘apc.php’ from your APC source directory into a folder on your webserver and visit it frequently to see how it behaves. Depending on your other settings like the cache lifetime and garbage collection, if you have a large portion of FREE and a small USED then you can probably set the apc.shm_size lower. If it makes you feel better :)