Managing Kernel Resources

Server Configuration of the official documentation for PostgreSQL. Then let’s see the parameters more important that we should change if we start to use PostgreSQL for serious use and if we want to get the most out of our machine. There are many more parameters that can be and over time is must adjust, here we focus on the more important and which we should change before using PostgreSQL in a serious way. max_connections: maximum number of clients connected simultaneously to our databases. We should increase this value in proportion to the number of concurrent clients in our PostgreSQL cluster. A good value to start is 100: max_connections = 100 shared_buffers: this parameter is very important and defines the size of the memory buffer used by PostgreSQL. Not greatly increase this value we will have better response. On a dedicated server we can start with 25% of the total of our memory. Never more than 1/3 (33%) of the total. For example, on a server with 4Gbytes of memory, we can use 1024MB as initial value. shared_buffers = 1024MB work_mem: used in operations that contain ORDER BY, DISTINCT, joins,. On a dedicated server we can use a 2-4% of the total of our memory if we have only a few sessions (clients) large. 8 Mbytes can be used as the initial value. work_mem = 8 MB maintenance_work_mem: used in operations of the type VACUUM, ANALYZE, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. Its value will depend on much of the size of our database. For example, on a server with 4Gbytes of memory, we can use 256 MB as the initial value. maintenance_work_mem = 256 MB effective_cache_size: parameter used by the query planner of our database engine to optimize the reading of data. In a dedicated server we can begin with 50% of the total of our memory. A maximum of about 2/3 (66%) of the total. For example, on a server with 4Gbytes of memory, we can use 2048MB as initial value. effective_cache_size = 2048MB checkpoint_segments: this parameter is very important in databases with numerous write (insert, update, delete) operations. To start, we can start with a value of 64. In large databases with many Gbytes of data written we can increase this value to 128-256. checkpoint_segments = 64 It is very important to bear in mind that to increase the values by defect of many of these parameters, we should increase the values by defect of some parameters of the kernel of our system. Detailed information on how to do this is in section 16.4. Managing Kernel Resources of the official documentation for PostgreSQL. Anyway, this is just an appetizer of what we can do. With practice and experience can and will have to adjust many parameters. But this will be subject of a next article.

Tags: