PostgreSQL中怎么安装和使用postgresqltuner工具

本篇内容介绍了"PostgreSQL中怎么安装和使用postgresqltuner工具"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

一、安装

在REHL系列下安装：

#该工具基于Perl语言开发,首先安装Perl相关的开发包#yum -y install perl-DBD-Pg#获取工具包#cd /tmp#wget -O postgresqltuner.pl https://postgresqltuner.pl#chmod +x postgresqltuner.pl

二、基本使用

在数据库主机上执行:

[xdb@localhost bin]$ /tmp/postgresqltuner.pl --host=localhost --user xdb --database testdbpostgresqltuner.pl version 1.0.0Connecting to localhost:5432 database testdb with user xdb...Argument "11beta2" isn't numeric in numeric gt (>) at /tmp/postgresqltuner.pl line 818.[OK]      User used for report have super rights=====  OS information  =====[INFO]    OS: linux Version: 3.10.0-514.16.1.el7.x86_64 Arch: x86_64-linux-thread-multi[INFO]    OS total memory: 732.52 MB[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]    Currently used I/O scheduler(s) : deadline=====  General instance informations  =====-----  Version  -----Argument "11beta2" isn't numeric in numeric gt (>) at /tmp/postgresqltuner.pl line 818.[OK]      You are using last 11beta2-----  Uptime  -----[INFO]    Service uptime :  09m 53s[WARN]    Uptime is less than 1 day. postgresqltuner.pl result may not be accurate-----  Databases  -----[INFO]    Database count (except templates): 2[INFO]    Database list (except templates): postgres testdb-----  Extensions  -----[INFO]    Number of activated extensions : 1[INFO]    Activated extensions : plpgsql[WARN]    Extensions pg_stat_statements is disabled-----  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 :  08m 14s[WARN]    Average connection age is less than 10 minutes. Use a connection pooler to limit new connection/seconds-----  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 MBArgument "11beta2" isn't numeric in numeric gt (>) at /tmp/postgresqltuner.pl line 818.[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 : 33.19 MB[WARN]    shared_buffer is too big for the total databases size, memory is lost[INFO]    PostgreSQL maximum memory usage: 125.59% of system RAM[BAD]     Max possible memory usage for PostgreSQL is more than system total RAM. Add more RAM or reduce PostgreSQL memory[INFO]    max memory+effective_cache_size is 684.76% of total RAM[WARN]    the sum of max_memory and effective_cache_size is too high, the planer can find bad plans if system cache is smaller than expected-----  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  -----Argument "11beta2" isn't numeric in numeric gt (>) at /tmp/postgresqltuner.pl line 818.[OK]      Currently no two phase commit transactions-----  Autovacuum  -----[OK]      autovacuum is activated.[INFO]    autovacuum_max_workers: 3-----  Checkpoint  -----[WARN]    checkpoint_completion_target(0.5) is low-----  Disk access  -----[OK]      fsync is on[OK]      synchronize_seqscans is on-----  WAL  -----Argument "11beta2" isn't numeric in numeric gt (>) at /tmp/postgresqltuner.pl line 818.-----  Planner  -----[OK]      costs settings are defaults[BAD]     some plan features are disabled : enable_partitionwise_aggregate,enable_partitionwise_join=====  Database information for database testdb  =====-----  Database size  -----[INFO]    Database testdb total size : 11.44 MBArgument "11beta2" isn't numeric in numeric gt (>) at /tmp/postgresqltuner.pl line 818.[INFO]    Database testdb tables size : 8.38 MB (73.22%)[INFO]    Database testdb indexes size : 3.06 MB (26.78%)-----  Tablespace location  -----Argument "11beta2" isn't numeric in numeric gt (>) at /tmp/postgresqltuner.pl line 818.[OK]      No tablespace in PGDATA-----  Shared buffer hit rate  -----[INFO]    shared_buffer_heap_hit_rate: 99.03%[INFO]    shared_buffer_toast_hit_rate: 0.00%[INFO]    shared_buffer_tidx_hit_rate: 28.57%[INFO]    shared_buffer_idx_hit_rate: 98.43%[OK]      Shared buffer idx hit rate is very good-----  Indexes  -----[OK]      No invalid indexesArgument "11beta2" isn't numeric in numeric gt (>) at /tmp/postgresqltuner.pl line 818.[OK]      No unused indexes-----  Procedures  -----[OK]      No procedures with default costs=====  Configuration advices  =====-----  checkpoint  -----[MEDIUM] Your checkpoint completion target is too low. Put something nearest from 0.8/0.9 to balance your writes better during the checkpoint interval-----  extension  -----[LOW] Enable pg_stat_statements to collect statistics on all queries (not only queries longer than log_min_duration_statement in logs)-----  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.

"PostgreSQL中怎么安装和使用postgresqltuner工具"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！