Re: Really really slow select count(*)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx

______________________________________________

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

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux