You need to understand basically NES games are divided into two chips PRG and CHR. PRG is the program data that holds the script, the rules for the game, basically, almost everything. CHR on the other hand is graphical data, sprites, tilesets and effects.
Each of the mappers were designed with maximum allotted size limits. MMC3 I believe has a maximum of 256 KB PRG, and 512 KB of CHR. Only one game used these maximum values, that being Kirby's Adventure.
That said, MMC3 was not the upper limits of storage. The largest game to use an official mapper was Metal Slader Glory for the Famicom, which maxed out the MMC5 with a 512 KB PRG and a 512 KB CHR.
On the NES, this was actually outdone, but only by the horrible unlicensed Action 512, which used a total 1,536 KB PRG and 512 KB PRG with a custom mapper made specifically for that "game".
Most of the big Hong Kong Originals to come out over the past few years such as Final Fantasy VII use a custom Flash rom that combines PRG and CHR into one file. In the case of FF7 FC, that uses a 2048 KB PRG + CHR using mapper 90, which is similar in capabilities to MMC1.