Re: PG 9.1 much slower than 8.2 ? - Mailing list pgsql-novice
From | Marc Richter |
---|---|
Subject | Re: PG 9.1 much slower than 8.2 ? |
Date | |
Msg-id | 53FEE7C4.4000806@marc-richter.info Whole thread Raw |
In response to | Re: PG 9.1 much slower than 8.2 ? (Keith <keith@keithf4.com>) |
Responses |
Re: PG 9.1 much slower than 8.2 ?
|
List | pgsql-novice |
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@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@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@marc-richter.info > <mailto:mail@marc-richter.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@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 <tel:26.08.2014%2017>:42, schrieb Keith: > > > > > On Tue, Aug 26, 2014 at 11:10 AM, Marc Richter > <mail@marc-richter.info <mailto:mail@marc-richter.info> > <mailto: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> > <mailto: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> > > <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@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> > >
pgsql-novice by date: