Zpomalujici se Pg

[Karel Zak]

On Tue, Nov 05, 2002 at 09:33:45AM +0100, Jan Kasprzak wrote:

> : > deterministicke. Zakazat fsync() po kazde transakci? A verit ze mi
> : > system nespadne kvuli necemu jinemu? Ne, tohle budu delat az bude
> : 
> :  Je tam WAL log a ten (od verze 7.1) konzistenci garantuje. Ostatne
> :  muzes zkusit poslat -9 :-)
> : 
> 	OK, budu ti verit. Tohle ovsem uz vypada zajimaveji: Pokud si
> ta databaze dela log vsech transakci, tak proc teda musi delat to, ze si
> uchovava i stara data po updatovanych zaznamech? Pokud uz ma v logu celou
> commitnutou transakci, tak by prece nic nemelo branit tomu, aby se ta
> puvodni data prepsala, ne?

 Priznam se rovnou, ze jsem se zrovna v tomdle ve zdrojacich PostgreSQL 
 nestoural. 

 K tem starym verzim zaznamu. PostgreSQL pouziva Multi-Version
 Concurrency Control (MVCC) coz znamena, ze dve transakce pracujici se
 stejnymi daty mohou videt dvoje ruzna data. Takze ty ruzne verze
 radek musi nekde byt a jsou normalne v tabulach s identifikatorem materske
 transakce. Ty stare verze radek se tam nasledne nechavaji, protoze stale 
 jeste muze existovat transakce ktera je pouziva (je to tak proste
 rychlejsi a hlavne to nevyzaduje vubec zadny zamek). Je treba neustale 
 myslet na to, ze tech transakci tam muze byt nekolik najednou, takze 
 prepisovani starych dat neni tak jednoduche.

 To co resi WAL log je, konzistence v pripade padu a setri volani
 fsync() na jednotlive datove soubory. Fsync se vola jen na ten log.

 
Briefly, WAL's central concept is that changes to data files (where tables and
indexes reside) must be written only after those changes have been logged -
that is, when log records have been flushed to permanent storage. When we
follow this procedure, we do not need to flush data pages to disk on every
transaction commit, because we know that in the event of a crash we will be
able to recover the database using the log: any changes that have not been
applied to the data pages will first be redone from the log records (this is
roll-forward recovery, also known as REDO) and then changes made by uncommitted
transactions will be removed from the data pages (roll-backward recovery -
UNDO). 

 The first obvious benefit of using WAL is a significantly reduced number of
disk writes, since only the log file needs to be flushed to disk at the time of
transaction commit; in multiuser environments, commits of many transactions may
be accomplished with a single fsync() of the log file. Furthermore, the log
file is written sequentially, and so the cost of syncing the log is much less
than the cost of flushing the data pages. 

> 	Je nekde nejak rozumne (<5 stranek) popsano, jak vlastne Pg
> implementuje transakce, co se deje se starymi daty, jak se pozna ktera
> data uz jsou platna, co se deje s novymi daty dokud neni zapsana cela
> transakce, atd?

 Samotna dokumentace v tomdle moc informaci neobsahuje (neco malo o WAL). 
Googluj (MVCC). Ten clanek na dbsvetu co jsem na nej odkazoval nebyl spatny.
 
> 	Da se ten WAL log pouzit pro replikaci a pripadne vytvoreni
> (temer) hot standby databaze?

 Zatim ne.

    Karel

-- 
 Karel Zak  <zakkr na zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz