Puppy Linux absolutely flies on my Dell GX260 with a 1.8GHz P4 and 2GB of RAM. I have two more of these machines (albeit with only 512MB RAM each) that will get the same treatment shortly as soon as I source a CD drive for one and a power supply for the other. Silly SFF cases, they only have 130-watt PSU's, I'm going to put in 200-watt units from GX280 models as soon as I find some dirt cheap ones.
My main problem with using Linux (I have Win2K dual booting with it) is that I can't get it to recognize my Linksys WUSB54 wireless adapter. It took me two days to get 2000 SP4 to see it, too, and it drops out all the time. I'm wondering if the USB ports on this particular box are fried.
The best thing I can recommend other than stripping XPdown as best you can is to upgrade the video. I know, I know, it's not like you can throw in a HD5770 and go, you've got to match up the slot.
If you have an AGP 4x/8x slot, I'd go with Sapphire's ATI HD3650 512MB card, sells for around $64. If you can't afford that, try the ATI Radeon 9250 128MB for about $33. If you need speed above all else, XFX and Sapphire each make a HD4650 1GB card for $80 for 8X only, and HIS has a 4X/8X version for $85. Feel like spendng $110-$130 and you CAN get HD4670 1GB PCI cards from PowerColor (8X only $110) and HIS (4X/8X $130).
For a plain PCI (not Express or 1X or 16X) slot card, your choices are further limited. PNY makes an nVidia 8400GS 512MB card for about $40-$50 that is ok. For more speed than that, you can get a HIS Radeon HD4350 512MB card for about $70 or a Sparkle nVidia 9500GT 512MB for about $75.
edit: the Sparkle 9500GT is going in my kid's eMachines EL1200 machine as soon as I find one cheap. $75 is still a little high and I don't want to put in a 8400GS for $40 and waste the money if the 9500GT comes down in price. Now that the next generation of nVidia stuff is shipping (GTX480 and the like) I'm hoping to see the 9x00 cards drop a bit.