First of all, I highly recommend Rackspaces' Cloud Servers. We've been using them for many months now, and they are great to work with. It's absolutely painless to resize your server, add or delete RAM, and the bandwidth charges are incredibly reasonable.In our case, all of our websites are relatively low volume, so we have never tested the limits of the cloud servers but we do have a lot of websites running on our server, and we have the additional complications of running the web server, mail server and databases all on the same machine.
Of course, like most people we don't want to pay more than we need to for our web presence, so we run a Cloud Server and we've sized it to only have 1 Gig of RAM. Now, when you think about running the web server, mail server and the databases all on that configuration, you can see that we have to be a little creative to have good performance. In addition, one of our websites (NoCrappyApps.com) is integrated with the Android Market and we run several daemons constantly to keep the data as fresh as possible, so this poor little server is really being pushed a bit.
Now, with our configuration, we determined that the maximum RAM we could allow MySQL to use was 384M, and we currently have 22 databases in that MySQL instance. Most of the web sites are Joomla 1.5 or Joomla 1.6 sites, and many of them have over 200 databases tables. We happen to be a little obsessed with knowing what our users are using (because that's how we know which features are popular), so we log almost everything, and then run statistics routines every night to give ourselves and our users up to date information.
Obviously, we will have to add more memory to the server in the next few months, but Rackspace gives us options there also. We could just resize our existing Cloud Server to give it 2G of RAM, but for the same price, Rackspace gives us the option of adding another server with 1G of RAM. That has the advantage of being able to move all the databases to the new server, and moving the databases means that we can configure the new server specifically for MySQL and the old server configurations can be changed so that it is optimized for just the web server and mail server. We'll probably move to having the second server although it will be a little more setup work in the beginning. We use an Ubuntu server, and Rackspace makes it easy to setup and configure a new server. Actually, I should mention that Rackspace has really good documentation on setting up servers. I've been able to setup a new server and have it completely configured in less than 2 hours, and at the time, I had very little experience with cloud servers. In the beginning, I actually just setup about 3 or 4 test servers and then deleted them. I think Rackspace charged me about 4 cents per server (their charges are based on a per server per hour basis, so you only have to pay for what you actually use... and did I mention that their charges are incredibly low?).
Ok... so how the heck are we running 15 websites, MySQL with 22 databases, a web server and a mail server all on 1G of RAM? Well.. the first thing we had to do was configure Apache not to hog all the memory. Apache can easily use the entire 1 GIG of RAM and the mail server likes quite a bit of RAM also (all 15 websites have unlimited mail accounts). Once we had those under control, we had the 384M that we thought MySQL could use.
The first thing we had to do was optimize all the SQL. In our case, we're using Joomla 1.5 or 1.6 for all the websites, but Joomla 1.6 was missing a lot of indexes and even Joomla 1.5 doesn't have all the indexes it should. So... we setup MySQL to log the slow queries (set log_slow_queries for the file you want to log to and we set long_query_time = 2 (seconds) in my.cnf). Then every day, I spent about an hour checking the slow queries and figuring out which indexes to add to speed them up. Of course, I have to rewrite some queries to get the performance I wanted.This is an ongoing task, but I now only need to spend about an hour a week on it. I also monitor the MySQL error log.
I should mention also that you should not be using the mysql engine for your Joomla databases. Switch it to mysqli (the I stands for "improved") instead, or just go directly to InnoDB. InnoDB of course, is much better for large databases and tables that do a lot of read and writes simultaneously because it has row locking while the mysql and mysqli engines do table locking (not so good, although smaller databases or low volume databases won't notice so much). At this point, we are still using mysqli because of our memory constraints, so we turned the skip_InnoDb flag on so InnoDb doesn't load and use up some of our RAM even though it's not being used.
Now... you need more information on the innards of MySQL to do more. There are some links here that might help, and you can download a perl script here that is quite helpful in making suggestions for improvements.
NOTE - DO NOT JUST BLINDLY follow any recommendations without fully understanding what they do.
Of course, eveyone is looking for a magic formula to just tell them what settings to change and there are my.cnf files on the internet for large, small and medium databases. If you're just getting started these are ok, but you still need to go through each line in your my.cnf file and make sure you understand why it's set where it is... and make the changes you need.
The fact is that there just isn't a magic formula for managing a database.
You need to monitor your actual server use and then make changes based on what's actually happening... and you need to do this continually... this is not a set-and-forget thing. You can run SQL queries to monitor your server. Use the "SHOW STATUS" command to see what your server status, and "SHOW VARIABLES" to see your settings. Or just use Navicat (the paid version) and it has a Server monitor that does the same thing. You don't have to pay for tools but it is nice to be able to see it in a GUI form rather than a command-line format.
Some of the variables I've found to be very important are "query_cache_limit" and "query_cache_size". Read about them and understand them, and you need to experiment and know how your MySQL instance is actually being used to get them set correctly (TIP - the MySQL defaults cannot be relied on, they won't be right for your use). Essentially, the "query_cache_size" is the amount of RAM you allocate for querying caching. NOTE that the only queries that can be cached are queries like "SELECT * FROM some_table". It only caches static queries (queries that don't change), but those are most of your lookup tables which are the ones used the most frequently. You want to get most of your static queries into cache WITHOUT using any more RAM than you need to.
Then you want to play with the "table_open_cache" variable (early versions of MySQL before 5.1 had this set MUCH too low). The MySQL tuning script will help here, but the key status variable to watch to see if you're got it right are "Open_tables", "Opened_tables", "Open_files" and "Opened_files". You want the ratio of "Opened_files" to "Open_files" to be very low (not many "Opened_files), and the same with tables (very few "Opened_tables" compared to "Open_tables".
Also monitor your "key_blocks_used" and "key_blocks_unused" carefully. You don't want too many "key_blocks_unused" or you are just wasting RAM. On the other hand, you don't want the MySQL server to be continually having to go to the disk for it's "key_read_requests" either. Check the ratio of "key_read_requests" (the number of times the server needed a key) to the "key_reads" (the number of times the key was not in cache memory so the server needed to go to the disc drive to find it). The number of "key_reads" compared to the number of "key_read_requests" should be very low (probably much less than 5% if everything is setup properly, and hopefully lower than that). Of course, the variable to concentrate on here is "key_cache_block_size". Increase it if you have too many "key_reads" and decrease it is you have too many "key_blocks_unused".
Anyway that's a brief summary of the most important issues... and I've purposely not given too much detail on them because you need to understand them... AND know how your MySQL server is ACTUALLY being used to get them right. There is NO magic formula unless you simply add dozens of GIG's of RAM and let everything use mega memory if it wants. We can't afford that, but if you can, go for it. Until I win the lottery, I'll have to be content with actually knowing what my server does and how to set it up for that :).