溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“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工具”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
