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

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