Hi All, Thank you for the prompt reply on this.
Please find the output of the top command and the process availabe. Could explain what logger process is here for top - 12:41:57 up 17:51, 3 users, load average: 0.00, 0.04, 0.01 Tasks: 141 total, 1 running, 139 sleeping, 0 stopped, 1 zombie Cpu(s): 0.3%us, 1.4%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 1417256k total, 1300888k used, 116368k free, 41468k buffers Swap: 2097144k total, 22156k used, 2074988k free, 1017980k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 23466 postgres 15 0 4664 1508 1228 S 0.0 0.1 0:00.10 -bash 25983 postgres 15 0 44620 3032 2680 S 0.0 0.2 0:00.43 /usr/local/pgsql/bin/postgres 25984 postgres 18 0 8608 632 296 S 0.0 0.0 0:00.04 postgres: logger process 25986 postgres 15 0 44604 1656 1292 S 0.0 0.1 0:00.10 postgres: writer process 25987 postgres 15 0 44604 932 588 S 0.0 0.1 0:00.11 postgres: wal writer process 25988 postgres 15 0 44756 1232 680 S 0.0 0.1 0:00.17 postgres: autovacuum launcher process 25989 postgres 18 0 8604 648 304 S 0.0 0.0 0:00.04 postgres: archiver process 25990 postgres 15 0 8740 900 408 S 0.0 0.1 0:00.31 postgres: stats collector process 25993 postgres 15 0 4964 1464 1196 S 0.0 0.1 0:00.17 psql 25994 postgres 15 0 46264 6212 4672 S 0.0 0.4 0:00.23 postgres: postgres postgres [local] idle Regards Raghavendra On Fri, Mar 26, 2010 at 9:22 AM, Scott Marlowe <scott.marl...@gmail.com>wrote: > On Thu, Mar 25, 2010 at 9:03 PM, Tadipathri Raghu <traghu....@gmail.com> > wrote: > > Hi Scott, Thomas, > > > > Thank you for the update. > > > >> > >> >> Oracle uses a completely different implementation of MVCC > architecture. > >> >> It > >> >> overwrites the data and then uses rollback segments to provide > >> >> 'previous > >> >> versions' to running transactions etc. > >> >> > >> >> PostgreSQL does not overwrite the data - it just creates a copy of > the > >> >> row > >> >> and then decides which version should each session see (depending on > >> >> the > >> >> transaction IDs etc.). So it does not need to do rollbacks the way > >> >> Oracle > >> >> does, but it has to remove stale copies of the rows (such that no > >> >> running > >> >> transaction can see) - that's why there is VACUUM. > >> > > >> > > >> > Here, if you have issued a command pg_start_backup() at that time the > >> > cluster is freezed, and if any transaction takes place before the > >> > pg_stop_backup() issued at that time where the transaction data will > be > >> > kept > >> > if the undo's are not there. > >> > >> > >> What do you mean by 'freezed'? The cluster operates normally, the > >> pg_start_backup() just creates a backup label (and performs a > checkpoint), > >> but that's not a problem. OK, there could be a performance decrease > >> because of full page writes, but the data will be processed as if there > is > >> no backup running. PostgreSQL does not need the checkpoints to perform > >> backup. > > > >> The 'UNDO' data is just kept in the main data files. Then, based on > what > >> your xid is, and the xmin / xmax on each row, it's either visible or > not. > >> That's what vacuum does, clean up the rows that are for 'undo' and can > >> never been seen anymore. The REDO itself is kept in the transaction > logs. > > > > > >> > >> Like Thomas says, the pg_start_backup() just creates a label that tells > >> the restoring database what pg_xlog record to start with when you > 'restore' > >> your data. The 'UNDO' type of data (just the old rows actually) is just > >> kept in the main table until vacuum nukes 'em. > > > > --Scott > > Q1. Does vacuum process come into existence even you turn off the > > autovacuum.? What is the main work for vacuum process, to the clean the > > buffers or work only when autovacuum in on and to clean up the rows that > are > > 'undo' > > 1a: Yes, if you are approaching transaction id wraparound (txid wrap) > then autovacuum will crank up a vacuum to fix that situation whether > you like it or not. The alternative is db shutdown. > 1b: The main job of vacuum is to free dead tuples, which postgresql > accumulates as it updates or deletes tuples. When you have several old > versions of a tuple to collect, vacuum recycles the space and makes it > available to other updates to now use instead of allocating on the end > of the relation. > > > Q2. Do you mean to say there is a vacuum process which will clean up the > > buffers that are for 'undo'? > > No buffers for undo. It's all stored on disc, ready for instant > access. Try it on a test db. begin; load a table with 1M rows; > rollback; sub second response. A million changes just disappeared > instantly. And now you've got 1M dead tuples in that table. Vacuum's > job is to make them available for re-use. But they're all in one big > consecutive file so the performance is actually pretty good as the > table repopulates, especially if it'll get back up to 1M records again > in the near future. As long as it got vacuumed. > > > Q3. Fine, if the vacuum is off then your data files will hold the undo > data > > also in it? > > Yep. Until a forced vacuum due to txid wrap occurs. >
