Hi, On 12 August 2011 14:57, Greg Smith <greg@xxxxxxxxxxxxxxx> 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.ivanic@xxxxxxxxx) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
