I generally like the idea of fumbles in games, being both true to life and literature, although they can be a problem if they’re too frequent or severe. A fair number of published systems would have a tenth or more of an army incapacitating themselves over the course of a battle. Another thing that I think is a problem, albeit a minor one, is that most systems tie fumbles into failure, so it’s impossible to both succeed at a task but have something go awry.
Here’s the system I’m currently using in my D&D-esque game: whenever rolling a d20, a roll of a 13 means something unlucky potentially happened. Roll a Luck save (luck is a Stat in this system, but you could substitute some other sort of save). Success means nothing happened, failure means something bad but relatively minor or recoverable (weapon twists in your grip and you can’t attack next turn, sun gets in your eyes, etc.). A second roll of 13 means something quite unfortunate happened, such as dropping your weapon or falling down. Roll again and keep rolling if 13 keeps coming up, making the result more severe the more 13’s you get.
Obviously you can adjust just how bad it is to taste; I feel that dropping a weapon one in 400 times is probably bad enough, but you might prefer that to be the result of failing the luck save, and have the roll of a second 13 be more spectacular, such as a broken weapon. You could also make it more severe, so that e.g. a weapon breaks on a failed save after the initial 13, if you want things to be more chaotic; I lean against that, in part because in most RPGs that sort of thing can really make the PCs seem like klutzes. During a campaign players tend to make many times more roles than any individual NPC they encounter, so a 1 in 20 or 1 in 40 shot may well turn up for each character at least once a night; if the failures are particularly memorable that can be a problem. 1 in 400 is more like once a session or less for some PC or NPC…enough to add flavor without being overwhelming.
I like this because it’s an easy mnemonic, which can be important for something relatively rare. It’s a pain to have to, say, check each roll to see if it missed by more than X if it’s only going to really matter 1 in 400 times. I also like it because it makes it possible to both succeed (if 13 was good enough) and still have something untoward happen, such as hitting a target but having your weapon stick.