Yes with enough money and a zero latency network (
InfiniBand) one can make a supercomputer with each Mac Pro being a "node".
It's been done quite a bit with early PowerMac G5's and X-Servers and the most well known one "
Big Mac" reaching the third most powerful in the world for several months.
*
Now for a personal level if your just interested in setting up a few Mac Pro's in a local cluster. You would use the System Preferences>Sharing>Xgrid and chain up a local network using the extra Ethernet port.
Now you would need software that can take advantage of the X-grid, I know VisualHub and I think some of Apple's Pro Video software will break up a job and send pats out to the "nodes" to process. One Mac Pro will be the administrator of the X-grid and any unused processor cycles would be dedicated to the Xgrid and the job at hand.
One has to have a pretty big job to be broken up, like a several hours or days render to do. When each node is finished with their section of the job, the results are returned to the main machine for compilation.
*
Now if you want to hook up a bunch of Mac Pro's in the idea that you'll get more performance with one program, that's not going to happen. First off a lot of programs are not multi-threaded, because most programs are linear and it takes time to send something over to another core and wait for it to return. Sometimes it's just faster for the main core to do all the work itself.
Something that can be passed on, like sound processing, which will exit the machine via the speakers, can be passed over to another processor. Things like that can free up the main processor.
Now things get even slower if one tries to send the "job" to another machine on a network, network interfaces being slow. This is why it has to be a pretty big job or else it's just faster for the main processor or computer to do all the work itself.
*
If you want to "remote control" other Mac Pro's on a local network, you would use Apple's Remote Desktop.
What this will do is place a window of the screen of the other Mac on the first one. You could run more programs at once for sure, but the programs won't take advantage of the other Mac's unless they are specifically written to be like that. (and have a need obviously) Ethernet or faster is mandatory because of slow screen renders going through the slow network.
If you got the mega bucks you can set up a InfiniBand network which has virtually no latency. So screen redraws of the other Mac's controlled would be instantanous.
Most of the time people only use one active program at one time, the other open apps are just idle. But if you have a lot of heavy duty work to do, then Xgrid, ARD or something can do.
