greygraph on CentOS

Today I gave a look at greygraph, a mailgraph-based tool for displaying sqlgrey graphs.

Here are the adaptations to run it in CentOS. Have a look at the README inside the distribution, anyway, After unpacking the distribution tarball:

[code]
mkdir -p /var/cache/greygraph
chgrp apache /var/cache/greygraph/
chmod g+w /var/cache/greygraph/
[/code]


Review etc/default/greygraph and copy it to /etc/sysconfig/greygraph:
[code]
mv etc/default/greygraph /etc/sysconfig/greygraph
[/code]

Place files and directories directories:
[code]
mkdir -p /usr/share/greygraph
mv usr/lib/cgi-bin/greygraph.cgi /usr/share/greygraph/
mv var/www/css/greygraph.css /usr/share/greygraph/
mv usr/sbin/greygraph /usr/sbin
mkdir -p /var/lib/greygraph
[/code]

As for the SysV script, I've adapted mailgrah's. Download the script greygraph (remove .txt extension).

In the meantime, I'll try to add the generated RRDs to cacti [1, 2], let me know if you managed to do so.

Mounting disk images with multiple partitions

This is a common scenario: you made a disk image from a disk which wanted to wipe out, but when you finally arrange some time to do proper backup (you're probably not interested in backing up your /tmp, for instance), you don't even remeber what's on it.

First of all, you'll need to know the disk's partition layout:

[code]
[root@machine backup_disks]# parted disco2
GNU Parted 1.8.6
Using /array/backup_disks/disco2
Welcome to GNU Parted! Type 'help' to view a list of commands.
(parted) unit
Unit? [compact]? B
(parted) print
Model: (file)
Disk /array/backup_disks/disco2: 80060424192B
Sector size (logical/physical): 512B/512B
Partition Table: msdos

Number Start End Size Type File system Flags
1 32256B 106928639B 106896384B primary ext3 boot
2 106928640B 80056650239B 79949721600B primary lvm

(parted) quit
[/code]


I prefer 'parted' as it handles partition tables on files better than fdisk, but I suppose you could use 'fdisk -l' as well (ignoring the warnings and errors).

The easiest way (although not the quickest) is to simple 'dd' from it, using seek/skip counters, according to the partition table.

The following is the quickest approach: you can just mount simple partitions, like "boot" in the above example with:

[code]
[root@machine backup_disks]# mkdir mymount
[root@machine backup_disks]# mount -o loop,seek=32256,ro disco2 mymount
[/code]

But, in the case of LVM, you need to scan and join it to the LVM pool you probably already have (refer to the LVM howto, for more information). This is due to LVM not being a filesystem (which could be mounted) but rather a partition type, in this case. So, first of all, you'll want to "export" that partition as a block device, so it can be analyzed by the LVM tools. You can do that with the loopback tools:

[code]
[root@machine backup_disks]# losetup -o 106928640 /dev/loop1 disco2
[/code]

You should be able to access the raw partition at /dev/loop1 and, thus, it's now visible to 'pvscan':

[code]
[root@machine backup_disks]# pvscan
PV /dev/loop1 VG VolGroup00 lvm2 [74,44 GB / 32,00 MB free]
PV /dev/md0 VG VolGroup00 lvm2 [3,18 TB / 64,00 MB free]
[/code]

This is also another common situation, due to distribution defaults: you mounted a disk that shares the Volume Group name with the running disks. It's better to rename it ASAP, before confusion happens:

[code]
[root@machine backup_disks]# vgdisplay
--- Volume group ---
VG Name VolGroup00
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 3
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 2
Open LV 1
Max PV 0
Cur PV 1
Act PV 1
VG Size 74,44 GB
PE Size 32,00 MB
Total PE 2382
Alloc PE / Size 2381 / 74,41 GB
Free PE / Size 1 / 32,00 MB
VG UUID 4BAKDW-94eP-0ZjE-aa7n-Efab-gu0m-5NF0GX

--- Volume group ---
VG Name VolGroup00
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 2
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 0
Cur PV 1
Act PV 1
VG Size 3,18 TB
PE Size 64,00 MB
Total PE 52165
Alloc PE / Size 52164 / 3,18 TB
Free PE / Size 1 / 64,00 MB
VG UUID 9xAyLn-5jex-Bc00-0zmG-CPA0-x9Sx-3M3eio
[/code]


Check the ID of the new volume (in this case: 4BAKDW-94eP-0ZjE-aa7n-Efab-gu0m-5NF0GX) and use vgrename to do so:

[code]
[root@machine backup_disks]# vgrename 4BAKDW-94eP-0ZjE-aa7n-Efab-gu0m-5NF0GX VolGroup00_oldmachine
Volume group "VolGroup00" successfully renamed to "VolGroup00_oldmachine"
[/code]

Check everything went well, again with pvscan:
[code]
[root@machine backup_disks]# pvscan
PV /dev/loop1 VG VolGroup00_oldmachine lvm2 [74,44 GB / 32,00 MB free]
PV /dev/md0 VG VolGroup00 lvm2 [3,18 TB / 64,00 MB free]
Total: 2 [1,26 TB] / in use: 2 [1,26 TB] / in no VG: 0 [0 ]
[/code]


There you go! Now you should be able to follow standard LVM approaches to mount the volume.

Listing miscellaneous Apache parameters in SNMP

We recently had to look at a server which occasionaly died with DoS. I was manually monitoring a lot of stuff while I was watching a persistent BIG apache worker popping up occasionally and then disappear (probably being recycled). Yet more rarely I caught two of them. This machine was being flood with blog spam from a botnet. I did the math and soon I found that if the current number of allowed workers was filled the way this was, the machine would start swapping like nuts. This seemed to be the cause.

After corrected the problem (many measures were taken, see below), I searched for cacti templates that could evidence this behaviour. I found that ApacheStats nor the better Apache templates didn't report about Virtual Memory Size (VSZ) nor Resident Set Size (RSS), which is exaplained by mod_status not reporting it either (and they fetch the data by querying mod_status).

So here's a simple way of monitoring these. Suppose there is a server which runs some apache workers you want to monitor, and there is machine to where you want to collect data:

Edit your server's /etc/snmp/snmpd.conf
[code]
# .... other configuration directives
exec .1.3.6.1.4.1.111111.1 ApacheRSS /usr/local/bin/apache-snmp-rss.sh
[/code]

'1.3.6.1.4.1.111111.1' OID is a branch of '.1.3.6.1.4.1' which was assigned with meaning '.iso.org.dod.internet.private.enterprises', which is where one enterprise without IANA assignmed code should place its OIDs. Anyway, you can use any sequence you want.

Create a file named /usr/local/bin/apache-snmp-rss.sh with following contents:
[code]
#!/bin/sh
WORKERS=4
ps h -C httpd -o rss | sort -rn | head -n $WORKERS
[/code]

Notice that httpd is apache's process name in CentOS. In Debian, eg, that would be apache. Now give the script execution rights. Now go to your poller machine, from where you'll do the SNMP queries:
[code]
[root@poller ~]# snmpwalk -v 2c -c public targetserver .1.3.6.1.4.1.111111.1.101
SNMPv2-SMI::enterprises.111111.1.101.1 = STRING: "27856"
SNMPv2-SMI::enterprises.111111.1.101.2 = STRING: "25552"
SNMPv2-SMI::enterprises.111111.1.101.3 = STRING: "24588"
SNMPv2-SMI::enterprises.111111.1.101.4 = STRING: "12040"
[/code]

So this is reporting the 4 most consuming workers (which is the value specified in the script variable WORKERS) with their RSS usage (that's the output of '-o rss' on the script).

Now graphing these values is a bit more complicated, specially because the graphs are usually created on a "fixed number of values" basis. That means whenever your workers number increases or decreases, the script has to cope with it. That's why there is filtering ocurring on the script: first we reverse order them by RSS size, then we get only the first 4 - this means you'll be listing the most consuming workers. To avoid having your graphs asking for more values than the scripts generates, the WORKERS script variable should be adjusted to the minimum apache workers you'll ever have on your system - that should be the httpd.conf StartServers directive.

Now going for the graphs: this is the tricky part as I find cacti a little overcomplicated. However you should be OK with this Netuality post. You should place individual data sources for each of the workers, and group the four in a Graph Template. This is the final result, after lots of struggling trying to get the correct values (I still didn't manage to get the right values, which are ~22KB):



In this graph you won't notice the events I exposed in the beginning because other measures were taken, including dynamic firewalling, apache tuning and auditing the blogs for comment and track/pingback permissions - we had an user wide open to spam, and that was when the automatic process of cleaning up the blog spam was implemented. In any case, this graph will evidence future similar situations which I hope are over.

I'll try to post the cacti templates as well, as soon as I recover from the struggling :) Drop me a note if you're interested.

Analysing MySQL slow queries

While I'm engaged in my MySQL DBA mode, I usually come across the hard task of surfing around the slow query log.

Everybody trying to trace MySQL performance problems will hit the slow query log to keep track of those unperformant queries that seem to be hogging the system. The traditional tool to analyze the log is mysqldumpslow (provided with standard MySQL distribution) which aggregates the queries by pattern (fingerprint) and calculates some aggregated statistics. I personally find the tool very limited, and I don't seem to be the one (see below).