So before anything else, I am not calling for gunpowder to be nerfed as of now. In fact, I think all cannon galleons and bombard cannons should be firing projectiles that are as fast (if not faster) than the spanish cannon galleon. However, if this were to happen, gunpowder would become the exclusive go to unit. That said, gunpowder would need to be nerfed. And the mechanics to properly nerf gunpowder are simply not in the game today, and THAT is what this post is about.
In the real world, gunpowder units do not have infinite gunpowder. However, given good foraging after a battle, an archer can replenish their arrows. In modern times ammunition trucks accompany military forces. In world war 1 and world war 2, same thing. In the civil war it was powder trains. In the napoleonic wars? Again, gunpowder was carried with the army.
The gunpowder train was well guarded, and when an enemy scored a direct hit on a gunpowder magazine, it blew the army to kingom come. Like a large demo ship explosion. The gunpowder was sourced from powder mills and given to individual units and powder magazines which gave the powder to individual units.
This could translate quite easily to a powder mechanic. The gunpowder units have a set amount of powder charge, and every time they fire, the powder charge lowers until it runs out and they can no longer fire. It could also translate to a powder train unit which has to refill occasionally at buildings that can stash gunpowder.
This makes gunpowder slow, cumbersome to use, and only useful for quick hit, run and recharge raids, or in the context of army that if you destroy their powder magazine, they have to retreat immediately. Also, it creates the situation where powder magazines have to make the trip from the powder production building to the front lines. And if powder production builds up slowly, then simply making new powder production is a significant investment. It slows powder down, and now people have the choice between expensive slow, delicate armies, or weaker armies of xbows that can go forever?
The second way to balance gunpowder is their hit/miss rate. The cannon fire should not be dodgeable if its going to hit you on the launched trajectory. It should also not launch on the right trajectory most of the time.
The third way to balance is misfires. Yes you heard that right. Many ships had unfortunate accidents with their powder magazines and simply exploded in the middle of combat. Having cannon galleons have a 5% chance to explode like a demo ship on being hit by an enemy is a fairly big nerf. Even more so if they have a .5-1% chance to go boom every time they fired their gun. This is in the game somewhat already, but it needs to apply to bombard towers, cannons, and cannon galleons.