Re: Upgraded from 7.4 to 8.1.4 QUERIES NOW SLOW!!!

On 9/20/07, smiley2211 <smiley2211(at)yahoo(dot)com> wrote:
>
> No, I didn't UPGRADE it but that's what I inherited :( ...not sure of the
> code page stuff because I am not the one who did the upgrade...I'm not sure
> I know ENOUGH about POSTGRESQL to mess around with the codepage...
>
> Yes, I use vacuum analyze...
>
> Yes, I used the postgresql.conf of 7.4 and tried to match the 8.1.4 to
> that...I didn't know where else to start...The users have been complaining
> since DAY1 as I am told...

OK, a few things you need to look into.

Do you have horrendous bloating in the db. run vacuum verbose on your
db and see what it says. You should probably turn on the autovacuum
daemon either way. If your database has gotten bloated you may need
to vacuum full / reindex to get your space back.

What queries are slow, specifically. you can set the server to log
long running servers in postgresql.conf. Find the longest running
ones and run them by hand with explain analyze at the front, like:

explain analyze select .....

lastly, run

vmstat 10

from the command line while the machine is running slow and see where
effort is going. I'm guessing you'll see a lot of id in there.