I'll be honest with you: most English speakers wouldn't know what to do here. None of the style guides I checked gives clear guidance for this situation. If we were to follow the closest applicable suggestion, to add 's for any singular proper noun, even if it ends with s, we would get
St. John's's mayor
Interestingly enough, my spell checker doesn't even mark this as wrong, although it's clearly ungainly as heck.
The best suggestion I found was, "Avoid awkward possessives" -- which in this case means you should write:
The mayor of St. John's
If you absolutely must use the possessive, I'd go with either
St. John's' mayor
or simply
St. John's mayor
More in-depth discussion of this here: https://english.stackexchange.com/questions/89705/possessive-form-of-a-proper-noun-ending-in-a-plural-noun-ending-in-s