Piggybacking vacuum I/O
in [PgSql]
|
From: Alvaro Herrera on 26 Jan 2007 11:35 Pavan Deolasee wrote: > 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. Maybe. An idea that comes to mind is to never cache the latest page, since it'll most likely result in extra reads anyway because there'll be a lot of IN_PROGRESS transactions. Problem to solve: how much memory to dedicate to this? Could we mmap() portions of the pg_clog segment, so that the page could be shared across backends instead of allocating them for each? > 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. This has been suggested before, but I don't see how this could work. How does the UPDATE transaction go back to the pages it wrote to update the hint bits, _after_ it committed? Maybe have the bgwriter update hint bits as it evicts pages out of the cache? It could result in pg_clog read traffic for each page that needs eviction; not such a hot idea. I don't see how this is related to the above proposal though. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
From: "Pavan Deolasee" on 26 Jan 2007 11:47 On 1/26/07, Alvaro Herrera <alvherre(a)commandprompt.com> wrote: > > > Maybe have the bgwriter update hint bits as it evicts pages out of the > cache? It could result in pg_clog read traffic for each page that needs > eviction; not such a hot idea. I thought once we enhance clog so that there are no clog reads, bgwriter would be able to update hint bits without getting into any deadlock with pg_clog read. May be we can have this as a seperate TODO Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
From: Tom Lane on 26 Jan 2007 11:47 Heikki Linnakangas <heikki(a)enterprisedb.com> writes: > 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. What I see here is mostly evidence suggesting that we should consider raising NUM_CLOG_BUFFERS, rather than anything more invasive. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
From: Tom Lane on 26 Jan 2007 12:38 Alvaro Herrera <alvherre(a)commandprompt.com> writes: > Pavan Deolasee wrote: >> 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. > This has been suggested before, but I don't see how this could work. > How does the UPDATE transaction go back to the pages it wrote to update > the hint bits, _after_ it committed? I think what he's suggesting is deliberately not updating the hint bits during a SELECT ... but that's surely misguided. If you don't set the hint bit after discovering the transaction commit state, then the next visitor of the tuple will have to repeat the clog lookup, meaning that any such policy greatly increases clog read traffic and contention. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: "Pavan Deolasee" on 26 Jan 2007 13:44
On 1/26/07, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > > > I think what he's suggesting is deliberately not updating the hint bits > during a SELECT ... No, I was suggesting doing it in bgwriter so that we may not need to that during a SELECT. Of course, we need to investigate more and have numbers to prove the need. Also you have already expressed concerns that doing so in bgwriter is deadlock prone. So there is certainly more work needed for any such scheme to work. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com |