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 testdb postgresqltuner.pl version 1.0.0 Connecting 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 MB Argument "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 MB Argument "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 indexes Argument "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工具”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联-成都网站建设公司网站，小编将为大家输出更多高质量的实用文章！
文章标题：PostgreSQL中怎么安装和使用postgresqltuner工具-创新互联
文章源于：http://hbruida.cn/article/spphe.html
