Re: Upgrading from 9.2 to 9.3 causes performance degradation

On Mon, Mar 24, 2014 at 10:58 AM, Álvaro Nunes Lemos Melo <
al_nunes(at)atua(dot)com(dot)br> wrote:

Hi,
>
> Recently, I've been trough a datacenter migration, and in this operation
> I'd also upgraded my PostgreSQL version from 9.2 to 9.3. My new hardware is
> slightly better than the old one, but the PostgreSQL performance has shown
> degradation in the whole system.
>
> Trying to figure out what could be happening, I'd installed instances of
> both versions on both servers, and double checked all the configuration
> parameters on them. The 9.2 version results make sense, there's a minor
> upgrade in the performance on the new server, but 9.3 number are worst than
> 9.2 on both servers, and surprisingly, worst in the newest than in the old
> one. After some research, I tried to disable transparent hugepages on the
> new one, but it made no effect. I used and specific query to benchmark, but
> as I said before, the whole system is slower.
>
> Below is my data, and I really hope we can find what is happening, or I'll
> have to downgrade to 9.2 and wait for 9.4 release.
>
> Old Server:
> Ubuntu 10.04.4 LTS
> 2.6.32-45-generic
>
> New Server:
> Debian GNU/Linux 7.3 (wheezy)
> 3.2.0-4-amd64
>
> Query Execution Times (average time of three executions, in seconds)
>
> +--------+-------+-------+
> | Server | 9.2 | 9.3 |
> +--------+-------+-------+
> | Old | 129 | 216 |
> +--------+-------+-------+
> | New | 118 | 275 |
> +--------+-------+-------+
>

After the migration to hardware and to the new version 9.3, any changes
have been done in the postgresql.conf compared to the old settings ?

Any maintenance activities (VACUUM or VACUUM FULL and ANALYZE) have been
performed after the migration ? This is very important as there is no
guarantee that the old statistics will be available in the newly migrated
database. Average execution time of the queries will be accurate only after
collecting the latest statistics.

Regards,

Venkata Balaji N
Fujitsu Australia