Upgrading from 9.2 to 9.3 causes performance degradation

On 03/23/2014 04:58 PM, Álvaro Nunes Lemos Melo 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 |
+--------+-------+-------+

Have no answer, just some observations. Without the actual query content
and EXPLAIN, EXPLAIN ANALYZE on said query for each server there is not
really a path to an answer. In addition the minor version of each
Postgres instance could prove useful.

Also some more information on the hardware specifications for each
server would help. For instance harddrive, memory numbers.

Thanks,

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Mar 23, 2014, at 7:58 PM, Álvaro Nunes Lemos Melo wrote:
...

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.

I think this is a good starting place:
http://wiki.postgresql.org/wiki/SlowQueryQuestions

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

On Mon, Mar 24, 2014 at 10:58 AM, Á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

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

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, 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,

L

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

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

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@atua.com.br http://www.atua.com.br
(54) 9976-0106 (54) 3045-4144

----- Mensagem original -----
De: "Eduardo Morras" <emorrasg@yahoo.es>
Para: pgsql-general@postgresql.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@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, 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@yahoo.es>

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

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

On 03/24/2014 07:45 AM, Álvaro Nunes Lemos Melo wrote:

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 |
+--------+-------+-------+

==> 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

The actual query text would be useful, unless I am missing how to see
that at explain.depesz.com. The reason I ask is one thing that stands
out is the increase in the Nested Loop Left Join from a count of 2 and
a time of 1.910ms in 9.2 to a count of 4 and a time of 12782.414 ms in
9.3 . There is also a Materialize time of 44236.592 ms in 9.3 that is
not there in 9.2. Those two increases alone account for most of the
difference.

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@atua.com.br http://www.atua.com.br
(54) 9976-0106 (54) 3045-4144

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Mon, Mar 24, 2014 at 7:45 AM, Álvaro Nunes Lemos Melo <
al_nunes@atua.com.br> wrote:

==> 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.

How did you do the restore? Please VACUUM and retest. At best it will fix
the problem, and if it doesn't it will at least rule out a bunch of
possibilities for the source of the problem.

Cheers,

Jeff

Jeff: I ran a VACUUM FULL ANALYZE and retested. No significative change was notices, the explain is availiable in

9.3 - After VACUUM FULL ANALYZE - http://explain.depesz.com/s/rVoW
9.3 - Original - http://explain.depesz.com/s/Vwt

Adrian: I'll try to send the query attached. My doubts are: why 9.3 is slower then 9.2 with the same database/configuration/harware/query? Even worst, why 9.3 got slower results on a better HW, where 9.2 improved on the same scenario.

I've just ran the explains on my new server, links below:
9.2 - http://explain.depesz.com/s/AeD
9.3 - http://explain.depesz.com/s/1sp

For comparison sake, old server:
9.2 - http://explain.depesz.com/s/bXIo
9.3 - http://explain.depesz.com/s/Vwt

I really appreciate your help.

Thanks again,

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

----- Mensagem original -----

De: "Jeff Janes" <jeff.janes@gmail.com>
Para: "Álvaro Nunes Lemos Melo" <al_nunes@atua.com.br>
Cc: pgsql-general@postgresql.org
Enviadas: Segunda-feira, 24 de março de 2014 15:29:38
Assunto: Re: [GENERAL] Upgrading from 9.2 to 9.3 causes performance degradation

On Mon, Mar 24, 2014 at 7:45 AM, Álvaro Nunes Lemos Melo < al_nunes@atua.com.br > wrote:

==> 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.

How did you do the restore? Please VACUUM and retest. At best it will fix the problem, and if it doesn't it will at least rule out a bunch of possibilities for the source of the problem.

Cheers,

Jeff

On Monday, March 24, 2014, Álvaro Nunes Lemos Melo
<al_nunes@atua.com.br<javascript:_e(%7B%7D,'cvml','al_nunes@atua.com.br');>>
wrote:

Jeff: I ran a VACUUM FULL ANALYZE and retested. No significative change
was notices, the explain is availiable in

9.3 - After VACUUM FULL ANALYZE - http://explain.depesz.com/s/rVoW
9.3 - Original - http://explain.depesz.com/s/Vwt

OK, thanks. Your plans (and query) are very unwieldy and hard to think
about. If you took just one of the selects that participate in the UNION
ALL chain, can you reproduce the problem there?

Also, what is this supposed to do:

AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1
month')
FROM pessoa p_

WHERE p_.cd_pessoa = p.cd_pessoa)::DATE

You are selecting for p, not p_, so I don't understand what role p_ even
plays in that. I wonder of the work done on LATERAL in 9.3 did something
here.

Adrian: I'll try to send the query attached. My doubts are: why 9.3 is
slower then 9.2 with the same database/configuration/harware/query? Even
worst, why 9.3 got slower results on a better HW, where 9.2 improved on the
same scenario.

Any changed to complicated systems will have some unintended and
unfortunate consequences. Few people spend time investigating all of the
queries that now run faster in 9.3!

How big is your database? Any chance you can upload the relevant tables
someplace public, or write a generator that creates fake data which still
displays the problem?

Or, can you compile your own PostgreSQL from git checkouts? If so, running
git bisect to see just where the problem started in the development process
might be the faster than other investigation methods.

Cheers,

Jeff

On 3/24/2014 7:45 AM, Álvaro Nunes Lemos Melo wrote:

- New: Xeon E5-2430 2.20GHz
- Old: Xeon X3470 2.93GHz
Memory: 8 GBs

note your OLD server was faster per gigahertz. Intel PR not
withstanding, GHz remains more important for single threaded performance
than about anything else. the additional features of the E5 cpu don't
do that much for postgres, they are mostly of interest to video
processing and such. the e5 does have larger cache, but thats not
always that important. both processors have similar memory bandwidth.
that E5 is a 6-core, while that x3470 is a 4-core, but that has no
impact on single threaded performance, only matters if you're running
enough queries concurrently to use all the cores.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

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

On 03/24/2014 07:32 PM, Álvaro Nunes Lemos Melo wrote:

Jeff: I ran a VACUUM FULL ANALYZE and retested. No significative change
was notices, the explain is availiable in

9.3 - After VACUUM FULL ANALYZE - http://explain.depesz.com/s/rVoW
9.3 - Original - http://explain.depesz.com/s/Vwt

Adrian: I'll try to send the query attached. My doubts are: why 9.3 is
slower then 9.2 with the same database/configuration/harware/query? Even
worst, why 9.3 got slower results on a better HW, where 9.2 improved on
the same scenario.

Yikes, that query is above my level of expertise. Someone with more
knowledge of the planner than I is going to have to go over that. As to
why the change? Best guess, something changed in the planner and this
query is on the wrong side of the change. So are you seeing a general
slowdown or is it restricted to this particular query?

I've just ran the explains on my new server, links below:
9.2 - http://explain.depesz.com/s/AeD
9.3 - http://explain.depesz.com/s/1sp

For comparison sake, old server:
9.2 -http://explain.depesz.com/s/bXIo
9.3 -http://explain.depesz.com/s/Vwt&lt;http://explain.depesz.com/s/Vwt&gt;

I really appreciate your help.

Thanks again,

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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

I browsed through messages related to this issue? I have a few questions to
the OP. How did you upgrade? Did you reindex the database? Try reindexdb
and see what happens. If you used pg_upgrade, you need to reindex. I did
pg_upgrade on my production db and it wasn't any faster on 9.3 than on 9.0,
until I re-indexed.

-Joseph

On Tue, Mar 25, 2014 at 11:01 AM, Adrian Klaver
<adrian.klaver@aklaver.com>wrote:

Hi Alvaro

I had a look to the explain analyze you sent and after step 13 you have some differences between both planners.

At 21st step you have a sequence scan on 9.3 instead of a index scan on 19th step (9.2)

Following the process, you can find more differences too (45 & 43, seq scan salario again on subquery)

Those differences in the planner are maybe the reason, and why it happend could be the configuration for join and sort methods, the statistics stored on 9.2 (more even if that is a real process server, the system has been "learning" during this time) or some other related.

I am not pretty sure what is, but the explain you sent is telling you that both server are not doing the same work, so it may be reasonable to obtain differences among servers.

Good luck with the issue

Regards

Alfonso

El 25/03/2014, a las 02:32, Álvaro Nunes Lemos Melo <al_nunes@atua.com.br> escribió:

Jeff: I ran a VACUUM FULL ANALYZE and retested. No significative change was notices, the explain is availiable in

9.3 - After VACUUM FULL ANALYZE - http://explain.depesz.com/s/rVoW
9.3 - Original - http://explain.depesz.com/s/Vwt

Adrian: I'll try to send the query attached. My doubts are: why 9.3 is slower then 9.2 with the same database/configuration/harware/query? Even worst, why 9.3 got slower results on a better HW, where 9.2 improved on the same scenario.

I've just ran the explains on my new server, links below:
9.2 - http://explain.depesz.com/s/AeD
9.3 - http://explain.depesz.com/s/1sp

For comparison sake, old server:
9.2 - http://explain.depesz.com/s/bXIo
9.3 - http://explain.depesz.com/s/Vwt

I really appreciate your help.

Thanks again,

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

De: "Jeff Janes" <jeff.janes@gmail.com>
Para: "Álvaro Nunes Lemos Melo" <al_nunes@atua.com.br>
Cc: pgsql-general@postgresql.org
Enviadas: Segunda-feira, 24 de março de 2014 15:29:38
Assunto: Re: [GENERAL] Upgrading from 9.2 to 9.3 causes performance degradation

On Mon, Mar 24, 2014 at 7:45 AM, Álvaro Nunes Lemos Melo <al_nunes@atua.com.br> wrote:

==> 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.

How did you do the restore? Please VACUUM and retest. At best it will fix the problem, and if it doesn't it will at least rule out a bunch of possibilities for the source of the problem.

Cheers,

Jeff

<salario.sql>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alfonso Afonso