Can anyone here enlighten me on what type of sites load balancing could be good for?
Can you name hosting companies offering this type of service on their dedicated servers?

What kind of project are you going to run? I believe it will be allowed on many web hosting companies offereing dedicated servers.

Load balancing is good for any site which is outgrowing a single server or for sites where uptime is important and you want to mitigate the risk of a server going down causing downtime for your site.

Load balancing uses a multiple server setup to handle large number of requests that can't be possibly handled by a single server. There are several ways to create this like of a setup. For starters, have a look at:

http://www.linuxvirtualserver.org/

This has nothing to do with VPS technology.

There are many different kinds of load balancing, you can even use DNS to load balance between two different locations.

Sounds too good to be true, do you mean servers belonging to different companies?

Haven's heard of load balancing servers belonging to different companies, I think it's more often done within one company, you can load balance two shared accounts or two dedicated servers.

DNS isn't really a load balancing solution. Using DNS to send users in one region to a server in that same region and users in another to a server located elsewhere is probably 50% luck and 50% technology. Since it will work to some degree, it may fit your needs.

Look at this howto : http://howtoforge.com/high_availabil...artbeat_centos

You can easily do this, with say 2 -3 VPSes on different nodes, or actual physical machines.