Обсуждение: Vacuum Question
I have a Postgres application that must run 24x7. If postgres needs to be vacuumed periodically, must I take the application offline completely, or is it enough to disallow write (INSERT/UPDATE) access while allowing read access? I hope it is the latter, as I have a large amount of data and there are transactions going on all the time. Brian -- Brian Baquiran <brianb@edsamail.com> http://www.baquiran.com/ AIM: bbaquiran
[ Charset ISO-8859-1 unsupported, converting... ] > > I have a Postgres application that must run 24x7. If postgres needs to be > vacuumed periodically, must I take the application offline completely, or > is it enough to disallow write (INSERT/UPDATE) access while allowing read > access? > > I hope it is the latter, as I have a large amount of data and there are > transactions going on all the time. Applications will hang until the vacuum is completed. No changes required. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
"brianb" <brianb-pggeneral@evoserve.com> writes: > I have a Postgres application that must run 24x7. If postgres needs to be > vacuumed periodically, must I take the application offline completely, or > is it enough to disallow write (INSERT/UPDATE) access while allowing read > access? You're wasting your time to think of allowing or disallowing access; VACUUM is quite capable of locking out other accesses without help ;-) The short answer is that a table being vacuumed is locked against any other access, read or write; but only that one table. You might as well leave the rest of the database open for use. regards, tom lane
Tom Lane wrote: > > "brianb" <brianb-pggeneral@evoserve.com> writes: > > I have a Postgres application that must run 24x7. If postgres needs to be > > vacuumed periodically, must I take the application offline completely, or > > is it enough to disallow write (INSERT/UPDATE) access while allowing read > > access? > > The short answer is that a table being vacuumed is locked against any > other access, read or write; but only that one table. You might as > well leave the rest of the database open for use. I take it you don't give much credence to the previous reports that concurrently writing during vacuuming caused corruption? Was that a previous problem that's fixed now? Regards, Ed Loehr
Ed Loehr <eloehr@austin.rr.com> writes: >> The short answer is that a table being vacuumed is locked against any >> other access, read or write; but only that one table. You might as >> well leave the rest of the database open for use. > I take it you don't give much credence to the previous reports that > concurrently writing during vacuuming caused corruption? No, not really, for the simple reason that there aren't going to *be* any concurrent writes (or reads either) during a vacuum ... unless our table locking system fails, in which case we've got lots worse problems than vacuum. There are scenarios involving known (and fixed!) bugs that might be stressed by vacuum running concurrently with operations on other tables. I'm thinking particularly of shared cache invalidation: a vacuum on a system table will spit out a lot of SI messages for the tuples it moves around, and it used to be that we had bugs that could be triggered by SI buffer overflow, or more accurately by the cache flush that's used to recover from SI overflow. (Those bugs seem to be fixed in 7.0. I've successfully run parallel regression tests with the SI buffer reduced to a mere dozen messages or so. It spits out an impressive number of "SI buffer overflow" notices, but keeps going...) If you are feeling conservative you might want to vacuum just selected user tables during normal usage, and do a database-wide vacuum only at low load --- if there are any low-probability bugs still lurking in there, they're almost surely things that would be triggered by system table vacuums and not user-table vacuums. That'd probably be a good idea for performance reasons anyway, since locking a system table is much more likely to block other backends than locking any one user table. regards, tom lane
Tom Lane wrote: > > Ed Loehr <eloehr@austin.rr.com> writes: > >> The short answer is that a table being vacuumed is locked against any > >> other access, read or write; but only that one table. You might as > >> well leave the rest of the database open for use. > > > I take it you don't give much credence to the previous reports that > > concurrently writing during vacuuming caused corruption? > > No, not really, for the simple reason that there aren't going to *be* > any concurrent writes (or reads either) during a vacuum ... unless our > table locking system fails, in which case we've got lots worse problems > than vacuum. On my way to some performance testing I ran into this repeatable crash scenario. Just wondering if anyone else can reproduce the same or explain what is happening. Could very well be something peculiar/buggy about the bash/perl commands... First, start up this rather obfuscated perl/bash command which just inserts random records forever... % createdb pdb; psql -d pdb -c "create table foo(id serial, t timestamp not null default current_timestamp, f float, s varchar, i integer, l int8);"; perl -e 'for (my $j = 0; $j < 10000000; $j++ ) { print "-- Insert #$j\n"; $f = rand(2000000); $i = int(rand(2000000)) % 16000; $l = int(rand(2000000000)); $s = chr(65 + int(rand(20))) x int(rand(128)); $delim = chr(39); print "insert into foo (f,s,i,l) values ($f, $delim${s}$delim, $i, $l);\n"; }' | psql -d pdb Then, start this one in another bash window/terminal/whatever... % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; select count(*) from foo;"; sleep 3; done This seems to consistently crash after the first vacuum with the following message: NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. connection to server was lost Regards, Ed Loehr
Ed Loehr wrote: > > Tom Lane wrote: > > > > Ed Loehr <eloehr@austin.rr.com> writes: > > >> The short answer is that a table being vacuumed is locked against any > > >> other access, read or write; but only that one table. You might as > > >> well leave the rest of the database open for use. > > > > > I take it you don't give much credence to the previous reports that > > > concurrently writing during vacuuming caused corruption? > > > > No, not really, for the simple reason that there aren't going to *be* > > any concurrent writes (or reads either) during a vacuum ... unless our > > table locking system fails, in which case we've got lots worse problems > > than vacuum. > > On my way to some performance testing I ran into this repeatable crash > scenario. Just wondering if anyone else can reproduce the same or > explain what is happening. Could very well be something peculiar/buggy > about the bash/perl commands... > > First, start up this rather obfuscated perl/bash command which just > inserts random records forever... > > % createdb pdb; psql -d pdb -c "create table foo(id serial, t timestamp > not null default current_timestamp, f float, s varchar, i integer, l > int8);"; perl -e 'for (my $j = 0; $j < 10000000; $j++ ) { print "-- > Insert #$j\n"; $f = rand(2000000); $i = int(rand(2000000)) % 16000; $l = > int(rand(2000000000)); $s = chr(65 + int(rand(20))) x int(rand(128)); > $delim = chr(39); print "insert into foo (f,s,i,l) values ($f, > $delim${s}$delim, $i, $l);\n"; }' | psql -d pdb Sorry...word-wrap killed that one's cut-n-paste-ability...try this if you're interested: createdb pdb; psql -d pdb -c "create table foo( id serial, t timestamp not null default current_timestamp, f float, s varchar, i integer, l int8);"; perl -e 'for (my $j = 0; $j < 10000000; $j++ ) { print "-- Insert #$j\n"; $f = rand(2000000); $i = int(rand(2000000)) % 16000; $l = int(rand(2000000000)); $s = chr(65 + int(rand(20))) x int(rand(128)); $delim = chr(39); print "insert into foo (f,s,i,l) values \n"; print "($f, $delim${s}$delim, $i, $l);\n"; }' | psql -d pdb > > Then, start this one in another bash window/terminal/whatever... > > % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; > select count(*) from foo;"; sleep 3; done > > This seems to consistently crash after the first vacuum with the > following message: > > NOTICE: Message from PostgreSQL backend: > The Postmaster has informed me that some other backend died > abnormally and possibly corrupted shared memory. > I have rolled back the current transaction and am going to > terminate your database system connection and exit. > Please reconnect to the database system and repeat your query. > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > connection to server was lost > > Regards, > Ed Loehr
Ed Loehr wrote: > > On my way to some performance testing I ran into this repeatable crash > scenario. Just wondering if anyone else can reproduce the same or > explain what is happening. Could very well be something peculiar/buggy > about the bash/perl commands... > > First, start up this rather obfuscated perl/bash command which just > inserts random records forever... > > % createdb pdb; psql -d pdb -c "create table foo(id serial, t timestamp > not null default current_timestamp, f float, s varchar, i integer, l > int8);"; perl -e 'for (my $j = 0; $j < 10000000; $j++ ) { print "-- > Insert #$j\n"; $f = rand(2000000); $i = int(rand(2000000)) % 16000; $l = > int(rand(2000000000)); $s = chr(65 + int(rand(20))) x int(rand(128)); > $delim = chr(39); print "insert into foo (f,s,i,l) values ($f, > $delim${s}$delim, $i, $l);\n"; }' | psql -d pdb > > Then, start this one in another bash window/terminal/whatever... > > % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; > select count(*) from foo;"; sleep 3; done > > This seems to consistently crash after the first vacuum ... Oh, and the possibly relevant snippet from the server log: 20000606.01:13:27.116 [8659] query: insert into foo (f,s,i,l) values (941636.60146296, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH', 7223, 754988800); 20000606.01:13:27.164 [8679] DEBUG: --Relation pg_aggregate-- 20000606.01:13:27.164 [8679] DEBUG: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 45: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 100, MaxLen 107; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. 20000606.01:13:27.165 [8679] DEBUG: Index pg_aggregate_name_type_index: Pages 2; Tuples 45. CPU 0.00s/0.00u sec. 20000606.01:13:27.172 [8679] DEBUG: --Relation pg_ipl-- 20000606.01:13:27.172 [8679] DEBUG: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-u sing: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. 20000606.01:13:27.175 [8679] DEBUG: --Relation pg_inheritproc-- 20000606.01:13:27.176 [8679] DEBUG: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-u sing: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. 20000606.01:13:27.180 [8679] DEBUG: --Relation pg_rewrite-- 20000606.01:13:27.184 [8679] DEBUG: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 5: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 903, MaxLen 1236; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. 20000606.01:13:27.186 [8679] DEBUG: Index pg_rewrite_rulename_index: Pages 2; Tuples 5. CPU 0.00s/0.00u sec. 20000606.01:13:27.187 [8679] DEBUG: Index pg_rewrite_oid_index: Pages 2; Tuples 5. CPU 0.00s/0.00u sec. 20000606.01:13:27.188 [8659] ERROR: Function '<FF><FF><FF><FF>^Ap(int4)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts One day maybe I'll learn to include all the relevant stuff in one post...nah. Regards, Ed Loehr
Ed Loehr <eloehr@austin.rr.com> writes: >> Then, start this one in another bash window/terminal/whatever... >> >> % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; >> select count(*) from foo;"; sleep 3; done >> >> This seems to consistently crash after the first vacuum with the >> following message: This is a known gotcha that's got nothing to do with any sort of concurrency. You can't safely send a VACUUM followed by anything else in a single query string. The problem is that VACUUM forces a transaction commit, which releases all transiently allocated memory in its backend ... including the already-parsed querytrees for the rest of the query string. Oops. (cf. comment near line 560 in src/backend/tcop/postgres.c) You won't see the problem if you enter "vacuum analyze; select ..." interactively or as a script in psql, because it chops up the commands into separate query submittals. But apparently psql doesn't chop up a -c string. Non-psql frontends can expose the bug as well. It's possible that this will get cleaned up as a byproduct of the proposed rework of transaction-local memory contexts. But it's not a real high-priority problem, at least not IMHO. For now, the answer is "if it hurts, don't do it ;-)" regards, tom lane
Tom Lane wrote: > > Ed Loehr <eloehr@austin.rr.com> writes: > >> Then, start this one in another bash window/terminal/whatever... > >> > >> % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; > >> select count(*) from foo;"; sleep 3; done > >> > >> This seems to consistently crash after the first vacuum with the > >> following message: > > This is a known gotcha that's got nothing to do with any sort of > concurrency. You can't safely send a VACUUM followed by anything > else in a single query string. Well, I thought that select count(*) might've been causing a problem, so I experimented without it and found the same problem. Doesn't seem to happen with 'vacuum'or 'vacuum analyze foo', only 'vacuum analyze'... Regards, Ed Loehr
Ed Loehr <eloehr@austin.rr.com> writes: >>>>> % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; >>>>> select count(*) from foo;"; sleep 3; done >>>>> >>>>> This seems to consistently crash after the first vacuum with the >>>>> following message: >> >> This is a known gotcha that's got nothing to do with any sort of >> concurrency. You can't safely send a VACUUM followed by anything >> else in a single query string. > Well, I thought that select count(*) might've been causing a problem, so > I experimented without it and found the same problem. Doesn't seem to > happen with 'vacuum'or 'vacuum analyze foo', only 'vacuum analyze'... I can't reproduce any problem with just a "vacuum" (with or without analyze) and no following command. I did, however, notice that very occasionally the inserting process would spit out weird error messages like "Function '(int4)' does not exist" and "init_fcache: null probin for procedure 481". This seems to be due to VACUUM (on system tables) causing syscache entries to be flushed at unexpected times. I've committed patches for the two cases I observed, but there may be more lurking... regards, tom lane
Tom Lane wrote: > > Ed Loehr <eloehr@austin.rr.com> writes: > >>>>> % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; > >>>>> select count(*) from foo;"; sleep 3; done > >>>>> > >>>>> This seems to consistently crash after the first vacuum with the > >>>>> following message: > >> > >> This is a known gotcha that's got nothing to do with any sort of > >> concurrency. You can't safely send a VACUUM followed by anything > >> else in a single query string. > > > Well, I thought that select count(*) might've been causing a problem, so > > I experimented without it and found the same problem. Doesn't seem to > > happen with 'vacuum'or 'vacuum analyze foo', only 'vacuum analyze'... > > I can't reproduce any problem with just a "vacuum" (with or without > analyze) and no following command. > > I did, however, notice that very occasionally the inserting process > would spit out weird error messages like "Function '(int4)' does not > exist" and null probin for procedure 481". This seems > to be due to VACUUM (on system tables) causing syscache entries to be > flushed at unexpected times. I've committed patches for the two cases > I observed, but there may be more lurking... Yes, I was getting a similar Function error message, though I never saw the init_fcache message. And it appeared the backend would crash coincidentally with the function error message. If the patch can be applied to 7.0 (beta3), and you don't mind posting it, I could test it here... Regards, Ed Loehr
Ed Loehr <eloehr@austin.rr.com> writes: > Tom Lane wrote: >> I can't reproduce any problem with just a "vacuum" (with or without >> analyze) and no following command. >> >> I did, however, notice that very occasionally the inserting process >> would spit out weird error messages like "Function '(int4)' does not >> exist" and null probin for procedure 481". This seems >> to be due to VACUUM (on system tables) causing syscache entries to be >> flushed at unexpected times. I've committed patches for the two cases >> I observed, but there may be more lurking... > Yes, I was getting a similar Function error message, though I never saw > the init_fcache message. And it appeared the backend would crash > coincidentally with the function error message. If the patch can be > applied to 7.0 (beta3), and you don't mind posting it, I could test it > here... Hmm, I only saw error messages, no crashes --- but I suppose a crash is possible, since the root of the problem here is a dangling pointer. Patches for 7.0.2 are attached. Not sure if they will apply perfectly cleanly to beta3, but you should be able to make the right mods by hand if patch doesn't cope... regards, tom lane *** src/backend/parser/parse_type.c.orig Tue May 30 00:24:49 2000 --- src/backend/parser/parse_type.c Tue Jun 6 11:41:08 2000 *************** *** 48,54 **** return NULL; } typetuple = (Form_pg_type) GETSTRUCT(tup); ! return NameStr(typetuple->typname); } /* return a Type structure, given a type id */ --- 48,55 ---- return NULL; } typetuple = (Form_pg_type) GETSTRUCT(tup); ! /* pstrdup here because result may need to outlive the syscache entry */ ! return pstrdup(NameStr(typetuple->typname)); } /* return a Type structure, given a type id */ *************** *** 119,125 **** Form_pg_type typ; typ = (Form_pg_type) GETSTRUCT(t); ! return NameStr(typ->typname); } /* given a type, return its typetype ('c' for 'c'atalog types) */ --- 120,127 ---- Form_pg_type typ; typ = (Form_pg_type) GETSTRUCT(t); ! /* pstrdup here because result may need to outlive the syscache entry */ ! return pstrdup(NameStr(typ->typname)); } /* given a type, return its typetype ('c' for 'c'atalog types) */ *** src/backend/utils/cache/fcache.c~ Wed Apr 12 13:15:53 2000 --- src/backend/utils/cache/fcache.c Tue Jun 6 13:39:03 2000 *************** *** 14,19 **** --- 14,20 ---- */ #include "postgres.h" + #include "access/heapam.h" #include "catalog/pg_language.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" *************** *** 89,97 **** if (!use_syscache) elog(ERROR, "what the ????, init the fcache without the catalogs?"); ! procedureTuple = SearchSysCacheTuple(PROCOID, ! ObjectIdGetDatum(foid), ! 0, 0, 0); if (!HeapTupleIsValid(procedureTuple)) elog(ERROR, "init_fcache: Cache lookup failed for procedure %u", --- 90,98 ---- if (!use_syscache) elog(ERROR, "what the ????, init the fcache without the catalogs?"); ! procedureTuple = SearchSysCacheTupleCopy(PROCOID, ! ObjectIdGetDatum(foid), ! 0, 0, 0); if (!HeapTupleIsValid(procedureTuple)) elog(ERROR, "init_fcache: Cache lookup failed for procedure %u", *************** *** 258,263 **** --- 259,266 ---- } else retval->func.fn_addr = (func_ptr) NULL; + + heap_freetuple(procedureTuple); return retval; }