It's still here. Although it might be slowing down. I myself over the past few years signed up to some of the most popular ones.
Bebo is one of the most popular ones used here in Ireland (from what I can see). 2 friends from secondary school found me in Bebo, which was an unexpected surprise. Most Irish folks use this to keep in touch, especially when they are off backpacking all over the world. Even though I have my badge below, it's mostly for people whom I've added as my friend that can access my profile.
The other main one is Facebook. I don't use this much, but I noticed that it's alot cleaner than the other social networking sites.
Then for professionals (job seekers or headhunters), there's LinkedIn. My collegues from the last company hooked me up with this. It's a great way to find other work collegues.
The ickiest of them all is MySpace, 'cause a friend of mine was travelling around and his normal blog is bookmarked by his mom! Alot of his friends use MySpace, so that's where all the interesting stories are! http://www.myspace.com/shazsweetie
As far as I know, guys tend to sign up to this to try and find themselves a girl. I'm not making this up, they told me so, especially one friend. He says most of the girls that use MySpace that he chatted to are, ahem, sluts.
There is one I like alot and find darn useful... del.icio.us, a social bookmarking site. Why use bookmark folders when you can use del.icio.us, full of tagging goodness. http://del.icio.us/whykay
Meetup.com was kind of cool. Get like-minded people and meetup in a cafe for a chat. But it started to charge people, and one of my main meetup groups (ok, the only one I would go to), does not use it anymore.