Performance decrease after upgrade to 9.6.1 - Mailing list pgsql-performance

Hello!

We have a server with 8.4.1 that we want to migrate to 9.6.1

Before doing anything, we ran pgbench serveral times.

The results were always similar to the following:

$ pgbench -l -c 100 -T 30 pgbench

starting vacuum...end.

transaction type: TPC-B (sort of)

scaling factor: 1

query mode: simple

number of clients: 100

duration: 30 s

number of transactions actually processed: 36049

tps = 1193.682690 (including connections establishing)

tps = 1198.838960 (excluding connections establishing)

Then, we follow the procedure in https://www.postgresql.org/docs/9.6/static/pgupgrade.html to upgrade the server using pg_upgrade. 

To install the new version, we downloaded and compiled the sources, with the same option that we use with the previous version (configure --prefix=/var/lib/pgsql).

We upgrade only one server, so we don't run the steps for replication.

After this, we ran the script analyze_new_cluster.sh, that was created by pg_upgrade, to generate statistics.

At this point, we run pgbench again, serveral times, to make the comparision.

The results were always similar to the following:

$ pgbench -l -c 100 -T 30 pgbench

starting vacuum...end.

transaction type: <builtin: TPC-B (sort of)>

scaling factor: 1

query mode: simple

number of clients: 100

number of threads: 1

duration: 30 s

number of transactions actually processed: 27428

latency average = 110.104 ms

tps = 908.234296 (including connections establishing)

tps = 908.278187 (excluding connections establishing)

We ran the statistics again, this time with vacuumdb --all --analyze, no change at all.

In the postgresql.conf of the new version (9.6.1), we use this values:

max_connections = 100

superuser_reserved_connections = 3

shared_buffers = 512MB

maintenance_work_mem = 128MB

effective_cache_size = 1500MB

max_wal_size = 2GB

min_wal_size = 1GB

wal_level = replica

In the postgresql.conf of the old version (8.4.1), we use this values:

max_connections = 100

shared_buffers = 512MB

(The other values are set by default)

We try also with the default values in the new installation, without change in the times.

The hardware doesn't change, its a Intel(R) Pentium(R) CPU G3220 @ 3.00GHz with 2 cores, 2GB of RAM, 500GB SCSI hard disk. The operating system is Enterprise Linux Enterprise Linux Server release 5.8, 64 bits.

Any suggestion about what could be the problem?

Thanks!

Gabriela