Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-09-28 Thread Karl Denninger
On 9/28/2016 13:42, trafdev wrote: > Thanks Jov and Karl! > > What do you think about: > > primarycache=all > > for SELECT queries over same data sets? > >> Yes. >> >> Non-default stuff... >> >> dbms/ticker-9.5 compressratio 1.88x - >> dbms/ticker-9.5 mounted

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-09-28 Thread trafdev
Thanks Jov and Karl! What do you think about: primarycache=all for SELECT queries over same data sets? Yes. Non-default stuff... dbms/ticker-9.5 compressratio 1.88x - dbms/ticker-9.5 mounted yes- dbms/ticker-9.5 quota

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-09-28 Thread Karl Denninger
On 9/27/2016 23:06, Jov wrote: > > +1 > larger record size can increase compression ratio，so reduce the io. > > Did you set atime off for zfs? > > > 2016年9月28日 6:16 AM，"Karl Denninger" >写道： > > On 9/27/2016 16:38, Tomas Vondra wrote: >> On 09/27/2016 06:00 PM, To

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-09-27 Thread Jov
using zfs，you can tune full page write off for pg，which can save wal write io. 2016年7月29日 2:05 PM，"trafdev" 写道： > Hi. > > I have an OLAP-oriented DB (light occasional bulk writes and heavy > aggregated selects over large periods of data) based on Postgres 9.5.3. > > Server is a FreeBSD 10.3 with

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-09-27 Thread Jov
+1 larger record size can increase compression ratio，so reduce the io. Did you set atime off for zfs? 2016年9月28日 6:16 AM，"Karl Denninger" 写道： > On 9/27/2016 16:38, Tomas Vondra wrote: > > On 09/27/2016 06:00 PM, Torsten Zuehlsdorff wrote: > > > > On 29.07.2016 08:30, Tomas Vondra wrote: > > > >

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-09-27 Thread Karl Denninger
On 9/27/2016 16:38, Tomas Vondra wrote: > On 09/27/2016 06:00 PM, Torsten Zuehlsdorff wrote: >> >> >> On 29.07.2016 08:30, Tomas Vondra wrote: >>> >>> >>> On 07/29/2016 08:04 AM, trafdev wrote: Hi. I have an OLAP-oriented DB (light occasional bulk writes and heavy aggregated sel

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-09-27 Thread Tomas Vondra
On 09/27/2016 06:00 PM, Torsten Zuehlsdorff wrote: On 29.07.2016 08:30, Tomas Vondra wrote: On 07/29/2016 08:04 AM, trafdev wrote: Hi. I have an OLAP-oriented DB (light occasional bulk writes and heavy aggregated selects over large periods of data) based on Postgres 9.5.3. Server is a Fre

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-09-27 Thread Torsten Zuehlsdorff
On 29.07.2016 08:30, Tomas Vondra wrote: On 07/29/2016 08:04 AM, trafdev wrote: Hi. I have an OLAP-oriented DB (light occasional bulk writes and heavy aggregated selects over large periods of data) based on Postgres 9.5.3. Server is a FreeBSD 10.3 with 64GB of RAM and 2x500GB SSD (root on

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-07-28 Thread trafdev
> - from what I can see, Postgres uses memory too carefully. I would like > somehow to force it to keep accessed data in memory as long as possible. > Instead I often see that even frequently accessed data is pushed out of > memory cache for no apparent reasons. > This is probably a consequence o

Re: [PERFORM] PostgreSQL on ZFS: performance tuning

2016-07-28 Thread Tomas Vondra
On 07/29/2016 08:04 AM, trafdev wrote: Hi. I have an OLAP-oriented DB (light occasional bulk writes and heavy aggregated selects over large periods of data) based on Postgres 9.5.3. Server is a FreeBSD 10.3 with 64GB of RAM and 2x500GB SSD (root on ZFS, mirror). The largest table is 13GB (wi

[PERFORM] PostgreSQL on ZFS: performance tuning

2016-07-28 Thread trafdev
Hi. I have an OLAP-oriented DB (light occasional bulk writes and heavy aggregated selects over large periods of data) based on Postgres 9.5.3. Server is a FreeBSD 10.3 with 64GB of RAM and 2x500GB SSD (root on ZFS, mirror). The largest table is 13GB (with a 4GB index on it), other tables ar