Re: Upgrading from 9.2 to 9.3 causes performance degradation

Firstly, I'd like to thanks all of you guys for your help. Below, I'll try to provide all the information you asked. I know the query I'm testing could be improved, but I used it because it's not that fast that is hard to measure neither so slow (another report query took about 4 minutes on 9.2 and now takes 16 minutes on 9.3) that would take the whole day to run tests.
And again, performance on 9.2 improved on the new server (as expected), what surprises me is that 9.3 is slower on the old one and even slower on the new, as shown in data below.

> +--------+-------+-------+
> | Server | 9.2 | 9.3 |
> +--------+-------+-------+
> | Old | 129 | 216 |
> +--------+-------+-------+
> | New | 118 | 275 |
> +--------+-------+-------+

==> Eduardo:
> What filesystems do you use?
Ext4 over SSD disk in both servers

> Did you compile PostgreSQL or installed a binary?
Binary from Ubuntu/Debian packages

> Is some kind of debug mode on in new server?
Like track_io_activity? Not yet, but I can turn them on and send the results, if necessary.

> Does querys call plpgsql or other functions on external languages (non sql).
No.

==> Venkata
> 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 ?
No, as I wrote initially, I'd double checked and the four conf files have the same parameters.

> Any maintenance activities (VACUUM or VACUUM FULL and ANALYZE) have been performed after the migration ?
ANALYZE yes. VACUUM or VACUUM FULL no, because I ran the test on recently restores and unchanged databases.

==> Adrian
> Hardware specification (on both servers)
Processor:
- New: Xeon E5-2430 2.20GHz
- Old: Xeon X3470 2.93GHz
Memory: 8 GBs
Hard Drives: SSD

> Minor Versions
New server:
PostgreSQL 9.2.6
PostgreSQL 9.3.3

Old server:
PostgreSQL 9.2.3
PostgreSQL 9.3.3

> Explains (ANALYZE, BUFFERS)
Old server:
9.2 - http://explain.depesz.com/s/bXIo
9.3 - http://explain.depesz.com/s/Vwt

New server:
I'll run them at night (about 12 hours from now), so that the regular use of the server don't interfere in the results

--
Álvaro Nunes Melo Atua Sistemas de Informação
alvaro(at)atua(dot)com(dot)br http://www.atua.com.br
(54) 9976-0106 (54) 3045-4144

----- Mensagem original -----
De: "Eduardo Morras" <emorrasg(at)yahoo(dot)es>
Para: pgsql-general(at)postgresql(dot)org
Enviadas: Segunda-feira, 24 de março de 2014 6:00:54
Assunto: Re: [GENERAL] 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(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

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, Updated indexes, etc...)

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

--- ---
Eduardo Morras <emorrasg(at)yahoo(dot)es>

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general