In my experience this question is best indicated by the client. In our clinic, we usually start by suggesting a 1-10 pain scale and the pressure should be about at about 7. If the pressure is too much, then the muscle will tend to contract and any further pressure will be ineffective.
From a therapists point of view the muscle, by the end of the session, will be palpably softer and the experience of the client should describe the sensation as a "good pain". The experience of applying pressure should be like sinking into the muscle, this is achieved most effectively by slow and continuous pressure. A faster paced massage can feel lovely however it should always have some measure of slow and deliberate pressure to sink deeper into the tissues.
Should a massage hurt?
A massage will have more immediate resolution of pain symptoms if the pressure applied is strong enough to hurt the client, this is due to a "reversion to the mean". Once the massage is over, the sensation of relief will give the client a sensation of a reduction of symptoms.
I consider this a potentially useful use of massage to relieve symptoms however in our clinic we don't follow this method as on occasion the massage can cause painful symptoms that weren't there before the client came for treatment.