Re: Really really slow select count(*) - Mailing list pgsql-performance

On 02/04/2011 12:14 PM, felix wrote:

> do you think its possible that it just doesn't have anything to
> complain about ? or the password is affecting it ?

Why is it asking for the password over and over again? It shouldn't be
doing that. And also, are you running this as a user with superuser
privileges? You might want to think about setting up a .pgpass file, or
setting up local trust for the postgres user so you can run maintenance
without having to manually enter a password.

> In any case I'm not sure I want to run this even at night on
> production.

You should be. Even with auto vacuum turned on, all of our production
systems get a nightly vacuum over the entire list of databases. It's non
destructive, and about the only thing that happens is disk IO. If your
app has times where it's not very busy, say 3am, it's a good time.

This is especially true since your free space map is behind.

We actually turn off autovacuum because we have a very transactionally
intense DB, and if autovacuum launches on a table in the middle of the
day, our IO totally obliterates performance. We only run a nightly
vacuum over all the databases when very few users or scripts are using
anything.

> what is the downside to estimating max_fsm_pages too high ?

Nothing really. It uses more memory to track it, but on modern servers,
it's not a concern. The only risk is that you don't know what the real
setting should be, so you may not completely stop your bloating.

> and do you agree that I should turn CLUSTER ON ?

Cluster isn't really something you turn on, but something you do. It's
like vacuum full, in that it basically rebuilds the table and all
indexes from scratch. The major issue you'll run into is that it
reorders the table by the index you chose, so you'd best select the
primary key unless you have reasons to use something else. And you have
to do it table by table, which will really suck since we already know
your whole db has bloated, not just one or two tables.

You're going to be doing some scripting, buddy. :) Well, unless you just
do a dump/restore and start over with sane postgresql.conf settings.

> I have no problem to stop all tasks to this table at night and just
> reload it

That will work for this table. Just keep in mind all your tables have
been suffering since you installed this database. Tables with the
highest turnover were hit hardest, but they all have non-ideal sizes
compared to what they would be if your maintenance was working.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email