Re: Postgres on SSD

Hi,

On 12 August 2011 14:57, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
>> I'm about to evaluate this SSD card: FusionIO ioDrive Duo [1]. The
>> main reason for this experiment is to see if SSD can significantly
>> improve query performance

The result is that FusionIO will help to our queries which was
expected. Most of the long running queries return data between 5 and
30 sec range which is very good. The rest of the queries is super fast
but aggregates queries need sometimes several minutes. Anyway, the
overal performance is satisfactory and the rest could be fixed by
redesigning aggregates (keep/rollover top N counts not everything)

>> Database size is around ~1.4TB. Main tables occupied around 1/3
>> (450GB, ~220mil rows) and aggregated data occupied 2/3 (900GB). All
>> indexes are on separate table space (~550GB)

The redesign mentioned above can reduce aggregated data size to size
between 1/3 and 1/4 of the current size (and speed up queries). I've
tried to change several settings (work_mem, shared_buffers,
random/seq/... costs) but I wasn't able to get better benchmark
results. Our schema is very simple and query execution plan is
reasonable.

>>  checkpoint_segments          | 48
>>  maintenance_work_mem         | 256MB
>>  shared_buffers               | 9GB
>>  wal_buffers                  | 50MB
>>  work_mem                     | 256MB
>>
>
> checkpoint_segments should be higher, at least 64 and probably 128 to 256.
>  shared_buffers should be lower (at most 8GB, maybe even less).
>  maintenance_work_mem should be 1 to 2GB on a server with 80GB of RAM.
>  There's no proven benefit to increasing wal_buffers over 16MB.

I think this is the part which I have to look at...

Thanks,
--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)