Affordable web hosting - 598 CHAPTER 26 POSTGRESQL ADMINISTRATION your PostgreSQL
598 CHAPTER 26 POSTGRESQL ADMINISTRATION your PostgreSQL system, you need to run the VACUUM VERBOSE command on each database, and set this value to the total number of pages for all databases. This setting requires 6 max_fsm_pages bytes of memory, but it is critical for optimum performance, so don t set this value too low. This value requires a full restart of PostgreSQL for any changes to take effect. Managing Planner Resources The PostgreSQL planner is the part of PostgreSQL that determines how to execute a given query. It bases its decisions on the statistics collected via the ANALYZE command and on a handful of options in the postgresql.conf file. Here we review the two most important options. effective_cache_size This setting tells the planner the size of the cache it can expect to be available for a single index scan. Its value is a number equal to one disk page, which is normally 8,192 bytes, and has a default value of 1,000 (8MB RAM). A lower value suggests to the planner that using sequential scans will be favorable, and a higher value suggests that an index scan will be favorable. In most cases, this default is too low, but determining a more appropriate setting can be difficult. The amount you want will be based on both PostgreSQL s shared_buffer setting and the kernel s disk cache available to PostgreSQL, taking into account the amount other applications will take and that this amount will be shared among concurrent index scans. It is worth noting that this setting does not control the amount of cache that is available, but rather is merely a suggestion to the planner, and nothing more. This value requires a full restart of PostgreSQL for any changes to take effect. random_page_cost Of the settings that control planner costs, this is by far the most often modified by PostgreSQL experts. This setting controls the planner s estimate of the cost of fetching nonsequential pages from disk. The measure is a number representing the multiple of the cost of a sequential page fetch (which by definition is equal to 1) and has a default value of 4. Setting this value lower will increase the tendency to use an index scan, and setting it higher will increase the tendency for a sequential scan. On a system with fast disk access, or on a database in which most if not all of the data can safely be held in RAM, a value of 2 or lower is not out of the question, but you ll need to experiment with your hardware and workload to find the setting that is best for you. This value requires a full restart of PostgreSQL for any changes to take effect. Managing Disk Activity One of the most common bottlenecks to performance is that of disk input/output (I/O). In general, it is more expensive to read from and write to a hard drive than to compute information or retrieve the information from RAM. Thus, a number of settings have been created to help manage this process, as discussed in this section. fsync This setting controls whether or not PostgreSQL should use the fsync() system call to ensure that all updates are physically written to disk, rather than rely on the OS and hardware to ensure this. This is significant because, while PostgreSQL can ensure that a database-level crash will
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.