BRUTAL
http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html max_fsm_pages See Section 17.4.1<http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html#SYSVIPC> for information on how to adjust those parameters, if necessary. I see absolutely nothing in there about how to set those parameters. several hours later ( where is my data directory ? 8.4 shows it in SHOW ALL; 8.3 does not. conf files ? "in the data directory" no, its in /etc/postgres/8.3/main where is pg_ctl ? what user do I need to be ? postgres then why was it installed in the home dir of a user that does not have permissions to use it ?? ) cd /home/crucial/bin /home/crucial/bin/pg_ctl -D /var/lib/postgresql/8.3/main reload reload does not reset max_fsm_pages, I need to actually restart the server. postgres@nestseekers:/home/crucial/bin$ /home/crucial/bin/pg_ctl -D /var/lib/postgresql/8.3/main restart waiting for server to shut down............................................................... failed pg_ctl: server does not shut down OK, my mistake. probably I have to disconnect all clients. I don't want to do a "planned maintenance" right now. so I go to sleep the server restarts itself an hour later. but no, it fails to restart because this memory setting you recommend is not possible without reconfiguring the kernel. postgres@nestseekers:/home/crucial/bin$ 2011-02-06 05:18:00 EST LOG: could not load root certificate file "root.crt": No such file or directory 2011-02-06 05:18:00 EST DETAIL: Will not verify client certificates. 2011-02-06 05:18:00 EST FATAL: could not create shared memory segment: Invalid argument 2011-02-06 05:18:00 EST DETAIL: Failed system call was shmget(key=5432001, size=35463168, 03600). 2011-02-06 05:18:00 EST HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 35463168 bytes), reduce PostgreSQL's shared_buffers parameter (currently 3072) and/or its max_connections parameter (currently 103). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information about shared memory configuration. ^C *and the website is down for the next 6 hours while I sleep.* total disaster after a few tries I get it to take an max_fsm_pages of 300k postgres@nestseekers:/home/crucial/bin$ 2011-02-06 05:19:26 EST LOG: could not load root certificate file "root.crt": No such file or directory 2011-02-06 05:19:26 EST DETAIL: Will not verify client certificates. 2011-02-06 05:19:26 EST LOG: database system was shut down at 2011-02-06 00:07:41 EST 2011-02-06 05:19:27 EST LOG: autovacuum launcher started 2011-02-06 05:19:27 EST LOG: database system is ready to accept connections ^C 2011-02-06 05:33:45 EST LOG: checkpoints are occurring too frequently (21 seconds apart) 2011-02-06 05:33:45 EST HINT: Consider increasing the configuration parameter "checkpoint_segments". ?? >From my perspective: the defaults for postgres 8.3 result in a database that does not scale and fails dramatically after 6 months. changing that default is brutally difficult and can only really be done by adjusting something in the kernel. I have clustered that table, its still unbelievably slow. I still don't know if this bloat due to the small free space map has anything to do with why the table is performing like this. On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas <stho...@peak6.com> wrote: > > You can stop the bloating by setting the right max_fsm_pages setting, > > but you'll either have to go through and VACUUM FULL every table in your > database, or dump/restore to regain all the lost space and performance (the > later would actually be faster). Before I even touch an older PostgreSQL DB, > I set it to some value over 3-million just as a starting value to be on the > safe side. A little used memory is a small price to pay for stopping gradual > expansion. > >
