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
