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] Unexpected expensive index scan

2016-09-28 Thread Jake Nielsen
On Wed, Sep 28, 2016 at 6:04 AM, Tom Lane wrote: > [ Please don't re-quote the entire damn thread in each followup. Have > some respect for your readers' time, and assume that they have already > seen the previous traffic, or could go look it up if they haven't. > The point of quoting at all is j

Re: [PERFORM] Millions of tables

2016-09-28 Thread Stephen Frost
Greg, * Greg Spiegelberg (gspiegelb...@gmail.com) wrote: > On Wed, Sep 28, 2016 at 11:27 AM, Stephen Frost wrote: > > * Greg Spiegelberg (gspiegelb...@gmail.com) wrote: > > > Bigger buckets mean a wider possibility of response times. Some buckets > > > may contain 140k records and some 100X more

Re: [PERFORM] Millions of tables

2016-09-28 Thread Greg Spiegelberg
On Wed, Sep 28, 2016 at 11:27 AM, Stephen Frost wrote: > Greg, > > * Greg Spiegelberg (gspiegelb...@gmail.com) wrote: > > Bigger buckets mean a wider possibility of response times. Some buckets > > may contain 140k records and some 100X more. > > Have you analyzed the depth of the btree indexes

Re: [PERFORM] Millions of tables

2016-09-28 Thread Stephen Frost
Greg, * Greg Spiegelberg (gspiegelb...@gmail.com) wrote: > Bigger buckets mean a wider possibility of response times. Some buckets > may contain 140k records and some 100X more. Have you analyzed the depth of the btree indexes to see how many more pages need to be read to handle finding a row in

Re: [PERFORM] Millions of tables

2016-09-28 Thread Greg Spiegelberg
On Wed, Sep 28, 2016 at 9:39 AM, Vitalii Tymchyshyn wrote: > Have you considered having many databases (e.g. 100) and possibly many > postgresql servers (e.g. 10) started on different ports? > This would give you 1000x less tables per db. > The system design already allows for many database serv

Re: [PERFORM] Millions of tables

2016-09-28 Thread Richard Albright
If going that route, why not just use plproxy? On Wed, Sep 28, 2016 at 11:39 AM, Vitalii Tymchyshyn wrote: > Have you considered having many databases (e.g. 100) and possibly many > postgresql servers (e.g. 10) started on different ports? > This would give you 1000x less tables per db. > >> >>>

Re: [PERFORM] Millions of tables

2016-09-28 Thread Vitalii Tymchyshyn
> > Have you considered having many databases (e.g. 100) and possibly many postgresql servers (e.g. 10) started on different ports? This would give you 1000x less tables per db. > >>

Re: [PERFORM] Unexpected expensive index scan

2016-09-28 Thread Tom Lane
[ Please don't re-quote the entire damn thread in each followup. Have some respect for your readers' time, and assume that they have already seen the previous traffic, or could go look it up if they haven't. The point of quoting at all is just to quickly remind people where we are in the discussion

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