Performance analysis at full power Julien Rouhaud pgconf.eu 2019 - - PowerPoint PPT Presentation

1/40

Performance analysis at full power

Julien Rouhaud

pgconf.eu 2019

• Oct. 16th 2019

Julien Rouhaud Performance analysis at full power

2/40

Who am I

Julien Rouhaud, from France

Working with PostgreSQL since 2008 DBA, consulting, developer

Author of HypoPG and other tools Some contributions to PostgreSQL

Julien Rouhaud Performance analysis at full power

3/40

Why this talk

My own experience

Based on my experience as database administrator

(subset of) Existing (or new) facilities I find most useful Open source For performance analysis !

There are many other facilities availables and other approaches

Sometime complementary (some info are only available in the logs, pgBadger is so useful)

Julien Rouhaud Performance analysis at full power

4/40

Why this talk

PostgreSQL’s moving fast

PostgreSQL changes

New features for better performance New bottlenecks New performance counters

Lot of metrics available on the OS side

top, perf, iostat...

PostgreSQL’s core statistics

some metrics available Cumulated statistics No underlying system metrics but extensible, there are tools to help !

Julien Rouhaud Performance analysis at full power

5/40

PostgreSQL statistics

How it works

Some in core, some in contrib, some in external extensions Almost all of them are cumulated counters over time Usually store information in shared memory Accessible with views or Set Returning Functions

Julien Rouhaud Performance analysis at full power

6/40

PostgreSQL statistics

List of in-core views

Julien Rouhaud Performance analysis at full power

7/40

PostgreSQL statistics

The limits

No historisation done by PostgreSQL You know the cumulated counters since the last reset Are those counters always increasing the same way ? What happened yesterday between 9AM and 2PM ?

Julien Rouhaud Performance analysis at full power

8/40

PostgreSQL statistics

Raw data

Julien Rouhaud Performance analysis at full power

9/40

PostgreSQL statistics

The solution

Get all metrics every few minutes, and store it somewhere You can do that manually with cron or custom script Or use PoWA

Extensible infrastructure to historize multiple data sources

• ptional background worker for a self contained

solution

• ptional daemon for more complex setup

Custom UI to vizualize and analyze metrics

Julien Rouhaud Performance analysis at full power

10/40

PostgreSQL statistics

Time visualisation

Julien Rouhaud Performance analysis at full power

11/40

pg_stat_statements

Must have extensions

Official contrib Global view of what’s happening on your server Query normalization, based on object identifiers Cumulate many statistics per queryid, userid, dbid

cumulated runtime and number of execution min, max, mean time shared/local buffers access (hit, read, dirtied, written) temps files IO timing (depending on track_io_timing)

Julien Rouhaud Performance analysis at full power

12/40

pg_stat_statements

What can we learn ?

Most frequent queries Slowest queries Queries generating most amount of temporary files Per-query hit-ratio Queries requiring more work_mem . . .

Julien Rouhaud Performance analysis at full power

13/40

pg_stat_statements

Query example

SELECT round(total_time::numeric/calls, 2) AS avg_time, mean_time, rows/(calls) AS avg_rows, shared_blks_hit * 100 / (shared_blks_hit+shared_blks_read) AS hit_ratio, query FROM pg_stat_statements s JOIN pg_database d ON d.oid = s.dbid WHERE datname = 'bench' AND (shared_blks_hit+shared_blks_read) > 0 ORDER BY total_time / calls DESC;

Julien Rouhaud Performance analysis at full power

14/40

pg_stat_statements

Over time

Query runtime per second (kind of SQL load) global, per-database or per-query

Julien Rouhaud Performance analysis at full power

15/40

pg_stat_statements

On time interval

And general consumption over a specific interval Drill-down approach to investigate performance issues

Julien Rouhaud Performance analysis at full power

16/40

pg_stat_statements

Identify slow queries

Julien Rouhaud Performance analysis at full power

17/40

pg_stat_kcache

Kernel metrics

github.com/powa-team/pg_stat_kcache Wrapper around get_rusage(2) Gives access to kernel metrics, aggregated per (queryid, dbid, userid) :

Physical disk reads and writes User and system CPU Context switches, page faults

Julien Rouhaud Performance analysis at full power

18/40

pg_stat_kcache

What can we learn ?

"Real" hit-ratio : shared_buffers vs OS cache vs Disk access CPU intensive queries T

• o high number of active queries

Julien Rouhaud Performance analysis at full power

19/40

pg_stat_kcache

Examples - per database

Julien Rouhaud Performance analysis at full power

20/40

pg_stat_kcache

Examples - per query

Julien Rouhaud Performance analysis at full power

21/40

pg_wait_sampling

Wait events monitoring

github.com/postgrespro/pg_wait_sampling/ Developed by Postgres Professional Efficient high frequency sampling of wait events Default period is 10ms, customisable Aggregated per queryid, dbid For 9.6+ only, when Wait Events were introduced

Julien Rouhaud Performance analysis at full power

22/40

pg_wait_sampling

What can we learn ?

Low level bottlenecks that can’t be seen at SQL level

Costly parts of a query execution Lightweight locks contention (Buffer mapping, WAL write lock. . .) IPC, IO and other events

Julien Rouhaud Performance analysis at full power

23/40

pg_wait_sampling

Examples

Per database :

Julien Rouhaud Performance analysis at full power

24/40

pg_wait_sampling

Examples

Per query :

Julien Rouhaud Performance analysis at full power

25/40

pg_qualstats

Statistics on predicates

github.com/powa-team/pg_qualstats Gather statistics on predicates (WHERE / JOIN clauses)

Number of underlying query executions Number of predicate’s operator execution Selectivity Sequential scan or index scan

Per queryid, userid, dbid Sampled to avoid overhead (default is 1 / max_connections)

Julien Rouhaud Performance analysis at full power

26/40

pg_qualstats

What can we learn ?

Detect missing indexes Differentiate most executed, most/least filtering, most frequent constants Detect possible partial indexes If sampled over time, avoid suggesting indexes for night batches

Julien Rouhaud Performance analysis at full power

27/40

pg_qualstats

Constant distribution

Julien Rouhaud Performance analysis at full power

28/40

pg_qualstats

Index suggestion

Julien Rouhaud Performance analysis at full power

29/40

HypoPG

Hypothetical indexes

github.com/HypoPG/hypopg Hypothetical indexes, aka. "What if this index existed ?" Create "fake" indexes instantly, without any resource consumption EXPLAIN can use such index

Julien Rouhaud Performance analysis at full power

30/40

pg_qualstats + HypoPG

Index validation

Julien Rouhaud Performance analysis at full power

31/40

pg_qualstats + HypoPG

Global index suggestion

Get all executed queries on the given time interval Get all interesting predicates (seq scan, filtering at least 30%. . . Get information about indexing capabilities (operators, datatype, opclass. . .) Analyze and suggest indexes to optimize all queries with the least amount of indexes Check with HypoPG that indexes would be used

Julien Rouhaud Performance analysis at full power

32/40

pg_qualstats + HypoPG

Global index suggestion

Julien Rouhaud Performance analysis at full power

33/40

pg_track_settings

History of configuration changes

github.com/rjuju/pg_track_settings/ SQL only extension detect and store the settings changed since last call both global and object specific (eg. ALTER DATABASE SET) and also postgres restart

Julien Rouhaud Performance analysis at full power

34/40

pg_track_settings

Example

What changed since yesterday ?

# SELECT * FROM pg_track_settings_diff(now() - interval '1 day', now()); name | from_setting | from_exists | to_setting | to_exists

• --------------------+--------------|-------------|------------|----------

checkpoint_segments | 30 | t | 35 | t (1 row)

Julien Rouhaud Performance analysis at full power

35/40

pg_track_settings

Example

What’s the full history for a specific setting ?

# SELECT * FROM pg_track_settings_log('checkpoint_segments'); ts | name | setting_exists | setting

• --------------------------+---------------------+----------------+---------

2015-01-25 01:01:42.58+01 | checkpoint_segments | t | 35 2015-01-25 01:00:37.44+01 | checkpoint_segments | t | 30 (2 rows)

Julien Rouhaud Performance analysis at full power

36/40

pg_track_settings

Example

What was the configuration like at a specific timestamp ?

# SELECT * FROM pg_track_settings('2015-01-25 01:01:00'); name | setting

• -----------------------------+---------

[...] checkpoint_completion_target | 0.9 checkpoint_segments | 30 checkpoint_timeout | 300 [...]

Julien Rouhaud Performance analysis at full power

37/40

pg_track_settings

Graph annotation

Available in PoWA, filtered by database if applicable

Julien Rouhaud Performance analysis at full power

38/40

Demo

Demo dev-powa.anayrat.info

(not credential required, just click connect)

Julien Rouhaud Performance analysis at full power

39/40

Conclusion

A lot of tool are there to help Can be used alone or together Or even integrated in your own solution

Julien Rouhaud Performance analysis at full power

40/40

Questions ?

rjuju.github.io @rjuju123 powateam (pg12 compatible)

Julien Rouhaud Performance analysis at full power