Intel is demonstrating a Teraflop chip at the International Solid States Circuits Conference in San Francisco today. The chip has 80 cores. (See pictures) The prototype isn't compatible with current Intel processors and it doesn't talk to external memory yet--so don't start dreaming of chipping your current machine just yet, but Intel plans to develop commercial versions of the chip which would be.
What do you do with all that capacity? Run even more full featured versions of Microsoft Office, of course. Seriously, datacenters will find plenty of uses. Being able to run multiple "machines" on a single chip is doable with today's technology.
The advent of such chips makes virtualization more important than ever. I've got a dual core in my laptop and for some tasks--like video encoding--the tasks is parallelizable enough that they make good use of both cores, but most tasks don't; and that's just with two processors. When you've got 80 or 512, or 1024, virtualization provides an easy way to split up course grained tasks.
There are, of course, power issues to be concerned about. Power and cooling are the two most limiting factors in datacenters today. You can't get enough of either. The prototype was air-cooled--that's good news. Most people have an aversion to getting liquids near their computers.
Beyond that, Intel is touting some "futuristic uses," including video play lists compiled in real time by and automatic editor and virtual dance instruction. Almost all of the anticipated future uses revolve around video because it's the things that's limiting so many things now. I'm confident that people will find new uses we can't even imagine right now.
How soon until you can buy one? Probably five years.