Re: Upgrading from 9.2 to 9.3 causes performance degradation

On Sun, 23 Mar 2014 20:58:03 -0300 (BRT)
Álvaro Nunes Lemos Melo <al_nunes@atua.com.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

Different versions of Kernel, and IIRC some Linux vesions on 3.x has performance problems with PostgreSQL.

Some questions:

What filesystems do you use?
Did you compile PostgreSQL or installed a binary?
Is some kind of debug mode on in new server?
Does querys call plpgsql or other functions on external languages (non sql).

I think you did it correctly but, did you measure the performance with warmup systems? (Filled Caches, Analyzed tables,
Updatedindexes, etc...) 

>
> Query Execution Times (average time of three executions, in seconds)
>
> +--------+-------+-------+
> | Server |  9.2  |  9.3  |
> +--------+-------+-------+
> | Old    |   129 |   216 |
> +--------+-------+-------+
> | New    |   118 |   275 |
> +--------+-------+-------+
>
> Thanks,

L

---   ---
Eduardo Morras <emorrasg@yahoo.es>