This expression is not really used very commonly anymore in daily spoken English anymore. You're more likely to see it in print, I think. Another similar one is the use of the word 'bust,' which means to go broke or to fail financially. For instance one might say, 'the dot come boom went bust.'
I would say it is most common in relation to business. Its very common to hear that something has gone belly up. If you work in investment banking etc they have a whole raft of such expressions. You could say it's walked south for example. North and South being up and down on a compass and the direction the stock price has moved. I always assumed belly up referred to death. In the old cowboy movies they always seemed to land on thier backs after being shot in a gun fight.