I have basic understanding of gearboxes and why we use them, but what is baffling me is that after about 4000 RPM (at least in my car) it doesn't accelerate further (or it does slowly)... This seems to apply to any kind of gearbox. For example in bicycles, when we select a lower gear after a while we feel with no resistance under our feet and the bicycle will not accelerate more. If we add more revs, why don't we get more speed?
The amount of acceleration you feel depends on the amount of power the engine can deliver, and the speed you're driving at.
Internal combustion engines are more efficient at some speeds than others. To illustrate, here's a random power and torque curve:
Torque (which is the property you feel when you accelerate) peaks around 3000 rpm, and then starts to drop: the engine gets less efficient at higher rpm.
Drag increases with the cube of speed, so it rises quickly when you accelerate. The faster you go, the more difficult it becomes to accelerate (the more torque you need). At some point, all of the engine's torque is spent counteracting drag (and other losses), and none is available anymore to accelerate.
On a bike, when you select a low gear and start pedaling as fast as you can, you run into the rev limit of your legs: the point where you can't pedal any faster because your nerves and muscles can't expand/contract any faster.
In a car something similar happens when you accelerate in low gear. Usually, the engine has a rev limiter to prevent damage. 4000 rpm is a very low limit for a petrol engine, but diesels can have limits in that region.
First the bicycle example: resistance is acceleration essentially. If you feel it, you accelerate. You can spin your pedal only so fast, so after some time, you can no longer increase the speed of your pedals, and thus the speed of the bike, that's the time when your efficiency dropped, and you need to shift.
Now the cars: Similar concept applies to car engines. Each engine type has it's own efficiency range. If you noticed, car manufacturers would specify peak torque and HP readings at a certain RPM. For some engines it's at 4k RPM, for some at 6.5k RPM. It depends on the engine design, displacement, type, etc.
Also, efficiency range can be adjust with different profile camshafts for different engines types, it's usually diled specifically for the vehicle needs - if it's a city vehicle, it would be making power rather early and feel snappy of the lights, but will run out of steam in the higher RPM. On the other side, sport vehicles would have peak power available rather late in the RPM.
Once you are out of the efficiency range, the forces acting on the engine components like friction and inertia increase, efficiency decreases and you feel loss of power.
It is very expensive to make an engine that is efficient in most of the RPM range, but many companies actually have variable timing like V-TEK for Honda, VVTI for Toyota, MIVEC for mitsubishi. This feature adjusts the cam-shaft profiles based on pre-programmed tune and helps to increase the range in which engine makes the power most efficiently. Alternatively, Constant Variable Transmission are also there to always keep you in the most efficient range.