Re: Single client performance on trivial SELECTs

I did some more research into the memory allocation hotspots found in
the profile, in regards to what MySQL has done in those areas. (And by
"research" I mean "went to the bar last night and asked Baron about
it") The issue of memory allocation being a limiter on performance has
been nagging that community for long enough that the underlying malloc
used can even be swapped with a LD_PRELOAD trick:
http://dev.mysql.com/doc/refman/5.5/en/mysqld-safe.html#option_mysqld_safe_malloc-lib

Plenty of people have benchmarked that and seen a big difference between
the implementations; some sample graphs:

http://locklessinc.com/articles/mysql_performance/
http://blogs.sun.com/timc/entry/mysql_5_1_memory_allocator
http://mysqlha.blogspot.com/2009/01/double-sysbench-throughput-with_18.html

To quote from the last of those, "Malloc is a bottleneck for sysbench
OLTP readonly", so this problem is not unique to PostgreSQL. As of 5.5
the better builds are all defaulting to TCMalloc, which is interesting
but probably not as useful because it's focused on improving
multi-threaded performance:
http://goog-perftools.sourceforge.net/doc/tcmalloc.html

I'm not sure exactly what is useful to be learned from that specific
work. But it does suggest two things: one, this is far from an easy
thing to fix. Two, the only reason MySQL does so well on it is because
there was some focused work on it, taking a quite a while to accomplish,
and involving many people. Doing better for PostgreSQL is something I
see as more of a long-term goal, rather than something it would be
reasonable to expect quick progress on.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us