Re: [TIPS] Tuning PostgreSQL 9.2 - Mailing list pgsql-admin

From drum.lucas@gmail.com
Subject Re: [TIPS] Tuning PostgreSQL 9.2
Date
Msg-id CAE_gQfWMVFRrWRrU9G_2JyVU9_zZvWoAvY4Yb1vMzOc-f3sayQ@mail.gmail.com
Whole thread Raw
In response to Re: [TIPS] Tuning PostgreSQL 9.2  (Scott Marlowe <scott.marlowe@gmail.com>)
List pgsql-admin
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.
Inline images 1

Disk utilization during that period
Inline images 2

PostgreSQL buffer cache during that period:
Inline images 3

Postgres checkpoints:
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?
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 

Attachment

pgsql-admin by date:

Previous
From: "drum.lucas@gmail.com"
Date:
Subject: Re: [TIPS] Tuning PostgreSQL 9.2
Next
From: Scott Marlowe
Date:
Subject: Re: [TIPS] Tuning PostgreSQL 9.2