I see many people talking about how they have built water cooling into their system. Is there any real benefit in performance by doing this or is it purely for aesthetics?
The advantages of using water cooling over air cooling include water's higher specific heat capacity, density, and thermal conductivity. This allows water to transmit heat over greater distances with much less volumetric flow and reduced temperature difference.
For cooling CPU cores in computing equipment, the primary advantage of water cooling is that its tremendously increased ability to transport heat away from source to a secondary cooling surface allows for large, more optimally designed radiators rather than small, inefficient fins mounted directly on the heat source.
So basically it can be beneficial when some of computer components get too hot (overclocking, warm room, etc.), as it is more efficient than air coolers.
As for noise, see this nice benchmark:
With fans on low speed, liquid coolers are not able to stay as silent as the quietest state-of-the-art air coolers. Some air coolers can maintain 25 dB(A) or less at a 10 centimeter distance, while the most silent water cooler (Corsair H55) creates 31 dB of noise. Only three liquid coolers stay below 35 dB(A) which is virtually inaudible: the H55, the Antec H20 920 (the original version!) and the Corsair H60. The Corsair H100i and H80i don't go below 40 dB(A), which isn't that loud but also not very quiet.
Water-cooling is better than traditional fan-cooling. Using it is reasonable if you have hardware that gets hot very often and reaches its temperature limits; and you can't cool it down in traditional ways. Another (bad) reason is something like "hey people look, I have watercooling because its cool". And the last reason is - it's not noisy and dusty.
And what about performance?
Hardware may slow down on high temperatures. That includes CPU, GPU.
Hardware can get damaged on high temperatures. For example, HDD (yes, it's max temperatures are usually about 55-60C, whereas CPU can be 70-100C), especially HDD on notebooks.
There is no performance gain when using watercooling. But there can be performance loss or damage if such temperature-sensitive devices don't get enough cooling. And then watercooling is a good option to re-gain the lost performance.
I'm going to divide water cooling into two categories: Closed loop and Open loop. Now we can compare Tower (air), CLC, Open loop and exotic (LN2 or similar) cooling methods.
Big air coolers are generally around the $50-80 mark and produce decent performance for the 50-150W CPUs that you see in everyday life. Pretty heavy, and mounted on the motherboard, in small cases, these are not options. Smaller ones are cheaper, but they cool far worse.
CLCs are generally designed for CPU cooling only. The small radiator ones (120/140 mm) do not offer an improvement over air cooling. The big ones (240/280/360 mm) are advantageous for high TDP (above 200 W) or big overclocks.
Another advantage is that the big, heavy metal block is mounted on the case instead of the motherboard, which reduces the stress on the motherboard, and you don't have to remove the cooler when shipping it.
CLCs are not customizable, and good units cost about $80 - 100, around $20 more than your high end air cooler. They are also a fair bit noisier, because of the pump. Finally, there are sometimes issues with VRM overheating, because of the reduced airflow near the VRM.
Open loops are more of the same, but you can add GPUs and your Motherboard to the list of things that can be cooled by the loop. You can have far larger or more radiators, add dye to your cooler, etc, etc. Unfortunately, open loops are also more vulnerable to leakage, because they're not sealed. Good open loops cost around the $200 mark, though you can get hybrid systems that start out as ordinary, but unsealed CLCs that you can eventually modify.
Exotic systems are only used when you want to push your hardware to the limit. Often, this means below ambient cooling, where the temperature is below air temperature in the local area. Beware of the condensation. LN2 systems are some of the more popular ones. I'm not sure exactly how much these cost, but be prepared to pay more than $400. If you want to have 4 GPUs and one 8 core CPU all in the same system and push the clock to double the stock frequency, this cooling is for you.