Re: What are these tables?
These are great issues to bring up on the buddypress mailing list and I encourage you to do it. I do not develop WPMU or Buddypress and these are just my opinions. This is just background information as well. WPMU is known to be extremely server intensive as is.
Right now, most scaling solutions have been implemented for WPMU and the database structure that is already developed and in place. Mainly, blog sites like wordpress.com and edublogs.org. They have implemented multi-database solutions where the request gets analyzed for the blog hash and directed to the proper database and the connection only occurs to the database that houses that data. Databases can reside on any server in any location.
Images are also housed in directories created through the blog hash as well. This also allows you to have multiple image directories in multiple locations if you wanted. Some even offload some of their images to 3rd party services like S3.
At present time, AFAIK, there is no solution for spreading around the buddypress data. It hasn’t been addressed yet.
I am not going to argue and say this is the most ideal circumstance or setup. There are plenty of discussions on scaling both in the WPMU forums as well as in the WPMU Advanced Forums as well as on the WPMUDEV Premium Forums. Not to mention the WP Hackers list is also a great resource from the real developers and hackers of WP in general.
Buddypress, like WPMU, are open-source communities and all input is not only welcomed, it is encouraged! Feel free to add anything that you feel would be better. I am sure Andy is welcome to hearing all feedback if you know a better way to do something.
My previous comment on this topic was purely my opinion on how the current setup could be scaled based on my experience with already ongoing projects. Right or wrong, it is just my opinion.