Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL 8.4 performance tuning questions

From: Rauan Maemirov <rauan(at)maemirov(dot)com>
To: tv(at)fuzzy(dot)cz
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL 8.4 performance tuning questions
Date: 2009-07-30 15:31:00
Message-ID: c78bcec0907300831h168ea8a5l73a24964b2f9ef22@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
> OK, this seems to be the cause. What were the original values of the
> config variables? If you've lowered the work_mem and you need to sort a
> lot of data, this may be a problem. What amounts of data are you working
> with? If the data were not analyzed recently, the execution plans will be
> inefficient and this may be the result.

The reason is that i'm using config that i used before and it worked
perfect. work_mem is 192mb.
I tried ANALYZE, but it didn't change anything.

Amounts of data... at least, backup is over ~2.2Gb.
I tried to use EXPLAIN ANALYZE for slow queries that i get from
serverlog, but it also didn't change anything.


2009/7/30  <tv(at)fuzzy(dot)cz>:
>> Unfortunately had to downgrade back to 8.3. Now having troubles with
>> that and still solving them.
>>
>> For future upgrade, what is the basic steps?
>
> 1. create database
> 2. dump the data from the old database
> 3. load the data into the new database
> 4. analyze etc. (I prefer to do this manually at the beginning)
> 5. check that everything is working (that the correct execution plans are
> used, etc.)
>
> You may even run the (2) and (3) at once - use pipe instead of a file.
>
>>
>>>Was the database analyzed recently?
>> Hm... there was smth like auto analyzer in serverlog when i started it
>> first time, but i didn't mention that.
>> Should I analyze whole db? How to do it?
>
> Just execute 'ANALYZE' and the whole database will be analyzed, but when
> the autovacuum daemon is running this should be performed automatically (I
> guess - check the pg_stat_user_tables, there's information about last
> manual/automatic vacuuming and/or analysis).
>
>> And how should I change _cost variables?
>
> I haven't noticed you've not modified those variables, so don't change them.
>
>> I/O was very high. at first memory usage grew up and then began to full
>> swap.
>
> OK, this seems to be the cause. What were the original values of the
> config variables? If you've lowered the work_mem and you need to sort a
> lot of data, this may be a problem. What amounts of data are you working
> with? If the data were not analyzed recently, the execution plans will be
> inefficient and this may be the result.
>
> regards
> Tomas
>
>

In response to

pgsql-performance by date

Next:From: Steve CrawfordDate: 2009-07-30 16:05:09
Subject: Re: PostgreSQL 8.4 performance tuning questions
Previous:From: tvDate: 2009-07-30 14:24:38
Subject: Re: PostgreSQL 8.4 performance tuning questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group