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

This article will explain in detail how to analyze Postgresql IO. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope you have a certain understanding of relevant knowledge after reading this article.

Today we are going to talk about POSTGRESQL I/O system and the learning sharing of some peripheral systems.

First we look at a diagram, this is a PG memory structure diagram

But today is not about memory, but about buffer cache problems,

In fact, to put it bluntly, it is the interactive process between the data in memory and disk. If the extracted data is not in memory, you need to read the data from disk to page change and if the data changes in memory, you need to brush the data into disk.

And we need to know the following.

Buffered writes: usually non-blocking

Buffered reads: usually blocked

Sequential writing is faster than random writing

Operations covering larger data blocks are generally faster

In addition, checkpointer ,Background writer , wal writer, etc. are all used in I/O systems. So tuning PostgreSQL I/O is essential to building a high-performance, scalable database architecture.

For example:

1 Whether there is a read-ahead mechanism for data, and what read-ahead mechanism can make more efficient use of memory than a large number of I/O reads.

2 Better concurrent reads, more threads can be used to read the required data into the system simultaneously

Because POSTGRESQL buffer cache itself is not all HOLD master, but joint OS buffer to process the database, we compare four cases

1 No postgresql buffer ,OS buffer

2 No OS only cache No postgresql cache

3 POSTGRESQL OS cache and POSTGRESQL buffer shared cases

So POSTGRESQL does more optimization for related operations such as

1 Write data back to the operating system when the working set does not fit into the shared buffer

2 Reduce back-end write operations

3 Improve IO throughput/utilization, especially random IO

4 Reduce random I/O, merge it more, and make it sequential I/O

5 Mapping data structures with sorting support via shared_buffer

Unlike other databases, postgresql places more emphasis on the system's role in data access, and newer systems such as LINUX's core version upgrade are beneficial to postgresql itself.

There are a few caveats in Postgresql

Checkpoint settings, such as max_wal_size , min_wal_size, checkpoint_timeout and comkpoint_completion_target, will increase or decrease the burden on your disk system. Dirty pages can be washed into your disk system in a centralized or decentralized manner.

Note your daily transaction volume, the number of WAL arvhive files archived, how often you monitor checkpoints, and whether the above parameters are appropriate for your current busy or non-busy database system.

3 Vacuum, cleaning up discarded data is also very important to prevent table expansion, and vacuum will also cause a burden on the disk system, but also to log_autovacuum_min_duration attention, check the automatic vacuum time length. Generally speaking, automatic vacuum can meet the needs of most systems, but for large meters or thermal meters, manual or semi-automatic vacuum can be carried out when night work is not busy, such as repack, etc.

4 has some consistency with MYSQL in some aspects, for example, try to assign appropriate work_men to each query, especially in some systems that often use group by , order by, increase sorting activities that can be solved in memory, and avoid more ways to give results after sorting on disk.

5 More rational and efficient use of table space, like other database systems, the way a database is spread across multiple physical disks is also applicable in POSTGRESQL, and if the wal log is placed on an advanced I/O system, such as SSD disk, it is also effective for system performance.

6 Generally speaking, in configuring POSTGRESQL, it is preferable to set the system shared_buffer reasonably, but the effective_cache_size setting will be ignored. If you want to make an initial setting, you can set the shared_buffer to 25% of the total memory and the effective_cache_size to 50% or more of the system amount. The important effective_cache_size setting has a lot to do with the performance of your query.

About Postgresql IO how to analyze how to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see 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.