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
