{"id":107,"date":"2023-09-24T22:04:26","date_gmt":"2023-09-24T19:04:26","guid":{"rendered":"http:\/\/pgdataeraold.local\/?p=107"},"modified":"2023-09-24T22:04:26","modified_gmt":"2023-09-24T19:04:26","slug":"memory-structure","status":"publish","type":"post","link":"https:\/\/pgdataera.com\/en\/memory-structure\/","title":{"rendered":"Postgresql Memory Structure"},"content":{"rendered":"
\n
\"\"<\/figure><\/div>\n\n\n
\n\n\n\n

When we compare both I\/O and OIPS performances, RAM is many times faster than<\/span> <\/span>SSD<\/span> <\/span>disk.<\/span> <\/span>Magnetic<\/span> <\/span>disks<\/span> <\/span>are<\/span> <\/span>also<\/span> <\/span>slower<\/span> <\/span>than<\/span> <\/span>SSD<\/span> <\/span>disks.<\/span> <\/span>For<\/span> <\/span>performance,<\/span> <\/span>it<\/span> <\/span>is<\/span> <\/span>desirable<\/span> <\/span>to<\/span> <\/span>perform<\/span> <\/span>I\/O operations<\/span> <\/span>on<\/span> <\/span>memory<\/span> <\/span>as<\/span> <\/span>much as<\/span> <\/span>possible.<\/span> Memory<\/span> <\/span>structures<\/span> <\/span>in<\/span> <\/span>Postgresql<\/span> <\/span>have<\/span> <\/span>different<\/span> <\/span>uses<\/span> <\/span>and<\/span> <\/span>methods.<\/span> <\/p>\n\n\n\n

For example, one part (memory structure) is used to hold code, while another part is
used to hold data blocks.
Some memory areas are shared for all users, while others are private to the user.<\/mark><\/p>\n\n\n\n

Shared Memory:<\/mark><\/strong> When the Postgresql instance is opened, it takes the memory allocated for it from the system.<\/p>\n\n\n\n

The amount of memory it will take is determined by shared_buffer, wal_buffer<\/code>, etc. parameters. Some of these areas are also divided into subsections within themselves.<\/p>\n\n\n\n

Shared memory structures are shared with all users and backend processes.<\/p>\n\n\n\n

Per Backend Memory (local memory area):<\/mark><\/strong> It is used for SQL query operations. Each backend process takes this memory space from the system for its own use. It is not shared. It is divided into subsections within itself.<\/p>\n\n\n\n

It will take memory equal to the product of (session number) * (Backend Memory)<\/mark>
from the system.
So, care should be taken when calculating the number of sessions and Backend
memory areas. You can find details in the following topics.<\/p>\n\n\n\n

RAM: Random Access Memory
I\/O: Read write
IOPS: Input\/output operations per second. The number of operations that can be
performed per second.
(The speed of copying a 1 GB image file and a folder containing 10240 1 KB files are
different.)<\/p>\n\n\n\n

Shared Memory<\/h2>\n\n\n\n

Shared Buffer<\/h3>\n\n\n
\n
\"\"<\/figure><\/div>\n\n\n
a<\/strong><\/mark>lter system set shared_buffers ='24G<\/strong><\/mark>B<\/mark><\/strong>';<\/strong><\/mark><\/span><\/code><\/pre>\n\n\n\n
\n\n\n\n
    \n
  • It is shared. It contains data blocks. <\/li>\n\n\n\n
  • It works integrated with OS Cache.
    The shared buffer area is shared by all users and background processes. <\/li>\n\n\n\n
  • The default value is 128MB.<\/li>\n\n\n\n
  • If the unit (MB, GB) is not given when setting shared_buffers, it will perceive the given
    number as the number of blocks and multiply it by the block size (size) and configure it
    in KB.<\/li>\n<\/ul>\n\n\n\n
    Example: alter system set shared_buffers=100;<\/code>
    Since the block size is 8KB (default value), it assigns shared_buffers=800KB.<\/p>\n\n\n\n
    In order for the change made in shared_buffers to take effect, it is necessary to close
    and open the instance.<\/mark><\/p>\n\n\n\n
    The recommended value for the shared buffer is 1\/4 of the RAM in the system.
    In some high-load instances, up to 40% of RAM can be assigned to shared buffer,
    since postgresql works as an integrated part of the operating system cache (OS
    Cache), it does not mean that more than 40% of memory will have a positive effect on
    performance.<\/p>\n\n\n\n
    Increasing the shared suffer will increase the size of modified (update\/delete<\/code>) and\/
    or new (insert<\/code>) data (increasing DML capacity). The max_wal_size<\/strong><\/code> value should also
    be increased in order to extend the writing process of memory to disk over a longer
    period of time.<\/p>\n\n\n\n
    Note: The operating system has a dependency on the kernel settings. If you get an
    error while updating the parameter values, it may be related to the kernel settings of
    your operating system.<\/mark>
    For detailed information about buffer cache usage, pg_buffercache extension<\/code> can
    be installed.<\/p>\n\n\n\n
    Shared Buffer and OS Cache<\/h3>\n\n\n
    \n
    \"\"<\/figure><\/div>\n\n\n
    \n\n\n\n
    When a query is executed;<\/p>\n\n\n\n
      \n
    1. The backend process first checks the shared buffer if it is available, logical I\/O takes
      place.<\/li>\n\n\n\n
    2. If it is not in the buffer cache, the OS cache is checked. If it is found in the OS cache,
      logical I\/O takes place.<\/li>\n\n\n\n
    3. If they are not in the OS cache, the requested blocks are read from the disk and
      physical I\/O is done, and the cost of physical I\/O is high.<\/li>\n<\/ol>\n\n\n\n
      You do not want to make settings in Postgresql, such as giving most of the RAM to the
      buffer cache and ignoring the OS cache, because it will reduce performance at another
      point.
      The purpose of both cache areas is to keep the data with intensive I\/O in memory and
      to reduce the I\/O operations on the disk as much as possible.<\/p>\n\n\n\n
      Continuous read and write operations are performed in the pg_xact (before 9.6,
      pg_clog)<\/code> folder, where the commit log information is kept. Therefore, the operating
      system wants to keep the files in the pg_xact<\/code> folder in memory (OS Cache). In this
      case, keeping the share of OS cache high also contributes to performance.<\/p>\n\n\n\n
      Postgresql is better at memory (shared buffer) management than OS Cache. Postgresql
      assigns values in the range of 0-5 to the blocks, based on the processing frequency.
      It gives high value to blocks with more transactions and low value to blocks with
      a smaller number of transactions. When it is necessary to free up memory, blocks
      with low values are removed from memory. This way of working is called the clock
      sweep algorithm<\/strong><\/code>.
      OS Cache usually uses various methods of LRU (Least Recently Used) as an algorithm.
      Blocks to be cached are given a chance or two. It does not go into a detailed analysis.
      That\u2019s why postgresql\u2019s shared buffer management is better than OS Cache.<\/p>\n\n\n\n
      WAL Buffer<\/h3>\n\n\n
      \n
      \"\"<\/figure><\/div>\n\n\n
      alter system set wal_buffer = '64MB' ;<\/mark><\/strong><\/code><\/pre>\n\n\n\n
      \n\n\n\n
        \n
      • It is shared, it keeps track of changes made in the database.<\/li>\n\n\n\n
      • It keeps the information necessary to redo the changes made with DML and DDL.<\/li>\n\n\n\n
      • Postgresql supports the write-ahead log mechanism, which is necessary to ensure data consistency.<\/mark><\/li>\n\n\n\n
      • WAL (transaction log) keeps the metadata information of the changes made in the database.<\/li>\n\n\n\n
      • When necessary, it regenerates (REDO) or modifies the data using the records here.<\/li>\n\n\n\n
      • WAL mechanism processes the committed data to disk even in case of system errors (crash, power outage, hardware failure,  etc.) and prevents data loss and inconsistency by rolling back the uncommitted data.<\/li>\n\n\n\n
      • WAL Buffer is the buffer area, where WAL data that has not yet been written to the disk is kept temporarily.<\/li>\n\n\n\n
      • It is shared, and accessible by all background server and backend processes. <\/li>\n\n\n\n
      • In each commit operation, the data in the WAL buffer area is written to the disk.Even MB level increases can provide performance boosts.<\/li>\n\n\n\n
      • The default is \u201c-1\u201d, which is the automatic tuning setting. Takes 1 out of 32 of shared_buffers. (shared_buffers\/32).  For example, if shared_buffers=512MB<\/code>, it takes wal_buffer=16MB<\/code>.<\/li>\n\n\n\n
      • It cannot be less than 64KB and larger than the WAL segment size. <\/li>\n\n\n\n
      • It is a static parameter, the instance must be closed and opened for the change to take effect.<\/mark><\/li>\n<\/ul>\n\n\n\n
        Note: In order for the WAL mechanism to work properly, your system and database must be adjusted according to the best practice rules.<\/mark><\/p>\n\n\n\n
        Clog Buffer<\/h3>\n\n\n
        \n
        \"\"<\/figure><\/div>\n\n\n
        \n\n\n\n
          \n
        • It is the shared memory area where the commit logs are kept.<\/li>\n\n\n\n
        • A transaction has 4 states. The commit log keeps the below statuses.\n
            \n
          • in-progress<\/li>\n\n\n\n
          • committed<\/li>\n\n\n\n
          • aborted<\/li>\n\n\n\n
          • sub_committed<\/li>\n<\/ul>\n<\/li>\n\n\n\n
          • Accessible by all background server and backend processes.
            It is part of the concurrent access mechanism (Concurrency Control mechanism).<\/li>\n\n\n\n
          • Although there is no parameter that can change its size, it can be increased by
            compiling from the source code (CLOGShmemBuffers()<\/code>) or patching during the first
            installation.<\/li>\n\n\n\n
          • Writes to the pg_xact<\/code> directory. (Before version 10 –> pg_clog<\/code>).<\/li>\n<\/ul>\n\n\n\n
            Lock Space<\/h3>\n\n\n
            \n
            \"\"<\/figure><\/div>\n\n\n
            \n\n\n\n
            It is shared. It has no parameters that can be changed directly.
            It is the memory area where more than average locks are stored. This lock is shared by
            the background and backend user processes.
            Changes in max_locks_per_transaction<\/code> and max_pred_locks_per_transaction<\/code> parameters also affect lock space<\/strong>.<\/p>\n\n\n\n
            Per Backend Memory<\/h2>\n\n\n\n
            Work Memory<\/h3>\n\n\n
            \n
            \"\"<\/figure><\/div>\n\n\n
            \n\n\n\n