pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedreindexdb dying with SIGPIPE on 8.2.5
9+ messages / 5 participants
[nested] [flat]
* reindexdb dying with SIGPIPE on 8.2.5
@ 2008-08-06 15:10 Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-08-07 03:37 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Tom Lane <tgl@sss.pgh.pa.us>
2008-12-17 09:10 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 2 replies; 9+ messages in thread
From: Torsten Luettgert @ 2008-08-06 15:10 UTC (permalink / raw)
To: pgsql-admin
Hello all,
we're using PostgreSQL 8.2.5 with some 300 databases under CentOS 4.6.
To reduce on-disk size of our DBs, we're doing the following after
the nightly dumps:
reindexdb dbname
vacuumdb -f -z dbname
Now, this works fine about 50% of the time, but on some machines, the
reindexdb dies from a SIGPIPE signal. The logs look like this:
Aug 3 04:08:10 prospero postgres[27101]: [1-1] NOTICE: table
"pg_class" was reindexed
Aug 3 04:08:11 prospero postgres[27101]: [2-1] NOTICE: table
"sql_sizing" was reindexed
Aug 3 04:08:11 prospero postgres[27101]: [3-1] LOG: could not send
data to client: Broken pipe
Aug 3 04:08:11 prospero postgres[27101]: [4-1] NOTICE: table
"sql_sizing_profiles" was reindexed
Aug 3 04:08:11 prospero postgres[27101]: [5-1] NOTICE: table
"sql_features" was reindexed
...
Aug 3 04:08:18 prospero postgres[27101]: [39-1] NOTICE: table
"shoprules" was reindexed
Aug 3 04:08:18 prospero postgres[27101]: [40-1] LOG: unexpected EOF on
client connection
So, it looks to me like the REINDEX command is completed, but the
reindexdb tool dies. This is run via a nightly cronjob; I never saw
those problems if I invoke reindexdb on the command line.
We did get a notice to increase max_fsm_pages at first though, so
we increased it with good margin, but the SIGPIPE problem persists.
What is going wrong here?
Thanks for your help,
Torsten
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: reindexdb dying with SIGPIPE on 8.2.5
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
@ 2008-08-07 03:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-10 14:15 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2008-08-07 03:37 UTC (permalink / raw)
To: Torsten Luettgert <t.luettgert@pressestimmen.de>; +Cc: pgsql-admin
Torsten Luettgert <t.luettgert@pressestimmen.de> writes:
> Now, this works fine about 50% of the time, but on some machines, the
> reindexdb dies from a SIGPIPE signal. The logs look like this:
> Aug 3 04:08:10 prospero postgres[27101]: [1-1] NOTICE: table
> "pg_class" was reindexed
> Aug 3 04:08:11 prospero postgres[27101]: [2-1] NOTICE: table
> "sql_sizing" was reindexed
> Aug 3 04:08:11 prospero postgres[27101]: [3-1] LOG: could not send
> data to client: Broken pipe
> Aug 3 04:08:11 prospero postgres[27101]: [4-1] NOTICE: table
> "sql_sizing_profiles" was reindexed
> Aug 3 04:08:11 prospero postgres[27101]: [5-1] NOTICE: table
> "sql_features" was reindexed
> So, it looks to me like the REINDEX command is completed, but the
> reindexdb tool dies.
Yeah, so it would seem.
> We did get a notice to increase max_fsm_pages at first though, so
> we increased it with good margin, but the SIGPIPE problem persists.
max_fsm_pages isn't going to have any impact on a client's behavior.
I'm wondering if the reindexdb is being run under a restrictive ulimit
setting, or something else that would prevent it from just sitting and
waiting.
Is there anything in the kernel log at the time of the failure report?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: reindexdb dying with SIGPIPE on 8.2.5
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-08-07 03:37 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-08-10 14:15 ` Torsten Luettgert <t.luettgert@pressestimmen.de>
0 siblings, 0 replies; 9+ messages in thread
From: Torsten Luettgert @ 2008-08-10 14:15 UTC (permalink / raw)
To: pgsql-admin
On Wed, 2008-08-06 at 23:37 -0400, Tom Lane wrote:
> I'm wondering if the reindexdb is being run under a restrictive ulimit
> setting, or something else that would prevent it from just sitting and
> waiting.
I checked... there's no limits defined in /etc/security/limits.conf.
There's more weirdness here: on the same machine, one day, everything
runs through fine.
Another day, one or two DBs are reindexed and vacuumed properly, then
all successive tries fail with the mentioned SIGPIPE error.
I'll have STDOUT and STDERR redirected to a file before the call of
reindexdb now. Perhaps there's something in the console output that
helps.
> Is there anything in the kernel log at the time of the failure report?
Don't see anything except postgresql messages at that time
in /var/log/messages, and nothing at all in /var/log/secure.
Regards,
Torsten
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: reindexdb dying with SIGPIPE on 8.2.5
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
@ 2008-12-17 09:10 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-18 07:58 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 1 reply; 9+ messages in thread
From: Michael Monnerie @ 2008-12-17 09:10 UTC (permalink / raw)
To: pgsql-admin
On Mittwoch 06 August 2008 Torsten Luettgert wrote:
> reindexdb dbname
> vacuumdb -f -z dbname
Just a question on this: Is the order correct or would a vacuum+reindex
be more correct?
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net Key-ID: 1C1209B4
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: reindexdb dying with SIGPIPE on 8.2.5
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-12-17 09:10 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
@ 2008-12-18 07:58 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-12-19 02:11 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 1 reply; 9+ messages in thread
From: Guillaume Lelarge @ 2008-12-18 07:58 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@is.it-management.at>; +Cc: pgsql-admin
Michael Monnerie a écrit :
> On Mittwoch 06 August 2008 Torsten Luettgert wrote:
>> reindexdb dbname
>> vacuumdb -f -z dbname
>
> Just a question on this: Is the order correct or would a vacuum+reindex
> be more correct?
>
You should do a VACUUM and then a REINDEX.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: reindexdb dying with SIGPIPE on 8.2.5
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-12-17 09:10 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-18 07:58 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Guillaume Lelarge <guillaume@lelarge.info>
@ 2008-12-19 02:11 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-19 03:09 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Monnerie @ 2008-12-19 02:11 UTC (permalink / raw)
To: pgsql-admin
On Donnerstag 18 Dezember 2008 Guillaume Lelarge wrote:
> You should do a VACUUM and then a REINDEX.
Thank you. And as I just learned about CLUSTER... would the correct
order be CLUSTER, VACUUM, REINDEX ? I guess so, as CLUSTER helps a lot
with cleaning up already, so there's less work for VACUUM/REINDEX.
Right?
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: reindexdb dying with SIGPIPE on 8.2.5
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-12-17 09:10 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-18 07:58 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Guillaume Lelarge <guillaume@lelarge.info>
2008-12-19 02:11 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
@ 2008-12-19 03:09 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-12-21 20:21 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2008-12-19 03:09 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@is.it-management.at>; +Cc: pgsql-admin
Michael Monnerie wrote:
> On Donnerstag 18 Dezember 2008 Guillaume Lelarge wrote:
> > You should do a VACUUM and then a REINDEX.
>
> Thank you. And as I just learned about CLUSTER... would the correct
> order be CLUSTER, VACUUM, REINDEX ? I guess so, as CLUSTER helps a lot
> with cleaning up already, so there's less work for VACUUM/REINDEX.
> Right?
You don't need to vacuum or reindex after a cluster; it removes all dead
rows (except those that vacuum won't remove anyway)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: reindexdb dying with SIGPIPE on 8.2.5
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-12-17 09:10 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-18 07:58 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Guillaume Lelarge <guillaume@lelarge.info>
2008-12-19 02:11 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-19 03:09 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-12-21 20:21 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-22 00:46 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Monnerie @ 2008-12-21 20:21 UTC (permalink / raw)
To: pgsql-admin
On Freitag 19 Dezember 2008 Alvaro Herrera wrote:
> You don't need to vacuum or reindex after a cluster; it removes all
> dead rows (except those that vacuum won't remove anyway)
OK, but I do not cluster all tables, as it only makes sense for tables
which get read in some sequential (based on that index) way. For
completely random accessed tables I skip cluster, and rely on
vacuum/reindex. Also, I think pg_toast tables cannot be clustered,
right?
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: reindexdb dying with SIGPIPE on 8.2.5
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-12-17 09:10 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-18 07:58 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Guillaume Lelarge <guillaume@lelarge.info>
2008-12-19 02:11 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-19 03:09 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Alvaro Herrera <alvherre@commandprompt.com>
2008-12-21 20:21 ` Re: reindexdb dying with SIGPIPE on 8.2.5 Michael Monnerie <michael.monnerie@is.it-management.at>
@ 2008-12-22 00:46 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 9+ messages in thread
From: Alvaro Herrera @ 2008-12-22 00:46 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@is.it-management.at>; +Cc: pgsql-admin
Michael Monnerie wrote:
> On Freitag 19 Dezember 2008 Alvaro Herrera wrote:
> > You don't need to vacuum or reindex after a cluster; it removes all
> > dead rows (except those that vacuum won't remove anyway)
>
> OK, but I do not cluster all tables, as it only makes sense for tables
> which get read in some sequential (based on that index) way. For
> completely random accessed tables I skip cluster, and rely on
> vacuum/reindex.
Right -- I'm just saying that if you cluster a table, you don't need to
vacuum it. Of course, you need to vacuum all the other tables.
> Also, I think pg_toast tables cannot be clustered, right?
Not directly, but if you cluster a plain table, its toast table is
clustered too.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2008-12-22 00:46 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-06 15:10 reindexdb dying with SIGPIPE on 8.2.5 Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-08-07 03:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-10 14:15 ` Torsten Luettgert <t.luettgert@pressestimmen.de>
2008-12-17 09:10 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-18 07:58 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-12-19 02:11 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-19 03:09 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-12-21 20:21 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2008-12-22 00:46 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox