PostgreSQL configuration optimization

2010-12-30  来源:本站原创  分类:Database  人气:79 

PG configuration file is a database directory of the postgresql.conf file, to support future versions 8.0 K, M, G such parameters, modify the parameters as long as the PG service to restart OK.

shared_buffers: This is the most important parameters, postgresql shared_buffers and the kernel and the disk through the deal, it should be as large as possible, so that more data in the cache in the shared_buffers. Usually set to 50% of the actual RAM.

kernel.shmmax: the largest single process cache block size set in sysctl.conf, equal to the physical memory

work_mem can be called working memory or the operating memory. The charge of the internal sort and hash operations, the appropriate size to ensure that these operations work_mem in memory. Defined too small, then, sort or hash operation will need to hard disk swap, this will greatly reduce system performance; too large can result in the completion of the operation in memory to reduce the number of the other part of the operation need to swap disks to increase the IO performance degradation. System provides the default value is 1M, in the actual production environment, to analyze data on the system monitor to make the best choice.
There are basically two ways: estimation and calculation. The first one is based on the size and type of traffic, the general statement that running time to a rough estimate. The second way is through the database, monitoring, data collection, and then calculate its size. In short the appropriate size of the system performance is critical.

Maintenance in the actual analysis can explain analyze statements work_mem size is appropriate. Work_mem parameters set in the statement can take advantage of the size of the memory, to improve the efficiency of the statement.
Work_mem memory allocation for database concurrency should also consider the situation, max_connections system determines the maximum number of simultaneous connections. Must take into account both how to adjust work_mem max_connections * work_mem + shared_buffers + temp_buffers + maintenance_work_mem + operating system memory requirements can not exceed the size of the entire RAM, which is very important.
work_mem parameters on the performance of the system is so important, let's meet the real-time operational status of the database was the unlikely, but can be run through the database of the monitoring period, summed up the appropriate data, and then customize a special script, devoted work_mem to modify the size, make it more responsive to stage the status of the system, after all, a good way. 20% of memory can be set to the maximum 1G.

maintenance_work_mem called the maintenance of working memory, mainly for database maintenance operations or statements. These operations as much as possible in memory. Mainly for VACUUM, CREATE INDEX, REINDEX such an operation. VACUUM on the database index, or the larger reconstruction, an appropriate adjustment of the parameters is necessary.
postresql documents suggest features enabled autoacuum case, the parameters can not be configured too large.

effective_cache_size query optimizer assumes that the available disk cache size, the greater the value of the bias in the use of the index, the smaller the value of the bias in the use of sequential scan. For 75% of physical memory.

相关文章
  • PostgreSQL configuration optimization 2010-12-30

    PG configuration file is a database directory of the postgresql.conf file, to support future versions 8.0 K, M, G such parameters, modify the parameters as long as the PG service to restart OK. shared_buffers: This is the most important parameters, p

  • Miscellanies postgresql configuration parameters 2010-10-06

    shared_buffers Quote if you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1 / 4 of the memory in your system. If you have less ram you'll have to account more carefully for how much RAM the OS is taking up,

  • Tomcat configuration optimization transfer 2010-11-03

    1. Tomcat and configuration files to streamline 1.1. Remove unwanted applications management applications and help to improve the tomcat security. # Delete all the files under the webapps # Rm-fr $ CATALINA_HOME / webapps / * # Remove server / wenapp

  • Squid uses the sum (installation configuration optimization ...) 2011-06-19

    Earlier work logs, down memo 1. Compile and install Temporary use CENTOS 5.2 version on the default installation of 2.6STABLE7 2. Squid configuration 2.1 The simplest configuration http_port 80 cache_peer 127.0.0.1 parent 8080 0 no-query default orig

  • postgresql configuration parameters Miscellanies 2010-10-06

    shared_buffers Quote if you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1 / 4 of the memory in your system. If you have less ram you'll have to account more carefully for how much RAM the OS is taking up,

  • postgresql memory optimization 2011-04-07

    PG configuration file is a database directory of the postgresql.conf file, version 8.0 can support future K, M, G such parameters, just modify the appropriate parameters to restart the PG service is OK. shared_buffers: This is the most important para

  • PostgreSQL Configuration Files --PostgreSQ配置文件 2015-03-23

    Three main configuration files control basic operations of a PostgreSQL server instance. These files are all located in the default PostgreSQL data folder. You can edit them using your text editor of choice, or using the admin pack that comes with pg

  • RHEL5.4 yum configuration optimization 2010-03-20

    Optimal preconditions: 1, rhel5 yum is already installed 2, yum update the file addresses: / etc / yum.repos.d / 3, backup the original configuration file # Cd / etc / yum.repos.d # Mv mv CentOS-Base.repo CentOS-Base.repo.bak # Mv CentOS-Media.repo C

  • Arch-05-11-[spring + ibatis + postgresql] configuration problem Affairs 2011-06-22

    When inserting data, then execute a query, this query can not record, plus a transaction, but reported 25P02 wrong, check for a long time, they would understand is the spring configuration of the transaction is incorrect. Wife as follows: ===========

  • CentOS 6.0 server installation. Configuration. Optimization (Beginners) 2011-09-07

    Let me know about the recent head, Linux CentOS under the server installation, configuration, natural means in the linux solution may be batch start. Previously, the college to find out about the operation even less, now had to study step by step fro

  • mysql configuration optimization practice 2011-09-03

    Our game uses mysql as database software, several optimization, has been relatively stable, with stable operation of the records for months, but a relatively recent phenomenon of strange, the database has more than 100 processes suddenly locked, with

  • Nginx configuration optimization 2010-04-12

    http://brainspl.at/articles/2007/01/03/new-nginx-conf-with-optimizations # User and group to run as user ez ez; # Number of nginx workers worker_processes 6; # Pid of nginx master process pid / var / run / nginx.pid; # Number of worker connections. 1

  • postgressql performance optimization configuration parameter 2010-11-05

    pg configuration parameters, performance optimization: PostgreSQL configuration file is a database directory (/ opt/PostgresPlus/8.3/data) under the postgresql.conf file, to support future versions 8.0 K, M, G such parameters, as long as modify the p

  • PostgreSQL 8.4 under Ubuntu 10.04 Installation and Configuration 2011-05-04

    The first step: to install Postgresql on Ubuntu [Source Content] root @ ubuntuserver: ~ # sudo apt-get install postgresql-8.4 postgresql-client-8.4 postgresql-contrib-8.4 [Code Description] Install server and command line client psql. [Function] / Us

  • PostgreSQL 8.4 under Ubuntu 10.04 installation configuration 2011-05-04

    Step One: Installing Postgresql on Ubuntu [Source Content] root @ ubuntuserver: ~ # sudo apt-get install postgresql-8.4 postgresql-client-8.4 postgresql-contrib-8.4 [Code Description] Install server and command line client psql. [Function] / Usr/lib/

  • postgressql configuration parameters to optimize performance 2010-11-05

    pg configuration parameters, performance optimization: PostgreSQL configuration file is a database directory (/ opt/PostgresPlus/8.3/data) under the postgresql.conf file, version 8.0 can support future K, M, G such parameters, just modify the appropr

  • System tuning the configuration 2010-06-12

    Recent projects related configuration parameters to adjust jetty following: JAVA_OPTIONS = "$ JAVA_OPTIONS-server-Xmx2048m-Xms2048m-XX: NewRatio = 3-Xnoclassgc-XX: MaxNewSize = 256m-XX: MaxPermSize = 500m-XX: MaxGCPauseMillis = 5000-Dfile . encoding

  • Profile based on Hadoop MapReduce data flow optimization 2010-06-20

    Hadoop Hadoop platform configuration file to set the various properties at run time. Experimental results demonstrate that a reasonable allocation will greatly improve the performance of Hadoop. In Hadoop-0.19.2 version, Hadoop configuration file con

  • Website performance optimization of large general method 2010-08-27

    1, HTML static fact, we all know, the highest efficiency, consumption is the least of pure static html page, so we make our site as a page with static pages to realize this fact, the simplest and most effective way to method. But for a lot of content

  • LAMP (Linux + Apache + Mysql + PHP) optimization techniques 2010-11-18

    [LAMP platform] LAMP origin of the word was first started in the German magazine "c't Magazine", Michael Kunze in 1990, the first of these projects together to create the acronym LAMP. Although these components are not designed to work together