How big should I set AGP Aperture size in my BIOS?

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.

What is it from a technical point of view?

When using an AGP card the video memory on the graphics adapter is mapped into the 4 GB memory address space (above the region of the physical installed memory). Any accesses to this memory region are directly forwarded to the video memory, greatly increasing transfer rates. However in earlier days of video cards graphics memory was rather limited and ran out quickly (a single 32-bit 512x512 MIP-mapped texture consumes ~1.5 MB) so AGP added a mechanism to use the system's main memory as additional storage for graphics data such as textures. This is what the AGP Aperture is. Usually directly below the mapped video memory the system reserves a contiguous space of addresses the size of your Aperture (no physical memory will be consumed at this time).
When free video RAM is running low the system dynamically allocates 4K sized pages of system memory for use as AGP Aperture Memory. The problem with this dynamic allocation is that in many cases the pages are spread in a non-contiguous form throughout the physical memory. Accessing these pages directly would hinder performance because of scattering/gathering requiring extra logic. To get around this limitation the GART (Graphics Address Remapping Table) which is implemented in hardware in the Northbridge's Memory Controller Hub provides an automatic physical-to-physical mapping between the scattered pages and the AGP Aperture. See the following illustration:


The actual usable amount of this 'virtual' AGP memory is less than half the AGP Aperture size set in the BIOS. This is because the Aperture is divided into two areas. One uncached half and another write-combined area.