PostgreSQL source analysis of shared buffer and disk files

Source: Internet
Author: User
Tags postgresql

We know that the information in the PostgreSQL database is ultimately written to the persistent device. So how does PostgreSQL store the information organization on disk? Bruce Momjian has a slide "insider PostgreSQL Shared Memory", inside the picture is very intuitive description, shared buffer,page, the relationship between disk files, please see. The next few blogs, from different levels, tell about the memory associated with PostgreSQL storage:

The bottom-left corner is the page's organization. PostgreSQL 8K is a page that writes relation corresponding disk file from share buffer, or reads 8K to shared buffer from the relation corresponding disk file. The shared buffers is a set of 8K pages that act as a cache. For the relation of the database, a record (item or tuple), different size, does not occupy exactly 8K of space, probably only dozens of bytes, so how to store multiple records into the 8K shared buffer, which is the form of the page, I'll introduce it in another blog post.
For Linux we know that reading a file will first read the contents of the disk into memory, write the file will first write to the cache, the cache is marked as dirty, at the appropriate time to write to disk. For this unfamiliar, you can read one of my previous blog file and page cache some things, PostgreSQL shared buffers to relation file in disk is equivalent to the Linux page cache to file In disk.

To view/Set the shared buffers size:
First of all, PostgreSQL buffers how big, how many 8KB buffers, of course, this is configurable, we can see the configuration by the following methods:

    1. Show Shared_buffers

Or:

    1. Select Name,unit,setting,current_setting (name) from pg_settings where name = ' Shared_buffers ';


The above is the view, how to modify it? You need to modify the configuration file postgresql.conf:

    1. [Email protected]:/usr/pgdata# cat postgresql.conf | grep ^shared_buffers
    2. shared_buffers = 24MB # min 128kB

We can change the shared_buffers to a different value, as to how much value is reasonable, depending on your hardware environment, such as your hardware is very strong, 16GB of memory, then this value set to 24MB is too stingy. As for the shared buffers is reasonable, there are many words on the internet, some say that the amount of memory 10%~15%, some say the total memory of 25%, fortunately PostgreSQL provides some performance measure tools, So that we can monitor the performance of PostgreSQL operation, we can adjust the size of this shared buffers according to the performance statistics of PostgreSQL.
However, shared buffer is allocated as shared memory, and if the value configured in the configuration file exceeds the operating system's maximum limit for share memory, the PostgreSQL initialization fails. For example, I will postgresql.conf in shared_buffers = 64MB, resulting in a startup failure as shown in:

The reason is kernel Shmmax max is only 32MB, below I view and modify to 512MB

After you've changed, you can start PostgreSQL, and we can see that Shared_buffers has become 64MB:

    1. manu_db=# show Shared_buffers;
    2. Shared_buffers
    3. ----------------
    4. 64MB
    5. (1 row)

    
    Simple content is over, we need to drill down into the principles of Code Analysis Shared buffers, how to organize memory, how to allocate it, how to page replacement, Find the answers in the source code. Detailed content, I intend to introduce in the next post, because the principle part itself will have a lot of content, will inevitably lead to my this article is relatively long. The rest of this article is about how the in-memory shared buffer learns about the corresponding disk file. Because the 8K content in the shared buffer will eventually sync to the disk file. PostgreSQL corresponds to a shared buffer in memory and a file on the disk.

  shared buffer correspondence to relation disk File
    First diagram of this article, The upper part is about the structure of the shared buffer, which is divided into two parts  
   1 buffer,n 8K blocks of red fruit, each of which holds a 8K of content read from the relation corresponding disk file.
   2 manages the structure of buffer, also n, there are several buffer, there are several management structures. of Course, the management structure occupies much less memory space than the red fruit buffer, otherwise the memory utilization is too low.  
    This is the time to initialize, allocate space for these two parts:

    1. Bufferdescriptors = (BUFFERDESC *)
    2. Shmeminitstruct ("Buffer descriptors",
    3. Nbuffers * sizeof (BUFFERDESC), &founddescs);
    4. Bufferblocks = (char *)
    5. Shmeminitstruct ("Buffer Blocks",
    6. Nbuffers * (Size) Blcksz, &FOUNDBUFS);

This management buffer structure called BUFFERDESC, my IQ is not high, but also know that the corresponding buffer is not used, corresponding to which disk file of the 8K block, in order to deal with concurrency, there will definitely be a lock. Let's look at the definition of this structure:

  1. typedef struct SBUFDESC
  2. {
  3. Buffertag tag; /* ID of page contained in buffer */
  4. Bufflags flags; /* See bit definitions above */
  5. UInt16 Usage_count; /* Usage counter for Clock sweep code */
  6. unsigned refcount; /* # backends holding pins on buffer */
  7. int wait_backend_pid; /* Backend PID of pin-count waiter */
  8. slock_t Buf_hdr_lock; /* Protects the above fields * *
  9. int buf_id; /* buffer ' s index number (from 0) */
  10. int freenext; /* link in freelist chain */
  11. Lwlockid Io_in_progress_lock; /* To-wait for I/O to complete */
  12. Lwlockid Content_lock; /* To lock access to buffer contents */
  13. } Bufferdesc;

OK, we return to the question of our initial relationship, the current shared buffer and which db, which Table,which type (later interpreted type), which file which 8KB block corresponds. The tag field of the first Buffertag type determines the corresponding relationship:

  1. typedef enum FORKNUMBER
  2. {
  3. Invalidforknumber =-1,
  4. Main_forknum = 0,
  5. Fsm_forknum,
  6. Visibilitymap_forknum,
  7. Init_forknum
  8. /*
  9. * Note:if Add a new fork, change max_forknum below and update the
  10. * Forknames Array in catalog.c
  11. */
  12. } Forknumber;
  13. typedef struct RELFILENODE
  14. {
  15. Oid Spcnode; /* tablespace */
  16. Oid Dbnode; /* Database */
  17. Oid Relnode; /* Relation */
  18. } Relfilenode;
  19. /*
  20. * Buffer tag identifies which disk block the buffer contains.
  21. *
  22. * Note:the Buffertag data must is sufficient to determine where to write the
  23. * block, without reference to Pg_class or pg_tablespace entries. It ' s
  24. * Possible that the backend flushing the buffer doesn ' t even believe the
  25. * Relation is visible yet (it XACT may has started before the XACT that
  26. * created the rel). The storage Manager must is able to cope anyway.
  27. *
  28. * Note:if there ' s any pad bytes in the struct, Init_buffertag would have
  29. * To was fixed to zero them, since this struct is used as a hash key.
  30. */
  31. typedef struct BUFTAG
  32. {
  33. Relfilenode Rnode; /* Physical Relation identifier */
  34. Forknumber Forknum;
  35. Blocknumber Blocknum; /* Blknum relative to begin of Reln */
  36. } Buffertag;

We can see the rnode in Buffertag, which is characterized by which relation. The type of the Rnode is the Relfilenode type, including the database space/database/relation, from top to bottom level three structure, the only one relation that determines the PostgreSQL. For relation, there is not only one type of disk file,

    1. -RW-------1 Manu Manu 270336 June 3 21:31 11785
    2. -RW-------1 Manu Manu 24576 June 3 21:31 11785_FSM
    3. -RW-------1 Manu Manu 8192 June 3 21:31 11785_VM

As shown in Figure 11785 corresponds to a relation, but there are three types of disk space, including two files for FSM and VM suffixes. Let's take a look at Forknumber's notes:

    1. /*
    2. * The physical storage of a relation consists of one or more forks. The
    3. * Main fork is all created, but addition to that there can be
    4. * Additional forks for storing various metadata. Forknumber is used when
    5. * We need to refer to a specific fork in a relation.
    6. */

Main_forknum type always exists, but some relation still exist fsm_forknum and visibilitymap_forknum two kinds of files, these two kinds of I know now unknown, I will not nonsense.
Let's take it slow, let's put down Blocknum this member variable, step too big easy to pull the egg, we first find the disk corresponding file according to Rnode+forknum?
The thing to look for in a disk file is RELPATH This macro is implemented by calling Relpathbackend:

  1. char *
  2. Relpathbackend (Relfilenode Rnode, Backendid backend, Forknumber forknum)
  3. {
  4. if (Rnode.spcnode = = globaltablespace_oid)
  5. {
  6. ...
  7. }
  8. else if (Rnode.spcnode = =defaulttablespace_oid)
  9. {
  10. Pathlen = 5 + oidchars + 1 + oidchars + 1 + forknamechars + 1;
  11. Path = (char *) palloc (Pathlen);
  12. if (forknum! = main_forknum)
  13. snprintf (Path, Pathlen, "base/%u/%u_%s",
  14. Rnode.dbnode, Rnode.relnode,
  15. Forknames[forknum]);
  16. Else
  17. snprintf (Path, Pathlen, "base/%u/%u",
  18. Rnode.dbnode, Rnode.relnode);
  19. }
  20. Else
  21. {
  22. ...
  23. }
  24. }



Because we are pg_default, so we go defaulttablespace_oid this branch. Determines that we are in the base directory, the OID of the db (that is, Buffertag->rnode->dbnode) is 16384 determines the Base/16384/,buffertag->rnode->relnode + Buffertag->forknum decided whether it was base/16384/16385 or BASE/16384/16385_FSM or BASE/16384/16385_VM.

Find the basic end of the file, however, some of the relation is larger, more records, will cause the disk file is very large, in order to prevent the file system to the disk file size limit caused by the write failure, PostgreSQL did a segmented mechanism. Take my friends as an example, if the record continues to insert, the last friends corresponding disk file 16385 is getting larger, when more than 1G, PostgreSQL will create a new disk file called 16385.1, more than 2G when PostgreSQL again segmented , create a new file 16385.2. This 1G is a block size = 8KB and blocks per segment of large relation=128k (each) jointly determined.

The definition in the source code has a comment that explains a lot of things:

  1. /* Relseg_size is the maximum number of blocks allowed in one disk file. Thus,
  2. The maximum size of a single file is relseg_size * BLCKSZ; Relations bigger
  3. than that is divided into multiple files. Relseg_size * Blcksz must be
  4. Less than your OS ' limit on file size. This is often 2 GB or 4GB in a
  5. 32-bit operating system, unless you has large file support enabled. By
  6. Default, we make the limit 1 GB to avoid any possible integer-overflow
  7. Problems within the OS. A limit smaller than necessary only means we divide
  8. A large relation into more chunks than necessary, so it seems best to err
  9. In the direction of a small limit. A power-of-2 value is recommended to
  10. Save a few cycles in MD.C, but was not absolutely required. changing
  11. Relseg_size requires an initdb. */
  12. #define Relseg_size 131072

Of course, the value of this 128K is the default value, we compile the PostgreSQL phase configure, you can specify other values through--with-segsize, but this I did not try.
Considering the segment, the real disk file name FullPath is on the horizon:
If segmented, after the name obtained by RelPath, add the segment number Segno, if the segment number is 0, then FullPath is the RelPath.

  1. Static char *
  2. _mdfd_segpath (smgrrelation Reln, Forknumber forknum, Blocknumber segno)
  3. {
  4. Char *path,
  5. *fullpath;
  6. Path = RelPath (Reln->smgr_rnode, forknum);
  7. if (Segno > 0)
  8. {
  9. /* Is sure we have enough space for the '. Segno ' * *
  10. FullPath = (char *) palloc (strlen (path) + 12);
  11. sprintf (FullPath, "%s.%u", Path, Segno);
  12. Pfree (path);
  13. }
  14. Else
  15. FullPath = path;
  16. return fullpath;
  17. }

How do you judge Segno? This is too easy, (buffertag->rnode->blocknum/relseg_size).
OK, with the corresponding relationship between the 8K block in the shared buffer and the relation disk file, we can safely tell the contents of the shared buffer. Tragedy Ah, the article wrote for a long time.
Reference documents:
1 PostgreSQL Performance Tuning
2 PostgreSQL 9.1.9 Source Code
3 Bruce Momjian's insider PostgreSQL shared memory

PostgreSQL source analysis of shared buffer and disk files

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.