Shulou(Shulou.com)06/01 Report--

Postgres performance Optimization system Optimization Modification / etc/grub.conf

Close numa=off and modify disk IO scheduling method elevator=deadline

Modify method:

Grubby-- update-kernel=ALL-- args= "transparent_hugepage=never"-- args= "elevator=deadline" Verification: grubby-- info=ALL returns args= "ro crashkernel=auto rhgb quiet numa=off elevator=deadline" turn off memory large page hugepage to verify whether method 1: if the following file does not exist, THP has been removed from the kernel. / sys/kernel/mm/transparent_hugepage or / sys/kernel/mm/redhat_transparent_hugepage verify whether to enable method 2: cat / sys/kernel/mm/transparent_hugepage/ enabled [always] madvise never [always] = "means to enable the disable large memory page method:

Modify / etc/rc.local

# vi / etc/rc.local # append, disable large page if test-f / sys/kernel/mm/transparent_hugepage/enabled; then echo never > / sys/kernel/mm/transparent_hugepage/enabledfiif test-f / sys/kernel/mm/transparent_hugepage/defrag; then echo never > / sys/kernel/mm/transparent_hugepage/defragfi kernel optimization vi / etc/sysctl.conf

The content is added as follows:

Vm.swappiness = 0kernel.shmmax=135497418752net.core.rmem_max = 4194304net.core.wmem_max = 4194304net.core.rmem_default = 262144net.core.wmem_default = 262144net.ipv4.ip_local_port_range = 9000 65535kernel.sem = 50100 64128000 50100 1280vm.dirty_background_bytes = 102400000vm.dirty_ratio = 80vm.nr_hugepages = 102352 etc. * soft nofile 655360 * hard nofile 655360 * soft nproc 655360 * hard nproc 655360 * soft stack unlimited* hard stack unlimited* soft memlock 250000000 * hard Memlock 250000000 device pre-read blockdev-- setra 16384 / dev/sda setting boot takes effect: echo "blockdev-- setra 16384 / dev/sda" > > / etc/rc.local postgres data plane optimization involves ordering ALTER SYSTEM SETALTER SYSTEM SET configuration_parameter {TO | =} {value | 'value' | DEFAULT}

Case:

Alter system set max_connections='50';SHOW configuration_parameter

Case:

Show max_connections implementation principle:

The user who invokes the command must have superuser privileges. Modify the system configuration file postgresql.conf, through the system signal, reload the configuration file.

Query the effective level and default value information of the postgresql configuration option; select name, context,setting,unit,source,sourcefile from pg_settings;internal: settings during compilation, which will only take effect if you recompile. Postmaster: only service restart can take effect. Sighup: sending a HUP signal to the server will reload the postgresql.conf configuration on the server, which will take effect immediately. Backend: similar to sighup, but does not affect running sessions, superuser only takes effect in new sessions: changes can only be made using superuser (such as postgres), and all configurations do not have to be reloaded. User: a single session user can make changes at any time, only affecting the session. Optimize database host configuration system more / etc/redhat-release CentOS Linux release 7.5.1804 (Core) kernel uname-aLinux jp33e506-3-22.ptengine.com 3.10.0-862.2.3.el7.x86_64 # 1 SMP Wed May 9 18:05:47 UTC 2018 x86 "64 GNU/Linux memory Mem: 128763 Core Optimization option: postmaster: only service restart can take effect. Alter system set max_connections='50';alter system set shared_buffers='30GB';alter system set wal_buffers='64MB';sighup: sending a HUP signal to the server will reload the postgresql.conf configuration on the server, which will take effect immediately. Alter system set checkpoint_completion_target='0.9';alter system set checkpoint_timeout='15min';alter system set fsync='off';superuser: use superuser (such as postgres) to change and take effect without reloading all configurations. Alter system set commit_delay='10';user: a single session user can make changes at any time, only affecting the session. Alter system set autovacuum_work_mem='1GB';alter system set commit_siblings='6';alter system set effective_cache_size='50GB';alter system set maintenance_work_mem='1GB';alter system set work_mem='16MB'; to view optimization results show max_connections;show shared_buffers;show wal_buffers;show checkpoint_completion_target;show checkpoint_timeout;show fsync;show commit_delay;show autovacuum_work_mem;show commit_siblings;show effective_cache_size;show maintenance_work_mem;show work_mem The method of reconfiguring database

Some optimizations will not take effect until they are restarted:

1. Run postgres=# SELECT pg_reload_conf () with the superuser; 2. Manually initiate the HUP signal $kill-HUP PID3 with UNIX's kill. Use the pg_ctl command to trigger the SIGHUP signal $pg_ctl reload

Or

Performance comparison of optimized systemctl reload postgresql-11.servicesystemctl restart postgresql-11.servicesystemctl status postgresql-11.service

The green line is the dividing line.

CPU

Reference document: https://www.kancloud.cn/taobaomysql/monthly/140098

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.