Обсуждение: WAL question
Hello. Lets's imagine a sequence in time: (1) Some transactions commit data (simple table inserts/updates). (2) I get data from a table using select. (3) A CHECKPOINT occurs, data from pg_xlog is copied to actual table data files. Questions: Where does PostgreSQL read data during (2) process? For example, I've got some old data and recently committed data there(but not passed CHECKPOINT). Would it scan both pg_xlog(for committed data that dind't pass CHECKPOINT) and table data files? What happens if an index could be used? Evgeny.
Evgeny Gridasov wrote: > Hello. > > Lets's imagine a sequence in time: > (1) Some transactions commit data (simple table inserts/updates). > (2) I get data from a table using select. > (3) A CHECKPOINT occurs, data from pg_xlog is copied to actual table data files. > > Questions: > > Where does PostgreSQL read data during (2) process? Shared buffers, which means that data that's not in memory is read from disk, and data that is in memory is read from there. A checkpoint evicts stuff from shared buffers to disk. > Would it scan both pg_xlog(for committed data that dind't pass > CHECKPOINT) and table data files? pg_xlog is not read except in crash recovery. > What happens if an index could be used? Same thing. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Hello. So, what happens if I set checkpoint_segments and checkpoint_timeout very high, and, shared_buffers to a very low value? Will PostgreSQL force a checkpoint when it has not enough memory in shared_buffers for some new "un-checkpointed" data ? On Fri, 12 Jan 2007 10:49:04 -0300 Alvaro Herrera <alvherre@commandprompt.com> wrote: > Evgeny Gridasov wrote: > > Hello. > > > > Lets's imagine a sequence in time: > > (1) Some transactions commit data (simple table inserts/updates). > > (2) I get data from a table using select. > > (3) A CHECKPOINT occurs, data from pg_xlog is copied to actual table data files. > > > > Questions: > > > > Where does PostgreSQL read data during (2) process? > > Shared buffers, which means that data that's not in memory is read from > disk, and data that is in memory is read from there. A checkpoint > evicts stuff from shared buffers to disk. > > > Would it scan both pg_xlog(for committed data that dind't pass > > CHECKPOINT) and table data files? > > pg_xlog is not read except in crash recovery. > > > What happens if an index could be used? > > Same thing. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >
Evgeny Gridasov wrote: > Hello. > > So, what happens if I set checkpoint_segments and checkpoint_timeout very high, > and, shared_buffers to a very low value? > Will PostgreSQL force a checkpoint when it has not enough memory in shared_buffers for some new "un-checkpointed" data? It won't force a checkpoint -- it will release memory by causing individual buffer evictions. (What checkpoint does is evicting all buffers in one go, but backends can cause a buffer to be evicted if they need it for something else). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.