作者
digoal
日期
2020-12-17
标签
PostgreSQL , pg_flame , explain , 火山图
背景
https://github.com/mgartner/pg_flame
demo
https://mgartner.github.io/pg_flame/flamegraph.html
pg_flame 
A flamegraph generator for Postgres EXPLAIN ANALYZE output.
Demo
Try the demo here.
Installation
Homebrew
You can install via Homebrew with the follow command:
$ brew install mgartner/tap/pg_flame
Download pre-compiled binary
Download one of the compiled binaries in the releases
tab. Once downloaded, move
pg_flame into your $PATH.
Docker
Alternatively, if you'd like to use Docker to build the program, you can.
$ docker pull mgartner/pg_flame
Build from source
If you'd like to build a binary from the source code, run the following
commands. Note that compiling requires Go version 1.13+.
$ git clone https://github.com/mgartner/pg_flame.git
$ cd pg_flame
$ go build
A pg_flame binary will be created that you can place in your $PATH.
Usage
The pg_flame program reads a JSON query plan from standard input and writes
the flamegraph HTML to standard ouput. Therefore you can pipe and direct input
and output however you desire.
Example: One-step
bash
$ psql dbname -qAtc 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT id FROM users' \
| pg_flame \
> flamegraph.html \
&& open flamegraph.html
Example: Multi-step with SQL file
Create a SQL file with the EXPLAIN ANALYZE query.
sql
-- query.sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id
FROM users
Then run the query and save the JSON to a file.
bash
$ psql dbname -qAtf query.sql > plan.json
Finally, generate the flamegraph HTML.
$ cat plan.json | pg_flame > flamegraph.html
Example: Docker
If you've followed the Docker installation steps above, you can pipe query plan
JSON to a container and save the output HTML.
$ psql dbname -qAtc 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT id FROM users' \
| docker run -i pg_flame \
> flamegraph.html
Background
Flamegraphs were invented by
Brendan Gregg to visualize CPU consumption per code-path of profiled software.
They are useful visualization tools in many types of performance
investigations. Flamegraphs have been used to visualize Oracle database
query
plans
and query
executions
, proving useful for debugging slow database queries.
Pg_flame is in extension of that work for Postgres query plans. It generates a
visual hierarchy of query plans. This visualization identifies the relative
time of each part of a query plan.
This tool relies on the
spiermar/d3-flame-graph plugin to
generate the flamegraph.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
