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

What does EFFECTIVE_CACHE_SIZE refer to in Postgresql? for this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Sometimes every chapter or part of a book is not written to make you feel that you can read it carefully. In this state, the first sentence of the book mentions that effecitve cache size should evaluate how the memory of the standard system can satisfy the caching of the disk in the operating system and the use of memory when the database is working properly. For postgres as a whole, settings with a value of 50%-70%, such as random_page_cost, will affect which index scan or sequential scans is more biased in data lookups. The default is 4. 0 when using san/nas technology, it can be adjusted to 3 SSD, and it can be adjusted to 1.5 to 2.5.

In fact, after reading this paragraph, I didn't quite understand what he was trying to say.

So the following text is for the bold extension above.

For example, if we have 100 gigabytes of memory, how much EFFECTIVE_CACHE_SIZE can we have? we have 2G for applications, 3GB for postgresql to run its own processes, and the rest is shared buffers and FILESYSTEM CACHE for postgresql.

The main function of Share buffers and filesystem cache is to cache data. When caching data to satisfy data processing, the specific information must exist in memory.

In fact, two points are vague here: 1 whether the SESSION connected to POSTGRESQL needs memory, 2. The sorting of data and the memory release of temporary tables are included in the meaning of SGA PGA in effective_cache_size, that is, ORACLE, and whether there is a clear distinction in PG.

These are all issues that need to be clarified in this issue.

In addition, it should be clear that where the meaning of effective_cache_size is set, or back to the root, the real meaning of database performance, effective configuration and setting effective cache size is to improve the performance of the database runtime.

Effectively carry this data, so that the query optimizer can recognize it, make more efficient use of the memory, and have a comment in the source code

In fact, the memory of Postgresql is also divided into two parts with other databases, where PG memory is mainly composed of local memory area and shared buffer pool, shared buffer pool includes share buffer wal buffer commit log, and local memory area is mainly composed of work_mem maintenance work mem and temp buffer.

CLOG is the commit log (CLOG) that holds the state of all transactions and is part of the concurrency control mechanism. The commit log is allocated to shared memory and used throughout the transaction.

At this point, the above two questions are very clear.

The allocation of 1 share buffer is very important, it provides how many databases in the database service can be hit buffer, which seriously affects the performance and quality of the database external service.

2 work_mem allocation, a connection will use a work_mem for data processing, the number of connections * work_mem is the use of memory in your local memory area.

For example

We support 500 connections, each connection using a maximum of 4MB work_mem is 500 * 4MB, nearly 2000MB will be used in this item.

So this leads to some other questions, if the index data will reside in share buffer, what kind of index type is more conducive to query and save memory, which can be discussed in POSTGERSQL, because there are many TYPE of indexes supported by PG, and some types of indexes can become very small in some application scenarios, which must be beneficial to system performance and memory saving.

The other part is the setting of work_mem. If the work_mem is larger, it will waste too much memory when the number of connections is large, and if the setting is too small, it will also affect the query performance of the system. This is also a matter of opinion. For example, if your system is OLAP OR OLTP, there will be different practices in this setting.

Here is a stress test tool for pg to get a preliminary understanding of the system performance of your current postgresql.

The answer to the question about what EFFECTIVE_CACHE_SIZE refers to in Postgresql is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.