Обсуждение: reindexdb dying with SIGPIPE on 8.2.5
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
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
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
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
Вложения
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
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
Вложения
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.
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
Вложения
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.