First of all, AGP Aperture memory will not be used until your video card's on-board memory is running low. That means it will usually not impact your gaming performance because developers are trying hard to not exceed the on-board memory limits.

The bigger your video memory, the smaller your Aperture Size could be. However with later games requiring more and more texture memory a good number seems to be 128MB Aperture Size for all cards with 64 MB to 256 MB Video RAM.

Setting the Aperture Size to HUGE values will not increase performance because this merely sets the maximum amount of physical memory that can be used. It only makes the GART Table bigger because every 4K page has its own entry, no matter if allocated or not.

Setting the Aperture Size to too small values could result in running out of available texture memory especially on a low-mem video card. It is also possible that developers make use of the GART's features by creating textures as 'non-local'.

If you experience in-game stuttering try playing with the size of your Aperture, otherwise you're okay.

"Some people think that the more RAM you share with the graphics card, the larger the performance boost. In some cases, this might be true. In others, it might result in a performance drop. There are two common outcomes from setting the AAS too high: (1) If you share too much RAM with your graphics card and an application takes advantage of that, you will have less RAM for other functions of the software, and possibly for background applications. (2) If the graphics card does not utilize the memory, it is not taken from system memory. In this case, there are no problems with AAS sucking all of your system RAM, despite its setting in the BIOS configuration utility. The latter is the most common case, since most people have enough memory now to compensate for a high AAS, and most applications would not take all of the memory anyway.

[...]

As a new rule of thumb, never set the AAS below 16 MB, as performance and stability will be compromised. Another rule of thumb is that you should always set AAS to either be less than or equal to your total system memory, as you cannot use memory you do not have in the first place.
Performance is nearly identical from 16 to 256 MB, with minor dips at 64 MB AAS and problems with 256 MB on the GeForce3 Ti500 card. With new games coming out all the time that push our PCs to the limit, it might be a good idea to try and run a higher AGP aperture size, to be sure you’ve allocated enough memory for the video card. For this reason, we suggest at least a 128 MB AAS unless system memory is very low, or problems are experienced. " from Tweak3D.NET

Also check out the following reviews, articles and benchmarkings:
techPowerUP!
Tweak3d.NET