There are two LEDs, a green one near the USB port and a red one near the cables.
The green LED indicates that the USB connection was sucessful. If the green LED doesn't ever light and you're sure it's in right, there was a problem with enumeration. If you're using a Windows or Linux machine and the green LED does not light up when you plug it in, theres a problem. If you have a newer Mac OS machine, try sending it commands via avrdude - the LED should light up then (strange but true!)
The red LED indicates that the USBtinyISP is 'busy' programming. You probably don't want to unplug it or the device being programmed while it's lit. However, if there's a software crash the LED may remain on even though it's not doing anything.
There are two cables for programming: a 10-pin ISP cable and a 6-pin ISP cable. They are the two prevailing standards for in-circuit AVR programming. This programmer doesn't do JTAG programming
There's a jumper sticking out near the cables, JP3. When the jumper is in place (connecting the two wires) then that means that the USBtinyISP is providing 5V power to the device being programmed. If you don't want to power the device then just take the jumper out or make sure it's only on one of the wires.
The USBtinyISP can only provide 5V, up to about 100mA to the device. If you need more power then you should remove the jumper and power the device seperately. (Alternately, if you're feeling adventurous you can reprogram the USBtinyISP to requires 500mA from the USB port instead of 100mA but if you don't know how to do this I'd suggest not.)
Version 1.0 of USBtinyISP sends data to the device at 5V level no matter whether it's powering the device or not so make sure it's 5V compliant! (Note that there are 2 1.5K resistors in series with the data lines for protection)
Version 2.0 which is almost certainly what you've got, uses a level shifter so that if the jumper is not in place, it will use whatever the target voltage is, a lot better for your low-voltage devices!
So, if you have a device that needs to run at 3.3V, don't have the jumper in place!
USBtinyISP can be used as a 'generic' SPI device. The best place to look for examples of how to use this is download the avrdude source code and read usbtiny.c
Here is a submitted example of using it under Linux, in c++. Thanks Matt D!