how loud is too loud ?
Depends on the exhaust note. A small two-stroke (um well, there are no large two-strokes, not any more) at even mildly elevated sound levels will be experienced as rather annoying to the average ear, while a big chunky four-stroke at significantly higher sound levels might not.
Case in point, I live on a moderately busy street, whenever some school kid on his badly silenced moped passes through, I curse. But there's this guy round the corner who has this old Ducati Paso, and when he passes through, the windows rattle and I smile. But YMMV, and I should state that my ear is certainly not the average Daily Mail reader's ear.
On the utility of "loud" pipes in traffic, well they're not going to help to avoid a head-on collision. But they do certainly help in avoiding jerky reactions from car drivers who did not notice you were overtaking them, and often they're a literal wake-up call to car drivers who might be about to turn into your path.
Unless you've clocked some miles on two wheels, you would not realize, nor believe, how distracted car drivers often are. The amount of gadgetry available to them lately (satnavs, mobile phones, car stereos) certainly doesn't help. When you're on a bike, you don't have time for distractions: you constantly keep a tab on anything that moves and the slightest lapse of attention might result in you ending up in a wheel chair or worse.