Postgresql 调优工具postgresqltuner

cd opt/postgresqltuner-1.0.1
# ./postgresqltuner.pl --host localhost --database postgres --user postgres
postgresqltuner.pl version 1.0.1
Checking if OS commands is available on localhost...
[OK]      OS command OK
Connecting to localhost:5432 database postgres with user postgres...
[OK]      User used for report have super rights
=====  OS information  =====
[INFO]    OS: linux Version: 3.10.0-957.1.3.el7.x86_64 Arch: x86_64-linux-thread-multi
[INFO]    OS total memory: 11.73 GB
[BAD]     Memory overcommitment is allowed on the system. This can lead to OOM Killer killing some PostgreSQL process, which will cause a PostgreSQL server restart (crash recovery)
[INFO]    sysctl vm.overcommit_ratio=50
[BAD]     vm.overcommit_ratio is too small, you will not be able to use more than 50*RAM+SWAP for applications
[INFO]    Running in kvm hypervisor
[INFO]    Currently used I/O scheduler(s) : deadline
[WARN]    On virtual machines, sys/block/DISK/queue/rotational is not accurate. Use the --ssd arg if the VM in running on a SSD storage
=====  General instance informations  =====
-----  Version  -----
[OK]      You are using last 15.1
-----  Uptime  -----
[INFO]    Service uptime :  26m 32s
[WARN]    Uptime is less than 1 day. postgresqltuner.pl result may not be accurate
-----  Databases  -----
[INFO]    Database count (except templates): 1
[INFO]    Database list (except templates): postgres
-----  Extensions  -----
[INFO]    Number of activated extensions : 3
[INFO]    Activated extensions : plpgsql pageinspect pg_freespacemap
[WARN]    Extensions pg_stat_statements is disabled in database postgres
-----  Users  -----
[OK]      No user account will expire in less than 7 days
[OK]      No user with password=username
[OK]      Password encryption is enabled
-----  Connection information  -----
[INFO]    max_connections: 100
[INFO]    current used connections: 6 (6.00%)
[INFO]    3 are reserved for super user (3.00%)
[INFO]    Average connection age :  22m 07s
-----  Memory usage  -----
[INFO]    configured work_mem: 4.00 MB
[INFO]    Using an average ratio of work_mem buffers by connection of 150% (use --wmp to change it)
[INFO]    total work_mem (per connection): 6.00 MB
[INFO]    shared_buffers: 128.00 MB
[INFO]    Track activity reserved size : 0.00 B
[WARN]    maintenance_work_mem is less or equal default value. Increase it to reduce maintenance tasks time
[INFO]    Max memory usage :
                  shared_buffers (128.00 MB)
                + max_connections * work_mem * average_work_mem_buffers_per_connection (100 * 4.00 MB * 150  100 = 600.00 MB)
                + autovacuum_max_workers * maintenance_work_mem (3 * 64.00 MB = 192.00 MB)
                + track activity size (0.00 B)
                = 920.00 MB
[INFO]    effective_cache_size: 4.00 GB
[INFO]    Size of all databases : 29.68 MB
[WARN]    shared_buffer is too big for the total databases size, memory is lost
[INFO]    PostgreSQL maximum memory usage: 7.66% of system RAM
[WARN]    Max possible memory usage for PostgreSQL is less than 60% of system total RAM. On a dedicated host you can increase PostgreSQL buffers to optimize performances.
[INFO]    max memory+effective_cache_size is 41.75% of total RAM
-----  Logs  -----
[OK]      log_hostname is off : no reverse DNS lookup latency
[WARN]    log of long queries is desactivated. It will be more difficult to optimize query performances
[OK]      log_statement=none
-----  Two phase commit  -----
[OK]      Currently no two phase commit transactions
-----  Autovacuum  -----
[OK]      autovacuum is activated.
[INFO]    autovacuum_max_workers: 3
-----  Checkpoint  -----
[OK]      checkpoint_completion_target(0.9) OK
-----  Disk access  -----
[OK]      fsync is on
[OK]      synchronize_seqscans is on
-----  WAL  -----
-----  Planner  -----
[OK]      costs settings are defaults
[BAD]     some plan features are disabled : enable_partitionwise_aggregate,enable_partitionwise_join
=====  Database information for database postgres  =====
-----  Database size  -----
[INFO]    Database postgres total size : 15.41 MB
[INFO]    Database postgres tables size : 8.61 MB (55.88%)
[INFO]    Database postgres indexes size : 6.80 MB (44.12%)
-----  Tablespace location  -----
[OK]      No tablespace in PGDATA
-----  Shared buffer hit rate  -----
[INFO]    shared_buffer_heap_hit_rate: 99.97%
[INFO]    shared_buffer_toast_hit_rate: 86.88%
[INFO]    shared_buffer_tidx_hit_rate: 95.06%
[INFO]    shared_buffer_idx_hit_rate: 99.99%
[OK]      Shared buffer idx hit rate is very good
-----  Indexes  -----
[OK]      No invalid indexes
[WARN]    Some indexes are unused since last statistics: t.t_info_idx
-----  Procedures  -----
[WARN]    Some user procedures does not have custom cost and rows settings : public.tuple_data_split public.tuple_data_split public.bt_metap public.bt_page_stats public.page_header public.brin_page_type public.brin_metapage_info public.fsm_page_contents public.gin_metapage_info public.gin_page_opaque_info public.hash_page_type public.hash_page_stats public.hash_metapage_info public.heap_tuple_infomask_flags public.gist_page_opaque_info public.get_raw_page public.get_raw_page public.page_checksum public.pg_freespace


=====  Configuration advices  =====
-----  extension  -----
[LOW] Enable pg_stat_statements in database postgres to collect statistics on all queries (not only queries longer than log_min_duration_statement in logs)
-----  index  -----
[MEDIUM] You have unused indexes in the database since last statistics. Please remove them if they are never use
-----  proc  -----
[LOW] You have custom procedures with default cost and rows setting. Please reconfigure them with specific values to help the planer
-----  report  -----
[URGENT] Use the --ssd arg if the VM in running on a SSD storage
-----  sysctl  -----
[URGENT] set vm.overcommit_memory=2 in /etc/sysctl.conf and run sysctl -p to reload it. This will disable memory overcommitment and avoid postgresql killed by OOM killer.

如果语句运行至少指定的时间量，将导致记录每一个这种完成的语句的持续时间。
如果指定值时没有单位，则以毫秒为单位。将这个参数设置为零将打印所有语句的执行时间。
设置为 -1 （默认值）将停止记录语句持续时间。例如，如果你设置它为250ms，那么所有运行 250ms 或更久的 SQL 语句将被记录。
启用这个参数可以有助于追踪应用中未优化的查询。只有超级用户可以改变这个设置。


对于使用扩展查询协议的客户端，解析、绑定和执行步骤的持续时间将被独立记录。


当把这个选项和log_statement一起使用时，已经被log_statement记录的语句文本不会在持续时间日志消息中重复。
如果你没有使用syslog，我们推荐你使用log_line_prefix记录 PID 或会话 ID，这样你可以使用进程 ID 或会话 ID 把语句消息链接到后来的持续时间消息。

指定能同时运行的自动清理进程（除了自动清理启动器之外）的最大数量。默认值为3。
该参数只能在服务器启动时设置。

指定检查点完成的目标，作为检查点之间总时间的一部分。
默认是 0.5。 这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

max_wal_size：默认最大1G
min_wal_size：默认最小80M

enable_partitionwise_aggregate：
/** 允许或者禁止查询规划器使用面向分区的分组或聚集，
这使得在分区表上的分组或聚集可以在每个分区上分别执行。
如果GROUP BY子句不包括分区键，只有部分聚集能够以基于每个分区的方式执行，并且finalization必须最后执行。
由于面向分区的分组或聚集在规划期间会使用可观的CPU时间和内存，所以默认值为off。
**/


enable_partitionwise_join：
/**
允许或者禁止查询规划器使用面向分区的连接，
这使得分区表之间的连接以连接匹配的分区的方式来执行。
面向分区的连接当前只适用于连接条件包括所有分区键的情况，连接条件必须是相同的数据类型并且子分区集合要完全匹配。
由于面向分区的连接规划在规划期间会使用可观的CPU时间和内存，所以默认值为off。
**/