Обсуждение: AW: Plans for solving the VACUUM problem
> > Vadim, can you remind me what UNDO is used for? > 4. Split pg_log into small files with ability to remove old ones (which > do not hold statuses for any running transactions). They are already small (16Mb). Or do you mean even smaller ? This imposes one huge risk, that is already a pain in other db's. You need all logs of one transaction online. For a GigaByte transaction like a bulk insert this can be very inconvenient. Imho there should be some limit where you can choose whether you want to continue without the feature (no savepoint) or are automatically aborted. In any case, imho some thought should be put into this :-) Another case where this is a problem is a client that starts a tx, does one little insert or update on his private table, and then sits and waits for a day. Both cases currently impose no problem whatsoever. Andreas
Zeugswetter Andreas SB wrote: >>> Vadim, can you remind me what UNDO is used for? >> >> 4. Split pg_log into small files with ability to remove old ones (which >> do not hold statuses for any running transactions). > > > They are already small (16Mb). Or do you mean even smaller ? > This imposes one huge risk, that is already a pain in other db's. You need > all logs of one transaction online. For a GigaByte transaction like a bulk > insert this can be very inconvenient. > Imho there should be some limit where you can choose whether you want > to continue without the feature (no savepoint) or are automatically aborted. > > In any case, imho some thought should be put into this :-) > > Another case where this is a problem is a client that starts a tx, does one little > insert or update on his private table, and then sits and waits for a day. > > Both cases currently impose no problem whatsoever. Correct me if I am wrong, but both cases do present a problem currently in 7.1. The WAL log will not remove any WAL files for transactions that are still open (even after a checkpoint occurs). Thus if you do a bulk insert of gigabyte size you will require a gigabyte sized WAL directory. Also if you have a simple OLTP transaction that the user started and walked away from for his one week vacation, then no WAL log files can be deleted until that user returns from his vacation and ends his transaction. --Barry > > > Andreas > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Barry Lind wrote: > > > Zeugswetter Andreas SB wrote: > > >>> Vadim, can you remind me what UNDO is used for? > >> > >> 4. Split pg_log into small files with ability to remove old ones (which > >> do not hold statuses for any running transactions). > > > > > > They are already small (16Mb). Or do you mean even smaller ? > > This imposes one huge risk, that is already a pain in other db's. You need > > all logs of one transaction online. For a GigaByte transaction like a bulk > > insert this can be very inconvenient. > > Imho there should be some limit where you can choose whether you want > > to continue without the feature (no savepoint) or are automatically aborted. > > > > In any case, imho some thought should be put into this :-) > > > > Another case where this is a problem is a client that starts a tx, does one little > > insert or update on his private table, and then sits and waits for a day. > > > > Both cases currently impose no problem whatsoever. > > Correct me if I am wrong, but both cases do present a problem currently > in 7.1. The WAL log will not remove any WAL files for transactions that > are still open (even after a checkpoint occurs). Thus if you do a bulk > insert of gigabyte size you will require a gigabyte sized WAL > directory. Also if you have a simple OLTP transaction that the user > started and walked away from for his one week vacation, then no WAL log > files can be deleted until that user returns from his vacation and ends > his transaction. As a rule of thumb, online applications that hold open transactions during user interaction are considered to be Broken By Design (tm). So I'd slap the programmer/design team with - let's use the server boxsince it doesn't contain anything useful. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
At 04:41 PM 21-05-2001 -0400, Jan Wieck wrote: > > As a rule of thumb, online applications that hold open > transactions during user interaction are considered to be > Broken By Design (tm). So I'd slap the programmer/design > team with - let's use the server box since it doesn't contain > anything useful. > Many web applications use persistent database connections for performance reasons. I suppose it's unlikely for webapps to update a row and then sit and wait a long time for a hit, so it shouldn't affect most of them. However if long running transactions are to be aborted automatically, it could possibly cause problems with some apps out there. Worse if long running transactions are _disconnected_ (not just aborted). Regards, Link.
> > As a rule of thumb, online applications that hold open > > transactions during user interaction are considered to be > > Broken By Design (tm). So I'd slap the programmer/design > > team with - let's use the server box since it doesn't contain > > anything useful. > > Many web applications use persistent database connections for performance > reasons. Persistent connection is not the same as an OPEN transaction BTW. > I suppose it's unlikely for webapps to update a row and then sit and wait a > long time for a hit, so it shouldn't affect most of them. > > However if long running transactions are to be aborted automatically, it > could possibly cause problems with some apps out there. > > Worse if long running transactions are _disconnected_ (not just aborted). -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
Lincoln Yeoh wrote: > At 04:41 PM 21-05-2001 -0400, Jan Wieck wrote: > > > > As a rule of thumb, online applications that hold open > > transactions during user interaction are considered to be > > Broken By Design (tm). So I'd slap the programmer/design > > team with - let's use the server box since it doesn't contain > > anything useful. > > > > Many web applications use persistent database connections for performance > reasons. > > I suppose it's unlikely for webapps to update a row and then sit and wait a > long time for a hit, so it shouldn't affect most of them. > > However if long running transactions are to be aborted automatically, it > could possibly cause problems with some apps out there. > > Worse if long running transactions are _disconnected_ (not just aborted). All true, but unrelated. He was talking about open transactions holding locks while the user is off to recycle some coffee or so. A persistent database connection doesn't mean that you're holding a transaction while waitingfor the next hit. And Postgres doesn't abort transaction or disconnect because of their runtime. Then again, it'd take care for half done work aborted by the httpd because a connection loss inside of a transaction causes an implicit rollback. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com