You can't run more than one mod at the same time, so that's not an issue. Interface mods like BUG shouldn't affect your performance notably at all, Kmod actually increases performance a little because it streamlined a lot of code to make it more efficient while only making relatively minor changes to the gameplay itself. Beyond that I guess you have to test for yourself, but I imagine major kitchensink mods like Cavemen to Cosmos or Realism Invictus will be very demanding.
To give you a better overview of what the modding community has to offer let me categorize mods I know about like so:
Minor mods - Improving performance or interface or AI etc. without actually adding much in the form of more buildings/units/civs etc.:
*BUG/BULL/BAT (just interface improvements, doesn't affect gameplay itself)
*Kmod (lots of under the hood changes and balance improvements, the best AI possible)
Major mods - adding various more stuff and possibly game mechanics:
*Legends of Revolution (styles itself as an unofficial expansion pack, basically what Warlords or Beyond the Sword did to the Vanilla game, adding some more units, buildings, wonders, civs, a few game mechanics etc.)
*Civ4Reimagined (also kind of has the same goal as Legends of Revolution, more stuff, better balance, new game mechanics etc.)
*Realism Invictus (this mod be big yo)
*Caveman to Cosmos (beware, this is like really really really humongous, like you have to spend a few thousand years as cavemen trying to discover fire before you even settle your first city)
*History Rewritten (I never actually played this one)
*Vincentz Infinite Projects (dito)
Historical mods - mods that attempt to emulate history on maps of real parts of the world or Earth entirely:
*Pie's Ancient Mediterranean (THE mod for when you want to relive the glory of Rome, or really any ancient civilization around the Mediterranean.)
*Road to War (I think it's bundled with BtS, but some modders in this forum have further improved it. Basically replay World War II.)
*Earth 2010 (or 2012? Or 2015? I don't even know anymore at this point, but somewhere on this forum is a mod or several with an accurate map of the world we live in today.)
*Rhye's and Fall of Civilization (This is the big one. It even came shipped with BtS, and it has spawned a bazillion modmods. Replay history on an accurate Earth map where every civ spawns at the right historical date and location. I recommend the Dawn of Civilization modmod (or my own personal modmodmod for it) for basically being the same but a lot better, fixing bugs and adding more content. There are also other modmods like RFC Europe or Sword of Islam, which utilize RFC's core mechanics but focus on certain geographical areas instead of the entire world at once.)
Total Conversion mods - for when you just want something completely different:
*Final Frontier Plus (Final Frontier came shipped with BTS, Final Frontier Plus fixes its bugs and improves it in other ways as well. Basically it's Civilization IN SPACE, where instead of cities on a planet in the past you control star systems in a galaxy in the future.)
*Planetfall (basically a remake of Alpha Centauri)
*Fall From Heaven II (A must have if you are a fantasy aficionado, and even if you are not, it is still extremely impressive for its immersive gameplay. Magic, gods, vampires, dragons, dwarves, elves, demons, I don't even know where to start. You can literally play a god! Like RFC it has a ton of modmods improving it further. Personally I recommend at least the More Naval AI modmod for balance, gameplay and AI improvements, and Magistermod for more immersively bringing the vast lore or Erebus to the player.)