Re: [TIPS] Tuning PostgreSQL 9.2

Hi there!
So the server is: HP DL380 G9 Dual Socket Octo Core Intel Xeon E5-2630v3
2.4GHz 128 GB RAM
It's a production server, so I can't "play" with it.

I'll post here some graphs about the SPIKE I had yesterday (2016-02-18) -
Note that the spike isn't so big, but usually they are.
[image: Inline images 1]

Disk utilization during that period
[image: Inline images 2]

PostgreSQL buffer cache during that period:
[image: Inline images 3]

Postgres checkpoints:
[image: Inline images 4]

>
> There are a lot of things you can do to improve performance, but we
> don't know your usage patterns or underlying IO subsystem. What does
> your IO sybsystem look like? How fast can you get something like
> pgbench to go on this machine?
>
> Might I ask where the idea for shared_buffers being 51GB came from?
> Generally speaking shared_buffers don't work well that big, except in
> some very specific circumstances maybe.
>

The shared_buffers idea, was made by the previous DBA.

>
> So when you say IO is 100% utilized, is that being used by sorts, the
> background writer, reads?
>
> How many active and idle connections do you typically have on this
> machine? If you have a lot of connections have you considered pooling?
>
[image: Inline images 5]

>
> What are max_connections, effective_io_concurrency, ramdom_page_cost,
> wal_writer_delay, commit_delay, commit_siblings, checkpoint_segments,
> temp_buffers, set to?
>

max_connections = 200

effective_io_concurrency = 0

wal_writer_delay = 200ms

commit_delay = 0

#####commit_siblings = 5

checkpoint_segments = 64

#####ramdom_page_cost (Couldn't find that parameter in postgresql.conf)

temp_buffers = 16MB

>
> Turn on things like log_temp_files, log_checkpoints.
>
They are already on:
log_temp_files = 4096
log_checkpoints = on

>
> Also got a slow query and an explain analyze output?
>
Sometimes slow queries are INSERT/UPDATEs ... so no explain analyze for
them