Monthly Archives: August 2013

MySQL count all rows in database by table

Posted on by
Reply

This gets a list of tables in database ‘interesting’ and how many rows are in each table. The second query just sums them up for a grand total.

mysql> use mysql;
Database changed
mysql> SELECT table_name,table_rows FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'interesting';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| a       |         29 |
| b       |          4 |
| c       |       1104 |
| d       |   55378308 |
| e       |   56925066 |
| f       |   53479079 |
| g       |   55963435 |
| h       |   53966893 |
| i       |   55839182 |
| j       |   56854816 |
| k       |   54613112 |
| l       |   55454111 |
| m       |   56133247 |
| n       |          7 |
| o       |          4 |
| p       |         18 |
| q       |         30 |
| r       |          5 |
| s       |          5 |
| t       |         68 |
| u       |         22 |
| v       |          5 |
| w       |          7 |
| x       |      66046 |
| y       |      69439 |
| z       |      87361 |
| aa      |      89519 |
| ab      |      75892 |
| ac      |      61785 |
| ad      |      64964 |
| ae      |      69905 |
| af      |      65599 |
| ag      |      69710 |
| ah      |        381 |
| ai      |        181 |
| aj      |    2285397 |
| ak      |    2272764 |
| al      |         10 |
+---------+------------+
38 rows in set (0.04 sec)

mysql> SELECT sum(table_rows) FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'interesting';
+-----------------+
| sum(table_rows) |
+-----------------+
|       559887795 |
+-----------------+
1 row in set (0.05 sec)

mysql>

find files modified today

Posted on by
2

find is an amazing command. With the proper manipulation it can be used to massage out the file data you need.

To find files modified in the last 24 hours is straight fowrard. Just look for files with a modified time of now() minus 1 day (24 hours):

find . -type f -mtime -1 -ls

But if you just want files modified today it’s a bit more involved:

touch -t `date +%m%d0000` /tmp/$$
find . -type f -newer /tmp/$$ -ls
rm /tmp/$$

touch a file with the timestamp of 12am today. The file can have any name, but we just use the bash pid here. Then find files newer than that file. It will return files modified some time since 12am. Then remove the touched file.

ESXi add vlans

Posted on by
Reply

Using ESXi 4.1:

vm705:~ # esxcfg-vswitch --add-pg=vlan301 vSwitch0           
vm705:~ # esxcfg-vswitch --vlan=301 --pg=vlan301 vSwitch0

And this was to move a bunch of VLANs from one vSwitch to another:

vm705:~ # for i in `seq 221 235`; do esxcfg-vswitch -D vlan$i vSwitch0; done;
vm705:~ # for i in `seq 221 235`; do esxcfg-vswitch -A vlan$i vSwitch1; done;
vm705:~ # for i in `seq 221 235`; do esxcfg-vswitch -p vlan$i -v $i vSwitch1; done;

monitor Apache memory usage

Posted on by
Reply

When looking at a webserver for memory usage, it’s important to consider the VSZ and RSS memory usage.

This little one liner gets the Total and Average VSZ and RSS usage as well as thread count, and prints those statistics every 5 seconds:

# while true; do ps auxfww | grep apache | grep -v -e cronolog -e grep | awk '{ vsum+=$5; rsum+=$6 } END { print "VSZ:", vsum, "(", vsum/NR, ") RSS:", rsum, "(", rsum/NR, ") Procs:", NR }'; sleep 5; done;
VSZ: 9896272 ( 341251 ) RSS: 1716216 ( 59179.9 ) Procs: 29
VSZ: 9547608 ( 340986 ) RSS: 1650100 ( 58932.1 ) Procs: 28
VSZ: 9546328 ( 340940 ) RSS: 1649044 ( 58894.4 ) Procs: 28
VSZ: 9861976 ( 340068 ) RSS: 1687968 ( 58205.8 ) Procs: 29
VSZ: 9868632 ( 340298 ) RSS: 1694496 ( 58430.9 ) Procs: 29
VSZ: 9853272 ( 339768 ) RSS: 1679112 ( 57900.4 ) Procs: 29
VSZ: 9853272 ( 339768 ) RSS: 1679264 ( 57905.7 ) Procs: 29
^C
#

So there are around 29 threads running right now on this server. The threads are using an average of 340MB per thread VSZ, and 59MB per thread RSS. The total of around 1.7GB of RSS looks good, on a machine with 8G physical memory.

get absolute path of running script

Posted on by
Reply

Using $0 or basename in a script works well if you are calling it using it’s absolute path or if it’s in $PATH. The test script shows how basename and $0 are displayed. Calling the test script from within /usr/local/sbin, does not give any absolute path. 

# pwd
/usr/local/sbin
#
# cat test.sh
#!/bin/bash
echo $0
echo `basename $0`
#
#
#
# test.sh 
/usr/local/sbin/test.sh
test.sh
#
# ./test.sh
./test.sh
test.sh
#
#echo 'echo $(readlink -f $0)' >> test.sh
#
# ./test.sh 
./test.sh
test.sh
/usr/local/sbin/test.sh

readlink gives us the full path to the script, no matter how it’s called.

F5 BigIP ssh monitor

Posted on by
1

I created a pool of load balanced ssh servers. In order to monitor them for availability, I needed to create a custom monitor. This is a very old F5 load balancer:

# uname -r
BIG-IP 4.5.14

It seems like the easiest way to monitor ssh servers would be with ssh. After tinkering with it, I didn’t like the idea. I didn’t like the interactive quality of ssh, and didn’t want to make a custom user just for health checks. I also, didn’t want to put shared keys on the load balancers themselves. These are very custom ssh servers, and trigger filesystem mounting, and all sorts of other auth methods. I don’t actually want to ssh to them, I just want to see if ssh port is open. expect or nc or nmap were not available. I hit tab a few times and viewed the 500 or so commands available. I saw curl and gave that a try.

For our purposes, all we care about is that the port is open and we get a response to a request on that port:

#!/bin/sh
node_ip=`echo $1 | sed 's/::ffff://'`

pidfile="/var/run/`basename $0`.$node_ip..$2.pid"
if [ -f $pidfile ]
then
   kill -9 `cat $pidfile` > /dev/null 2>&1
fi
echo "$$" > $pidfile

curl http://${node_ip}:22 --connect-timeout 5 > /dev/null 2>&1

status=$?
if [ $status -eq 0 ]
then
    echo "UP"
fi

rm -f $pidfile

If the server is down completely, curl returns 7. If sshd has crashed the port is closed and curl again returns 7. If the port is open, curl exits 0.

NetApp show disk firmware progress

Posted on by
1

During disk firmware upgrades, you may wonder how long it’s taking or how it’s moving along. Use this one liner to count how many disks have the old and new firmware versions:

# ssh toaster "sysconfig -a" | grep NA0 | awk '{ if (/NA06/)i++; if (/NA01/)j++; } END{ print "NA01: "j" NA06: "i}'
NA01: 133 NA06: 91

So it’s moving along.

scan range of IP addresses

Posted on by
Reply

nmap is an amazing utility. With all of it’s flags and options it really gives you the power to know what is out in a network.

I used this to do a simple ping scan of my home network: 

# nmap -nsP 192.168.1.0/24
Starting Nmap 5.00 ( http://nmap.org ) at 2013-08-22 09:06 MST
Host 192.168.1.1 is up (0.0051s latency).
MAC Address: 00:18:39:4E:82:60 (Cisco-Linksys)
Host 192.168.1.90 is up.
Nmap done: 256 IP addresses (2 hosts up) scanned in 5.51 seconds
#

Hosts that allow icmp echo requests will show up. I can see my router and my workstation, but no other hosts (no other pingable hosts at least). I’m looking for a RPi that I recently plugged into the network. It doesn’t look like it’s up. Time to drag out the spare monitor and keyboard…