Re: Really really slow select count(*)

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(at)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(at)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(at)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(at)peak6(dot)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.
>
>