From: | Tadipathri Raghu <traghu(dot)dba(at)gmail(dot)com> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | Scott Mead <scott(dot)lists(at)enterprisedb(dot)com>, tv(at)fuzzy(dot)cz, pgsql-general(at)postgresql(dot)org |
Subject: | Re: How many Mandatory Process are there in Postgres |
Date: | 2010-03-28 07:14:41 |
Message-ID: | 645d9d71003280014p69cf27fr8366e34c076fb621@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)marlowe(at)gmail(dot)com>wrote:
> On Thu, Mar 25, 2010 at 9:03 PM, Tadipathri Raghu <traghu(dot)dba(at)gmail(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tadipathri Raghu | 2010-03-28 08:05:48 | Why index occupy less amount of space than the table with same structure. |
Previous Message | Faheem Mitha | 2010-03-28 06:08:45 | simultaneously reducing both memory usage and runtime for a query |