Here's my take.
Mana is a resource. Resource management is REALLY important in RPGs, and Mana is one of the ways in which resource management happens. You're not really supposed to leave a dungeon every time you get hurt. If you challenge yourself to make it through a dungeon in one go, that's where the importants of items and the limits of MP really get tested, and it's fun!
But sometimes there are RPGs that aren't about treks through dungeons. Sometimes they're more about individual battles themselves. Some games can mix, long dungeons, or short ones with difficult enemies. I definitely feel that MP as a system can work for all of these!
But that doesn't mean it's always successful.
Think it is in a lot of games purely for the reason that people are used to it.
I can provably say this is fact. Have you ever played Final Fantasy 1? If so, which version? There's a big reason many prefer the original NES or PS1 port versions. And that's because of the magic system.
FF1 uses Spell charges, similarly to DnD. Each level of spell has a certain amount of times it can be used until you replenish it by resting. The amount of times it can be used increases by levelling up. So for example, your red mage might have 5 uses of level 1 spells, 3 uses of level 2 spells and 1 use of a level 3 spell left. All level 1 spells share charges, so you have to choose whether to use them between fire and cure. But no matter how many times you use them, your level 2 and 3 charges won't be used, so you can conserve them for bosses or tougher fights. This system is REALLY interesting and fun, and at higher levels let mages fire off basic spells without worrying about running out of magic for harder enemies.
This changed in later versions. Every version after that, such as the GBA and PSP ports switch to a basic MP system. No explanation given, I feel like it's purely because people are too used to MP. Maybe also because Final Fantasy 8 also used spell charges (albeit differently from FF1) and a lot of people complained about FF8's gameplay (not me though, I think FF8 is fantastic).
So yes, while MP can be used to great effect, it's also true that many games use it just because it's standard. When a game uses it well, it's great. Examples:
Bravely Default, I was really hurting on resources making my way through vampire castle, which is a LONG dungeon with *really tough enemies* when by that point in the game in many other RPGs I'd forget MP existed entirely. It gets even more fun when you add items that do spells without any cost (but they can't get high level spells) and specials that restore MP (but require you to cast certain spells to unlock them)
Kingdom Hearts 1. It's not really resource management in this game since it's a platformer with levels rather than a dungeon crawler, and savepoints restore it, but managing it is still fun, because MP is used for most of your moves (which are all useful) and you replenish it by landing regular attacks or, with MP rage, taking damage. This is called MP charge, and makes even counting MP a lot more complicated than what you see on the screen (an MP stat of 3 means 3 MP bars, so you'd think it means 3 uses of 1MP magic like Fire, but with MP charge it actually can be 5 or 6 uses in a row!) It introduces an interesting effect with balancing. The MP stat and the magic stat are the same in this game, meaning the more MP you have, the more powerful your magic is. However, the amount of MP restored through MP Charge depends on the damage you deal, so strength builds will have an easier time restoring and maintaining MP to continuously use than magic builds.
Tales is an example of... the good AND the bad. It doesn't apply to all Tales games, only the ones that use TP. TP essentially is MP, it's just not for magic exclusively, and you'll be using it for every battle if you want more than the basic AAA combo. Managing resources is especially important in the first 3 games, where the only way to restore them is orange gels and resting, but you'll need to use them because most of the damage comes from spellcasters, and the physical attacker's job is not to deal damage but to do extended combos to keep enemies busy until spellcasters are ready. Destiny 2 evened out the damage between the two roles (though it didn't quite use TP). Symphonia took many of destiny 2's systems and brought back TP. Now you can restore TP with basic attacks. Management isn't as important now because you can fight many battles with only basic attacks to get TP back for harder enemies. And then at high enough levels you might forget about TP costs entirely.
However, what makes it still good is the item system. On a regular run, you can only hold up to 15 of one consumable (NG+ has uncapping this limit as an option) so you can't just buy 99 at one shop and be good for the rest of the game. But more importantly than that, it's how it interacts with the grade system. Tales scores you per battle, do better to get higher grade, and the total is given when you beat the game, adding an arcadey element. Using items in battle means grade penalties (and you can get negative grade from a fight, detracting from your total). Items outside is ok, though. This is interesting because it has the rank penalty you'd expect from a more arcadey game, so you don't want to use them in battle... but you're allowed to use it outside of battle, meaning you can still use it as a resource that way. Smart use of items outside of battle can help you avoid using items in battle to get the penalty! It can get even more complicated than that too. Perhaps you want to avoid using items in battle because of the penalty... but a character death is an even higher penalty, and then you'd have to use a revive item to dig yourself deeper! Using an apple gel to heal, or an orange gel to keep your healer going can prevent that, basically meaning you got a smaller penalty to avoid a bigger one.
Of course, Symphonia didn't have Grade breakdowns, so I wouldn't blame someone for not knowing about the item penalty. Later games let you see exactly how you got your score.
That was a bit of a tangent, but I'm sure you can see how Mana is one piece of a greater puzzle and can shape an experience like that. Of course, you have to care about getting a higher grade in the first place, so it wouldn't matter to people doing more casual runs.
I suppose I should mention Kingdom Hearts 2 and 3, but the MP system in those games is more of a traditional meter that's just *called* MP, that has a period of automatic recharge where you can't use any MP abilities when it reaches 0 until you get full again.
Some SMT games. It's not that MP management itself is all that special, so much as it is that *physical* skills cost *HP*. Using MP for magic skills would be preferable in games with this system, but skills on both spectrums are useful!
Those were just a few examples, but long story short I feel like MP can work really well, but as a component in a bigger system rather than as a system itself. It's doubly true that many RPGs might simply put it there because it "balances" magic attacks, or something. Ironically despite my complaints about Final Fantasy 1 I think Square is one of the developers willing to put more thought into the MP system because they admittedly don't think everything needs to be balanced.