Thanks Jeremy,
Plan is on the cards to upgrade to 10 this or next week, and then later on towards v12.
hot_standby_feedback is enabled, yes.
So the process list only shows high cpu usage for the postgres process itself, and all other ones are < 1.5%.
it's a bit baffling as I don't have anything heavy running on the master and all the heavy reads have been directed to the standby.
As soon as a large query hits the standby the load goes up in the master, but there's no correlation with IO memory or anything on the master, its just cpu load.
- azul
On Wed, 3 Jun 2020 at 14:59, Jeremy Schneider <schnjere@xxxxxxxxxx> wrote:
On 6/3/20 04:04, Azul wrote:
> so I have a RDS 9.5 master 16vcpu/64GB RAM/5000 IOPS which is RDS
> replicating to a replica with the same specs.
If you're not already making plans to upgrade this, many people would
strongly urge you start now. The final release for 9.5 is less than a
year away, after which you will no longer be able to get security
updates or bug fixes.
https://www.postgresql.org/support/versioning/
Please consider moving in the direction of v12.
Just for posterity in case someone googles this email thread in the
future, an RDS "read replica" is a "hot standby" in PostgreSQL terminology.
https://www.postgresql.org/docs/9.5/high-availability.html
> I am running the above on the replica to avoid causing an extra load on
> the master, that query takes a long time (lets ignore the fact that it
> badly needs an analyse to finish), roughly an hour or so.
Just curious, did you happen to enable the hot_standby_feedback
parameter? Looks to me like it's off by default in 9.4.
> Now what is baffling me is the CPU load on the master goes up steadily
> all the way to 100% while this select count is running on the slave.
> Worth mentioning that CPU on the slave increases by about 10% of so.
Which PIDs/processes are using the CPU? (Vacuum? App connections running
queries?) For people who manage PostgreSQL themselves, they'd use normal
unix utilities like top, ps, etc. On RDS you want to enabled "Enhanced
Monitoring" and check the "process list". (In the web console, as of
last time I checked: go to the database, choose the "Monitoring" tab,
click the dropdown box at the top right that says "Monitoring" and
choose "OS Process List".)
If you see that it's user sessions, then you can connect with psql at
the same time as the high activity and query the contents of
pg_stat_activity to get a little more information about what the
particular process is doing.
--
Jeremy Schneider
Database Engineer
Amazon Web Services
