Thread: When should I start and setup a slave replication?
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!
Tomer Praizler wrote: > 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
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?
On Sun, 4 Feb 2018 at 23:57 Laurenz Albe <laurenz.albe@cybertec.at> wrote:
Tomer Praizler wrote:
> 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