Piggybacking vacuum I/O
in [PgSql]
|
From: Heikki Linnakangas on 25 Jan 2007 06:52 Pavan Deolasee wrote: > Another simpler solution for VACUUM would be to read the entire CLOG file > in local memory. Most of the transaction status queries can be satisfied > from > this local copy and the normal CLOG is consulted only when the status is > unknown (TRANSACTION_STATUS_IN_PROGRESS) The clog is only for finished (committed/aborted/crashed) transactions. If a transaction is in progress, the clog is never consulted. Anyway, that'd only be reasonable for vacuums, and I'm actually more worried if we had normal backends thrashing the clog buffers. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: Bruce Momjian on 25 Jan 2007 18:03 Is there a TODO here? --------------------------------------------------------------------------- Heikki Linnakangas wrote: > Pavan Deolasee wrote: > > Another simpler solution for VACUUM would be to read the entire CLOG file > > in local memory. Most of the transaction status queries can be satisfied > > from > > this local copy and the normal CLOG is consulted only when the status is > > unknown (TRANSACTION_STATUS_IN_PROGRESS) > > The clog is only for finished (committed/aborted/crashed) transactions. > If a transaction is in progress, the clog is never consulted. Anyway, > that'd only be reasonable for vacuums, and I'm actually more worried if > we had normal backends thrashing the clog buffers. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian bruce(a)momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
From: Heikki Linnakangas on 26 Jan 2007 04:31 I'd like to see still more evidence that it's a problem before we start changing that piece of code. It has served us well for years. Bruce Momjian wrote: > Is there a TODO here? > > --------------------------------------------------------------------------- > > Heikki Linnakangas wrote: >> Pavan Deolasee wrote: >>> Another simpler solution for VACUUM would be to read the entire CLOG file >>> in local memory. Most of the transaction status queries can be satisfied >>> from >>> this local copy and the normal CLOG is consulted only when the status is >>> unknown (TRANSACTION_STATUS_IN_PROGRESS) >> The clog is only for finished (committed/aborted/crashed) transactions. >> If a transaction is in progress, the clog is never consulted. Anyway, >> that'd only be reasonable for vacuums, and I'm actually more worried if >> we had normal backends thrashing the clog buffers. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
From: Alvaro Herrera on 26 Jan 2007 09:14 Heikki Linnakangas wrote: > I'd like to see still more evidence that it's a problem before we start > changing that piece of code. It has served us well for years. So the TODO could be "investigate whether caching pg_clog and/or pg_subtrans in local memory can be useful for vacuum performance". > Bruce Momjian wrote: > >Is there a TODO here? > > > >--------------------------------------------------------------------------- > > > >Heikki Linnakangas wrote: > >>Pavan Deolasee wrote: > >>>Another simpler solution for VACUUM would be to read the entire CLOG file > >>>in local memory. Most of the transaction status queries can be satisfied > >>>from > >>>this local copy and the normal CLOG is consulted only when the status is > >>>unknown (TRANSACTION_STATUS_IN_PROGRESS) > >>The clog is only for finished (committed/aborted/crashed) transactions. > >>If a transaction is in progress, the clog is never consulted. Anyway, > >>that'd only be reasonable for vacuums, and I'm actually more worried if > >>we had normal backends thrashing the clog buffers. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
From: "Pavan Deolasee" on 26 Jan 2007 11:27
On 1/26/07, Alvaro Herrera <alvherre(a)commandprompt.com> wrote: > > Heikki Linnakangas wrote: > > I'd like to see still more evidence that it's a problem before we start > > changing that piece of code. It has served us well for years. > > So the TODO could be "investigate whether caching pg_clog and/or > pg_subtrans in local memory can be useful for vacuum performance". > > As Heikki suggested, we should also investigate the same for normal backends as well. It would also be interesting to investigate whether early setting of hint bits can reduce subsequent writes of blocks. A typical case would be a large table being updated heavily for a while, followed by SELECT queries. The SELECT queries would set hint bits for the previously UPDATEd tuples (old and new versions) and thus cause subsequent writes of those blocks for what could have been read-only queries. Thanks, Pavan EnterpriseDB http://www.enterprisedb.com |