0

Aurora Postgres 11.8 I have to segregate pg_stat_statements on an hourly basis on Reader instance. On Writer instance it's simple, create a table and backup pg_stat_statements on an hourly basis and then clean up pg_stat_statements using pg_stat_statements_reset().

Since I can't create a table on Reader so need experts remarks for that, One thought is to create FDW table on Writer and copy data on an hourly basis but how to sync pg_stat_statements_reset() along with that on Reader? not look like a robust solution to have two separate cronjobs for copy and pg_stat_statements_reset().

Being from Oracle background I am tunned up for hourly ADDM reports. pg_stat_statements is found to be a somewhat satisfactory alternate to ADDM. I want to divide pg_stat_statements on an hourly basis to get a more in-depth analysis since a single summary of all busy and off-hours is less meaningful.

Thanks

user3706888
  • 159
  • 2
  • 11

1 Answers1

0

Thete are many possible soutions, choose one based on simplicity and how well it suits your setup.

My idea would be not to run pg_stat_statements_reset() at all, but calculate the difference between subsequent snapshots of the data. That saves you the trouble of synchronizing the two activities.

For the snapshot, my idea would be a (possibly NFS-mounted) directory on the standby into which you write a hourly snapshot with COPY, scheduled by a cron job.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Laurenz: Thanks for the reply. I want to stick to FDW for the copy as it's simple for me and I can manage multiple reader stats in one writer this way. As you said 'not to run pg_stat_statements_reset()' its good to calculate the difference for incremental columns like calls, total_time, rows etc. but I fail to manger diff for group columns like min_time, max_time, mean_time etc. I want them on an hourly basis and can't get hourly max_time unless pg_stat_statements_reset(). Can you tell me an easy way out of it? or any way to do pg_stat_statements_reset() from writer to reader instances? – user3706888 Nov 18 '20 at 07:07
  • 1
    `mean_time` is easy - it can be calculated from `calls` and `total_time`, which are additive. You are right about the other columns; perhaps you can do without them or you live with the all-time-maximum values. `pg_stat_statements_reset()` doesn't modify the database, creates no WAL and is not replicated. It has to be run on the standby, and I know no way out from this. – Laurenz Albe Nov 18 '20 at 08:32