Thread: Performance decrease after upgrade to 9.6.1
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
Gabriela Serventi <gabrielaserventi@hotmail.com> writes:
> $ 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)
That's not a tremendously exciting benchmark case, for a number of
reasons:
* 100 sessions in a scale-factor-1 database are all going to be fighting
over updating the single row in the pgbench_branches table.
* 100 sessions driven by a single pgbench thread are probably going to be
bottlenecked by that thread, not by the server.
* 100 sessions on a machine with only 2 cores is going to be all about
process-swap contention anyhow.
My first thought about why the difference from 8.4 to 9.6 is that pgbench
has grown a lot more measurement apparatus since then (for example, the
transaction latency numbers, which weren't there at all in 8.4). You
might try testing 9.6 server with 8.4 pgbench and vice versa to tease out
how much of this is actually on pgbench changes not the server. But in
the end, what you're measuring here is mostly contention, and you'd need
to alter the test parameters to make it not so. The "Good Practices"
section at the bottom of the pgbench reference page has some tips about
that.
regards, tom lane
Hi Tom!
Thanks for the answer.
This is just one of the benchmark that we run, we test with fewer clients and much more time, but you're right about de scale-factor, we didn't realize about that.
We are going to test using your recomendations.
Thanks!
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviado: martes, 15 de noviembre de 2016 19:35:03
Para: Gabriela Serventi
Cc: pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] Performance decrease after upgrade to 9.6.1
Enviado: martes, 15 de noviembre de 2016 19:35:03
Para: Gabriela Serventi
Cc: pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] Performance decrease after upgrade to 9.6.1
Gabriela Serventi <gabrielaserventi@hotmail.com> writes:
> $ 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)
That's not a tremendously exciting benchmark case, for a number of
reasons:
* 100 sessions in a scale-factor-1 database are all going to be fighting
over updating the single row in the pgbench_branches table.
* 100 sessions driven by a single pgbench thread are probably going to be
bottlenecked by that thread, not by the server.
* 100 sessions on a machine with only 2 cores is going to be all about
process-swap contention anyhow.
My first thought about why the difference from 8.4 to 9.6 is that pgbench
has grown a lot more measurement apparatus since then (for example, the
transaction latency numbers, which weren't there at all in 8.4). You
might try testing 9.6 server with 8.4 pgbench and vice versa to tease out
how much of this is actually on pgbench changes not the server. But in
the end, what you're measuring here is mostly contention, and you'd need
to alter the test parameters to make it not so. The "Good Practices"
section at the bottom of the pgbench reference page has some tips about
that.
regards, tom lane
> $ 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)
That's not a tremendously exciting benchmark case, for a number of
reasons:
* 100 sessions in a scale-factor-1 database are all going to be fighting
over updating the single row in the pgbench_branches table.
* 100 sessions driven by a single pgbench thread are probably going to be
bottlenecked by that thread, not by the server.
* 100 sessions on a machine with only 2 cores is going to be all about
process-swap contention anyhow.
My first thought about why the difference from 8.4 to 9.6 is that pgbench
has grown a lot more measurement apparatus since then (for example, the
transaction latency numbers, which weren't there at all in 8.4). You
might try testing 9.6 server with 8.4 pgbench and vice versa to tease out
how much of this is actually on pgbench changes not the server. But in
the end, what you're measuring here is mostly contention, and you'd need
to alter the test parameters to make it not so. The "Good Practices"
section at the bottom of the pgbench reference page has some tips about
that.
regards, tom lane