The propeller is the primary RPM-limiting item on a direct-drive engine. The engine itself can often handle more revs, as some have mentioned here in regard to racing versions of the O-200. The propeller's operating range is limited by tip speeds and by centrifugal forces. Once the tip speeds get much beyond 600 MPH the drag goes up, much more noise is generated, more power is lost, and the forces trying to pull the prop apart get too large. Remember that those forces multiply by the square of the increase in speed, so a 10% overspeed puts 21% more pull on those blades. A 20% overspeed puts 44% more stress on them. That prop is already the most highly stressed part on the airplane, having to fight thrust bending loads, drag bending loads, center-of-pressure pitch-increasing loads and centrifugal pitch-decreasing loads, and the usual ordinary centrifugal loads.
Centrifugal loads can reach 40 tons or more on each blade. Remember that next time you preflight that prop and find a significant nick in it (nicks create stress risers that can cause cracking), and try to understand what happens if a foot or so of a blade breaks off: the vibration will likely tear the engine off the airplane. What happens, besides a loss of power, when hundreds of pounds leave the nose? Where does the CG end up? Will the airplane glide? Nope.
At 600 MPH tip speed the air over the cambered surface is much faster, same as the air over the top of an airfoil, so it is approaching the speed of sound. It generates shock waves we hear as the snarl of takeoff. Drag is the result, and some operators of engines like the IO-520, redlining at 2850, find they get as good or better takeoff performance if they pull the RPM back a little for takeoff.
A manufacturer like McCauley defines overspeeds in their maintenance and overhaul manuals. Typically, they call for prop removal and NDT if there's an overspeed of 10% for more than five seconds or something like that. They're worried about stress cracking. The engine manufacturer also has overspeed recommendations but they're a bit less stringent.