Re: PG 9.1 much slower than 8.2 ? - Mailing list pgsql-novice

Hi everyone,

as Merlin suggested, I've spend the last days with bringing PostgreSQL
versions 8.2.5 and 9.3.5 on the same hardware and re-ran my tests with this.

The Hardware-Specs are already told in the initial mail, quoted below
(under "PG Version 9.1.13"). Both are using the SSD-Raid for storing
their data.
Version 8.2.5 was compiled by hand (using "./configure
--prefix=/usr/local --with-openssl --enable-thread-safety"), version
9.3.5 was taken from PGDG Repo (https://wiki.postgresql.org/wiki/Apt).
PostgreSQL 8.2.5 binds to Port 5433, 9.3.5 binds to the default of 5432.

I've used a dump of one of our production sites, which results in ~12 GB
of storage used in PostgreSQL Data Directory.

Here's where the first sign of low speed of 9.3.5 shows up:

These are the lines I used to insert my Dump into both Postgres DBs,
using the "matching" version of psql of the two versions. I ran these
one after another to not interfere each other in performance. This is
dedicated, yet unproductive labor-hardware, so there is nothing else
that may interfere:

sync ; sleep 10 ; time zcat /usr/src/db.gz | \
/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433
[...]
real    37m57.023s
user    7m9.683s
sys     1m15.613s

sync ; sleep 10 ; time zcat /usr/src/db.gz | \
/usr/bin/psql -U postgres -h 127.0.0.1 -p 5432
[...]
real    80m57.667s
user    89m45.597s
sys     1m57.479s

So, inserting the Dump takes more than twice the time in 9.3 than it
takes to do the same in 8.2 .

After this, I issued "SELECT *" on a table, containing 360881 rows in
both versions and took the time for this. I did this with the following
"for", three times each:

for x in 1 2 3 ; do
   sync
   sleep 10
   time /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \
   "SELECT * FROM billing_events;" db | wc -l
done

This is the "for" for 8.2; in 9.3 I used Port 5432 instead of 5433 and
/usr/bin/psql instead of /usr/local/bin/psql.

These are the results for 8.2:
360881
real    0m6.044s
user    0m4.428s
sys     0m0.692s

360881
real    0m5.916s
user    0m4.476s
sys     0m0.596s

360881
real    0m6.023s
user    0m4.520s
sys     0m0.656s

These are the results for 9.3:
360881
real    0m12.885s
user    0m9.741s
sys     0m0.652s

360881
real    0m12.679s
user    0m9.613s
sys     0m0.724s

360881
real    0m12.717s
user    0m9.749s
sys     0m0.616s

As you can see: PostgreSQL 9.3 takes round about the double amount of
time to return these results than 8.2 needs to; quite the same
dimensions like the initial Insert does.

The config is like the following:

PG 8.2.5:
listen_addresses = '*'
port = 5433
max_connections = 512
shared_buffers = 400MB
temp_buffers = 8MB
work_mem = 256MB
maintenance_work_mem = 256MB
max_fsm_pages = 204800
checkpoint_segments = 16
effective_cache_size = 6GB
log_destination = 'stderr'
redirect_stderr = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
syslog_ident = 'postgres'
client_min_messages = warning
log_min_messages = notice
log_min_error_statement = info
log_line_prefix = '%m %r %u '
log_statement = 'mod'
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
datestyle = 'iso, dmy'
lc_messages = 'de_DE.UTF-8'
lc_monetary = 'de_DE.UTF-8'
lc_numeric = 'de_DE.UTF-8'
lc_time = 'de_DE.UTF-8'
regex_flavor = advanced

PG 9.3.5
data_directory = '/var/lib/postgresql/9.3/main'
hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.3-main.pid'
listen_addresses = '*'
port = 5432
max_connections = 512
unix_socket_directories = '/var/run/postgresql'
ssl = true
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 4GB
temp_buffers = 8MB
work_mem = 256MB
maintenance_work_mem = 1GB
checkpoint_segments = 16
effective_cache_size = 24GB
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
client_min_messages = warning
log_min_messages = notice
log_min_error_statement = info
log_line_prefix = '%m %r %u '
log_statement = 'mod'
log_timezone = 'localtime'
datestyle = 'iso, dmy'
timezone = 'localtime'
lc_messages = 'de_DE.UTF-8'
lc_monetary = 'de_DE.UTF-8'
lc_numeric = 'de_DE.UTF-8'
lc_time = 'de_DE.UTF-8'
default_text_search_config = 'pg_catalog.german'

I hope I measured valid things and delivered all information properly
for everyone to comprehend.

Can anybody explain what else to try or why recent Postgres is slower
than quite exactly 7 years old 8.2.5 Release (Release Date: 2007-09-17)
this noticeable?

Best regards,
Marc

Am 26.08.2014 21:57, schrieb Merlin Moncure:
> On Tue, Aug 26, 2014 at 10:10 AM, Marc Richter <mail@marc-richter.info> wrote:
>> Hi everyone,
>>
>> I'm in the process of migrating a really old PostgreSQL DB from 8.2.5 to a
>> (more) recent PostgreSQL 9.1. I know that 9.1 is somewhat old already, too,
>> but since we are stuck to Debian stable and don't want to start using
>> self-compiled software and this is the version which is included in Debian
>> stable currently, this is the version of choice.
>>
>> I've managed to create a dump of the database from 8.2.5 and inserting it
>> into 9.1.13 successfully, thanks to the help of this list ("Upgrading from
>> PG 8.2.5 to 9.1.13" - Thread). So I gave the result to another department to
>> make their compatibility- and overall-tests on it.
>> They did not come up with incompatibilities, but with a performance-related
>> issue:
>>
>> When we do a "SELECT *" on a table with 355332 rows in it without using an
>> index or limit or such, this takes round about 10.5 seconds on the
>> PostgreSQL 8.2.5 host and 12.2 seconds on the PostgreSQL 9.1.13 host. Both
>> servers are using the same database.
>>
>> I know, this seems like near to nothing, but the hardware of the 9.1.13 host
>> is way more recent than the one of the 8.2.5 PostgreSQL, too:
>>
>> PG Version 8.2.5:
>> * CPU:  Intel Xeon CPU E5506 (4-Core 2,13 GHz)
>> * RAM:  4 GB (2x2GB) DDR3 1066
>> * Storage:
>> System, SWAP und PostgreSQL Data:       RAID1 - ST3500320NS
>>
>> PG Version 9.1.13:
>> * CPU:  AMD Opteron 4334 (6 Core 3,1 GHz)
>> * RAM:  32 GB (4x8GB) DDR3 1600
>> * Storage:
>> System + SWAP:          RAID1 - ST1000DM003-1CH1
>> PostgreSQL Data:        RAID1 - SD6SB1M2 (SSD)
>>
>> I know that PostgreSQL has little chance to optimize a query like this, when
>> no logic and no index is used to lookup a result, but taking this into
>> account, we would have expected that issuing the same, bad query on old
>> hardware and newer hardware once, should deliver results on the better/newer
>> hardware a lot faster than on the older one. Instead, we experience the
>> opposite.
>
> very possibly you are measuring hardware differences or something else
> not related to the database itself.  do isolate that, fire up 8.2.5 on
> the same server and run queries side by side.  Also on both sides be
> sure to run the test several times (say, 10) and take the median
> speed.  Better yet, use pgbench; feel free to supplement the stock
> tpc-b with custom test of your choosing (even if select * from table).
>
> merlin
>
>