Re: Really really slow select count(*)
| От | felix |
|---|---|
| Тема | Re: Really really slow select count(*) |
| Дата | |
| Msg-id | AANLkTin8ndBSU9ZJ2uAdnvYQ7oLBg2S04bqAXn-s5qv+@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Really really slow select count(*) (Shaun Thomas <sthomas@peak6.com>) |
| Ответы |
Re: Really really slow select count(*)
Re: Really really slow select count(*) Re: Really really slow select count(*) |
| Список | pgsql-performance |
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@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.
В списке pgsql-performance по дате отправления: