Regardless of any restrictions set up by a host, you should sensibly limit your queries whenever possible. This goes if you are on shared hosting, or own 10 racks full of servers that are entirely at your disposal.
The fewer queries needed to render a page:
- The faster database connections are closed, allowing the RDBMS to release memory
- The faster connection resources are closed, allowing your application to release memory
- The faster HTTP server processes exit, allowing them to release memory
- The faster the user gets the information they were looking for
A typical shared web host will (as you note) have a single server mentality. Running a RDBMS on the same computer as a web server is almost never a good idea if you want to scale. Why? Both have to allocate way more memory than they actually need or use in order to be able to deal with requests and return the data that is asked for. This is especially true for any RDBMS that supports type affinity.
Also, take a look at how long the queries that you actually need are taking to return. The faster they finish, the faster resources are released (and pretty much everything else in the list above).
This means, the less time your app spends connected to the database, the less likely you will be to hit a connection limit. 50 can serve 500, or more users. Be that limiting queries, optimizing them or both.
Take a good look at your app. Where can you implement caching for information that is not likely to change on every page load? How can you make better use of sessions? Is that groovy ajax interface making a query for EVERY event?
Most people already ensure this is not the case, so questions like this would fall into the micro optimization category. Its really a fundamental design concept.
Design it to scale and work around such constraints, and you can usually avoid the constraints until time and money permits addressing them.
Also, a side note, a VPS where you control everything is almost as cheap as a typical re-seller hosting account. Why not build your own sandbox and play by your own rules?
As for load balancing, first decide on a replication scheme. You then decide on how best to distribute the work. In some instances, you can read from one slave and write to another, in other cases you need to employ some kind of reverse proxy, be it hardware or software. Your question is a little too generic to offer a more comprehensive answer in that regard.
