Horror in roleplaying games most often involves elements of the supernatural, from vampires and vengeful spirits to unkillable boogeymen and tentacled creatures from another dimension.
Relatively less common, in my experience, are mundane, non-supernatural horrors appropriate to modern or historical roleplaying games.
The serial killer
Serial killers are well-represented in both fact and fiction, from Jack the Ripper, Elizabeth Báthory, the marquise de Brinvilliers, and Burke and Hare to Hannibal Lecktor, Sweeny Todd, and Norman Bates, but my impression is that this is less common in roleplaying games, perhaps because the squick factor is too high - dealing with a fantastic monster like a werewolf is less disturbing than a killer inspired by the likes of Ed Gein.
Other examples of the serial killer could include mad scientists, such as a vivisectionist, or cultists practicing ritual sacrifice, such as the Aztecs or the Celts - the latter could include those who practice cannibalism as part of ritual life, or even those driven to it in extremis.
Note that a serial killer isn't necessarily mentally ill.
The stories of the beast of Gévaudan and the lions of Tsavo open up all sorts of interesting possibilities for encounters with animals which are both monstrous and mundane. They could be wild animals running amok, trained pets (as in "The Murders in the Rue Morgue"), or 'lost' species.
The megalomaniac suffers from a range of mental illnesses which manifest as feelings of omnipotence and delusions of grandeur. Mister Kurtz and the Phantom of the Opera are fictional examples of what I have in mind here.
The mountebank uses horror as a means of covering up his (usually criminal) activities. He is Cosgoode Creeps or Asa Shanks, posing as a ghost to scare off the nosy townsfolk or bilk his relatives out of their inheritance.
I have a few traces of horror in my cape-and-sword campaign, particularly around Auvergne, which I tend to think of as the 'scary Appalachia' of my game-world's France.