Take this for example: the other day, I was playing Grand Theft Auto IV. I was doing the street races for Brucie in an effort to get the Genetically Superior achievement. I checked my stats to see how many races I had left to do. When I exited the pause menu, I got a call from Brucie explaining that I could call him for a ride in the helicopter. I thought nothing of this, until the phone hung up and I was awarded with "That's How We Roll!" for 10 points. Needless to say, I was pretty stoked.
I think that achievements can certainly cause frustration, but I love getting surprised with such a small and insignificant bonus from time to time. I think that when taken in that measure, the gamerscore system isn't just a virtual competition of "Who's is bigger?" Instead, it allows the player to be filled with a sense of accomplishment (and sometimes a little motivation to keep on challenging themselves with their $60 games).
For what its worth, I've been a bit of an achievement whore myself, but that experience went awry and I haven't driven quite as hard for achievements since then.
The game was Star Wars: The Force Unleashed. I had looked at the achievement guide a couple of times and decided that I could get 1000 points off of it in the period of one rental. On my first playthrough, I played the apprentice level difficulty (the easiest), and made sure I had gathered all of the holocrons (little power-ups scattered throughout every level). Unfortunately, I fell victim to the holocron glitch and my collecting went unrecognized. I was furious. I tried to playthrough again on Sith Master difficulty, but at one point when I had died for the 12th time on the first level, I asked myself why I would go through with this? Since when did games require you to punish yourself?
Why are some achievements designed to have the player collect everything or repeat a motion a million times? Why do developers insist you play through a game in a manner unbecoming of your own motivations and desires? Shouldn't you just want to have fun?
I've asked these questions when playing other games too. Call of Duty 4 for example (how I hate "Heat" on Veteran...). I think achievements work best when players are given rewards every so often throughout a game, and when the achievement is a reward for seeing or doing something significant in the game while also not encouraging behavior that is going to make a player throw their controller down in disgust.