The Config File of PostgreSQL is PostgreSQL. Conf files in the database storage directory. The setting of the parameters in this file has a great impact on the performance of the PostgreSQL database. You need to pay attention to the following five aspects of parameter configuration.
- Shared buffer
PostgreSQL interacts with the kernel and disk through a shared buffer, so this parameter should be set as large as possible so that more data is in the shared buffer.
The shared buffer corresponds to the parameter shared _ buffers in the PostgreSQL. Conf. The default value is 1024KB and must not be less than 128KB. It is usually set to 10% of the actual RAM, say 50000 (400 M).
- Working memory
When EnterpriseDB performs a sorting operation, it will decide whether to split a large result set into several temporary files of the same size as the working memory according to the size of the working memory. Obviously, the split result slows down the sorting speed. Increasing the size of the working memory can help speed up sorting.
The working memory corresponds to the parameter of work _ mem in the PostgreSQL. Conf. The default value is 1024KB, which is usually set to 2% -4% of the actual RAM, depending on the size of the result set to be sorted, such as 81920 (80m).
- Valid cache
The effective cache is the maximum cache that PostgreSQL can use, and this number should be as large as possible for a stand-alone PostgreSQL server.
The effective cache corresponds to the parameter of effective _ cache _ size in the PostgreSQL. Conf. Usually, the memory of 4G can be set to 3.5G (437500).
- Maintain working memory
Maintaining working memory is only used during CREATE INDEX, VACUUM, etc., so it is not used frequently, but these instructions often consume more resources, so these instructions should be quickly Executed as soon as possible.
Maintaining the parameter of working memory corresponding to the maintence _ work _ mem in the PostgreSQL. Conf usually requires maintence _ work _ mem large memory, such as 512m (524288).
- Maximum number of connections The purpose of
setting the maximum number of connections is that "maximum number of connections * working memory" exceeds the actual memory size. For example, if the work _ mem is set to 2% of the actual memory, in an extreme case, if 50 queries have sorting requirements and all use 2% of the memory, swap will be generated, and the system performance will be greatly reduced.
The maximum number of connections corresponds to the parameter Max _ connections in the PostgreSQL. Conf. The default value is 100.