When should I start and setup a slave replication?

[Tomer Praizler]

Hey!

I currently have one Postgres server running on an ec2 instance 32GB, and 8
cores. My DB is under a heavy load and sometimes queries might get super
slow.
I guess it is doing too much and has many access patterns which don't let
it optimize correctly.

I wonder if introducing a slave replication (making all reads going to the
slave, and writes to the master) will make my setup more performant.
Are there any good metrics to measure before making such decision? I really
want to be able to see the improvement in case I decide to go with setting
a replication.

Thanks!

[Laurenz Albe]

I currently have one Postgres server running on an ec2 instance 32GB, and 8 cores.
My DB is under a heavy load and sometimes queries might get super slow.
I guess it is doing too much and has many access patterns which don't let it optimize correctly.
I wonder if introducing a slave replication (making all reads going to the slave,
and writes to the master) will make my setup more performant.
Are there any good metrics to measure before making such decision?
I really want to be able to see the improvement in case I decide to go with setting a replication.

Before you decide on measures, you have to determine the cause of the problem.

- Is it I/O or CPU load?
- What queries are causing the biggest load?

Very often, a few CREATE INDEX can take care of the problem quite nicely.
Perhaps hiring a consultant can help.

For many I/O problems, increasing RAM is also a simple way to help.

Yours,
Laurenz Albe

[Tomer Praizler]

Thanks!
- So I see both CPU and I/O load.
- I have found many problematic queries and optimized them, but there are
some which cannot be optimized anymore.
- I have added all indexes needed to make my queries faster.
- I am trying to "squeeze the lemon" more before I use the help of an
advisor, this is why I asked about those interesting metrics.

I use postgres 10.1. Should I try and play with the workers configuration
to optimize for example aggregations queries?

I am trying to understand when should I use a hot standby configuration
assuming all (most) of my queries are optimized.

Any other ideas?

Post by Tomer Praizler
I currently have one Postgres server running on an ec2 instance 32GB,

and 8 cores.

Post by Tomer Praizler
My DB is under a heavy load and sometimes queries might get super slow.
I guess it is doing too much and has many access patterns which don't

let it optimize correctly.

Post by Tomer Praizler
I wonder if introducing a slave replication (making all reads going to

the slave,

Post by Tomer Praizler
and writes to the master) will make my setup more performant.
Are there any good metrics to measure before making such decision?
I really want to be able to see the improvement in case I decide to go

with setting a replication.
Before you decide on measures, you have to determine the cause of the problem.
- Is it I/O or CPU load?
- What queries are causing the biggest load?
Very often, a few CREATE INDEX can take care of the problem quite nicely.
Perhaps hiring a consultant can help.
For many I/O problems, increasing RAM is also a simple way to help.
Yours,
Laurenz Albe