Thread: PG 9.1 much slower than 8.2 ?
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
On Tue, Aug 26, 2014 at 11: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.
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)
To make changes to your subscription:
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
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
Marc Richter schrieb am 26.08.2014 um 17:10: > 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. Can you share the output of explain analyze for both servers? (for 9.1 maybe even "explain (analyze true, verbose true, buffers true) select ...") Also: try to run a "vacuum full" on the 9.1 database - just to make sure Thomas
On Wed, Aug 27, 2014 at 2:55 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Marc Richter schrieb am 26.08.2014 um 17:10:> I've managed to create a dump of the database from 8.2.5 andCan you share the output of explain analyze for both servers?
> 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.
(for 9.1 maybe even "explain (analyze true, verbose true, buffers true) select ...")
Also: try to run a "vacuum full" on the 9.1 database - just to make sure
Thomas
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Actually, what may be more important than a vacuum full would be a full database analyze.
Just run "ANALYZE" while logged into your database via psql. With no tables given to the command, it should just analyze the whole thing. This should update the planner statistics which are probably empty after a full dump/restore.
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
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> 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:42, schrieb Keith:hba_file = '/etc/postgresql/9.1/main/pg___hba.conf'<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'
ident_file = '/etc/postgresql/9.1/main/pg___ident.conf'
external_pid_file = '/var/run/postgresql/9.1-main.__pid'<mailto:pgsql-novice@postgresql.org>)
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.orghttp://www.postgresql.org/__mailpref/pgsql-novice
To make changes to your subscription:
<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
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> > >
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.http://askubuntu.com/questions/392214/postgresql-installation-on-ubuntu-12-04
On Thu, Aug 28, 2014 at 4:26 AM, Marc Richter <mail@marc-richter.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@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:Am 26.08.2014 17 <tel:26.08.2014%2017>:42, schrieb Keith:
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<mailto:mail@marc-richter.info
On Tue, Aug 26, 2014 at 11:10 AM, Marc Richter
<mail@marc-richter.info <mailto:mail@marc-richter.info>hba_file = '/etc/postgresql/9.1/main/pg_____hba.conf'
<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'
ident_file = '/etc/postgresql/9.1/main/pg_____ident.conf'
external_pid_file = '/var/run/postgresql/9.1-main.____pid'<mailto:pgsql-novice@__postgresql.org http://www.postgresql.org/____mailpref/pgsql-novice
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>
<http://www.postgresql.org/__mailpref/pgsql-novice>https://wiki.postgresql.org/__wiki/Apt
<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
<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>
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 > >
Hi Thomas and Keith, After I have re-tested the performance after a plain insert of the dump without any vacuuming or reindexing, I just have executed the following on both PostgreSQL versions. I know, REINDEXING after a VACUUM FULL is only suggested with pre-9.0 versions, but I haven't heard of any harm doing it with >=9.0 versions, either, so I executed them there as well: a) VACUUM FULL: for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db -c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' | expand | sed 's# \+##g'); do /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "VACUUM FULL VERBOSE ${table};" db done b) REINDEX TABLE: for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db -c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' | expand | sed 's# \+##g'); do /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX TABLE ${table};" db done c) REINDEX DATABASE: /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX DATABASE db;" db I did all these commands on the 9.3 Postgres as well by replacing Port 5433 with 5432 and /usr/local/bin/psql with /usr/bin/psql . After this, I re-issued the test to selecting a 360881 rowed table again without getting different results: 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 Again, I used Port 5432 instead of 5433 and /usr/bin/psql instead of /usr/local/bin/psql for testing the 9.3 Postgres: 8.2: 360881 real 0m5.996s user 0m4.448s sys 0m0.724s 360881 real 0m6.023s user 0m4.520s sys 0m0.664s 360881 real 0m6.077s user 0m4.580s sys 0m0.664s 9.3: 360881 real 0m12.835s user 0m9.737s sys 0m0.708s 360881 real 0m12.689s user 0m9.685s sys 0m0.652s 360881 real 0m12.700s user 0m9.649s sys 0m0.700s After this, I ran "ANALYSE;" while connected to the DB "db" without any further arguments, as Keith suggested. It echoed nothing but "ANALYZE" after a few seconds on both psql shells. After this, I ran the "SELECT *" again, identically with to what is described above. The result is still the same: 9.3 needs twice the time of 8.2 to return the results. As you asked for, here comes the "EXPLAIN ANALYSE" output for 8.2: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Seq Scan on billing_events (cost=0.00..16098.40 rows=360940 width=316) (actual time=0.015..84.507 rows=360877 loops=1) Total runtime: 114.922 ms (2 rows) ... followed by 9.3 output for "EXPLAIN ANALYSE": QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Seq Scan on billing_events (cost=0.00..15409.77 rows=360877 width=302) (actual time=0.035..97.698 rows=360877 loops=1) Total runtime: 128.252 ms (2 Zeilen) ... followed by 9.3 output for "EXPLAIN (analyze true, verbose true, buffers true) select ...": QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.billing_events (cost=0.00..15409.77 rows=360877 width=302) (actual time=0.033..96.809 rows=360877 loops=1) Output: id, callid, name, type, callingnumber, callednumber, translatednumber, inserted, eventstart, duration, freeofchargeflag, eventdata, envoxid, cpc, taskid Buffers: shared hit=11801 Total runtime: 130.506 ms (4 Zeilen) These don't me tell anything. Do they help you understanding this issue? Best regards, Marc Am 27.08.2014 17:00, schrieb Keith: > > > > On Wed, Aug 27, 2014 at 2:55 AM, Thomas Kellerer <spam_eater@gmx.net > <mailto:spam_eater@gmx.net>> wrote: > > Marc Richter schrieb am 26.08.2014 um 17:10: > > 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. > > Can you share the output of explain analyze for both servers? > > (for 9.1 maybe even "explain (analyze true, verbose true, buffers > true) select ...") > > Also: try to run a "vacuum full" on the 9.1 database - just to make sure > > Thomas > > > > -- > 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 > > > Actually, what may be more important than a vacuum full would be a full > database analyze. > Just run "ANALYZE" while logged into your database via psql. With no > tables given to the command, it should just analyze the whole thing. > This should update the planner statistics which are probably empty after > a full dump/restore.
> After this, I ran "ANALYSE;" while connected to the DB "db" without any further arguments, as Keith suggested. > It echoed nothing but "ANALYZE" after a few seconds on both psql shells. > After this, I ran the "SELECT *" again, identically with to what is described above. This statement: > The result is still the same: 9.3 needs twice the time of 8.2 to return the results. doesn't match the results of explain analyze: > "EXPLAIN ANALYSE" output for 8.2: > Total runtime: 114.922 ms vs. > ... followed by 9.3 output for "EXPLAIN ANALYSE": > Total runtime: 128.252 ms So it took 114ms on 8.2 and 128ms on 9.3. That's hardly "twice as long". My naive interpretation of that (not really knowing Linux) would be that the "time" command adds additional overhead that. One thing I also noticed: the 8.2 psql seems to be in an english environment (because of the "(2 rows)" feedback), whereas 9.3 seems to be a germanenvironment (because of the "(2 Zeilen)" psql feedback). I wonder if different locales can make a difference - althoughI there is no string comparison involved in your query. Thomas
Hey Thomas, thank you for answering. You are right: Comparing values of what Postgres tells about it's performance isn't worth noticing really. But the Linux command "time" does nothing else than measuring how long the command executed takes to complete; it is quite the same as hiting ENTER and a stop watche's button at the same time, just more accurate since human reaction time isn't sophisticating results. Also, since "time" is used on both, PostgreSQL 8.2 and 9.3 commands, it is hardly the reason for the longer execution times. To further prove that, I just ran tests for both PostgreSQL versions using "time" and the same psql - client (the one of 9.3), to make sure minimalistic differences in the output of the two client versions doesn't confuse time measurement. Also, I remove "wc -l" from command pipe, which just count the lines from the output of the psql client and redirect the output to /dev/null to make sure this program doesn't affect the measurement: for x in 1 2 3 ; do sync sleep 2 time /usr/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \ "SELECT * FROM billing_events;" db >/dev/null done Postgres 8.2: real 0m10.086s user 0m8.601s sys 0m0.388s real 0m10.116s user 0m8.625s sys 0m0.388s real 0m10.030s user 0m8.513s sys 0m0.416s Postgres 9.3: real 0m12.600s user 0m9.549s sys 0m0.428s real 0m12.552s user 0m9.569s sys 0m0.380s real 0m12.614s user 0m9.601s sys 0m0.392s Well, this alone is quite odd: It seems as if the psql client shiped with Postgres 9.3 is slowing down the response of Postgres 8.2 server by 4 seconds; which is 66,67 percent (!) slower than 6 seconds. But to remove the possible slowdown "time" might bring in, I remove this command as well and run "date" right before and after the psql command instead, which doesn't affect psql at all, but only prints the current date and time: for x in 1 2 3 ; do sync sleep 2 date /usr/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \ "SELECT * FROM billing_events;" db >/dev/null date done Postgres 8.2: Do 4. Sep 15:03:40 CEST 2014 Do 4. Sep 15:03:50 CEST 2014 Do 4. Sep 15:03:52 CEST 2014 Do 4. Sep 15:04:02 CEST 2014 Do 4. Sep 15:04:04 CEST 2014 Do 4. Sep 15:04:14 CEST 2014 Postgres 9.3: Do 4. Sep 15:05:37 CEST 2014 Do 4. Sep 15:05:49 CEST 2014 Do 4. Sep 15:05:51 CEST 2014 Do 4. Sep 15:06:04 CEST 2014 Do 4. Sep 15:06:06 CEST 2014 Do 4. Sep 15:06:18 CEST 2014 As you can see from this measure, without "time" it takes psql 10 seconds to read and print the values, too for Postgres 8.2 and 12 seconds with Postgres 9.3. So, I come to the result: 1) The majority of the issue may consist in psql client and not in PostgreSQL Server, since the newer psql client delivers the results of a PostgreSQL 8.2 server a lot slower than the 8.2 client. 2) There is still a difference of ~2 seconds between the different server versions, which is 20 percent slower than older PostgreSQL. Thus, the issue remains, but involves the psql client, additionally. You also mentioned the german locale in PostgreSQL 9.3 response. I looked at the result, psql 9.3 prints from both server versions and both are german. So, not the Server seems to print the result in german, but the client does. Nevertheless, I changed the following settings: In PostgreSQL 9.3's postgresql.conf: from: 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' to: lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8' default_text_search_config = 'pg_catalog.english' After I have restarted PostgreSQL 9.3, the output of psql was still german for both servers. To have psql print it's output in english, I had to export the environment variable LANG to en_US.UTF-8 . Having these steps done, I re-run my test and it doesn't change anything to the time required. Best regards, Marc Am 04.09.2014 13:35, schrieb Thomas Kellerer: >> After this, I ran "ANALYSE;" while connected to the DB "db" without any further arguments, as Keith suggested. >> It echoed nothing but "ANALYZE" after a few seconds on both psql shells. >> After this, I ran the "SELECT *" again, identically with to what is described above. > > This statement: > >> The result is still the same: 9.3 needs twice the time of 8.2 to return the results. > > doesn't match the results of explain analyze: > >> "EXPLAIN ANALYSE" output for 8.2: >> Total runtime: 114.922 ms > > vs. > >> ... followed by 9.3 output for "EXPLAIN ANALYSE": >> Total runtime: 128.252 ms > > So it took 114ms on 8.2 and 128ms on 9.3. That's hardly "twice as long". > > My naive interpretation of that (not really knowing Linux) would be that the "time" command adds additional overhead that. > > One thing I also noticed: > > the 8.2 psql seems to be in an english environment (because of the "(2 rows)" feedback), whereas 9.3 seems to be a germanenvironment (because of the "(2 Zeilen)" psql feedback). I wonder if different locales can make a difference - althoughI there is no string comparison involved in your query. > > Thomas > > > > >
Marc Richter schrieb am 04.09.2014 um 15:31: > Well, this alone is quite odd: It seems as if the psql client shiped with Postgres 9.3 is slowing down the response of > Postgres 8.2 server by 4 seconds; which is 66,67 percent (!) slower than 6 seconds. This could also mean opening the connection takes longer in 9.3 than in 8.2 > But to remove the possible slowdown "time" might bring in, I remove this command as well > and run "date" right before and after the psql command instead, which doesn't affect psql at all, but only prints the currentdate and time: > > 1) The majority of the issue may consist in psql client and not in PostgreSQL Server, > since the newer psql client delivers the results of a PostgreSQL 8.2 server a lot slower than the 8.2 client. > > 2) There is still a difference of ~2 seconds between the different server versions, which is 20 percent slower than olderPostgreSQL. I don't think it's the "server versions" that are different. It's the _invocation_ of the psql client that is different, and that is hardly a realistic performance test. In reality any large scale application will use a connection pool which would make your test unrealistic as well (at leastin my eyes)
Hi Gilles, somehow your answer missed the list; I hope it is OK that I'm inserting it in my reply. Indeed, setting "ssl = false" in postgresql.conf of PostgreSQL 9.3 leads to an improvement. I have retried switching SSL on and off several times and it leads to psql delivering the result 3 seconds faster (9.5 seconds instead of 12.5). This is a way better result, and it even beats the speed of PostgreSQL 8.2 when queried with the psql client of PostgreSQL 9.3 by 0.5 seconds. Thank you for pointing me to this! But there is still something I do not understand or would like to track, at least: I just tried the connection vice versa: I issued the test, using psql client of PostgreSQL 8.2 to access PostgreSQL server 9.3. And these results also beats PostgreSQL 8.2 server: While PostgreSQL 8.2 needs 5.8 to 6 seconds to answer the query, PostgreSQL 9.3 server needs 5.3 to 5.5 seconds. So this is great! What makes me still wonder and ask myself, if there is an error in the binary (bug?) or another configuration-issue is the slower delivering of results of both server versions, when the psql 9.3 client is used: While both server versions need less than 6 seconds to get the results using psql 8.2, they both need somewhat around 10 seconds to get the results when psql 9.3 client is used. Has anybody an idea to this? Best regards, Marc Am 04.09.2014 13:59, schrieb gparc@free.fr: > > Marc, > > just a shot in the dark.. > Can you repeat the test with ssl off in your 9.3 conf ? > > Gilles > - > Regards > > Selon Marc Richter <mail@marc-richter.info>: > > Hi Thomas and Keith, > > After I have re-tested the performance after a plain insert of the dump > without any vacuuming or reindexing, I just have executed the following > on both PostgreSQL versions. I know, REINDEXING after a VACUUM FULL is > only suggested with pre-9.0 versions, but I haven't heard of any harm > doing it with >=9.0 versions, either, so I executed them there as well: > > a) VACUUM FULL: > for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db > -c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' | > expand | sed 's# \+##g'); do > /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "VACUUM FULL > VERBOSE ${table};" db > done > > b) REINDEX TABLE: > for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db > -c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' | > expand | sed 's# \+##g'); do > /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX > TABLE ${table};" db > done > > c) REINDEX DATABASE: > /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX > DATABASE db;" db > > I did all these commands on the 9.3 Postgres as well by replacing Port > 5433 with 5432 and /usr/local/bin/psql with /usr/bin/psql . > > After this, I re-issued the test to selecting a 360881 rowed table again > without getting different results: > > 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 > > Again, I used Port 5432 instead of 5433 and /usr/bin/psql instead of > /usr/local/bin/psql for testing the 9.3 Postgres: > > 8.2: > 360881 > real 0m5.996s > user 0m4.448s > sys 0m0.724s > > 360881 > real 0m6.023s > user 0m4.520s > sys 0m0.664s > > 360881 > real 0m6.077s > user 0m4.580s > sys 0m0.664s > > > 9.3: > 360881 > real 0m12.835s > user 0m9.737s > sys 0m0.708s > > 360881 > real 0m12.689s > user 0m9.685s > sys 0m0.652s > > 360881 > real 0m12.700s > user 0m9.649s > sys 0m0.700s > > After this, I ran "ANALYSE;" while connected to the DB "db" without any > further arguments, as Keith suggested. It echoed nothing but "ANALYZE" > after a few seconds on both psql shells. > After this, I ran the "SELECT *" again, identically with to what is > described above. > The result is still the same: 9.3 needs twice the time of 8.2 to return > the results. > > As you asked for, here comes the "EXPLAIN ANALYSE" output for 8.2: > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------- > Seq Scan on billing_events (cost=0.00..16098.40 rows=360940 > width=316) (actual time=0.015..84.507 rows=360877 loops=1) > Total runtime: 114.922 ms > (2 rows) > > > ... followed by 9.3 output for "EXPLAIN ANALYSE": > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------- > Seq Scan on billing_events (cost=0.00..15409.77 rows=360877 > width=302) (actual time=0.035..97.698 rows=360877 loops=1) > Total runtime: 128.252 ms > (2 Zeilen) > > ... followed by 9.3 output for "EXPLAIN (analyze true, verbose true, > buffers true) select ...": > > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Seq Scan on public.billing_events (cost=0.00..15409.77 rows=360877 > width=302) (actual time=0.033..96.809 rows=360877 loops=1) > Output: id, callid, name, type, callingnumber, callednumber, > translatednumber, inserted, eventstart, duration, freeofchargeflag, > eventdata, envoxid, cpc, taskid > Buffers: shared hit=11801 > Total runtime: 130.506 ms > (4 Zeilen) > > These don't me tell anything. Do they help you understanding this issue? > > Best regards, > Marc > > Am 27.08.2014 17:00, schrieb Keith: > > > > > > > > On Wed, Aug 27, 2014 at 2:55 AM, Thomas Kellerer <spam_eater@gmx.net > > <mailto:spam_eater@gmx.net>> wrote: > > > > Marc Richter schrieb am 26.08.2014 um 17:10: > > > 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. > > > > Can you share the output of explain analyze for both servers? > > > > (for 9.1 maybe even "explain (analyze true, verbose true, buffers > > true) select ...") > > > > Also: try to run a "vacuum full" on the 9.1 database - just to make sure > > > > Thomas > > > > > > > > -- > > 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 > > > > > > Actually, what may be more important than a vacuum full would be a full > > database analyze. > > Just run "ANALYZE" while logged into your database via psql. With no > > tables given to the command, it should just analyze the whole thing. > > This should update the planner statistics which are probably empty after > > a full dump/restore. > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > > >