Re: PG 9.1 much slower than 8.2 ?

Check the last response here

http://askubuntu.com/questions/392214/postgresql-installation-on-ubuntu-12-04

You might have two versions of libpq installed and they're conflicting.

On Thu, Aug 28, 2014 at 4:26 AM, Marc Richter <mail(at)marc-richter(dot)info>
wrote:

> This is not what I'm doing here: I stoped PostgreSQL 9.1, uninstalled it
> and removed it's data folder at /var/lib/postgresql/9.1 completely.
> Now it is completely empty.
>
> Also, just trying to run "/usr/lib/postgresql/9.3/bin/pg_ctl" without any
> parameters normally prints a usage overview like the following:
>
> root(at)prod-cl3:/etc/postgresql# /usr/lib/postgresql/9.1/bin/pg_ctl
> pg_ctl: cannot be run as root
> Please log in (using, e.g., "su") as the (unprivileged) user that will
> own the server process.
> root(at)prod-cl3:/etc/postgresql#
>
> The data stor isn't involved in this yet. instead I get this "symbol
> lookup error: undefined symbol: PQping".
>
> I tried to purge all packages from the PGDG Repo and tried to reinstall
> 9.1 from Debian repo. Now I get the same issue with these Packages, too.
>
> *sigh* ... I'm taking the "Restart from scratch" - road now, focusing in
> the initial performance-issue this time :/
>
> Am 27.08.2014 17:24, schrieb Keith:
>
>> You cannot use your old 9.1 cluster with 9.3. You either have to redo
>> your dump and restore using a newly initialized cluster or perform a
>> pg_upgrade on the 9.1 cluster. I'd recommend going with the dump and
>> restore since it's much easier for those new to Postgres.
>>
>>
>> On Wed, Aug 27, 2014 at 11:18 AM, Marc Richter <mail(at)marc-richter(dot)info
>> <mailto:mail(at)marc-richter(dot)info>> wrote:
>>
>> 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(at)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(at)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(at)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 <tel:26.08.2014%2017>:42, schrieb Keith:
>>
>>
>>
>>
>>
>> On Tue, Aug 26, 2014 at 11:10 AM, Marc Richter
>> <mail(at)marc-richter(dot)info <mailto:mail(at)marc-richter(dot)info>
>> <mailto:mail(at)marc-richter(dot)info
>>
>> <mailto:mail(at)marc-richter(dot)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(at)postgresql(dot)org <mailto:pgsql-novice(at)postgresql(dot)org>
>> <mailto:pgsql-novice(at)__postgresql(dot)org
>>
>> <mailto:pgsql-novice(at)postgresql(dot)org>>)
>>
>> To make changes to your subscription:
>> http://www.postgresql.org/____mailpref/pgsql-novice
>> <http://www.postgresql.org/__mailpref/pgsql-novice>
>>
>>
>> <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
>> <https://wiki.postgresql.org/wiki/Apt>
>>
>>
>>
>> --
>>
>> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org
>> <mailto:pgsql-novice(at)postgresql(dot)org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/__mailpref/pgsql-novice
>> <http://www.postgresql.org/mailpref/pgsql-novice>
>>
>>
>>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>