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

This article introduces the relevant knowledge of "DB buffer problem Analysis of PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The first part of buffer that we are going to talk about today, if you want to understand it in other databases, there are PGA and SGA in ORALCE, and this part is SGA, which is shared memory in the database. The difference between this shared memory in postgresql and other databases is that most database MYSQL generally sets its own innodb_buffer_pool_size to 60-80% of the total memory. SQL SERVER generally eats most of the system memory and generally does not release it. We can understand that these databases (mysql, oracle, sql server) have their own memory managers and tend to be exclusive.

In contrast, postgresql generally recommends that the setting of share_buffer is 25% of the total memory of the system, which runs counter to the above three databases. So it leads to today's question of what makes POSTGRESQL "different" in the use of memory and caching.

Select current_setting (name), * from pg_settings where name like 'share%'

In most cases, postgresql tends to use OS buffering to get the job done, that is, the data read goes through OS CACHE, and skipping system cache is roughly the only WAL writes. The question is why you need buffer cache. If you can use OS cache directly, you can use it.

The reason why there is buffer cache in the end is that buffer cache has a unique algorithm for the database. In the buffer cache of postgresql, the data that is often used can be "glued" to buffer cache by approximate LRU algorithm, increasing the utilization of database buffers.

Then there may be another question: why does PG recommend setting buffer cache to 25% of total memory, instead of bigger, bigger, isn't it better? In fact, according to the relevant books in PG9.X PG 10, if this may be counterproductive, the point made in the book is that part of the operation of PG is more direct through the CACHE of the system.

So this leads to another question, how do we set the shared memory (in the LINUX system) to make the system better serve the database? here is a script that can get the size of the LINUX shared memory.

. / shmsetup > > / etc/sysctl.conf

The student asked another question, that is, since PG has buffer cache and OS cache, such a design will waste memory space.

What I want to say here is no, you can think about it, the database system buffer cache can stick to the frequently accessed data, but the system buffer does not have such a function, that is, the possibility that there is the same data between the system buffer and the database BUFFER CACHE is relatively low, a data will often be accessed before it can be put into the buffer, and if a frequently accessed data is put into the database BUFFER The CACHE of the system will no longer be accessed, so the system will clean it up, and it is less likely that the buffer of the system can have the same data as the buffer of the database.

This is the end of "PostgreSQL's DB buffer problem Analysis". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.