| From: | Gabriela Serventi <gabrielaserventi(at)hotmail(dot)com> |
|---|---|
| To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Performance decrease after upgrade to 9.6.1 |
| Date: | 2016-11-15 21:57:08 |
| Message-ID: | DM3PR1001MB105562015F5D5577967E6EA4DCBF0@DM3PR1001MB1055.namprd10.prod.outlook.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
work_mem = 5MB
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2016-11-15 22:35:03 | Re: Performance decrease after upgrade to 9.6.1 |
| Previous Message | Henrik | 2016-11-15 17:23:38 | Re: Sql Query :: Any advice ? |