Re: Best system for a data warehouse application

Hello,

One of the 9.6 new features is "Parallel Query".
Therefore , in my view more cores do favor your dataware house system.
My concern is the share buffer usage.
By my experience in 9.2 , 8.3 and 8.4 and ever consulting EDB technical
support,
do no set share_buffer over 8GB, the more the setting, the more memory
you are wasting.
Postgres mostly count on file system cache for I/O performance.
However, multiprocess access the same data do need share memory to
co-work.
If share buffer ages out, postgres still page in data from OS cache.
Though memory access is much faster than DISK I/O, it still triggers
some overhead.
Greg also stats the same situation in his own book , high performance 9.
pg_prewarm and pgfincore do give some more control on memory management.
But I still concern the postgres share memory management when taking
care of large amount of data.

Can anyone in postgres kernel development team gives comments or advices
??

Best Regards,
Steven

2017-02-21 21:02 GMT+08:00 Lazaro Garcia <lazaro3487(at)gmail(dot)com>:

> You could use pg_upgrade utility.
>
>
>
> Regards.
>
>
>
> *De:* pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner@
> postgresql.org] *En nombre de *John Scalia
> *Enviado el:* martes, 21 de febrero de 2017 06:58 p. m.
> *Para:* Yuri Paes Leme
> *CC:* pgsql-admin(at)postgresql(dot)org
> *Asunto:* Re: [ADMIN] Best system for a data warehouse application
>
>
>
> Good to know, We haven't upgraded to 9.6 yet, however, but I'll suggest
> that for this upgrade.
>
>
>
> On Tue, Feb 21, 2017 at 12:46 PM, Yuri Paes Leme <yuripl(at)gmail(dot)com> wrote:
>
> https://www.postgresql.org/docs/9.6/static/how-parallel-query-works.html
>
> So, IMHO, if you can use the 9.6.x version, more cores, more power
>
>
>
> On Tue, 21 Feb 2017 at 14:39 John Scalia <jayknowsunix(at)gmail(dot)com> wrote:
>
> Hi all,
>
> One of my system admins has approached me about replacing our production
> data warehouse system. I believe I know the answer, but would just like
> more opinions. He's giving me two options so far:
>
> 1) CPU = Intel e5-2637 3.5GHz 4 core with hyperthreading
>
> vs.
>
> 2) CPU = Intel e5-2650v4 2.2GHz 14 core with hyperthreading
>
> The rest of the specs are identical with 512Gb RAM and more than 2 Tb SSD
> drives. Our data warehouse is just shy of 2Tb now. I suspect the the first
> option would be more optimal as I don't think PostgreSQL can make effective
> use of a lot of processor cores. So fewer cores would be better optimized
> with the O/S. Can anyone weigh in on whether my assumptions are correct?
> --
>
> Jay
>
>
>