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
