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?
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