BRUTAL
max_fsm_pages
SeeÂSection 17.4.1Â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 <sthomas@xxxxxxxxx> 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.
