On Fri, Mar 26, 2010 at 10:06 AM, Alan McKay <alan.mckay@gmail.com> wrote:
> We are trying a test right now where "initdb" was run against
> /ramdisk/data so that absolutely everything should be in there. Will
> report back with results.
>
> We are also about to try another test with a regular disk-based DB and
> fsync turned OFF
>
>> Note that the query planner wasn't designed with RAM as the storage
>> space for pg, so it might make some bad decisions until you adjust
>> postgresql.conf to stop that. and then it still might make some bad
>> decisions.
>
> What thinks might need adjusting?
if you are chasing tps in volatile cpu bound problems, you can try:
*) disable fsync
*) full_page_writes = off
*) disable ALL statistics (track_activities, etc)
application side:
*) using libpq? consider moving to binary style queries
and
*) prepare queries
or
*) if possible, stack queries together, perhaps stacking data into arrays
well, if you are looking at extreme TPS rates beyond what postgres can
handle on your hardware (there are some hard cpu limits besides what
the disks are doing), you may have to start looking at an 'in process'
cache management library. of course, you have to balance this against
the loss of backend flexibility that postgres provides. also beware
this route if you have any type of concurrency requirements.
at one point due to curiosity I hacked a test into the backend by
inserting queries directly into the protocol handling switch in tcop.c
(bypassing the protocol completely) and was able to observe TPS rates
that are simply impossible in the current architecture (with the
backend processing humming along at 100% cpu from a single client).
until postgres supports this type of mode of operation (which is not
likely to happen anytime soon), it is going to remain relatively
unsuited for super high tps rate low concurrency 'cache' type
applications where the real strengths of the database don't play (all
that said, you may still get it to work for your own stuff).
merlin
