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 <schnj...@amazon.com> 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 >
