public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kevin Grittner <[email protected]>
To: Flávio Henrique <[email protected]>
Cc: Merlin Moncure <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: Slow query after 9.3 to 9.6 migration
Date: Thu, 5 Jan 2017 11:10:57 -0600
Message-ID: <CACjxUsPd0nsLh37tpP+9dU92EYUAujSuhrEvncej_=EsSKiQ9A@mail.gmail.com> (raw)
In-Reply-To: <CAOGex3=0DB-R9V558CkeoSOuU4KG_RyNh5etzo85o43xGcuVvQ@mail.gmail.com>
References: <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
<CAHyXU0wDi9VjfGC8aQeLsBq4ncLVOKJ=1QR6iRq71U2HXQso4Q@mail.gmail.com>
<CAOGex3=0DB-R9V558CkeoSOuU4KG_RyNh5etzo85o43xGcuVvQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
On Thu, Jan 5, 2017 at 10:51 AM, Flávio Henrique <[email protected]> wrote:
> Replying your comment, I think they tunned the server:
> effective_cache_size = 196GB
> shared_buffers = 24GB (this shouldn't be higher?)
Probably not, although it may be a good idea to try settings either
side of that (say, 16GB and 32GB) and monitor performance compared
to the current setting.
> autovacuum_max_workers |3
If you ever see all workers busy at the same time for 30 minutes or
more, you should probably consider raising that so that small,
frequently updated tables are not neglected for too long.
> autovacuum_vacuum_cost_limit |-1
That is going to default to vacuum_cost_limit, which is usually
200. If the server is actually big enough to merit
"effective_cache_size = 196GB" then you should probably bump this
setting to something like 2000.
> autovacuum_work_mem |-1
That is going to default to maintenance_work_mem. On a big
machine, you probably want that set to somewhere between 1GB and
2GB.
Some other tuning to the cost parameters might be helpful, but
there's not enough data on the thread to know what else to suggest.
If you hit some other slow query, you might want to report it in
the manner suggested here:
https://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Slow query after 9.3 to 9.6 migration
In-Reply-To: <CACjxUsPd0nsLh37tpP+9dU92EYUAujSuhrEvncej_=EsSKiQ9A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox