Re: [PERFORM] Performance decrease after upgrade to 9.6.1
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 <t...@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 Gabriela Serventi <gabrielaserve...@hotmail.com> writes: > $ pgbench -l -c 100 -T 30 pgbench > starting vacuum...end. > transaction type: > 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
Re: [PERFORM] Performance decrease after upgrade to 9.6.1
Gabriela Serventiwrites: > $ pgbench -l -c 100 -T 30 pgbench > starting vacuum...end. > transaction type: > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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: 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