My experiences with music and gaming have been mixed. Here's my two coppers.
First off, it's easy to do it "wrong." If you find that music detracts from your experience, stop using it. Easy enough.
First... Many people up-thread have posted that it's important to keep the volume low. That's good advice. A good level is to have it loud enough that you can hear it distinctly when there is a quiet moment around the table, but not so loud that people have to speak up to be heard.
Second... Pick something appropriate to what you're playing. It might seem like an obvious thing, but for some people, it's not. Basil Pouledoris' score to Conan: the Barbarian is insanely evocative and a joy to listen to. It's jarring when someone plays it during a pirate-themed adventure or when your characters attend the duchess' ball. Your players will probably wonder why you chose that music for that scene and lose some of their immersion.
For D&D/Pathfinder, I use the music from Troika's Temple of Elemental Evil computer game, Blizzard's Diablo, and the Record of Lodoss War series. Very little of it contains lyrics and it loops more-or-less seamlessly, in addition to being generally good, evocative music for fantasy gaming.
Don't be afraid to experiment. Music can be used to great effect, even strange and unusual music. I once had a game that partially took place in a Greek-style temple complex and played ancient Greek music for it. Some people told me to avoid period music because it wouldn't sound like orchestrated institutional Hollywood stuff--I was "making a mistake" because ancient Greek music didn't sound like the Lords of the Rings score. I'm glad that I decided to do it, despite advice to the contrary. It was really awesome because the music was different and alien to contemporary ears--it was just jarring enough that the players took notice, but not so jarring that it became a distraction. The players told me that the session was one of their favorites, and part of that was the music.
I always use ambient tracks and music for horror-themed games because players have told me that it really helps set the mood for them. It seems that there just isn't beating creepy music to achieve creepy ambiance for most people. I tend to agree there, horror games seem much better to me with horror-themed music. For that, I get a lot of mileage out of the soundtracks for the various Resident Evil and Silent Hill video games, as well as the excellent music for the Vampire: the Masquerade computer games (both Redemption and Bloodlines). The music is good, and most people don't really recognize it for what it is unless they're huge fans of the games.
I think that Vampire: the Masquerade is worth a special mention here. Music is not only good for a Vampire game, I consider it absolutely essential. Rob Zombie, Lords of Acid, Genitorturers, Professional Murder Music, and Zombie Girl play when I run games. High-energy music and the whole music scene are as much a fixture to Vampire's milieu as knights and castles are to D&D--I just can't imagine playing Vampire without music to drive it along. Sex, blood, and rock 'n roll.
Third... Don't play music just for the sake of playing music. I know DMs or hosts that just play music for the heck of it and it bugs me--not even an attempt at ambiance, they just play music to have noise in the background. Listening to 80's easy listening or 60's rock-and-roll during a game reminds me too much of muzak, it's difficult for me to get into the game when the play space sounds like a restaurant. That might not bother some people, but it's a distraction for me.