Re: [PERFORM] Very poor read performance, query independent - Mailing list pgsql-performance

From Rick Otten
Subject Re: [PERFORM] Very poor read performance, query independent
Date
Msg-id CAMAYy4Lyjt3iSUi9wQtasX+q_SZoH8GCHvJeYmiSj1owWrzUSQ@mail.gmail.com
Whole thread Raw
In response to Re: [PERFORM] Very poor read performance, query independent  (Charles Nadeau <charles.nadeau@gmail.com>)
List pgsql-performance


On Wed, Jul 12, 2017 at 9:38 AM, Charles Nadeau <charles.nadeau@gmail.com> wrote:
Rick,

Should the number of page should always be correlated to the VmPeak of the postmaster or could it be set to reflect shared_buffer or another setting?
Thanks!


The documentation implies that you may need to adjust its size when you change shared_buffer settings. 

I usually check it every now and then (I haven't build a formal monitor yet.) to see if all of the huge pages are free/used and if it looks like they are all getting consumed - consider bumping it higher.  If there are lots free, you are probably fine.

cat /proc/meminfo | grep -i "^huge"

--

Also regarding my note on effective_io_concurrency, which I'm not sure you tried tweaking yet.

With file system and hardware caching between you and your spindles, your best setting for effective_io_concurrency may be much higher than the actual number of spindles.   It is worth experimenting with.   If you can, try several values.  You can use pg_bench to put consistent workloads on your database for measurement purposes.


Charles

On Mon, Jul 10, 2017 at 5:25 PM, Rick Otten <rottenwindfish@gmail.com> wrote:
Although probably not the root cause, at the least I would set up hugepages  ( https://www.postgresql.org/docs/9.6/static/kernel-resources.html#LINUX-HUGE-PAGES ), and bump effective_io_concurrency up quite a bit as well (256 ?).

pgsql-performance by date:

Previous
From: Igor Neyman
Date:
Subject: Re: [PERFORM] Very poor read performance, query independent
Next
From: bricklen
Date:
Subject: Re: [PERFORM] Very poor read performance, query independent