lightdb/postgresql性能分析之ltcenter

• 查看整体情况

ltcenter top -h 1.2.3.4 -U postgres production_db

 　　如果内存、activity这些信息缺失，则需要检查plperlu依赖已经安装，它会执行一些初始配置。

　　create extension plperlu;

　　pgcenter config -i -d postgres -U postgres

 参考：https://github.com/lesovsky/pgcenter/blob/master/doc/examples.md

https://github.com/lesovsky/pgcenter/blob/master/doc/pgcenter-top-readme.md

• profile某个特定backend（LightDB PSH提供类似功能，可以分析过去的运行情况）

ltcenter profile -h 127.0.0.1 -p 5432 -P 99846 postgres

ltcenter profile -h 127.0.0.1 -p 5432 -P 99846 -F 50（指定频率，1/50，表示每隔20秒采用） postgres

　　看起来不直接，ltcenter profile一个很重要的作用是如果某个SQL执行慢，执行计划各种优化后还没有足够的思路，就可以通过它分析SQL的主要瓶颈，例如：

------ ------------ -----------------------------
% time      seconds wait_event                     query: update pgbench_accounts set abalance = abalance + 100;
------ ------------ -----------------------------
72.15     30.205671 IO.DataFileRead
20.10      8.415921 Running
5.50       2.303926 LWLock.WALWriteLock
1.28       0.535915 IO.DataFileWrite
0.54       0.225117 IO.WALWrite
0.36       0.152407 IO.WALInitSync
0.03       0.011429 IO.WALInitWrite
0.03       0.011355 LWLock.WALBufMappingLock
------ ------------ -----------------------------
99.99     41.861741
这样就可以知道语句主要花时间在哪里。

• 采集数据库负载情况，保存到特定文件。和tshark和tcpdump一样。

[lightdb@lightdb1 ~]$ ltcenter record -f ltcenter_stats.tar -h 127.0.0.1 -p 5432 postgres
INFO: some statistics is not supported by the current version of Postgres and will be skipped
INFO: recording to ltcenter_stats.tar
^Cgot interrupt

[lightdb@lightdb1 ~]$ ll | grep ltcenter_stats.tar 
-rw-------  1 lightdb lightdb    17950208 Jan 16 16:51 ltcenter_stats.tar

　　收集完成后，可以通过pgcenter report -f /tmp/stats.tar --options进行分析，具体选项如下：

[lightdb@lightdb1 ~]$ ltcenter report --help
'ltcenter report' reads statistics from file and prints reports.

Usage:
 ltcenter report [OPTIONS]...

Options:
 -f, --file FILE        read stats from file (default: ltcenter.stat.tar)
 -s, --start TIMESTAMP        starting time of the report (format: [YYYY-MM-DD] HH:MM:SS)
 -e, --end TIMESTAMP        ending time of the report (format: [YYYY-MM-DD] HH:MM:SS)
 -o, --order COLNAME        order values by column
     --desc            use descendant order (default)
     --asc            use ascendant order
 -g, --grep COLNAME:PATTERN    filter values in specfied column (format: colname:filtertext)
 -l, --limit INT        print only limited number of rows per sample (default: unlimited)
 -t, --strlimit INT        maximum string size to print (default: 32, 0 disables)

Report options:
 -A, --activity            show pg_stat_activity statistics
 -R, --replication        show pg_stat_replication statistics

 -T, --tables            show pg_stat_user_tables statistics
 -I, --indexes            show pg_stat_user_indexes and pg_statio_user_indexes statistics
 -S, --sizes            show statistics about tables sizes
 -F, --functions        show pg_stat_user_functions statistics
 -W, --wal                show pg_stat_wal statistics
 -D, --databases SELECTOR    show pg_stat_database statistics, use additional selector to choose stats:
                'g' - general; 's' - sessions
 -X, --statements SELECTOR    show pg_stat_statements statistics, use additional selector to choose stats:
                'm' - timings; 'g' - general; 'i' - io; 't' - temp files io; 'l' - local files io; 'w' - wal statistics
 -P, --progress SELECTOR    show pg_stat_progress_* statistics, use additional selector to choose stats:
                'v' - vacuum; 'c' - cluster; 'i' - create index; 'a' - analyze; 'b' - basebackup; 'y' - copy

 -d, --describe            show statistics description, combined with one of the report options

General options:
 -?, --help        show this help and exit

　　除非分析很具体已知问题，针对性收集，否则不如PWR那么事半功倍。