Thread: Vacuumdb Fails: Huge Tuple
Hey list, After some downtime of my site while completing rigorous database maintenance, I wanted to make sure all the databases were fully vacuumed and analyzed. I do run autovacuum, but since I made several significant changes, I wanted to force a vacuum before I brought my site back online. Here's what happened: $ vacuumdb --all --full --analyze --no-password vacuumdb: vacuuming database "postgres" vacuumdb: vacuuming database "web_main" vacuumdb: vacuuming of database "web_main" failed: ERROR: huge tuple I was told on IRC that this may be related to a GIN index. I do have several GIN indexes used for full-text searching. I tried googling, but nothing much came up. Thanks for the help.
On Thu, Oct 1, 2009 at 3:10 PM, APseudoUtopia <apseudoutopia@gmail.com> wrote: > Hey list, > > After some downtime of my site while completing rigorous database > maintenance, I wanted to make sure all the databases were fully > vacuumed and analyzed. I do run autovacuum, but since I made several > significant changes, I wanted to force a vacuum before I brought my > site back online. > > Here's what happened: > > $ vacuumdb --all --full --analyze --no-password > vacuumdb: vacuuming database "postgres" > vacuumdb: vacuuming database "web_main" > vacuumdb: vacuuming of database "web_main" failed: ERROR: huge tuple > > I was told on IRC that this may be related to a GIN index. I do have > several GIN indexes used for full-text searching. I tried googling, > but nothing much came up. > > Thanks for the help. > Sorry, I failed to mention: PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 32-bit
On Thu, Oct 1, 2009 at 1:12 PM, APseudoUtopia <apseudoutopia@gmail.com> wrote: > Sorry, I failed to mention: > > PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) > 4.2.1 20070719 [FreeBSD], 32-bit Have you tried updating to 8.4.1 to see if that fixes the problem?
On Thu, Oct 1, 2009 at 4:21 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Thu, Oct 1, 2009 at 1:12 PM, APseudoUtopia <apseudoutopia@gmail.com> wrote: > >> Sorry, I failed to mention: >> >> PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) >> 4.2.1 20070719 [FreeBSD], 32-bit > > Have you tried updating to 8.4.1 to see if that fixes the problem? > I have not. The FreeBSD port for postgres has not yet been updated to 8.4.1, so I'm unable to upgrade (I'd strongly prefer to use the ports system rather than manually compiling). As soon as the port is updated, I'm going to upgrade.
On Thu, Oct 1, 2009 at 2:27 PM, APseudoUtopia <apseudoutopia@gmail.com> wrote: > On Thu, Oct 1, 2009 at 4:21 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> On Thu, Oct 1, 2009 at 1:12 PM, APseudoUtopia <apseudoutopia@gmail.com> wrote: >> >>> Sorry, I failed to mention: >>> >>> PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) >>> 4.2.1 20070719 [FreeBSD], 32-bit >> >> Have you tried updating to 8.4.1 to see if that fixes the problem? >> > > I have not. The FreeBSD port for postgres has not yet been updated to > 8.4.1, so I'm unable to upgrade (I'd strongly prefer to use the ports > system rather than manually compiling). > > As soon as the port is updated, I'm going to upgrade. Wow, that's pretty slow. I'd assumed it was a semi-automated process and the new version would be out now, 3 weeks later. At least look through the release notes to see if any mention is made of this bug being fixed in 8.4.1 I guess.
Scott Marlowe escribió: > Wow, that's pretty slow. I'd assumed it was a semi-automated process > and the new version would be out now, 3 weeks later. At least look > through the release notes to see if any mention is made of this bug > being fixed in 8.4.1 I guess. Both files on which that error message appears are still at the same versions there were at when 8.4.0 was released, so I doubt the bug has been fixed. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
APseudoUtopia <apseudoutopia@gmail.com> writes:
>> Here's what happened:
>>
>> $ vacuumdb --all --full --analyze --no-password
>> vacuumdb: vacuuming database "postgres"
>> vacuumdb: vacuuming database "web_main"
>> vacuumdb: vacuuming of database "web_main" failed: ERROR: huge tuple
> PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC)
> 4.2.1 20070719 [FreeBSD], 32-bit
This is evidently coming out of ginHeapTupleFastCollect because it's
formed a GIN tuple that is too large (either too long a word, or too
many postings, or both). I'd say that this represents a serious
degradation in usability from pre-8.4 releases: before, you would have
gotten the error upon attempting to insert the table row that triggers
the problem. Now, with the "fast insert" stuff, you don't find out
until VACUUM fails, and you have no idea where the bad data is. Not cool.
Oleg, Teodor, what can we do about this? Can we split an oversize
tuple into multiple entries? Can we apply suitable size checks
before instead of after the fast-insert queue?
regards, tom lane
> APseudoUtopia <apseudoutopia@gmail.com> writes:
>>> Here's what happened:
>>>
>>> $ vacuumdb --all --full --analyze --no-password
>>> vacuumdb: vacuuming database "postgres"
>>> vacuumdb: vacuuming database "web_main"
>>> vacuumdb: vacuuming of database "web_main" failed: ERROR: б═huge tuple
>
>> PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC)
>> 4.2.1 20070719 [FreeBSD], 32-bit
Pls, apply attached patch. Patch increases max size from approximately 500 bytes
up to 2700 bytes, so vacuum will be able to finish.
> This is evidently coming out of ginHeapTupleFastCollect because it's
> formed a GIN tuple that is too large (either too long a word, or too
> many postings, or both). I'd say that this represents a serious
> degradation in usability from pre-8.4 releases: before, you would have
> gotten the error upon attempting to insert the table row that triggers
> the problem. Now, with the "fast insert" stuff, you don't find out
> until VACUUM fails, and you have no idea where the bad data is. Not cool.
>
> Oleg, Teodor, what can we do about this? Can we split an oversize
> tuple into multiple entries? Can we apply suitable size checks
> before instead of after the fast-insert queue?
ginHeapTupleFastCollect and ginEntryInsert checked tuple's size for
TOAST_INDEX_TARGET, but ginHeapTupleFastCollect checks without one ItemPointer,
as ginEntryInsert does it. So ginHeapTupleFastCollect could produce a tuple
which 6-bytes larger than allowed by ginEntryInsert. ginEntryInsert is called
during pending list cleanup.
Patch removes checking of TOAST_INDEX_TARGET and use checking only by
GinMaxItemSize which is greater than TOAST_INDEX_TARGET. All size's check is now
in GinFormTuple.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Attachment
Teodor Sigaev <teodor@sigaev.ru> writes:
> Patch removes checking of TOAST_INDEX_TARGET and use checking only by
> GinMaxItemSize which is greater than TOAST_INDEX_TARGET. All size's check is now
> in GinFormTuple.
Looks reasonable, although since the error is potentially user-facing
I think we should put a bit more effort into the error message
(use ereport and make it mention the index name, at least --- is there
any other useful information we could give?)
Will you apply this, or do you want me to?
regards, tom lane
> Looks reasonable, although since the error is potentially user-facing
> I think we should put a bit more effort into the error message
> (use ereport and make it mention the index name, at least --- is there
> any other useful information we could give?)
Only sizes as it's done in BTree, I suppose.
> Will you apply this, or do you want me to?
I'm not able to provide a good error message in good English :(
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Teodor Sigaev <teodor@sigaev.ru> writes:
>> Will you apply this, or do you want me to?
> I'm not able to provide a good error message in good English :(
OK, I'll take care of it later today.
regards, tom lane
Teodor Sigaev <teodor@sigaev.ru> writes:
> ginHeapTupleFastCollect and ginEntryInsert checked tuple's size for
> TOAST_INDEX_TARGET, but ginHeapTupleFastCollect checks without one ItemPointer,
> as ginEntryInsert does it. So ginHeapTupleFastCollect could produce a tuple
> which 6-bytes larger than allowed by ginEntryInsert. ginEntryInsert is called
> during pending list cleanup.
I applied this patch after improving the error reporting a bit --- but
I was unable to get the unpatched code to fail in vacuum as the OP
reported was happening for him. It looks to me like the original coding
limits the tuple size to TOAST_INDEX_TARGET (512 bytes) during
collection, but checks only the much larger GinMaxItemSize limit during
final insertion. So while this is a good cleanup, I am suspicious that
it may not actually explain the trouble report.
I notice that the complaint was about a VACUUM FULL not a plain VACUUM,
which means that the vacuum would have been moving tuples around and
hence inserting brand new index entries. Is there any possible way that
we could extract a larger index tuple from a moved row than we had
extracted from the original version?
It would be nice to see an actual test case that makes 8.4 fail this way
...
regards, tom lane
On Thu, Oct 1, 2009 at 5:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > APseudoUtopia <apseudoutopia@gmail.com> writes: >>> Here's what happened: >>> >>> $ vacuumdb --all --full --analyze --no-password >>> vacuumdb: vacuuming database "postgres" >>> vacuumdb: vacuuming database "web_main" >>> vacuumdb: vacuuming of database "web_main" failed: ERROR: huge tuple > >> PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) >> 4.2.1 20070719 [FreeBSD], 32-bit > > This is evidently coming out of ginHeapTupleFastCollect because it's > formed a GIN tuple that is too large (either too long a word, or too > many postings, or both). I'd say that this represents a serious > degradation in usability from pre-8.4 releases: before, you would have > gotten the error upon attempting to insert the table row that triggers > the problem. Now, with the "fast insert" stuff, you don't find out > until VACUUM fails, and you have no idea where the bad data is. Not cool. > > Oleg, Teodor, what can we do about this? Can we split an oversize > tuple into multiple entries? Can we apply suitable size checks > before instead of after the fast-insert queue? > > regards, tom lane > Thanks for the explanation. Is there any way I can fix this? I'd rather not recompile pgsql with the patch on my systems at the moment because they are production servers. Would a dump/reload of the table help anything? The output from vaccuumdb verbose is below, if it helps narrow anything down. Does the log mean that the huge tuple is in the search_index_idx index? Thanks. INFO: index "email_confirmations_pkey" now contains 0 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "pg_toast.pg_toast_31065" INFO: "pg_toast_31065": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pg_toast_31065_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "public.tags_groups" INFO: "tags_groups": found 0 removable, 10 nonremovable row versions in 1 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 40 to 52 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 7708 bytes. 0 pages are or will become empty, including 0 at the end of the table. 1 pages containing 7708 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "tags_groups_pkey" now contains 10 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "tags_groups": moved 0 row versions, truncated 1 to 1 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "public.tfiles" INFO: "tfiles": found 232 removable, 1592 nonremovable row versions in 806 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 289 to 2036 bytes long. There were 4893 unused item pointers. Total free space (including removable row versions) is 4388880 bytes. 184 pages are or will become empty, including 2 at the end of the table. 791 pages containing 4370168 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.30 sec. INFO: index "tfiles_pkey" now contains 1592 row versions in 18 pages DETAIL: 314 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.02 sec. INFO: index "name_index_idx" now contains 1592 row versions in 51 pages DETAIL: 1206 index row versions were removed. 4 index pages have been deleted, 16 are currently reusable. CPU 0.00s/0.01u sec elapsed 0.28 sec. INFO: index "search_index_idx" now contains 1592 row versions in 1289 pages DETAIL: 36557 index row versions were removed. 118 index pages have been deleted, 159 are currently reusable. CPU 0.03s/0.50u sec elapsed 8.77 sec. vacuumdb: vacuuming of database "web_main" failed: ERROR: huge tuple