0

I recently built a database monitoring plugin for postgresql that queries the pg_stat and pg_statio tables in postgresql. I want to understand/test how my plugin might affect my database but fear that if i just turned my plugin on my database, my database may crash and burn... I was hoping that someone could suggest some testing methods or answer some of my questions/concerns below:

1) I was wondering if these monitoring queries would cause my other non-monitoring queries (which I assume also need to write to the pg_statio tables to update these statistics) to lock up.

2) Is there a way for me to capture database traffic on a postgresql database table so I can maybe replay this traffic on a copy of the database?

Dave Lee
  • 205
  • 5
  • 14

1 Answers1

1

will db monitoring with pg_stat/pg_statio slow down my other queries?

No. Not significantly, anyway.

1) I was wondering if these monitoring queries would cause my other non-monitoring queries (which I assume also need to write to the pg_statio tables to update these statistics) to lock up.

No. In PostgreSQL reads do not block writes.

2) Is there a way for me to capture database traffic on a postgresql database table so I can maybe replay this traffic on a copy of the database?

Not easily, at the present time.

You can record statements and parameters in the logs, along with a log_line_prefix that lets you reassemble them into transactions and sessions. It'll be painful to parse the logs for this though. Also, IIRC super-long statements can be truncated.

You can in PostgreSQL 9.4 extract the rows changed using logical decoding, but that doesn't tell you which statements changed them, and it doesn't let you reproduce the load. Replaying the change stream is a very different load to creating it in the first place.

Tools like pg_stat_statements can help a bit, but won't give you a verbatim stream of changes.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778