Re: PG 9.1 much slower than 8.2 ?

Hey Keith,

thanks for pointing me to this. I have removed the Debian postgres 9.1 packages, inserted the repo of PGDG and installed PostgreSQL 9.3 packages from there.

Now, what I get is this:

root@prod-cl4:/etc/postgresql/9.3/main# /etc/init.d/postgresql start
[....] Starting PostgreSQL 9.3 database server: main[....] Error: could not exec /usr/lib/postgresql/9.3/bin/pg_ctl /usr/lib/postgresql/9.3/bin/pg_ctl start -D /var/lib/postgresql/9.3/main -s -o -c config_fi[FAILetc/postgresql/9.3/main/postgresql.conf" : ... failed!
 failed!
root@prod-cl4:/etc/postgresql/9.3/main# /usr/lib/postgresql/9.3/bin/pg_ctl
/usr/lib/postgresql/9.3/bin/pg_ctl: symbol lookup error: /usr/lib/postgresql/9.3/bin/pg_ctl: undefined symbol: PQping
root@prod-cl4:/etc/postgresql/9.3/main#

I cannot find something what seems related using Google ... what am I doing wrong here?

Best regards,
Marc

Am 26.08.2014 17:42, schrieb Keith:

On Tue, Aug 26, 2014 at 11:10 AM, Marc Richter <mail@marc-richter.info

<mailto: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.

    Are we missing a "OMG - how can you even start a postgres without
    doing .... first???" step here? What else can be the reason for this?

    These are the postgres.conf - files in use:

     >>>>>>> PostgreSQL 8.2.5:

    listen_addresses = '*'
    max_connections = 100
    shared_buffers = 24MB
    max_fsm_pages = 153600
    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'

     >>>>>>> PostgreSQL 9.1.13:

    data_directory = '/var/lib/postgresql/9.1/main'

    hba_file = '/etc/postgresql/9.1/main/pg___hba.conf'
    ident_file = '/etc/postgresql/9.1/main/pg___ident.conf'
    external_pid_file = '/var/run/postgresql/9.1-main.__pid'

    listen_addresses = '*'
    port = 5432
    max_connections = 512
    unix_socket_directory = '/var/run/postgresql'
    ssl = true
    shared_buffers = 2048MB
    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'
    datestyle = 'iso, mdy'
    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.english'

    Thanks for reading and your help in advance.

    Best regards,
    Marc


    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org

    <mailto:pgsql-novice@postgresql.org>)

    To make changes to your subscription:

    http://www.postgresql.org/__mailpref/pgsql-novice

    <http://www.postgresql.org/mailpref/pgsql-novice>



I don't have a comment on the query performance at this time, but I just
wanted to point out that there is an apt repository maintained by the
PostgreSQL Global Development Group for debian based distros that
contains more recent packages of postgres

https://wiki.postgresql.org/wiki/Apt

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice