Textures are like spices, when used correctly they really enhance the food, but too much (or used incorrectly) then it just doesn't work.
That being said, I really like textures and have accumulated a long list of sites that offer high quality, high resolution textures for free.
Here's part of my list:
1. CGTextures has some of the best (and most) textures I have seen offered by one site. They have both a free and a paid service. The free service allows you to download 15mb of textures every 24 hours.
2. TextureZ.com also offers a large variety of textures and does take donations that will help support their efforts.
3. Mayang's Free Textures has listed over 3,800 free high res, well categorized textures.
4. Texturise not only offers a large variety (and number) of free textures, but it is also one of the few sites I know of the allow you to use them for commercial purposes.
5. TextureKing, the name pretty much says it all.
6. The Roxor has over 80 high res paper textures. They also offer free fonts, brushes, icons, and more.
Below is a video tutorial on how to apply textures using Photoshop, as there is more than one way to do it, you will find other (really well done) video tutorials here, here, here, and here.