Re: hyperthreadin low performance (and some discussion about benchmarking)

On 23 Jul 2015, at 13:37, domenico febbo <mimmopasticcio@gmail.com> wrote:

> is the problem also in PostgreSQL 9.4.x?
> I'm going to buy a production's server with 4 sockets E7-4850 12 cores
> so 12*4 = 48 cores (and 96 threads using HT).
>
> What do you suggest?
> Using or not HT?
>
> BR


1. If you have enough money to buy a 4-socket E7, then you certainly have enough money to pay someone (maybe yourself)
forthe 30 minutes of work needed to run a benchmark on the machine with and without hyperthreading and compare them.  I
meanliterally, run pgbench, reboot, turn on/off HT, run pgbench. Then you'll know what works best for your
configuration.Don't be lazy about this, it's as important as the money you're throwing at the hardware.  

2. Keep in mind most of the numbers people throw around are pgbench numbers. Pgbench is representative of some
workloads(e.g. bank transactions) and less representative of others (mixed query types, GIS work, scientific work,
heavyIO, interaction with other applications/libraries...). Are you using the server for other tasks besides postgres,
forexample? I find I get better performance with HT when I'm using postgres with GDAL on the same server. Probably
becausethe HT cores are being asked to do two different types of things, which is where HT shines.  

3. IMPORTANT : it doesn't matter how pgbench performs for other people on other computers and what they think is best.
What matters is 'how does YOUR normal workload perform on YOUR computer'.
The best way to do that is to put together a simple simulated workload that looks like your intended use of the system.
Leave it running.
If it's for an important system, look at all aspects of performance: transactions per second, I/O stalls, latency, ...
If you can't do that, pgbench can be used instead.

====

Finally. A serious point. The lack of diversity in postgres benchmarking is quite amazing, to my mind, and is probably
atthe root of the eternal disagreements about optimal settings as well as the existence of long-standing hidden
scaling/performancebugs (or weird kernel interactions). pgbench is useful, but really... let's make some more tools (or
sharelinks, if you know of them).  

Since contribution >>> gripe, here is my own (first, tiny) contribution, which I mentioned earlier in the month:
https://github.com/gbb/ppppt. 

As a point of contrast. Take a look at how computer game players measure the performance of graphics cards and disk
drivesin their product reviews.  
http://www.guru3d.com/articles-pages/radeon-r9-290-review-benchmarks,32.html

32 pages of data and discussion to test the performance of a single model (among thousands of possibilities and
millionsof configurations)! And this article is ordinary, run of the mill stuff in the gaming scene, literally the
firstlink I hit in Google. Has anyone ever in the history of these lists ever posted so much diverse and structured
evidencein support of their beliefs about a postgres setting? 

Gaming reviewers use a multitude of real-world games, synthetic benchmarks, theoretical estimates... as someone with a
footin both worlds it is quite amusing to see that game-players address benchmarking and optimisation of performance
farmore seriously, scientifically (and successfully) than most professional database admins.  

Many graphics card reviews care very much about reproducability/repeated results, surrounding test conditions (very
detailedinformation about other components used in the test, software versioning), warmup effects, benchmark quirks,
performanceat different scales/settings, and so on...  writing 'I saw some post where someone said they got a better
resultfrom XYZ' would certainly not be good enough in that community.   

Graeme Bell.