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

This article focuses on "Postgresql performance-related operating system and database description", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "Postgresql performance-related operating system and database instructions" bar!

-- related to pg performance adjustment-- memory buffer cache data that operates directly on the disk will be cached in the buffer cachepage cache file system and cached to page cache-- cache is not automatically reclaimed. When the database needs memory, cache can be quickly recycled, if swap partitions are not used. Can indicate enough memory-free cache syncecho 1 > / proc/sys/vm/drop_caches--vmstat virtual memory, processes, Overall situation such as cpu-r how many processes are currently waiting in the queue-b how many processes are currently in an uninterruptible sleep state-size of swap partitions used by swpd-size of free free memory-size of buffer used by buff-size of page cache used by cache-size of disks swapped from disk to swap partition and swapped from swap partition-size of disk read and write to disk by bi/bo Processes interrupted per second per blocks/s-in-how many cpu processes per second are in and out of cs-iostat monitors disk input and output-mpstat cpu performance details-sar is saved for 28 days by default Directory / var/log/sa modify / etc/sysconfig/sysstat-- View a certain period of time sar-Q-f / var/log/sa/sa15-s 22:00:00-e 23:00:00sar-Q # Summary cpu status sar-b # Summary io status-linux I go O scheduling # # View the currently supported scheduling algorithm dmesg | grep-I scheduler--cfg absolute fair scheduling algorithm Default-noop elevator scheduling algorithm is suitable for ssd--deadline absolute guarantee algorithm-check the current disk sda io scheduling algorithm cat / sys/block/sda/queue/scheduler-- temporarily modify echo noop > / sys/block/sda/queue/scheduler## disk pre-read sector / sbin/blockdev-- getra / dev/sda # default 256 You can set 16384 or greater / sbin/blockdev-- setra 16384 / dev/sda# or echo 16384 / sys/block/sda/queue/read_ahead_kb## disable swapswapoff-a # # enable swapon-Amure-transparent large pages To turn off cat / sys/kernel/mm/transparent_hugepage/enabledecho never > / sys/kernel/mm/transparent_hugepage/enabled##numa guanbi numactl-hardware--ornumastat# editable / add numa=off at the end of the etc/grub.conf line to disable # # Database-Statistics pg_stat_database-- cache hit rate, if less than 1, try to adjust shared_buffersselect blks_hit::float/ (blks_read + blks_hit) as cache_hit_ratio from pg_stat_database where datname=current_database () -transaction submission rate, below 1. Check if deadlock or other timeout is too much select xact_commit::float/ (xact_commit + xact_rollback) as successful_xact_ratio from pg_stat_database where datname=current_database () -- after optimization, it is recommended to execute the following statement to compare the data pg_stat_reset () before and after optimization-- Table-level statistics pg_stat_user_tables-- index utilization select sum (idx_scan) / (sum (idx_scan) + sum (seq_scan)) as idx_scan_ratio from pg_stat_all_tables where schemaname='your_schema' Select relname,idx_scan::float/ (idx_scan+seq_scan+1) as idx_scan_ratio from stat_all_tables where schemaname='your schema' order by idx_scan_ratio asc;-- statement-level statistics obtain pg_stat_statements-- enable shared_preload_libraries='pg_stat_statements'pg_stat_statements.track=all create extension pg_stat_statements through pg_stat_statements, postgres log and auto_explain -- the three queries with the longest average execution time of the query select calls,total_time/calls as avg_time,left (query,80) from pg_stat_statements order by 2 desc limit 3 analyze on-viewing the execution plan analyze, you can get the real execution plan explain analyze select * from tb1;-- except for the analyze option, you can use other explain (analyze on, timing on,verbose on,buffers on) select * from tb1 -- log_xxx_stat judgment problem at session level, system resources used, etc., set client_min_messages=log;set log_parser_stats=on;set log_planner_stats=on;--orset client_min_messages=log;set log_parser_stats=off;set log_planner_stats=off;set log_statement_stats=on;-- re-indexing create unique index concurrently on mytb1 using btree (id) -- the id field has two indexes select schemaname,relname,indexrelanme,pg_relation_size (indexrelid) as index_size,idx_scan,idx_tup_read,idx_tup_fetch from pg_stat_user_indexes where indexrelname in (select indexname from pg_indexes where schemaname='public' and tablename='mytb1');-- start the transaction to delete the host index and update the second index to the primary key constraint begin;alter table mytb1 drop constraint mytb1_pkey;alter table mytb1 add constraint mytb1_id_idx primary key using index mytb1_id_idx End;-- for large-scale, through the pg_repack tool for regular index reconstruction so far, I believe you have a deeper understanding of the "Postgresql performance-related operating system and database description", might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.