Yes, it can be a difficult balance to maintain. Even just for commercial success, you need to be at least somewhat known, but the moment you do enjoy commercial success, all the psychological baggage that comes with fame might start to affect you.
I think it's important to have some idea of why exactly you do what you do. Is it really for no other reason than to draw attention and be famous and popular and so on, or do you at least also enjoy what you do so much that you'd keep on doing it even if you lived in obscure poverty?
This phenomenon of celebrities getting destroyed by their own success seems to happen more in some streams than in others. We all know the stereotype of the pop singer or movie star ending in drug addiction and misery, and usually this can be directly traced back to their fame. But how often does this happen to, say, concert pianists, or artists who have successful but not super-famous careers? These are people heavily focused on the craft of what they do, and far less on looking good in public. The problem in a lot of popular music is that much of the artist's success is not just about their musical skills, but about their entire public persona and brand and appearance, all stuff that has zilch to do with music.
Nobody cares in the least what classical or jazz musicians look like (but they care a great deal about what they
sound like), whereas one wonders how far Amy Winehouse would have gotten without her trademark "look." And of course, she herself began to wonder more and more about that - did they love her music, or just her appearance, or her glamor, or what? She ended up having no clue who she really was - it might have served her well to retire from public life and only do studio albums, to see whether it was really her voice and music that attracted the audience (my guess is, she would have lost all the toxic fans and retained the ones who actually care about music).
As visual artists we are the lucky ones, because even very famous visual artists are not necessarily very public. E.g. I'm a huge fan of Aron Wiesenfeld, and I have no idea what he looks like and wouldn't recognize him if I saw him in the street. In fields like visual art and writing, it is possible to become very successful without sacrificing your private life, though many writers and artists apparently prefer to also become public personas. In acting and music it's not so easy - you're going to be recognized, and people will do everything from falling on their knees in front of you to throwing things at you. It's no wonder some of them end up cracking up.
And then, for most of us, there is the ever present specter of impostor syndrome - I'm actually no good, and what happens when I'm finally caught out and exposed for the unskilled amateur that I am? It occurred to me the other day that impostor syndrome is actually one more manifestation of our narcissism, because it presupposes that everyone is watching us, when in fact, they're not. Even if you are caught out, it will actually have no effect on anything, because those who can draw and paint better at you, while they are qualified to catch you out, will almost immediately forget about you (how many of us keep on thinking about some bad, bad painting we saw somewhere?), while those who are not as good as you can be laughed off.
Anyway, I think the self-destruction usually happens when people lose sight of why they're in the business to begin with. And alas, many people go into the arts not because they genuinely love music or acting or painting, but simply because they want to be famous. Lucky for them, there are nowadays much easier ways to do it than spend years battling it out with auditions or art courses: just get online and go make a fool of yourself on TikTok.