Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "PG architecture". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations. I hope you can read carefully and learn something!

##pg structure--logical structure--database instance usually refers to database cluster, a single instance manages database collection--a database cluster contains users and databases, and a separate user can be specified for each database. Each database contains schemas (namespace), which is public by default. Each schema contains tables, indexes, views, sequences, etc. -- Physical structures-data files, parameter files, control files, database running logs, and pre-write logs-catalog files can be viewed by For details, please refer to Postgresql Practical Practice on page 98 tree -L 1 -d /pgdata/10/data--control file location [postgres@pgtest global]$ pwd/pgdata/data/global[postgres@pgtest global]$ ls -l pg_control -rw----- 1 postgres 8192 Dec 7 04: pg_control--data file layout--oid, all database objects have their own object identifiers (oids) for internal management, they are unsigned 4-byte integers, such as select oid,datname from pg_database;select oid,relname,relkind from pg_class;--table space, the largest logical storage unit--default two table spaces,pg_global table space, directory is global, save system tables; The physical file location of pg_default table space is in base directory, which is the default table space of template0 and template1 database. --To create table space, you need to create directory first. Table space can solve the problem of insufficient disk space in the future, allocate disks with different performance, and improve database performance. mkdir -p /pgdata/10/mytblspccreate table space myspc location '/pgdata/10/mytblspc'; create table t(id int4) tablespace myspc;--data file name, for table size exceeds 1g, pg will automatically split multiple files, oid. sequence number, the real management table file is pg_class relfilenode--view table size select pg_size_pretty (pg_relation_size ('tb1 '::regclass));--table file internal structure--pg, blocks saved in disk are called Pages, blocks in memory are called Buffer, tables and indexes are called Relations, rows are called Tuples. Data read and write in Page as the smallest unit, Page default 8KB##process structure--daemon process and service process--postmaster process main responsibility database start and stop listening client connection for each client connection fork separate postgres service process repair when service process error management data file management related to database operation auxiliary process--auxiliary process background writer:bgwriter process, search shared buffer pool to find modified pages, and flush them from shared buffer pool autovacuum launcher: automatic cleanup garbage collection process WAL writer: Periodically write WAL data on WAL buffer to disk statistics collector: statistics collection process logging collector: log process, write messages or error information to log archiver:WAL archive process checkpointer: checkpoint process ##memory structure--local memory is mainly used by backend process, mainly three parts work_mem: maintenance operation when using order by or distinct, such as VACUUM REINDEX CREATE INDEX operation temp_buffers: Temporary table-related operations use--shared memory, allocated at server startup, shared by all back-end processes. Shared buffer pool :PostgreSQL loads table and index pages from persistent storage here and manipulates them directly. WAL buffer: Buffer before WAL file persistence CommitLog buffer:PostgreSQL saves the state of the transaction in CommitLog and retains it in a shared memory buffer, used throughout the transaction.

"PG architecture is what kind of content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.