Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to install and use postgresqltuner tools in PostgreSQL". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. installation

Install under the REHL series:

# this tool is developed based on Perl language, first install the Perl-related development kit # yum-y install perl-DBD-Pg# acquisition kit # cd / tmp#wget-O postgresqltuner.pl https://postgresqltuner.pl#chmod + x postgresqltuner.pl II, basic use

Execute on the database host:

[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 Currently used 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 11beta 2-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.00B) = 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 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. "how to install and use postgresqltuner tools in PostgreSQL" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.