LinuxCommando.com

You’re writing some nice big mySQL queries to generate the all-important reports your boss wants. You’ve got the previous 10 years’ sales summaries she asked for, but you aren’t looking forward to subtotaling all those rows month by month!

Luckily for you, there’s a better option. mySQL supports the WITH ROLLUP clause, a neat option to GROUP BY that summarizes each GROUP.

WITH ROLLUP can be used in any query with a GROUP BY. However, it cannot be used with ORDER BY clauses. Fortunately, you can usually order your data in the GROUP BY clause just as easily as you can in ORDER BY. For example, GROUP BY date_created DESC, member_id ASC is a perfectly valid query clause that does just what you think.

Learn more, and see some nice examples, at mysql.com!

use information_schema;

select TABLE_SCHEMA as db, TABLE_NAME as tbl, sum(DATA_LENGTH + INDEX_LENGTH) as size  from tables group by tbl  order by size;

There are probably better ways to do this, but here’s a little one liner to display per-second RX and TX Bytes from the output of ifconfig.

Requires:

• GNU ifconfig
• bash shell

netdev=wlan0; # set this to the right network interface
rx=0; tx=0;
while sleep 1; do
   /sbin/ifconfig $netdev | grep RX\ bytes | sed  's/.*RX bytes:\([0-9]\{1,\}\).*TX bytes:\([0-9]\{1,\}\).*/\1 \2/'>/tmp/rxtx;
   newrx=`cut -f 1 -d\ < /tmp/rxtx`;
   newtx=`cut -f 2 -d\ </tmp/rxtx;`;
   rxps=$(($newrx - $rx));
   txps=$(($newtx - $tx));
   if [[ $rx != 0 ]] ;
   then
      echo -ne "$rxps $txps (RX TX)\n";
   fi;
   tx=$newtx;
   rx=$newrx;
done

Explination

We sleep for 1 second, then run an ifconfig, grep for the RX/TX bytes line and sed out the numbers for rx and tx bytes which we save into /tmp/rxtx.  We then use cut to set newrx and newtx from the values in /tmp/rxtx.  We are now ready to compute the difference, which, since we slept for 1 second, is bytes per second.  If $rx has been set we echo RX and TX (the first pass through the while loop we don’t have an accurate rx or tx; we have to collect first one second and then another so I introduced this simple if).

Have you ever tried installing linux to a usb storage device? If so, you may have run into a kernel panic when you rebooted. You might also be having problems installing GRUB onto the USB drive.

Instructing the kernel to wait for it’s root device to become available

After the linux kernel boots, it attempts to mount the root filesystem immediately.  This usually works fine; hard drives and almost all other devices that do not require modules will already have been initialized.  However, USB mass storage devices (pen/thumb/flash drives and the like) often require a few seconds to “settle” before they can be accessed as hard drives.  If the linux kernel is supposed to mournt a USB device as the root of the filesystem, it must be told to wait until the specified boot device becomes available before attempting to boot, or it will invariably panic.

Fortunately this is very, very simple.  All you have to do is add a kernel parameter to your chosen bootloader:

rootwait

Generally it only takes a few seconds for a USB device to settle, but the kernel will wait indefinitely until the specified device becomes available.  If you would like to introduce a hard-coded pause, allowing the kernel to panic eventually rather than hang indefinitely, you may use rootdelay instead.

Alternative: using an initrd/initramfs

Although I’ve never tried it, I’d imagine that the use of an initrd/initramfs would give the USB device the necessary time to settle.  However I can’t guarantee it; I prefer to avoid the additional complexity of initrd myself, and so I’ve never tested it.

Booting with extlinux

We all love grub, but when booting from USB, it is a little slow.  Furthermore, its ability to load its boot stages from hard disks other than the one whose master boot record was invoked can sometimes lead to complications when used on a USB device.  The highly mobile nature of a USB device implies that it may be used on a variety of different computers; (hdX) may not always be consistent between boxes.

For this reason, you may want to consider using syslinux, or more specifically, extlinux, instead.  It always reads its configurations off the same device from which it was invoked.  It is not as full featured and flexible as GRUB but it does provide a config file that does not require MBR modifications to alter; in my book, that makes it better than LILO at least.

Syslinux and extlinux (pxelinux too) are essentially the same boot loader.  They are configured the same and installed the same; the only difference is that syslinux boots from an MS-DOS partition whereas extlinux boots from an ext2 partition (pxelinux, by the way, is an effective network boot client).

Unfortunately extlinux only supports ext2 (and maybe ext3/4, I haven’t tried) filesystems; if you wish to use another filesystem you’ll have to put the few extlinux files and the kernel on a small boot partition.  The extlinux folks recommend marking that partitino active.

syslinux insists on an ext2 filesystem

When I tried to use extlinux to boot a USB drive, I hit a bit of a snag.  After running mkfs.ext2 and then mounting the new filesystem, /etc/mtab showed the device mounted as an ext4 filesystem, and extlinux refused to install on it.  I am not sure whether mkfs.ext2 actually created an ext4 partition by default, or if it was mislabeled in /etc/mtab;  but I was able to successfully install extlinux after manually changing the partition type in /etc/mtab from ext4 to ext2.

The process for installing extlinux looks something like this:

mkfs.ext2 /dev/devicepartition
mkdir /mnt/boot
mount /dev/devicepartition /mnt/boot
[ edit /etc/mtab to list /mnt/boot as mounted as ext2 ]
extlinux -i /mnt/boot
dd if=/path/to/mbr.bin of=/dev/devicefile

All you have left to do is to edit the configuration file.  Don’t forget to specify rootwait as a kernel configuration!

Keeping device naming consistent

Remember, there’s no guarantee that the usb device will show up as the same device (eg sda) on every system! If you’re concerned about that, there are a few things you could do:

• Use kernel modules to drive SATA, SCSI, or the new SATA-like PATA driver modules so that your USB device is always /dev/sda
• Leave support for SATA, SCSI, and PATA drivers out of the kernel altogether.  I personally don’t much like this idea, as you’re likely to need to access a hard disk sooner than you might expect.

I’ve never tried these, but I’m pretty confident that they would work.

Conclusion

Well, there you have it.  You can install a linux system to a USB drive, add a kernel parameter, maybe install extlinux for bootloading, and enjoy the convenience of your favorite distribution regardless of which computer you are using.  Root filesystems on USB aren’t fast, but they’re fast enough.  (Incidentally, you may not want to use swap space or a system logger on a USB root if you can avoid it; it’s a sure way to increase disk contention to an irritating degree.)  You could also probably move an existing linux installation to a USB device.

I’d imagine these notes could be used more or less unmodified to boot from a USB-connected SD, CF, or other card.

My 3-core AMD Phenom II x3 720 has become an “AMD Phenom(tm) II X4 20 Processor”.   Linux sees four such cores in /proc/cpuinfo; interestingly enough, dmidecode suggests a maximum speed of 3200mHz, a full 600mHz above the 2.8gHz on the box.

The cores sadly only have 1/2MB of ram and, though the box boasts of  ‘7.5MB Total Cache’ (and I assume I gained another 512K from the fourth core, bringing the total up to 8mB), I can’t seem to locate the extra L3 cache.

Nevertheless, the system is amazingly fast – I was wowed when the system somehow managed to build wine in 7 minutes.   To get a better sense of what I mean, compare the following genlop output fro m these computers:

processor       : 7
vendor_id       : GenuineIntel
cpu family      : 6
model           : 30
model name      : Intel(R) Core(TM) i7 CPU 860 @ 2.80GHz
stepping        : 5
cpu MHz         : 2926.000
cache size      : 8192 KB
dan@soggy ~ $ genlop -i glibc
   Total builds: 1
   Global build time: 2 hours, 34 minutes and 36 seconds.

dan@soggy ~ $ genlop -i php
Total builds: 1
 Global build time: 11 minutes and 42 seconds.

dan@soggy ~ $ genlop -i mysql
Total builds: 2
 Global build time: 10 minutes and 18 seconds.
 Average merge time: 5 minutes and 9 seconds.
MAKEOPTS="-j10"

rocessor       : 3
vendor_id       : AuthenticAMD
cpu family      : 16
model           : 4
model name      : AMD Phenom(tm) II X4 20 Processor
stepping        : 2
cpu MHz         : 3214.536
cache size      : 512 KB
dan@pascal ~ $ genlop -i glibc
Total builds: 4
   Global build time: 1 hour, 59 minutes and 36 seconds.
   Average merge time: 29 minutes and 54 seconds.
dan@pascal ~ $ genlop -i php
Total builds: 2
 Global build time: 5 minutes and 41 seconds.
 Average merge time: 2 minutes and 50 seconds.
dan@pascal ~ $ genlop -i mysq
Total builds: 2
 Global build time: 7 minutes and 9 seconds.
 Average merge time: 3 minutes and 34 seconds.
MAKEOPTS="-j4"