For it's day, it packed a lot of compute into cheap package, so long as you could do something useful with a data set that fit into 256kB, the size of the local memory buffer on each SPE. If you overflowed that, the anemic system bandwidth would make it suck. Protein folding was an example of a problem that back then used tons of compute but could be fit into small space.
It was the biggest contributor to folding @ home at one point. It came bundled with the PS3 and played relaxing music and showed a heat map of the world ps3 compute nodes as it went on. There was also https://en.wikipedia.org/wiki/PlayStation_3_cluster
