Upgrade to Pro — share decks privately, control downloads, hide ads and more …

pg_profile - historical workload reporting tool...

pg_profile - historical workload reporting tool for PostgreSQL

This presentation was given at the "How to do database audits for PostgreSQL?" event by Postgres Professional on May 11, 2021 by Andrey Zubkov, Senior DBA.

Postgres Professional

May 11, 2021
Tweet

More Decks by Postgres Professional

Other Decks in Programming

Transcript

  1. Problem • Which workload causes the most resource consumption in

    a database? • What is the cause of that performance hit since last week when system performed well?
  2. Approaching the problem For a Postgres database we can do

    the following: • Setup detailed logging with further log analysis • Collect performance statistics and track changes This talk is about a tool, implementing the second approach
  3. Statistics approach pros and cons + Track all statements (even

    very short) + Track database object statistics + Avoid huge log-file writing and analysis - No parameter values - No plans - Failed statements are invisible
  4. pg_profile concepts • Pure pl/pgsql, i.e. no binaries/libraries, services, etc.

    • Sampling. 1-2 samples per hour (no much overhead) • Build a report between any two samples • Build a differential report on two intervals All you need is postgres!
  5. pg_profile sample contents pg_stat_database pg_stat_tablespaces pg_stat_all_tables pg_stat_all_indexes pg_statio_all_tables pg_settings pg_stat_bgwriter

    pg_stat_archiver pg_statio_all_indexes pg_stat_user_functions pg_profile (pgpro_pwr) sample pg_stat_statements pg_stat_kcache pgpro_stats
  6. Report contents Server statistics Database statistics Statement statistics by database

    Cluster statistics Tablespace statistics SQL Query statistics Top SQL by elapsed time Top SQL by planning time Top SQL by execution time Top SQL by executions Top SQL by I/O wait time Top SQL by shared blocks fetched Top SQL by shared blocks read Top SQL by shared blocks dirtied Top SQL by shared blocks written Top SQL by WAL size Top SQL by temp usage rusage statistics Top SQL by system and user time Top SQL by reads/writes done by filesystem layer Complete list of SQL texts Schema object statistics Top tables by estimated sequentially scanned volume Top tables by blocks fetched Top tables by blocks read Top DML tables Top tables by updated/deleted tuples Top growing tables Top indexes by blocks fetched Top indexes by blocks read Top growing indexes Unused indexes User function statistics Top functions by total time Top functions by executions Vacuum-related statistics Top tables by vacuum operations Top tables by analyze operations Top indexes by estimated vacuum I/O load Top tables by dead tuples ratio Top tables by modified tuples ratio Cluster settings during the report interval
  7. Issue time report Report on 11:00-13:00 should do the trick

    $ psql -Aqtc \ "SELECT profile.get_report(130,134)" \ -o report_issue.html $
  8. How can pg_profile help us? • Issue report findings: –

    Leading three statements consumed 65% of time and 91% of blocks – Leading two tables with indexes consumed 93% of blocks
  9. How can pg_profile help us? • Issue report findings: –

    Leading three statements consumed 65% of time and 91% of blocks – Leading two tables with indexes consumed 93% of blocks • Let’s build a differential report
  10. Differential report • Built on two time intervals • Statistics

    of the same objects located one next to other • The first interval values colored red, and the second interval values colored blue
  11. Differential report Differential report on 11:00-13:00 today with 11:00-13:00 yesterday

    $ psql -Aqtc \ "SELECT profile.get_report(82,86,130,134)" \ -o diffreport_issue.html $
  12. How can pg_profile help us? • Issue report findings: –

    Leading three statements consumed 65% of time and 91% of blocks – Leading two tables with indexes consumed 93% of blocks • Differential report findings: – Leading statements executed 4 orders of magnitude longer – The slowest query returned 4 orders of magnitude more rows – There are 4 orders of magnitude more blocks got from top table
  13. Investigation results • We’ve got their names • We’ve got

    their IDs and texts • We’ve got absolute numbers What is next? • Application optimization • Query optimization • Compare results
  14. Thank you! Andrey Zubkov, Postgres Professional, 2021 [email protected] pg_profile https://github.com/zubkov-andrei/pg_profile

    Postgres Pro Standard 13 https://bit.ly/3nOmUL8 Postgres Pro Enterprise 13 https://bit.ly/2QOo7q2